Report with table and chart from multiple data sources based on sub-queries

1. Introduction 
 
Sooner or later every report designer has to develop a report which combines data from several different data sources. Every BIRT report can retrieve data from many data sources; hence it seems not to be a problem. Things become more complicated when data from one data source depend somehow on data taken from some other. When this dependency can be described by simple join one can use a cross table. But when joins are not simple, i.e. when you need to bind data on more than one field and use sub-queries, cross tables cannot be used. Here is some workaround I use in my reports; I can build simple tables with data from many data sources retrieved with sub-queries. Additionally, I will show you how to build a chart based on that table with some Javascript tricks. 
I wish to thank Jason Weathersby, whose quick and professional help led me to this solution. 
 
2. Report with a table with sub-queries 
 
For this example we will use some data sets based on Classic Models Inc. Sample Database, but in real life you can use data sets pointing to separate data sources which can be separate databases or just a mix of any data source type accessible from BIRT. The example will contain a table with list of orders (as a main query) and total quantity and total price of each order (as two sub-queries). 
 
1. Create a report. In this example it is just a simple blank report. 
 
2. Add your data sources (here we will add just one data source but you can add as many as you need). Right click on Data Sources, choose New Data Source. Select "Classic Models Inc. Sample Database", press Next, then Finish. 
 
3. Create data set retrieving base data on which you will build your sub-queries. Right click on Data Sets, choose New Data Set, give it a name "Orders", press Next and enter following query:

select orderNumber, orderDate
from Orders

4. Let's limit amount of data retrieved from data set to 10 rows only by disabling "Fetch all rows from data source" checkbox in Options tab of data set and entering "10" into "Max number of rows to fetch from data source". Then press OK, main data set is ready. You can preview it in Preview Results tab. 
 
5. Create data set for the first sub-query retrieving total quantity ordered. Similarly like previous data set, just name it "Quantity" and use following query with a parameter:

select sum(quantityOrdered) as qty
from OrderDetails
where orderNumber=?

6. Switch to Parameters tab and edit automatically added parameter to following values: 
a. Name: order_number 
b. Data Type: Integer 
c. Direction: Input 
d. Default Value: 10100 
 
7. Create one more data set for the another sub-query retrieving total price of the order. Similarly, name it "Price" and use following query with a parameter:

select sum(priceEach) as price
from OrderDetails
where orderNumber=?

8. Switch to Parameters tab and edit automatically added parameter to the same values as in previous sub-query, as both sub-queries depend on order_number values from main query. We have all data sets necessary for the sample report ready. 
 
9. Drag-and-drop a grid of 1 column and 3 rows to the report layout to create a basic structure 
 
10. Drag-and-drop "Orders" data set to the first cell of the grid. A table with orders IDs and dates is automatically created. You may preview your report to see the data. 
 
11. Give a name (e.g. "data_table") to your table, you will need it when creating a chart. 
 
12. Right-click on the header of the last column in created table and add two more columns to the right by "Insert | Column to the right" option. Those two columns will contain data from sub-queries. 
 
13. Drag-and-drop "Data" report item into detail row in the fist added column. Cancel "New data binding" dialog which opens automatically when new data field is added to the report. 
 
14. Right-click on the data field and chose "Change data column" option. Chose "Quantity" in combo box with data sets and then select "QTY" value returned by this data set. Then press OK. 
 
15. Time to connect parameter of sub-query with actual data retrieved by the main query. Select the data field and switch to the "Binding" tab in the Property Editor. Press "Dataset Parameter Binding..." button and edit "order_number" parameter. Instead default value, open Expression Builder and select "ORDERNUMBER" from "Available column bindings", which will create following expression:

 row&#91;"ORDERNUMBER"]

16. Close Expression Builder and Dataset Parameter Binding dialogs with OK buttons. You may preview your report to see the data including the first sub-query. 
 
17. Add another data field and do the same for "Price" data set. Now you have a table with all data you need. You may work on the layout, fonts, labels etc. now. 
 
3. Chart based on data from sub-queries 
If you want to build a chart based on that table you will soon realize that you cannot access data from the sub-queries. It is normal as in BIRT you can bind a chart to one report item only and those fields are not a part of the table. What you have to do is building a chart with any data, store the data you need in Javascript arrays and then switch the data before final chart is generated. 
 
