BIRT Report Designer - split actual and budget data stored in one table into columns and add a variance

Question

I have financial data in the following format in a SQL database and I have to live with this format unfortunately (example dummy data below).

I have however been struggling to get it into the following layout in a BIRT report.

I have tried creating a data cube with Package, Flow and Account as Dimensions and Balance as a Measure, but that groups actual PER and actual YTD next to each other and budget PER and YTD next to each-other etc so is not quite what I need.

The other idea I had was to create four new calculated columns, the first would only have a value if it were a line for actual and per, the next only if it was actual and ytd etc, but could not get the IF function working in the calculated column.

What are the options? Can someone point me in the direction of how to best create the above layout from this data structure so I can take it from there?

Thanks in advance.

Answer 1

I am not sure what DB you are using in the back end, but this is how I did it with SQL Server.

The important bit happens in the Data Set. Here is the SQL for my Data Set:

SELECT 
    Account, 
    Package, 
    Flow, 
    Balance
FROM data

UNION

SELECT DISTINCT
    Account, 
    'VARIANCE', 
    Flow, 
    (SELECT COALESCE(SUM(Balance),0) FROM data WHERE Account = d.Account AND Flow = d.Flow AND Package = 'ACTUAL') - (SELECT COALESCE(SUM(Balance), 0) FROM data WHERE Account = d.Account AND Flow = d.Flow AND Package = 'BUD') as Balance
FROM data d

This gives me a table like:

Then I created a DataCube that contained

• Groups/Dimensions
  • Account
  • Flow
  • Package
• Summary Fields/Measures
  • Balance

Then I created a CrossTab Report that was based on that DataCube

And this produces the result of:

Hopefully this helps.