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

Advertisements