1. Create two-dimensional Javascript array to store data you need to create the chart. Fist dimension will store dates (to be shown on X axis), second will store quantity and the last one will store price from the data table. In "beforeFactory" event handler of the report add:

aData = new Array();
aData&#91;0] = new Array(); //Dates
aData&#91;1] = new Array(); //Qty
aData&#91;2] = new Array(); //Price

2. Select the detail row of data table and switch to Script tab, than in "onCreate" even handler add the following to store date and current row number (necessary for indexing the array in sub-query data fields which are not a part of the table so they do not know their current row number):

 oRow = this.getRowData();
iRowNumber = row.__rownum;
aData&#91;0]&#91;iRowNumber] = oRow.getColumnValue("ORDERDATE");

3. Select data field for the first sub-query (quantity) and in the event handler for "onCreate" add the following:

aData&#91;1]&#91;iRowNumber] = this.getValue();

4. Select data field for the second sub-query (price) and in the event handler for "onCreate" add the following:

 aData&#91;2]&#91;iRowNumber] = this.getValue();

5. Now, to check if data was properly stored in the array and to pass it to the chart we will add dynamic text fields (one for each array dimension). Drag-and-drop "Dynamic text" report items into empty grid cell below data table and set following expressions (notice differences in global variables names). You may make them invisible in the layout later on: 
 
a. For the first dimension (with dates)

