2013/09/20

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.

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:

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:

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:

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

2013/09/10

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

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:

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:

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:

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

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.

Notice the grouping on the CITY field and that the field appears in the header as well as in the footer row.

Now when I select the cell in the header row that has the CITY field in it, and choose “Detail” as the value for the drop property, this is the result:

And when I choose “All”:

So the difference is in the footer row. Drop All removes all occurrences of the CITY field beneath the header row, while Drop Detail only hides the CITY field in the detail section and still shows it in the footer row.

2013/03/05

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:

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.

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:

2013/02/18

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.

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

If you now Edit the data set and select Preview Results, you should see this:

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:

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:

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

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:

The result now looks like this:

*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

2013/01/15

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:

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:

select the table, go to the Groups tab and Edit the group ‘grpEmployee’ to add a filter like this:

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:

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: