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

Creating a simple BIRT report

Creating a simple BIRT report

This is a small tutorial to help you get started with BIRT report development. We will setup the BIRT report designer and create a simple tabular BIRT report accessing data from a MySQL database.

Below are the steps –

Getting the BIRT report designer
You can download the BIRT report designer at http://download.eclipse.org/birt/downloads/ .

It provides various download options. I went with the “All-in-One” download package which included eclipse with all the necessary BIRT plugins preinstalled. You just need to unzip the archive file and then start the eclipse application.

Adding required drivers for DB connection
If you want to add any drivers for your database connections to be used in the designer, copy the driver jars to the below folder in your eclipse installation before you start eclipse –

<eclipse home>\plugins\org.eclipse.birt.report.data.oda.jdbc_X.X.X.vxxxxxxxxxxxx\drivers

In this case, I added the mysql driver jar file.

Creating a project
Once you have the report designer all set up, create a new project in eclipse with the “new project wizard”. You can see a new Projects Category appearing in the wizard selection list – “Business Intelligence and reporting tools”, select “Report Project” option under it.

Fig 1 new  report project

 
Adding BIRT report to the project
Now add a new report file (rptdesign) to the project. Right click on the project in the navigator window and click new à report, give an appropriate name to the file which will contain the report definition of the BIRT report. It’s a XML based report design – a single “.rptdesign” file which contains everything that is required for the report to execute.

Fig 2 New report file

Designing the report
When the “rptdesign” file is open, in the main window, it shows 4 tabs. It shows a canvas in the first tab, the visual layout view of the report – where you can drag and drop various items from the palette to design the report. The other tabs are the master page, scripts tab for you to add any custom scripts, the XML source tab to view the source of the design file as it gets generated.

In the “Outline” window, you can see the entire list of things that will define your report. We will now use this window to start adding components to our report.

1. Creating a data source

Right click on the “Data Sources” in the Outline window and select “new Data Source” to open the wizard. It lists all the different types of data sources that can be created. For this tutorial, we select JDBC Data source and then fill in the required connection details to create the data source –
Fig 3 Data source proerties

2. Creating a Data Set

A dataset is basically the query that would fetch the data to be displayed in your report. You can associate a dataset with your tables, charts and any other report elements.

Right Click on the “Data Sets” in the outline, to define your dataset. The wizard allows you to first select one of the datasets and then select the “Data Set Type” which in our case would be a SQL query. Write your SQL query text, which in our case is a simple select statement –

Fig 4 Query for Dataset

Once finished, all the fields start showing up under the dataset in the outline

3. Adding a table to the report

Now drag a “table” component from the palette and drop it on the canvas. When you do, it asks for a dataset to associate with the table. Select the one we created in the step above. It will display all the fields from the dataset from which you select the ones to be displayed in the table. When you click finish, you see a table on the canvas, with 3 sections – Header, Detail and Footer.

The detail section columns will be automatically bound with the various fields we selected and header labels will also be set.

Fig 7 Table

You may use the “properties window” to format the table look and feel. I have added some borders and changed the font etc.

Fig 6 Properties Window

We are more or less done with a basic report. I also have changed the page heading for the report, by navigating to the “Master Page” tab and changing the same there.

Fig 8 Master Page

Previewing the report

There are various types of preview available –

Fig 9 Birt Report Previews

When you preview in web viewer, the BIRT report viewer is used to render the report.

Fig 10

With this, our simple BIRT report is done!! More posts on BIRT reporting to follow, stay tuned !

Shraddha Tambe | Helical IT Solutions

You may also like to read –
Introduction to BIRT (Business Intelligence and Reporting Tools)