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.

Advertisements

This post is the answer to a question in the comments of one of my earlier posts: https://enterprisesmartapps.wordpress.com/2011/12/13/birt-drop-group-header-property-and-table-border-lines/#comments.
Follow the link if you want more info on the drop property.

Miguel asks what the difference is between “Detail” and “All” as a value for the drop property. An example will show the difference.

In this example I have this very simple query that selects from the ClassicModels sample database:

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

This is what the report layout looks like:
avsdTableLayout

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:
avsdResultDetail

And when I choose “All”:
avsdResultAll

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.

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

In an earlier post I explained how to use a crosstab item to create a multi column BIRT report. But when there are one or more groups in your report, that approach does not suffice. In this post I’ll describe a slightly different way to determine row and column numbers, so that they can be used in the crosstab together with a grouping on the data.

In this example I use the PRODUCTS table from the Classicmodels database. My goal is to show products in three columns grouped by productline.

Report variables
First I create 5 report variables. They’ll be used in the onFetch event of the dataset:

The query
This is the query I used:

 SELECT productline,
       productname
FROM   products
ORDER  BY productline, productname

The “order by productline” is important here, as you’ll see when the onFetch event script is discussed.

Computed columns
Add four computed columns to the dataset. In the expression you can enter a dummy value, I entered zero for all of them:

Dataset onFetch event script
In the onFetch script of the dataset the report variables that were described earlier will be filled and assigned to the computed columns in the dataset:

 if (vars["groupName"] != row.PRODUCTLINE) {
   vars["groupName"] = row.PRODUCTLINE;
   vars["groupNum"]++;
   vars["groupRownum"] = 1;
   vars["cubeColnum"] = 0;
   vars["cubeRownum"] = 3;
}
else {
   vars["groupRownum"]++;
   vars["cubeColnum"] = BirtMath.mod(vars["groupRownum"]-1, 3)
   vars["cubeRownum"] = BirtMath.ceiling(vars["groupRownum"], 3)
}

row.groupNum = vars["groupNum"];
row.groupRownum = vars["groupRownum"];
row.cubeColnum = vars["cubeColnum"] + 1;
row.cubeRownum = vars["cubeRownum"];

The datacube
In the datacube I create two dimensions. The row dimension combines the PRODUCTLINE column and the cubeRownum computed column. The column dimension uses the cubeColumn computed column.

The PRODUCTNAME column will be the summary field. Don’t forget to change the datatype of this field to String and the function to FIRST.

The crosstab
This is what the crosstab should initially look like:

To get a decent layout, you can execute these simple steps:

  • select the crosstab and then select the “Hide Measure Header” checkbox in the general properties tab
  • select the cell that contains the cubeRownum data element and set the width property to zero

    If you execute the report after these modifications, it will look like this:

    As you can see, the data is nicely spread over three columns until the next group comes up. Exactly how we wanted it.

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: