Question about Gridify

[De-Novo-Research]

Hello,

If I do a gridify to create a pivot grid, I get the desired result. Similar to

if I pass the resulting dataItem to a BIChart.Data, the data graphs properly. I get one series for Angela, Claire, Johnny, Julia, Mike and the 'Year' column is the X axis.

But if I create a TChart and do chart.AddSeries(TChartData.From(gridifiedDataItem,chart1,TLineSeries) I only get one series. Stepping through the code, it seems that TChartData.From thinks there are only two dataItems and only one of them is numeric. I am presuming that the second dataItems is for a nested DataItem to hold the pivoted data. That also explains the multi-headers in the grid.

What is the easiest way to flatten this pivoted dataItem into a 'regular' asTable dataitem with no nested dataItems so I can easily just use a standard TChart. I have a lot of tools that already work with a TChart and I dont want to modify them to be able to handle a TBIChart.

[davidberneda]

I'll check what happens at TChartData.From, ideally it should work also with multiple nested-levels of sub-tables and pick the most inner level (the 5 person data, 5 series).

Meanwhile I've added a new small code to "flatten" a TDataItem structure into a simple one-level, but this process in the above picture will eliminate the "Happiness" and "Person" items, which is fine, but that names will be lost, just cosmetic.

TDataFlatten.Flatten method:
https://github.com/Steema/TeeBI/blob/master/sources/BI.Tools.pas

There is a new "Flatten" button at Gridify demo, right-top:

https://github.com/Steema/TeeBI/blob/master/demos/delphi/vcl/Grid/Gridify/Unit_Main_Gridify.pas

procedure TFromGridify.BFlattenClick(Sender: TObject);
begin
  // Remove sub-tables, leaving a flat Items structure
  TDataFlatten.Flatten(BIGrid2.Data);

  BIGrid2.RefreshData;
end;

[De-Novo-Research]

HI David,

The only problem I see here is a potential name collision. i.e. if the columns in the subtables that got flattened have the same name as some of the columns in the main table, there will be multiple columns with the same name. I suggest you pass through a "name prefix", which is the cocatenated name from all the parents.

I have provided the implementation below (untested). It has two advantages compared to Flatten. It checks for name collisions and also adds directly to the final root data item instead of recursively doing it at every level.

class procedure TDataFlatten.Flatten2(const AData: TDataItem);


  procedure InternalFlatten(ARoot,AItemBeingFlattened:TDataItem;const aNameCollisionPrefix:string);
  begin

    var t:=AItemBeingFlattened.Items.Count-1;

    if AItemBeingFlattened.AsTable then
    begin

      while t>=0 do
      begin
        InternalFlatten(ARoot,AItemBeingFlattened[t],aNameCollisionPrefix+'.'+AItemBeingFlattened.Name);
        Dec(t);
      end;

      if AItemBeingFlattened.Parent<>nil then
      begin

        if AItemBeingFlattened.Parent = ARoot then
          // need to remove this from the root, because the root will stay after the process
          // non-root tables will get destroyed, so no need to update their items array
          ARoot.Items.Delete(AItemBeingFlattened)
        else
          TDataItemsAccess(AItemBeingFlattened.Items).ClearArray; // they have already been added to the root

        AItemBeingFlattened.Free;
      end;
    end
    else
    begin
      // if its not a table, it just a single item. Although not sure why checking for the count outside the AsTable
      // node. I guess I am not sure what the term 'Table' means in this context
      if AItemBeingFlattened<>aRoot then
      begin
        if ARoot.Item[AItemBeingFlattened.Items[i].Name]<>nil then
          AItemBeingFlattened.Items[i].Name:=aNameCollisionPrefix+'.'+AItemBeingFlattened.Items[i].Name;

        ARoot.Items.Add(AItemBeingFlattened);

      end;
    end;

  end;


begin
  InternalFlatten(AData,AData,'');
end;

[davidberneda]

Thanks for this code, I'll gonna test it asap.

One thing I'd like to clarify, maybe here is not the appropiate place though, is that a TDataItem has 3 "modes" of operation:

1. As a simple column: Data1.AsTable:=False, Data1.Kind:=TDataKind.Int32
   Nothing more than an array of integers.

2. As a table: Data1.AsTable:=True, Data1.Items.Add(columns.....
   Typical grid, with the particularity that columns can also be tables (nested multi-table, like Gridify does)

3. As a container: Data1.AsTable:=False, Data1.Kind:=TDataKind.dkUnknown,
   Data1.Items.Add(..... )

This 3rd mode is like a "folder", you can put inside any dataitems of the 3 modes, including "folders" too.
This allows a single dataitem to be like an hierarchical tree, like the one displayed at the data manager, they are all dataitems, nothing else.

The trick is to make as much code as possible in the library to support the 3 modes. One and two are the important ones, but things start to be more useful supporting the container mode, for example at TBIComposer, you can have multiple charts, with multiple series and other controls, automatically created based on what structure has the dataitem you pass.

[davidberneda]

I've added a simple mechanism to avoid duplicate name collision:
551cede

Not by prefixing with the parent levels names yet.

[De-Novo-Research]

I get it, but I dont know if you need an extra Boolean, the structure of the data tells you everything you need to know.

It seems to me that right now

.AsTable := (self.Count>1)

if you feel these concepts are core, it could be an enum =(singleCol, table, container)

again, there should not be a setter on this enum, it should just look at the structure and see what the structure is. For example, I could now have a single column and set AsTable:=True. What does that mean compared to a dataItem with a single column with .AsTable=false. Would I want to process those differently?