Transforming Data (Pivot ?)

[david_navigator 12]

I have some data that is stored in rows, but I need in columns e.g

 

Instance	Setting			Value
==========================================
1			Fuel Level		10
1			Oil OK			True
1			Water OK		False
1			Temperature		Hot
2			Fuel Level		5
2			Oil OK			True
2			Water OK		False
2			Temperature		Warm
3			Fuel Level		100
3			Oil OK			false
3			Water OK		true
3			Temperature		Cold

Fuel		Oil			Water		Temperature
Level		Ok			Ok
==================================================
10			True		False			Hot
5			True		False			Warm
100			False		True			Cold

 

So the instance field groups the data together and the Setting field describes the Columns and the Value field the data for that intersection.

Of course at design time I have no idea how big an instance might be. It could have as little as two settings or maybe as many as 20 (I doubt it would ever be larger than that)

 

The database I'm using has no support for Pivoting data. I could write the transformation in SQL, but that'd be horribly slow to execute. 

I need to be able to display the data to the end user in a grid format and so I thought a DevExpress Pivot grid might do the job, but that only seems to like numeric data at the intersection - unless I misunderstand how it works.

Are there any other solutions available, Google didn't really help (maybe I'm searching on the wrong terms) or is this something that's fairly easy to code (to run efficiently) ?

 

David

[limelect 48]

Are there only 4 rows per Instance? If so do not put it in 1 database.

Put in 4 databases and you do not need to use SQL. Read in sequence.

Once from each other since all 4 are the same size

Edited April 20, 2021 by limelect

[david_navigator 12]

15 minutes ago, limelect said:

Are there only 4 rows per Instance? If so do not put it in 1 database.

 

No this was a simplification.
There are also many data sets e.g the original sample shown is storing data about an engine, there maybe a similar dataset storing say electrical data.

There are [user defined] rows per instance and actually the number of rows can vary between datasets

 

As the table will be filtered by dataset, they're not really relevant to the solution.

 

Since I posted the original Question I have got further with DexExpress's Pivot Grid, so it maybe that it is the prefered solution.

 

Dataset     Instance	Setting			Value
==========================================
1            1			Fuel Level		10
1            1			Oil OK			True
1            1			Water OK		False
1            1			Temperature		Hot
1            2			Fuel Level		5
1            2			Oil OK			True
1            2			Water OK		False
1            2			Temperature		Warm
1            3			Fuel Level		100
1            3			Oil OK			false
1            3			Water OK		true
1            3			Temperature		Cold
2            1			Voltage A		240
2            1			Voltage B		380
2            1			Current A		10
2            1			Current B		30
2            1			Warning Light	On
2            1			Filter Colour	Red
2            2			Voltage A		440
2            2			Voltage B		120
2            2			Current A		180
2            2			Current B		30
2            2			Warning Light	On
2            2			Filter Colour	Blue

 

[limelect 48]

So? still dataset 1 as before.Dataset2 same.No?

are there more? different data? sims to be very symmetric

Change your way of viewing the data.

MORE DATASET??

 

Edited April 20, 2021 by limelect

[david_navigator 12]

The number settings in a dataset is determined by the user, not the developer.

The number of different datasets is also determined by the user.

 

As the developer I have to come up with a row -> column solution that will work for any dataset the user designs.

[limelect 48]

Well good luck. But thinking out of the box mite help

[Guest]

Fiddle with the DX PivotGrid some and then some and i cannot guarantee, but i think you should be able to produce the desired result.

It was originally designed only to show rows/column values where a "sum" could be done (only numbers).

Recently you can use Max/Min instead of only Sum (according to the ticketing) but the actual change (quite recent, iirc) has passed me by.

If you are having a discussion with DX support, do have them realize that you are skilled enough to inherit from their code.

[Tom Chamberlain 47]

Create/load a TClientDataSet (or TFDMemTable) on the fly every time the user picks a different DataSet by reading the distinct Settings values within the DataSet and creating columns for each Setting (replace spaces with underscores or something) and if you know the data type of the Setting even better.  Then load the data by reading the DataSet and dropping the data into the correct column by the Setting name, when the Instance changes start a new record in the TClientDataSet, repeat.  Then you can display the data any way you would like.  You could do the same thing with a couple of dynamic arrays, one to hold the columns and another to hold the data if you don't need to know the data type in each column.

 

[Clément 148]

DxPivotGrid should give you the expected results. It's a matter of setting accordingly the rows , columns and values.
The trick is to transform the "Operational data" to "Pivotable data". Having "true" in pivot table is not meaningfull.. but display 75% Oil Ok will indicate something is wrong and your user will figure out what's ( or where it's) wrong by drilling down.

[Guest]

That is a solution, but if you succeed to have the Pivot handle it you will benefit from working end-user functionality like re-arranging the fields, filtering, exporting and a lot more.

Now i feel like a DevExpress salesperson. I'm out.

[david_navigator 12]

55 minutes ago, Dany Marmur said:

Fiddle with the DX PivotGrid some and then some and i cannot guarantee, but i think you should be able to produce the desired result.

It was originally designed only to show rows/column values where a "sum" could be done (only numbers).

Recently you can use Max/Min instead of only Sum (according to the ticketing) but the actual change (quite recent, iirc) has passed me by.

If you are having a discussion with DX support, do have them realize that you are skilled enough to inherit from their code.

 

Yes, got it working with the Pivot Grid. If I add some Custom Summary code, then it does what I want 🙂

 

procedure TForm50.cxDBPivotGrid1Field3CalculateCustomSummary(Sender:
    TcxPivotGridField; ASummary: TcxPivotGridCrossCellSummary);
begin
  with ASummary do
  begin
    if Records.Count >0 then
    begin
      Custom := GetValue(Records[0]);
    end
    else
      Custom := '';
  end;
end;

 

I was also hoping that the TcxPivotGridSummaryDataSet would create a dataset that reflects the grid e.g

 

Fuel		Oil			Water		Temperature
Level		Ok			Ok
==================================================
10			True		False			Hot
5			True		False			Warm
100			False		True			Cold

but is seems to just export the same dataset as the source of the Pivot grid. Will need to do some more playing with that.

 

i.e

 

Instance	Setting			Value
==========================================
1			Fuel Level		10
1			Oil OK			True
1			Water OK		False
1			Temperature		Hot

[Guest]

I am not 100% well and not at my workstation.

If i recall you are correct. But anyways, the "DrillDown" connection thingy is quite cool, end users can "pinpoint" the actual underlying record of a cell.

I have not implmented it in my "modern" projects, but did so back in 2010.

The "drilldown" was appreciated. It stands to reason that if you tweak the Pivot itself, you have to do some tweaking in the connector "classes".

Also consider using the "DX Printing System" vs the export library (excel). I have had to do a lot of tweaking for printing Pivots*.

 

And... IMHO - if you are at a late version wait for their "standalone" charting connectors. As things stand now it's a part of the QuantumGrid.

 

HTH,

 

/D

 

* Mainly cultural stuff as swedes do not appreciate all the shading and colorations and boxes and shadow on a printed paper.

To my end users it's completely idiotic that a printed paper looks different if you change the application skin.

To me too, actually.