In this post a crosstab with multiple detail rows is created. I used a question on the birt-exchange forum as a starting point for writing this post and I used the .csv file that was attached to that same question as the datasource. This is a link to the question

If you don’t feel like following the link, the person.csv file contains these rows:
pk,name,DOB,city,spouseName,spouseDOB
1,joey,19770222,nyc,jane,19790303
2,mark,19831103,nyc,leila,19850710
3,lu,19830803,boston,mary,19870905
4,bob,19761222,nyc,bobina,19750524
5,bobby,19670304,boston,andrea,19700103

Computed Column
First add a computed column to the data set. Actually it’s nothing more than a static value, that will be used as a dimension in the cube that will be created in the next step.
cmdrComputedColumn

The Cube
Create a data cube with two dimensions: one on the PK field and one on the computed column justANumber.
Next create summary items for both the person and the spouse’s names and their birthdays. Put all of these under the same Summary Field and make sure to edit the Data Type to String and the Function to FIRST:
cmdrCubeMeasure

The Crosstab
From the palette drag a crosstab item to the report layout, then take these steps:

  • drag the grpPK dimension to the columns area
  • drag the grpNumber dimension to the rowss area
  • drag the summary fields name and spousename to the summary area
  • create a grid (1 column, 2 rows) in the rows area
  • create two other grids (1 column, 2 rows) in the name and the spousename columns in the summary area

This is what you should have until now:
cmdrCrosstabHalfway

Now let’s move on:

  • Create labels “Name:” and “Date of Birth” in the grid in the row dimension area
  • Drag the name and the spousename fields – they are already in the crosstab – into the first line of the grid that is in the same cell
  • Drag the DOB and the spouseDOB fields from the cube into the second line of the grids. For some reason this can’t be done in 1 step, you first have to drag it underneath the grid, then drag from the new column that is in to the grid and finally, remove the newly created column and choose “no” if you are asked if you like to remove unused bindings

Now the crosstab should look like this:
cmdrCrosstabComplete

And, after doing some formatting of the gridlines and setting some visibility properties, this is the resulting report:
cmdrResults

With “some kind of column grouping”, I mean that the output of the report looks like this:
CGResults

What you see are employees listed by city, with each city in its own column. If you have a better name for this instead of “column grouping”, please post it in the comments and I’ll be glad to take it over in the title of this post if I like it.

In order to get this output, you need to have the rows in the data set numbered by city. That field will be used as the row dimension in a crosstab table. BIRT does not provide out of the box functionality to get this rownumbers in the data set, so I decided to share my approach to do it.

The Query
The data source for this sample report is the ClassicModels database. The data set query selects all employees and the city they work in:

select o.city,
       e.lastname
from   offices o,
       employees e
where  o.officecode = e.officecode
order  by o.officecode,
          e.lastname

Getting the rownumber
There are a couple of ways to get this number:

  • an analytical function in the query
  • the GROUPROWNUM function from the group functions plugin
  • other creative SQL solutions

I will further talk about the first two solutions.

The Analytical Function
This one is easy. If your database provides analytical functions, it is enough to adapt your query, so that it selects the rownumber by city. The query – tested in an Oracle database – looks like this:

select o.city,
       e.lastname
       row_number () over (partition by o.city order by e.lastname) as cityRownum
from   offices o,
       employees e
where  o.officecode = e.officecode
order  by o.officecode,
          e.lastname

The GROUPROWNUM function
To get this one to work, you need to install the group functions plugin. You can find a download and all you need to know about that in this Devshare post

After installing the group functions plugin, add a computed column to the data set. Use the GROUPROWNUM function and choose CITY in the Aggregate On field:
CGCoputedColumn

The Crosstab
The final step to complete the report is creating the crosstab.
First create a data cube with two Dimensions (city and cityRownum) and one Summary field (lastname). Make sure to use the FIRST function in the summary field.

The data cube should now look like this:
CGDataCube

Drag the cube to you report and make cityRownum a row dimension and CITY a column dimension:
CGCrosstab

After some formatting of the styles (removing the grid lines) and the crosstab (Hide Measure Header, set width of row Dimension to zero) your report should produce the output as shown on top of this post.

In a scripted data set in BIRT you don’t need to define parameters that are bound to report parameters. That is because you can refer to report parameters directly in the open or fetch script, like in this example of an open script:

cities = [];
if (params["pCOUNTRY"]=="France") {
   cities[0] = "Paris";
   cities[1] = "Marseille";
   cities[2] = "Lille";
} else if (params["pCOUNTRY"]=="USA") {
   cities[0] = "New York";
   cities[1] = "Chicago";
}

When you are dealing with a scripted data set that is bound to a nested table, and you need the parameter value to be a value from the outer table, you’ll need a different approach.

In that case, select the data set, choose Edit data set and add a parameter to the scripted data set.
ssspDsParam

Next, bind a value from the outer table to the parameter. Select the inner table, go to the bindings tab and select the Data Set Parameter Binding button:
ssspDsParamBinding

And finally, write the open script like this:

cities = [];
if (inputParams["pCOUNTRY"]=="France") {
   cities[0] = "Paris";
   cities[1] = "Marseille";
   cities[2] = "Lille";
} else if (inputParams["pCOUNTRY"]=="USA") {
   cities[0] = "New York";
   cities[1] = "Chicago";
}

For some reason it took me some time to find out about the use of inputParams[“datasetParam”]. So I hope that if you are dealing with the same issue, this post has eased your search.

Just a couple of days after I posted my Columns to Rows in BIRT Data Set I ran into this birt-exchange forum post where Robilco provides a solution for the exact same problem. I like his approach and he was kind enough to let me write about it on this blog.

This approach has no need for computed columns and a joint data set, just a scripted data set and some more scripting.

Problem Description
The starting point is a .csv file with this content:
Department;Infrastructure;Training;Comms;Consumables
X;100;150;200;125
Y;150;200;150;175

The different types of budgets need to be in rows instead of in columns.

CSV data set
Like in my previous post, a data set is created for the purpose of the .csv file. But this time some scripting is added.

In the BeforeOpen method of the data set, two variables are initialized:

idx = 0;
budgetRecs = [];

In the onFetch method of the data set, an array is created with the budget data. The budget types are already separated in this step:

budgetRec = {department: row["Department"], budgetType: "Infrastructue", budget: row["Infrastructure"]};
budgetRecs[idx++] = budgetRec;
budgetRec = {department: row["Department"], budgetType: "Training", budget: row["Training"]};
budgetRecs[idx++] = budgetRec;
budgetRec = {department: row["Department"], budgetType: "Communications", budget: row["Comms"]};
budgetRecs[idx++] = budgetRec;
budgetRec = {department: row["Department"], budgetType: "Consumables", budget: row["Consumables"]};
budgetRecs[idx++] = budgetRec;

Scripted data set
The scripted data set won’t do anything else but fetching the data that is entered in the array in the onFetch method of the CSV data set.

The open method initalizes the array index:

idx = 0;

The fetch method (not the onFetch method!) reads the array and moves the data into the data set columns.

if (idx < budgetRecs.length) {
	row["Department"] = budgetRecs[idx].department;
	row["BudgetType"] = budgetRecs[idx].budgetType;
	row["Budget"] = budgetRecs[idx].budget;
	idx++;
	return true;
}else{
	return false;
}

Dummy grid
To make sure the CSV data set is executed, drag a grid element to the report layout and bind it to the CSV data set. As long as no report items are put in the grid, it will not be visible when the report is executed.
rtc2GridBinding

Results
To check if things work as expected, drag the scripted data set to the report layout and run the report. As you can see: every budget type is on a separate row:
rtc2Results

Many thanks to Robilco for providing the inspiration to this post!

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

The last couple of months I noticed that people often struggle with using dates in BIRT, especially in the context of datasets. So I decided to write a few words about it.

First of all
Dates are not saved in a database in some date format. They are saved as a date and that’s it. Dates do get formatted when they are presented to you, for example on your screen. The format then depends on the settings of the software program you are using or on your machine settings.

Also, if a user enters a date that needs to be saved in a database, you’ll have to let the database know in what format the value is entered, so it can be converted to the correct date.

To achieve all this, you could rely on default date format settings, but that doesn’t make your software very portable, does it?

Dataset parameters of date type
In a typical BIRT reporting situation, you have a report parameter that is bound to a dataset parameter that is used in your SQL query.

If you make sure that:

  • the report parameter is of type date
  • the dataset parameter is of type date
  • the parameter value is compared to a date type column in the query

then there shouldn’t be any problem at all.

