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: