david_navigator 12 Posted April 19, 2021 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 Share this post Link to post
limelect 48 Posted April 20, 2021 (edited) 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 Share this post Link to post
david_navigator 12 Posted April 20, 2021 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 Share this post Link to post
limelect 48 Posted April 20, 2021 (edited) 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 Share this post Link to post
david_navigator 12 Posted April 20, 2021 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. Share this post Link to post
limelect 48 Posted April 20, 2021 Well good luck. But thinking out of the box mite help Share this post Link to post
Guest Posted April 20, 2021 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. Share this post Link to post
Tom Chamberlain 47 Posted April 20, 2021 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. 2 Share this post Link to post
Clément 148 Posted April 20, 2021 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. 1 Share this post Link to post
Guest Posted April 20, 2021 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. Share this post Link to post
david_navigator 12 Posted April 20, 2021 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 Share this post Link to post
Guest Posted April 20, 2021 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. Share this post Link to post