In this case, there’s only one place where the date format is important: the user has to enter a parameter value. This format can easily be adapted to your needs in the Edit Parameter dialog. In this example I created a rather exotic custom format “MM-yyyy-dd”:

Now the user has to enter the date in the custom format:

And when shown in the report using the default date format settings, the parameter value looks – at least on my machine – like this:

Concatenating a date parameter to queryText
Though in my opinion the use of concatenating to queryText should be avoided whenever possible, a lot of developers seem to like it. When concatenating a date type parameter to the queryText in the beforeOpen event of the dataset, the parameter value is implicitly converted to a string, which also means that a format is applied to it.

The best way to make sure your database will accept the concatenated value as a date, is to add a date formatting construction in the query, as well as at the parameter value that is implicitly converted into a string. The message is: do explicit conversion on both sides.

When using the Apache Derby sample database, this can be done like this:

importPackage( Packages.java.text );

// get the value of parameter dateParameter as entered by the user
var dateParamVal = params["dateParameter"];

// transform the date into a String using the yyyy-MM-dd format 
var dateFormatOut = new SimpleDateFormat("yyyy-MM-dd");
var formattedDate = dateFormatOut.format(dateParamVal);

// the database will convert the string value into a date value
// yyyy-MM-dd is one of the formats the Apache Derby database recognizes as a date
this.queryText = this.queryText + " AND orderdate = Date('" + formattedDate + "')";

(I’m not specialized in Apache Derby databases, I learned about date functions here: http://db.apache.org/derby/papers/JDBCImplementation.html#Conversion+of+a+string+type+to+a+JDBC+java.sql.Date)

To do the same for an Oracle database, it looks like this

importPackage( Packages.java.text );

// get the value of parameter dateParameter as entered by the user
var dateParamVal = params["dateParameter"];

// transform the date into a String using the yyyy-MM-dd format 
var dateFormatOut = new SimpleDateFormat("yyyy-MM-dd");
var formattedDate = dateFormatOut.format(dateParamVal);

// the database will convert the string value into a date value
// yyyy-MM-dd is the format that Oracle will use to convert the string into a date datatype
this.queryText = this.queryText + " AND orderdate = to_date('" + formattedDate + "', 'yyyy-mm-dd')";

In BIRT reports it is possible to create a data set that is based on a stored procedure in the database.  In this post is discussed in detail how this needs to be done.  As an example I’ll show how to create a stored procedure in Oracle that meets the requirements to act as a data set and how to create the data set in BIRT.  As a bonus you’ll learn how to show data on a BIRT report from a temporary table.

Temporary table
First of all, create a temporary table in Oracle.  Feel free to skip this step if you are not interested in working with a temporary table.

CREATE GLOBAL TEMPORARY TABLE tmp_objects (
   object_name VARCHAR2(30), 
   status VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

As you can see, we have the ON COMMIT PRESERVE ROWS clause.  This is necessary because the default value of the auto-commit property of the JDBC datasource is set to TRUE (or auto, which also means TRUE).

If you really insist on having the ON COMMIT DELETE ROWS clause in the temporary table definition, or if you need to reuse an existing table that has this clause, you’ll need to change to auto-commit property to false.

Stored procedure
The stored procedure needs to return data in some way.  To serve as a data set in BIRT, an Oracle stored procedure needs to have an output parameter of type SYS_REFCURSOR.  The procedure in this example performs two steps:

  • inserting all Oracle objects of a certain type in the temporay table
  • open the cursor – select all records from the temporary table
CREATE OR REPLACE PROCEDURE refcur_test (
   p_type IN VARCHAR2,
   p_refcur OUT SYS_REFCURSOR
)
IS
BEGIN
   -- pre-processing steps here
   INSERT INTO tmp_objects (
      object_name, status
   )
   SELECT o.object_name,
          o.status
   FROM all_objects o
   WHERE o.object_type = p_type;
   -- open the cursor
   OPEN p_refcur FOR SELECT * FROM tmp_objects;
END;

BIRT data set
To create the data set in BIRT, start with selecting ‘SQL Stored procedure Query’ from the Data Set Type dropdown box, after clicking ‘new data set’

In the query area, make a call to the stored procedure. Don’t forget to mention the database owner in your call. Without it, things won’t work. (I’m not sure, but I think this is a JDBC issue)

Now create an output parameter to correspond with the refcursor parameter in the Oracle procedure.

And finally, take a look at the Preview Results to see if everything works fine. In my case, it does:

Et voila!