This article describes a way to transform column data into row data with the help of a scripted data set, computed columns and a joint data set.

Most of the time I use SQL to perform the task of transforming columns to rows, but some time ago, when helping out someone on the birt-exchange forums, I needed to come up with a different approach. The poster got his data from a .csv file, so the use of SQL was no option. (See bottom of this post for a SQL based solution).

Problem Description
A pie chart needs to be created based on the data in a .csv file:
Department;Infrastructure;Training;Comms;Consumables
X;100;150;200;125
Y;150;200;150;175

The different types of budgets – Infrastructure, Training, Comms and Consumables – are all in separate columns and have to become the slices of the pie chart. If we take the csv based data set as it is, there is no unique column that can be selected as a values series field.

CSV Data Set
First of all: create a data source and data set on the .csv file. This is pretty straightforward.
Also, add a computed column that will always contain the value 1 and name it join_col. We will need this column when creating the Joint Data Set in one of the next steps.
rtcComputedCol

Scripted Data Set
Next, create a scripted data set that has two columns:

  • join_col
  • col_number

The join_col field will always contain the value 1 and will be used to join this data set to the .csv data set created in the previous step.
The col_number will add up for each row in this data set and the number of rows needs to correspond to the number of columns in the .csv that you want to transform to rows. In this case we need 4 rows as there are 4 types of budget in the .csv file.

To create a scripted data set take these steps:

  • create a new data source → make sure you choose Scripted Data Source and enter an appropriate name, e.g. dsScripted
  • create a new data set → Select dsScripted as the datasource and enter an appropriate name, e.g. dsScriptedData
  • add both join_col and col_number as Integer type columns
  • in the open script of the data set, add this code:
    joinCols = [];
    colNums = [];
    for (i=0;i<=4;i++) {
       joinCols[i] = 1;
       colNums[i] = i+1;
    }
    idx = 0;
    
  • in the fetch script of the data set, add this code:
    if (idx < numCols.length) {
    	row["join_col"] = joinCols[idx];
    	row["column_num"] = colNums[idx];
    	idx++;
    	return true;
    }else{
    	return false;
    }
    
  • If you now Edit the data set and select Preview Results, you should see this:
    rtcDsScriptedResults

Joint Data Set
In the joint data set, we will now join the csv data set and the scripted data set together based on the join_col field that exists in both data sets. Every row in the csv data set is joined to every row in the scripted data set. So for every department there will be 4 rows in this data set:
rtcJointDataset

Next step is to create two computed columns. One will hold the budget type and the other will hold the actual budget on each row. The first column, budgetType, has an expression like this:

switch(row["dsScriptedData::column_num"])
{
case 1:
  "Infrastructure";
  break;
case 2:
  "Training";
  break;
case 3:
   "Commissions";
   break;
case 4:
   "Consumables";
   break;
}

The second column, budget, has an expression like this:

switch(row["dsScriptedData::column_num"])
{
case 1:
  row["dsBudget::Infrastructure"];
  break;
case 2:
  row["dsBudget::Training"];
  break;
case 3:
   row["dsBudget::Comms"];
   break;
case 4:
   row["dsBudget::Consumables"];
   break;
}

This is what you should see when you Edit the data set, select Preview Results and scroll to the right:
rtcJointDatasetPreview

Result
With the joint data that we have created, it’s a piece of cake to create the pie chart. Put the budget column in the Series Definition, the budgetType column in the Category Definition and the dsBudget::Department column in the Optional Grouping:
rtcCreateChart

The result now looks like this:
rtcResult

*SQL Solution
If the data does not come from a csv file, but you are selecting it from a data base, you don’t have to worry about scripted data sets, computed columns and all the other fancy features I mentioned in above article. You can write a query like this and you are ready to move on:

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,
       'Commissions' 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

If you want to put a filter on the values series of a BIRT chart, you’ll need some kind of workaround. The values series can’t be used after selecting the filter button on the Select Data tab of the chart dialog. The easiest way to accomplish this, is to do add the grouping and aggregation in the query and then put a filter on the aggregated data column. However, if you want to use the ungrouped data in other parts of your report, you might prefer another workaround.

Let’s say you want to know from the classicmodels database the top 5 of employees that have taken the most orders.

The Data Set
This query selects the employee’s lastname and the ordernumbers of his customers:

select e.lastname,
       o.ordernumber
from   orders o,
       customers c,
       employees e
where  o.customernumber = c.customernumber
and    c.salesrepemployeenumber = e.employeenumber

The Report Table
As it is not possible to use a filter directly on the values series of the chart, we need to find some other item to put the filter on: a report table. The chart will be created in the header row of the table.

Take these steps to create a table:

  • drag a table element from the palette to the report, choose 1 row and 1 column and bind it to the data set you created
  • right-click on the table and select ‘Insert Group’ to add a group ‘grpEmployee’ to the dataset and choose lastname in the Group On field
  • select the table, go to the Binding tab and add an aggregation like this:
    aggbuilder
  • select the table, go to the Groups tab and Edit the group ‘grpEmployee’ to add a filter like this:
    editgroup
  • in the same edit group dialog, select Sorting and add a sort on row[“aggcount”] descending

The Chart
Now we are ready to create the chart:

  • drag a Chart item from the palette into the header row of the table
  • select the chart type you like (I chose the a simple Bar Chart)
  • move on to the Select Data tab, make sure the Select Data From Container checkbox is checked and then select row[“aggcount”] at the Value Series and row[“grpEmployee”] at the Category Series:
    chartdialog

The Result
To clean up things a bit, you can remove all the rows from the table, except for the header row and run the report. The result should look like this:results

This is a followup on a comment I received on an earlier post about generating a chart in a grouping header: Generate a separate chart for each group value.

If you take a look at the screenshot of the resulting report in that post, you see that for the year 2005 there’s only a bar for the months that have records in the data set. The commenter asked for what needs to be done to show all months of the year, also the ones that don’t have data in the dataset.

In the chart edit screen, on the Format Chart tab, select the X-axis on the left side and uncheck the “Is Category Axis”:

Then click the Scale button and modify it like this:

This is what the report looks like now:

In BIRT it is possible to create a table grouping and have a chart generated for each group value. In this blog I explain how it can be done using the sample database.

The Query
Create a dataset dsOrders on the ORDERS table from the sample database with this query:

select year(orderdate) order_year,
       month(orderdate) order_month,
       status
from   orders

The Report Table and the Chart
Drag a table element (1 row, 1 column) to the report design section and bind it to your data set dsOrders. Insert a group into the table where you Group On order_year. In the group header appears a datafield order_year.
Now add a second group header row underneath the first one and drag a chart item in it. Select Stacked Bar Chart as the chart subtype. On the Select Data tab, make sure to choose “use Data from dsOrders”, instead of the default selection which is “Inherit data from container”. Enter the dialog like this:

The number of orders will be counted grouped by month and status.

To make sure that every chart shows data from the corresponding year only, select the chart and add this filter:

The row.outer[“ORDER_YEAR”] part can be constructed through the expression.

Results
So if we run the report, a chart will be shown for each year: