nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Pie chart with segments from different fields

mogwai

I have a table with fields such as
Department
Infrastructure budget
Training budget
Comms budget
Consumables budget
h
I am trying to create a pie chart per department, wich shows these budgets as the segments of the pie, but the data for each segment is in a different field. I can't figure out how to do this. Changing the database table is not an option. Anyone who can help?

Find more posts tagged with

Comments

kclark

Would it be possible for your report to create two data sets and then join them? Then you could create a data cube based on the joined data and create your pie chart that way.

mogwai

I have all the data in one data set now. I'm not sure how splitting it and joining it will solve this. I considered a data cube, but I couldn't figure how to use this. I only know how to create rows/columns from data values in a cube, and not how to consolidate several fields/columns into one column. If I could manipulate the database I would use an append query, but I can't do that in BIRT, can I?
As a not very technical novice, I'm probably misunderstanding the data set join and cube concepts.

Clement Wong

If I'm understanding your original requirements correctly, you'll want to transpose your data. The quickest way is to push the transposing of the data down to your database server. You won't need to change the database table, just the query to get the data. It will depending on your database type and version whether it supports this feature. For example, here are samples for MySQL and SQL Server (<a class='bbc_url' href='http://www.artfulsoftware.com/infotree/qrytip.php?id=78'>click here</a>). 
 
Otherwise, you can do this in BIRT, and there is a DevShare entry @ <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/745-transposed-data-sets/'>http://www.birt-exchange.org/org/devshare/designing-birt-reports/745-transposed-data-sets/</a> which describes the technique.

mogwai

That is precisely what I want to do. Unfortunately the solution appears to require a development environment, which doesn't surprise me. I'm using BIRT RCP as an administrator and am not a Java developer, so I will have to find a solution outside of BIRT.
For anyone with an understanding of the BIRT development environment I'm sure this would be the solution.

Hans_vd

Hi, 
 
Can you rewrite your query like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT Department,
'Infrastructure' as budget_type
Infrastructure_budget as budget
FROM your_table
UNION ALL
SELECT Department,
'Training' as budget_type
Training_budget as budget
FROM your_table
UNION ALL
SELECT Department,
'Comms' as budget_type
Comms_budget as budget
FROM your_table
UNION ALL
SELECT Department,
'Consumables' as budget_type
Consumable_budget as budget
FROM your_table</pre>
 
With such a data set it should be possible to create the pie chart like you want it. 
 
Regards 
Hans

Hans_vd

Clement,

I'm sorry, I didn't see you were already pointing in that direction.

mogwai

Such I query I could handle, even with my limited knowledge. What I have ommitted to say is that the application is a black box to me, exporting .csv files as a result of a proprietary query building component. So all I have to work from is the .csv which does not allow sql querying.
The solution probably lies in data manipulation in Excel, unless someone has a different BIRT solution op their sleeve. Thanks for all your help and suggestions so far.

Hans_vd

Okay, maybe a bit overdone, but this should work: 
 
- add a dummy column (computed column, name it join_col) to your data set that always contains the value 1 
- create a dummy data set with two columns like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>join_col col_num
1 1
1 2
1 3
1 4</pre>
- create a joint data set between your two data sets a do a inner join on join_col -> this will result in a data set with 4 rows (= the number of columns you have in the csv) for each department) 
- add a computed column to the joint data set that has this expression (it's not real code, but you'll get the idea): 
<pre class='_prettyXprint _lang-auto _linenums:0'>if (col_num == 1) { infrastructure_budget; }
else if (col_num == 2 { training_budget; }
else ...</pre>
 
I think, now you can create the pie chart. 
I may not have explained in too many details, I don't have much time right now. If you have questions maybe I can get back to you in a couple of hours.

mogwai

This will work. Now my problem is that I don't know how to create the calculated column with 1, 2, 3, 4... (there are in fact 10 columns I want to transpose). I can sense we are very close to a clever solution.

Hans_vd

You will need indeed a data set with 10 rows then.
No need for it to be a calculated column though.
You can create a csv with the values in it. Or even better: a scripted data set.

mogwai

Not sure how to create a scripted data set, but creating the .csv is simple enough.
Almost there, but I'm failing with the expression builder syntax. This is what I have tried as a test:
if (row["Item_col"]==1) {row["HR"]}
esle if (row["Item_col"]==2) {row["Training"]}
And I get a syntax error. Can you help with some more detail. One day I will be an expert :rolleyes: .

mogwai

I'm sorry. As you can see, it is simply a matter of misspelling 'else'. You can stare at these things for hours and not see them. I'm well on my way to making this work. I love this forum and the help I get so promptly.