Charts in BIRT – A Simple Chart Example

Charts in BIRT – A Simple Chart Example

In this tutorial, we will create a BIRT report with a simple Bar chart showing the monthly sales in 3 different states of the US. We will be using the same food mart database in MySQL for a datasource. So, we are going to have 3 fields, the Month (on category axis), the States (Series) and the Total Sales (on the Value axis).

(Note: As in the previous tutorials, we are using the Eclipse based BIRT report designer to develop this report)

Let’s begin –

Define the Data Set

As we have seen in previous tutorials, first step would be to create a data source and then define a data set which will pull the data for my chart. Below is the data set we need –

DS_BIRT_Chart

There is a particular reason why I chose to select “first date of the month” as a column instead of the month column, though the grouping is by month column. This is to ensure that my category axis could be a date/time axis and would plot my months in the correct order.

As we are not using any parameters for our report, we have nothing more to specify.

Adding the Chart component

Drag and drop the “Chart” component from the palette onto the report. The Chart Editor will automatically pop up.

There are 3 tabs, the first one is to select the “Chart Type”. In this case we select the Bar Chart, the first one which a Grouped Bar Chart.

Chart-Edit-1

Then, we click on Next to go to the next tab, to select the data for the chart. This involves selecting the source of the data and then binding the columns to the chart.

We select the data set that we defined

Chart-Edit-2(1)

And then we bind the columns to the chart i.e. we select what columns to bind with the category, value and series axes.

Chart-Edit-2

As seen above, we selected the “the_date” to be used as category, “store_state” to be used as a series and “sales” as a value.

In the Next tab, we can format the chart, the different aspects of it, the axis, the title, legend, tooltips, colors, font, back ground and so on.

We will first click on “X-Axis” in the Left hand side of the panel, to change our Category Axis Type.

Chart-Edit-3(1)

Firstly, change the Type to “DateTime” and then click on the “Format” button right next to it. Set the format to Month (MMM) using “Pattern” option under “Advanced”. Though “MMM” is not listed in the drop down menu, it is a valid format.

Then we will go and add a Title to the chart

Chart-Edit-3(2)

There is a lot more formatting that we can do, but for now, we will go ahead and click on “Finish”.

Save the report and Preview.

BIRTChartReport

There you go, we have our Monthly sales chart ready.

More on the charts to follow in upcoming posts, so stay tuned!

Shraddha Tambe | Helical IT Solutions

Parameterized BIRT Reports – Simple Parameter

Parameterized BIRT Reports – Simple Parameter

In this tutorial, we will parameterize a simple BIRT report by adding a single select dynamic parameter to filter the report. (To learn how to create a simple BIRT report without parameter you may refer this blog)

We will take this simple sales report based on the FoodMart database. The report looks like –

Report_Before

Say we want to filter the above set of records by City, i.e. Store City. We will now see how we can add a simple single select parameter “Store City” to this report with a dynamic list of cities.

Create a dataset for the parameter

First Step is to create a dataset with the required query to fetch the list of Cities to display as a drop down list to the user for selection. This is required for a dynamic list input parameter, if you plan to give a static list of values, you do not need to define this dataset.

QueryForParam

Create a Report Parameter

Now we define a report parameter to accept user input for the city. You can create one by right clicking on the “Report Parameters” in the “Report Outline” window.

Add_Report_Param1

  • We will create a list box with the name “store_city”, data type will be string as this is a city name.
  • To make the parameter mandatory, check the “Is Required” check box.
  • Select “dynamic” in the ‘Selection List Values’ options. In the Dataset, select the dataset we created in the above step and give the value column and display column. We have only one column for both i.e. “store_city”.
  • We are currently creating a single-select parameter, so we will leave the “Allow Multiple Values” unchecked.
  • You may assign a default value, we are setting it to “Los Angeles”.

 

Add_Report_Param2

Updating the report Query to use parameter

Next step is to update the report query to use the parameter value. For that we will edit the report dataset. Firstly, we will add a where clause in the query for the column store_city. See the below image.

Alter_Report_Query

We added the clause “where s.store_city = ?” in the query Secondly, we define a query parameter whose value will be substituted in place of the question mark. See image below:

Add_Query_Param

We defined a query parameter “ds_store_city” and linked it to the report parameter that we created earlier. This means that whatever value we select for the report parameter will get assigned to this query parameter and then it will be substituted in place of the “?” in the query. If you have multiple parameters in the query, there will be multiple “?” place holders in the query and the values will be assigned sequentially from the query parameters that are defined.

Save and Execute

Save the report now and we are done!! Run the report in web viewer and we get a parameters window to select the parameter input for “Store City”

Parameter_Prompt

And the report result is filtered accordingly.

Report_After

In the upcoming blogs we will know more on the parameters – multiselect parameters, cascading parameters and so on. So, stay tuned!!

Shraddha Tambe | Helical IT Solutions

You may also like to read –

Introduction to BIRT (Business Intelligence and Reporting Tools)

Creating a Simple BIRT Report

Introduction to BIRT (Business Intelligence and Reporting Tools)

Introduction to BIRT (Business Intelligence and Reporting Tools)
 

BIRT is an Open source data visualization and reporting platform first released in the year 2004. It allows to pull data from a variety of data sources like SQL Databases, Web Services, XML, JDO Datastores, POJOs etc, structure the data, summarize, calculate and visualize the same. These reports or data visualizations can be very easily embedded into web applications or rich client applications, especially those based JAVA/Java EE.

BIRT Components

BIRT has 2 main components

1. BIRT report designer & the Design Engine

This comes as a set of plugins for Eclipse or also as a Rich Client Platform. This allows designing reports which are stored as XML files. The Design Engine is the actual core that is used for creating and modifying the reports. It provides the functionality via a Design Engine API. The BIRT report desinger uses this API to auto generate/construct the XML design files.

2. BIRT runtime / Report Engine

This component uses the Report Design File to generate reports by fetching data from the datasources mapped in the report and render the same in various formats like HTML, PDF, CSV, XLS etc. It exposes this functionality via its Report Engine API. Using this API, it can be embedded in any Java application.

Other components are

1. BIRT charting engine

This is used to design, generate charts in the reports. This is used in the design/report engines to provide the data visualization capabilities.

2. BIRT viewer

The Web Viewer can be used to generate and render reports, and also supports more interactive features such as table of contents, exporting report content to several formats, client and server side printing, as well as report pagination. It uses the BIRT runtime for the same.

BIRT Report Design

BIRT design engine constructs XML based report design – a single “.rptdesign” file which contains everything that is required for the report to execute. BIRT is based on a comprehensive report object model (ROM) that describes all aspects of the report. BIRT’s suite of report-specific XML elements and properties is called Report Object Model (ROM).

A BIRT design is structured as hierarchy of report elements. The term element simply means an object that appears as part of the report. The elements may be visual elements like text boxes, tables, lists, charts, cross tabs etc or non-visual elements like datasources, scripts, data sets etc.

Accessing Data in BIRT

In order to access data in BIRT reports, you need to typically work with the below :

1. Data sources– Typical data sources are relational Databases, XML files, POJOs etc. Data source elements hold all the connection details that are required to fetch data from the source. A BIRT report may have more than one Data sources.

2. Data sets – Typical datasets are SQL queries, stored procedures etc., which define what data is to be pulled into the report. A report may have multiple data sets, at least one dataset for every data source or even multiple datasets on a single data source. BIRT also allows us to join 2 datasets to create a “joint dataset”. This can be very useful to join data from disparate data sources or from non-relational data sources like xml files etc.

3. Data cubes – These are specifically introduced to support dynamic crosstabs. It allows you to specify the row fields and hierarchy,the column fields and hierarchy and also the measures to be shown in the body of the crosstab. It does not require the query data to already be in a multi-dimensional structure, the cube transforms the flattened data into hierarchical, analysis ready data.

4. Column bindings – The data from the datasets/data cubes can be displayed/used in various report elements like tables, charts, text boxes by creating “column bindings”. The Column bindings are an intermediate layer between the report elements
and the dataset fields. Typically a “column binding” is an expression which uses the dataset fields to formulate the data to be displayed to the end user in a report element. These are also useful to give descriptive names to the dataset fields, accessing data from functions or user defined calculations, it makes creation/updation of calculated fields quite easy.

Report Structure / Layout

A report document is typically structured as a series of sections like Report Title, multiple Detailed sections which may also contain groups (group headers, footers), summary sections. These sections may contain single or multiple report elements.

BIRT report elements broadly fall in 2 categories:

1. Elements that display/visualize data like Label, Text, Dynamic text, image, chart, crosstab etc
2. Elements that help organize other elements in to the section (Containers) like Grid, Table, Lists etc

A Grid is row/column format container, used to organize static elements like text, pictures into sections, similar to a HTML Table.

A Table on the other hand is used to display dynamic data in row and column format. Table has 3 types of rows, header, footer and detail row. It can be bound to a single dataset only.

A List is used to display dynamic data in any other format than rows and columns. It also contains header, footer and detail areas.

Content Formatting / Styling

BIRT provides a lot of formatting properties on the report elements which follows the CSS specification. The properties on various Elements depend on the type of element / type of data bound to the element as well.
Apart from this option to set formatting on every report element, BIRT also allows us to define Styles and applying the same to multiple elements. Styles may also be imported from CSS files or external CSS files can be linked to the report design.

BIRT also supports Conditional Formatting of the report elements by defining formatting rules.

BIRT has a concept of “Master Pages”. These are used to define the dimensions (page size) and styles like margins, orientation of the report pages. It has header and footer area where you can add report elements that you wish to see in the page header / footer area. You may use multiple master pages in the same report document.

This is just the most typical things, there is a lot more that can be done with BIRT and its highly customizable report elements and functionalities. Hope this gets you started on the journey to using BIRT reporting.

 
Shraddha Tambe | Helical IT Solutions

 
References:
1. BIRT – A Field Guide (3rd Edition)
2. http://www.innoventsolutions.com/birt-review.html
3. http://www.eclipse.org/birt/documentation/tutorial/
4. http://www.eclipse.org/birt/about/design.php
5. BIRT Developer Guide