Exporting to XML directly of TPivotMap

[Dev]

Hi,

I need to do an in memory export to XML of a cube using a set of slices/map. In other words - I have a set of cubes and compatible slices and need to export the results to XML. I cannot use the TPivotGrid as the process is unattended and runs daily as a Windows Service - so I need to to use TPivotMap and I need to write out both dimension and measures in a column and row structure.

All the code samples point either to TPivotGrid which does not explain how to do this on TPivotMap. More directly - I need to build my own in memory+non visual "pivot grid" using the classes in the library (loading TPivotGrid as a hidden form is not an option - so dont suggest that pls).

Any guidance possible on how to do it?

Tx
Dev

[AlexZencovich]

You may try to do something with TPivotMap.RowCellCount/RowCells (Column) and TPivotMap.GetCells.
There will 1 serious problem you will meet - RowCellCount/CoulmnCellCounts returns number of rows/coulmns without measures (like it you have only 1 measure) and of course without views. So all measures+viewes packed in single map cell.

But if you will save it in XML you may avoid that problem I think, just put measures/views in embedded branch

[Dev]

Is it possible to provide a simple code sample to help me along?

[AlexZencovich]

something like

var
i,j : integer;
f : double;
rcell,ccell : ICell;
begin
for i := 0 to Map.RowcellCount - 1 do
begin
rcell := map.RowCells[i];
SaveToXML(rcell.caption); // put row header
for j := 0 to Map.ColumnCellCount - 1 do
ccell := map.CoulmnCells[i];
SaveToXML(ccell.caption); // put column header
Map.GetCells(rcell.Index,ccell.Index,0,mvtValue,f);
SaveToXML(f); // save cell value for measure 0;
end;
end;

[Dev]

I would like to dump the tree on which the cube slice is based. I am using the following code:

Code: Select all

 
procedure ExpandCell(var AXML : string; ACell : ICell);
  var
    C : integer;
    n : string;
  begin
    if (ACell.ChildCount > 0) then
    begin
      n := ACell.Childs[0].DimensionItem.Dimension.DisplayName;
      XMLOpen(AXML, n);
      for C := 0 to (ACell.ChildCount - 1) do
      begin
        XMLAppend(AXML, 'item', ACell.Childs[C].Caption);
        ExpandCell(AXML, ACell.Childs[C]);
      end;
      XMLClose(AXML, n);
    end;
  end;

//call ExpandCell Recursively
ExpandCell(xn_nodes, FPivotMap.TopRows);


I have three issues:
1. How can I get the values for the child items (e.g. using Cells or RowTotal - IOW - where do I get the row index from to pass)
2. It seems I get a duplication of child items at deep levels in the tree
3. I only need to read the Row Totals - How Do I get the Index to pass to TPivotMap.RowTotal

[Dev]

A working solution (it seems):

dtest.zip

cCubeX.zip

Code: Select all

function TXMLPivotCubeExport.Execute(AFileName: string): boolean;

  procedure ExpandCell(var AXML : string; ACell : ICell);
  var
    C : integer;
    dname : string; //dimension name
  begin

    if (ACell.ChildCount > 0) then
    begin

      dname := ACell.Childs[0].DimensionItem.Dimension.DisplayName;
      XMLOpen(AXML, 'dimension', 'name="'+dname+'"');
      for C := 0 to (ACell.ChildCount - 1) do      begin
        if (not ACell.Empty) then
        begin
          XMLAppend(AXML, 'node', ACell.Childs[C].Caption);
          XMLAppend(AXML, 'value', floattostr(FPivotMap.RowTotal[ACell.Childs[C].Position, 0, mvtValue]));
          ExpandCell(AXML, ACell.Childs[C]);
        end;
      end;
      XMLClose(AXML, 'dimension');
    end;
   
  end;

var
  C, R, Ri, M : integer;
  xn_cube, xn_nodes : string;
  xml : TNativeXML;
  value : double;
  rcell, ccell : ICell;
  rname, cname : string;
begin

  //row "row style" exports
  ExpandCell(xn_nodes, FPivotMap.TopRows);

  XMLAppend(xn_cube, 'parts', xn_nodes);
  XMLWrap(xn_cube, 'cube');

  try
    {validate xml and save}
    xml := TNativeXML.Create;
    xml.ReadFromString(xn_cube);
    xml.SaveToFile(AFileName);
  finally
    xml.free;
  end;

end;



Please check if this is acceptable/correct/functional/sane.

Issues:
1. The XML structure is not finalized so the result looks odd but is correct and gets the RowTotal values (will be doing full columns dims and rows dims + columnstotals if this works)
2. Some Childs are still added which should not
- in the the XML file in the attached sample - see the nodes at lines 47, 51 etc or see the cube and view files and compare to XML

Thanks in advance

[AlexZencovich]

I may suggest you use ICell.Level to determine where your proc is now (max level is RowDimCount - 1) and add 'Total' cell for dim. items which have ICell.ChildCount > 0

[Dev]

For now the export is simple = only row totals are needed and ICell.Position seems to work ok

FYI : Updated code with all measures totals:

Code: Select all

function TXMLPivotCubeExport.Execute: string;

  procedure ExpandCell(var AXML : string; ACell : ICell);
  var
    C, M : integer;
    dname : string; //dimension name
    value : double;
  begin
    if (ACell.ChildCount > 0) then
    begin
      dname := ACell.Childs[0].DimensionItem.Dimension.DisplayName;
      XMLOpen(AXML, 'dimension', 'name="'+ UpperCase(dname) +'"');
      for C := 0 to (ACell.ChildCount - 1) do
      begin
        if (not ACell.Empty) then
        begin
          {read the measures}
          XMLOpen(AXML, 'measures', 'name="'+ UpperCase(ACell.Childs[C].Caption) +'"');
          for M := 0 to (FPivotMap.MeasureCount - 1) do
          begin
            value := FPivotMap.RowTotal[ACell.Childs[C].Position, M, mvtValue];
            XMLAppend(AXML, 'measure', floattostr(value), 'name="'+ UpperCase(FPivotMap.Measures[M].DisplayName) +'"');
          end;
          ExpandCell(AXML, ACell.Childs[C]);
          XMLClose(AXML, 'measures');
        end;
      end;
      XMLClose(AXML, 'dimension');
    end;
  end;

var
  xn_cube, xn_nodes : string;
begin
  ExpandCell(xn_nodes, FPivotMap.TopRows);
  XMLAppend(xn_cube, 'dimensions', xn_nodes);
  XMLWrap(xn_cube, 'cube');
  result := xn_cube;
end;



I will check out the other approach -

Thanks for the help