pivoting a dataset after aggregation

Hello all, wondered if you could help me: 
 
I have some data which looks like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
F1 F2 F3
A1 n n n
A2 n n n
A3 n n n
A4 n n n
</pre>
 
I want to sum up the F's so I have a single number and then plot the F's on a bar chart as a single series but when I aggregate them I cannot seem to find a way to pivot them so I can plot the chart. I end up with the data looking like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
F1 F2 F3
SUM sn sn sn
</pre>
 
I'm using a data cube to aggregate the data. I want the F's to be the x axes label and the SUM to be the series but in this format I cannot make this happen I can only add the individual columns as separate series and the SUM as the x axes label. 
 
I really want the data like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SUM
F1 sn
F2 sn
F3 sn
</pre>
 
Any help gratefully received

Find more posts tagged with

Comments

deeebee

Is this not possible? It seems like a common thing to want to do.

mwilliams

Is the A1, A2, etc. a field in your dataSet? Or is that column of your data irrelevant to this example?

deeebee

Hey Michael,

In the example the A1,A2 etc just disappear because I want to aggregate the rows.

In the real data these are asset names. I want to get the totals and then display these totals as a single series on a bar chart. Hence the need to pivot the dataset.

Thanks

Richard

mwilliams

Are there a variable amount of "F" columns? Or will it always be a fixed value? Like 3 F columns in the above example? Let me know.

deeebee

F can be fixed because I can define the exact columns required.

mwilliams

Then, one option could simple be to define them as different series' in the chart. Each bar would have a different color and the legend would show which was which. The drawback to this is that they'd have a common x-axis value.

If this doesn't work, let me know your BIRT version and I'll show another option.

deeebee

I did think of that but in reality there are going to be more than 20 series in that case, and it makes the report unwieldy to format, I wouldn't be able to sort the dataset or do something like top n on it. So it's not really a workable solution unfortunately.

the birt version I am using seems to be 2.5.2

mwilliams

Take a look at this example. I create a string array in a hidden table that uses the original data. I then take this array and create a scripted dataSet to display the values how you want and to create the chart. Hope this helps.