Jump to content

BIRT Developer Center Download


Photo
- - - - -

Pie chart with segments from different fields


  • Please log in to reply
12 replies to this topic

#1 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 28 January 2013 - 08:58 AM

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?

#2 kclark

kclark

    BIRT Guru

  • Administrators
  • 2085 posts

Posted 28 January 2013 - 10:34 AM

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.

Kristopher Clark

Actuate Corporation – The BIRT Company™

http://www.actuate.com | http://birtondemand.com

 

My Blog

Twitter

LinkedIn
Facebook
 


#3 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 28 January 2013 - 03:35 PM

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.

#4 Clement Wong

Clement Wong

    Senior Member

  • BIRT Experts
  • 1080 posts

Posted 28 January 2013 - 04:45 PM

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 (click here).

Otherwise, you can do this in BIRT, and there is a DevShare entry @ http://www.birt-exch...osed-data-sets/ which describes the technique.

Clement Wong
Principal Analytics Architect

OpenText Corporation
http://www.actuate.com | http://www.opentext.com


#5 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 29 January 2013 - 01:31 AM

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.

#6 Hans_vd

Hans_vd

    Junior Member

  • Members
  • 629 posts

Posted 29 January 2013 - 01:47 AM

Hi,

Can you rewrite your query like this:

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

With such a data set it should be possible to create the pie chart like you want it.

Regards
Hans

#7 Hans_vd

Hans_vd

    Junior Member

  • Members
  • 629 posts

Posted 29 January 2013 - 01:51 AM

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

#8 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 29 January 2013 - 02:01 AM

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.

#9 Hans_vd

Hans_vd

    Junior Member

  • Members
  • 629 posts

Posted 29 January 2013 - 03:58 AM

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:
join_col  col_num
         1        1
         1        2
         1        3 
         1        4
- 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):
if (col_num == 1) { infrastructure_budget; }
  else if (col_num == 2 { training_budget; }
  else ...

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.

#10 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 29 January 2013 - 04:22 AM

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.

#11 Hans_vd

Hans_vd

    Junior Member

  • Members
  • 629 posts

Posted 29 January 2013 - 05:18 AM

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.

#12 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 29 January 2013 - 06:39 AM

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: .

#13 mogwai

mogwai

    Advanced Member

  • Members
  • PipPipPip
  • 94 posts

Posted 29 January 2013 - 06:47 AM

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.