reportContext.setPersistentGlobalVariable("dtarry", aData&#91;0]);
aData&#91;0].toString()

b. For the second dimension (with quantities)

 reportContext.setPersistentGlobalVariable("qtyarry", aData&#91;1]);
aData&#91;1].toString()

c. For the third dimension (with prices)

 reportContext.setPersistentGlobalVariable("pricearry", aData&#91;2]);
aData&#91;2].toString()

6. Create a chart of your choice. I this example we will create a simple line chart. Drag-and-drop "Chart" report item into last empty grid cell. Chose a line chart type. On "Select data" tab chose the name of the table ("data_table") from "Report Items" section of combo box "Use data from". This way you will re-use already fetched data avoiding unnecessary fetches directly from the data source. Drag-and-drop "ORDERDATE" column into Category (X) Series field. Then create two Value (Y) Series with any data, you can use constants "1" and "2" for example. Those values will be replaced by the actual data stored in Javascript arrays. Format the chart in the way you want. 
 
7. Select the chart, switch to "Script" tab and enter following beforeGeneration() function. It is pretty self-explanatory - it just gets current axes, copies data from stored Javascript arrays into data sets and stores those data sets in the chart.

 function beforeGeneration( chart, icsc )
{
importPackage(Packages.java.util);
importPackage(Packages.org.eclipse.birt.chart.util);
importPackage(Packages.org.eclipse.birt.chart.model.data.impl);
importPackage(Packages.org.eclipse.birt.chart.model.type.impl);
importPackage(Packages.org.eclipse.birt.chart.model.component.impl);
  
var xAxis = chart.getAxes().get(0);
var yAxis = xAxis.getAssociatedAxes().get(0);

var xSerieDef = xAxis.getSeriesDefinitions().get(0);
var ySerieDefQty = yAxis.getSeriesDefinitions().get(0);
var ySerieDefPrice = yAxis.getSeriesDefinitions().get(1);

//Create ArrayLists for custom data from passed arrays
var xData = new ArrayList();
var yDataQty = new ArrayList();
var yDataPrice = new ArrayList();

//Get data from stored variables
aDataX =icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("dtarry");
aDataYQty =icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("qtyarry");
aDataYPrice =icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("pricearry");

//Convert time for X axis
var cal = new GregorianCalendar();
for( i=0;i
{
	  cal.setTime(aDataX&#91;i]);
	  var cdt = new CDateTime( cal.get(Calendar.YEAR), cal.get(Calendar.MONTH)+1, cal.get(Calendar.DAY_OF_MONTH) );
	  xData.add(cdt);
}

//Copy series vales. They suppose to have the same length
for( i=0;i
{
	  yDataQty.add(aDataYQty&#91;i]);
	  yDataPrice.add(aDataYPrice&#91;i]);
}

 //Create data sets for series
var categoryValues = DateTimeDataSetImpl.create( xData );
var qtyValues = NumberDataSetImpl.create( yDataQty );
var priceValues = NumberDataSetImpl.create( yDataPrice );
  
//Create custom series

//Get existing category series
var seCategory = xSerieDef.getRunTimeSeries().get(0);
//Or just create a new one
//var seCategory = SeriesImpl.create();

//Set new category values
seCategory.setDataSet( categoryValues );
  
//Get existing value series
var qtySeries = ySerieDefQty.getRunTimeSeries().get(0);
var priceSeries = ySerieDefPrice.getRunTimeSeries().get(0);
//Or just create a new one
//var qtySeries = LineSeriesImpl.create();
//var priceSeries = LineSeriesImpl.create();

//Possibility to change the identifier
//qtySeries.setSeriesIdentifier( "DynamicrnBar Series" );

//Set the values
qtySeries.setDataSet( qtyValues );
priceSeries.setDataSet( priceValues );

//Clear existing series
xSerieDef.getSeries().clear();
ySerieDefQty.getSeries().clear();
ySerieDefPrice.getSeries().clear();

//Bind new series to chart
xSerieDef.getSeries().add(seCategory);
ySerieDefQty.getSeries().add(qtySeries);
ySerieDefPrice.getSeries().add(priceSeries);
}

8. Preview the report - you should see a chart with data taken from your table copied through variables to the chart script. 
 
3.1. Pie Chart 
Simirarly, you can create a Pie Chart (or any other chart), just translate your data to proper categories. Here's a sample for a Pie Chart with two integer values passed to the chart in variables "value1" and "value2". (It's not included in the final report design)

function beforeGeneration( chart, icsc )
{
importPackage(Packages.java.util);
importPackage(Packages.org.eclipse.birt.chart.util);
importPackage(Packages.org.eclipse.birt.chart.model.data.impl);
importPackage(Packages.org.eclipse.birt.chart.model.type.impl);
importPackage(Packages.org.eclipse.birt.chart.model.component.impl);

seriesDef = chart.getSeriesDefinitions().get(0);
categoryRunSeries = seriesDef.getRunTimeSeries().get(0);
valueSeriesDef = seriesDef.getSeriesDefinitions().get(0);
valueRunSeries = valueSeriesDef.getRunTimeSeries().get(0);

//Get data from stored variables
iValue1 =icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("value1");
iValue2 =icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("value2");

//Create data for category
var categoryData = new ArrayList();
categoryData.add("Value 1");
categoryData.add("Value 2");

//Create data for values
var valueData = new ArrayList();
valueData.add(iValue1);
valueData.add(iValue2);

//Create data sets for series
var categoryValues = TextDataSetImpl.create( categoryData );
var valueValues = NumberDataSetImpl.create( valueData );


//Set new category values
categoryRunSeries.setDataSet( categoryValues );
valueRunSeries.setDataSet( valueValues );

}

4. Table data below the chart 
Last thing which you may want to do is to display the chart above the table with your data. You cannot simply move the chart above the table - data needed for the chart won't exist before the table is created. The only way I know is to make the original table invisible and copy the data to another one, placed below the chart. 
1. Drag-and-drop "Table" report item below the chart (you may create separate row in a grid which contains all report elements if you wish). Create table with enough columns to fit your data (three in this case: date, quantity and price) 
2. Switch to "Binding" tab for newly created table and chose "data_table" from combo box with report items. 
3. Drag-and-drop "Data" report item into first column into the detail row. Cancel "New data binding" dialog which opens automatically when new data field is added to the report. 
4. Right-click on the data field and chose "Change data column" option. Chose "data_table" in combo box with report items and then select "ORDERDATE" value from that table. Then press OK. 
5. Drag-and-drop two "Dynamic Text" report items into second and third column in the detail row. Set following expressions accordingly (first one for the column with quantity, second one for the one with price): 
 
 
aData[1][row.__rownum] 
aData[2][row.__rownum] 
 
 
6. Drag-and-drop "Label" report items into column headers and format the table according to your needs. 
The report is ready. You can find full definition attached to this tutorial. 
Happy reporting!

Find more posts tagged with

Comments

There are no comments yet