Transposed data sets

apaine

Sometimes it is useful to convert columnar data of the form:<br />
Key1 Value1 Value2 Value3 Value4 etc... A 1 2 3 4 B 5 6 7 8 <br />
<br />
...to row data in the form<br />
Key1 Category Value A Value1 1 A Value2 2 A Value3 3 A Value4 4 B Value1 5 B Value2 6 B Value3 7 B Value4 8 etc... <br />
<br />
This can be particularly useful for charts and crosstabs when the data is not in the format you need.<br />
<br />
It's also useful if you want to get the field names into data rows.<br />
<br />
The attached library contains helper components to achieve just this.<br />
<br />
Extend CachedDataSet in your report to select the data that needs transposing (don't worry that it shows an error in the library; this is because it is not associated with a data source until you use it). Make sure that the columns you want transposing come last and note how many columns there are that should be copied to each row. In the above example there is one fixed column, Key1, which is repeated in each transposed row.<br />
<br />
Extend TransposedDataSet in your report to get the transposed data. Transposed data is written to the Category and Value columns. The library defaults to one fixed output column but you can rename it in your subclass.<br />
<br />
If you have more than one fixed output column you should to add output columns to the data set for each of your fixed columns. Make sure these come in the same order as the CachedDataSet and are first in the list of output columns ahead of the Category and value fields. You should also override the Open script on your subclassed TransposedDataSet and update the number of fixed columns.