Excel date problem

[vcastro]

hi i'm trying the trial of Pivotcube VCL, but when i export data to Excel, it changes day by month, and if day > 12 then it leave the cell value as text

in mexico we use dd/MM/yyyy for day format,
but usa and other countries use MM/dd/yyyy

so january 7, 2006 is 7/01/2006 but after export excel shows it like
date 1/7/2006

january 13 shows it correctly ( 13/1/2006 ) but as if it was text, not a date formated cel
------------------------------------------------------------------------
on another issue i have when i activate row percent and col percent on a measure, the total by row column shows me the exact same value for both col and row percent, i think row should by 100% always

thanks

[AlexZencovich]

Hello,

1. PivotCube (PivotGrid.ExportToExcel) does not recognize dimension items caption type (date, time, money etc). It always treated as string, no special format performing.

But you may to knwo, Excel try to recognize cell data and transform it to specifed format. On pity it not always work correcly because Excel may not have embedded format template like ShortDate fromat in your locale.
For example my Excel 2000 does not have(and do not recognize it as date) dd/mm/yyyy format at all, but only have
dd/mm/yy.

Also, you may need understand, when cube saved to stream(xlm) item captions also saved as strings, so if you will change systme locale after cube saved and restore it back, string caption of date's still unchanged. Key (date values) will restored correctly so sortings by key(date) value will work correctly.

2. Row total % works as expected. For row totals it show % value relatied to common cube total, so if you will remove all dimension from columns, you will see correct values anyway.

[vcastro]

thanks for fast response
found a workaround for #1, if anybody have the same problem
saving it like a .csv file, excel opens it correctly

Code: Select all

 
  PivotGrid1.ExportToStringGrid(true,true,true,stringgrid1);
  memo1.Clear;
  for i:=0 to StringGrid1.RowCount do
      memo1.lines.add(StringGrid1.Rows[i].CommaText);
  memo1.Lines.SaveToFile('.\open_me_with_excel.csv');


[AlexZencovich]

Intresting.....Same text is recognozable in csv and locked in OLE (or XLS - I try to export in XLS with same problem)

Thank you very much for workaround!