Line Graph (percentage) above Bar Graph

You may want to take a look at the attached image first to hopefully understand what I'm trying to accomplish. 
 
I have 2 data series, one of "estimated time" and the other of "actual time" that is graphed by month (this works fine!) 
 
The data rows are structured like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>WORKORDERID | ESTIMATEDHOURS | REGULARHOURS | OVERTIMEHOURS | FINISHDATE
ABC123456 | 6.0 | 3.0 | 1.0 | 03/07/2012
ABC789012 | 5.0 | 6.0 | 0.0 | 03/08/2012
</pre>
 
"Estimated Time" is just the ESTIMATEDHOURS field. "Actual Time" is the sum of REGULARHOURS + OVERTIMEHOURS. Nothing too complicated there! My chart is working fine by grouping (by month) on the FINISHDATE on the x-axis and having the 2 y-axis series be "estimated hours" and "actual hours" (both aggregated as SUM). 
 
Now for the fun part. Above each month's bars, I'd like to have a line graph that shows a productivity percentage as (ESTIMATED / ACTUAL)*100. I know this may seem weird, my personal opinion is that it should be ACTUAL / ESTIMATED but management wants the ratio the other way, but I digress. I have successfully added a second y-axis, but I'm not sure how to aggregate the data. 
 
If the 2 sample rows listed above were the only 2 data points for March 2012, the percentage would be 110% ( Sum of all estimated hours / sum of all regular and overtime hours * 100 ) or (6+5)/(3+1+6+0)*100= 110% 
 
I tried PERCENTSUM but it didn't seem to work. Perhaps I was just doing it wrong. 
 
Any and all help is greatly appreciated! 
 
EDIT: As a better example using the sample chart below, if the blue bars were "estimated time" and the red bars were "actual time" then all that I want the line graph to be on the top is: (ignore the sample values that BIRT automatically fills in for the line graph) 
Jan: (6/42)*100 = 14% 
Feb: (4/91)*100 = 4% 
Apr: (12/62)*100 = 19% 
...etc..

Find more posts tagged with

Comments

drusso17

I had some potential thoughts but not sure if any of them help.

Do I need to create some sort of aggregation first and then use it on the chart?

lgudait

Could you summarize the data by month/year in the SQL? If you did that, all you would need to do is add a computed column for the (Estimated)/(Actual)*100

Then your chart could reference the data set as is.

drusso17

the issue is that each row would then be weighted equally (I think). And what aggregating function would I use then? Because I'd only be calculating the ratio for each individual row, not for all the rows (combined) in that group.

Example: (using the original two rows in my first post)

Wrong:

Row 1: 6/(3+1) = 1.5 (that would be the value of the computed column)
Row 2: 5/6 = .833 (that would be the value of the computed column)
Group: ??? (1.5 and .833 cannot be combined to know the correct value)

Right:
Row 1: (nothing is calculated here)
Row 2: (nothing is calculated here)
Group: (6+5)/(3+1+6)= 1.1 (this would be the value for the entire month which is what I want!)

lgudait

What I'm essentially saying to do is get rid of rows 1 & 2 in your SQL. I attached a sample using the classic models database. I group by the paid month/paid year, sum the paid amount and credit limit (substitute your hours fields here) and then in the computed columns, divide the summed paid amount by the summed credit limit

Data Set is the original data detail, Data Set_Aggregated is the aggregated set with the computed column.

I find it's always easier to do as much aggregation as possible in the SQL instead of the report design.