Splitting a bar chart into multiple pages in iReport or JasperReport Server

Sometimes you may need to split the chart into multiple pages as the Category Expressions dataset is huge, which is resulting the graph to be in an unreadable format.

Therefore, you may want something like, the Category Expression dataset to be distributed in blocks of 10, as to display first 10 categories in page1, and so on.

Steps to achieve this:-

1.      Open your report in iReport. The report query is the main report query, in my sample I                                     

 will refer to two fields: cat_exp as category and val_exp as value.

    2.   In the Report Inspector right click on the report and select Add Report Group.                        

          It will open a New group wizard

          Enter Group Name: - chartsplitter

          Select Group by the following report object:- 

                                $V{REPORT_COUNT} - 1 - ( ($V{REPORT_COUNT} - 1) % 10   

      Click next and select Add the group footer.

3.      Drag and drop a chart from the Palette into the report designer on the group footer band. Select Bar Chart, follow the Category Chart Wizard:

v  Choose Main report dataset from Dataset Click Next.

v  Change Series expression to what you need (e.g. "Sample"), Click Next.

v  Set Category expression to $F{cat_exp}

v  Set Value expression to $F{val_exp}.Click Finish.

4.      Right click on the chart, click on Chart Data. In the Dataset tab set Reset type to Group, select as Reset group your group chartsplitter. Click close.

I have attached a sample output where I set the splitter value to 3 on a sample dataset.

Recommendation 1: Select the chart and scroll in the Properties panel to Range Axis Max Value Expression, set the max value for your chart there. This will ensure that all charts will have the same scale. In the attached sample I have set it statically to 100, it'd be also good to provide the max value via database query.

Recommendation 2: When you run the report you might notice that the last bar chart might look different as JasperReports/jfreechart scales the width of the bars depending on the number of categories. To have a static width for the bar charts you'd need to create a chart customizer which sets a fixed width to a single bar.

Create a cascading input control in JasperReport Server

In Business Intelligence reports, input control play an important role, input control gives flexibility to the customer to manage large amount of data, to filter the dashboard values based on the input controls values.

              With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list. For example suppose we have two input controls and names are Shipcountry and Shipcity, In this condition if we select Shipcountry then next input control Shipcity should show only those cities which belongs to that selected Shipping country.

 In this post, we will learn how to create cascading input controls in JasperSoft.

For this post we are using previous report which we had created, just add two new input control p_shipcountry and p_shipcity and add condition in where clause .

(shipcountry=$P{p_shipcountry} or $P{p_shipcountry} is null)

And

 (shipcity=$P{p_shipcity} or $P{p_shipcity} is null)

If you have noticed I have added null condition ($P{p_shipcountry} or $P{p_shipcountry} is null )with parameter, it gives you all records if you didn’t select any input control value.

In JasperSerever create a new input control with resource id p_shipcountry and you do not have to use the data source for this input control, just use the bellow query:-

                              SELECT DISTINCT shipcountry FROM orders ORDER BY shipcountry

Create second input control with resource p_shipcity  , which will be cascading input control, but you have to use the data source for this input control and use the bellow query:-

                              SELECT DISTINCT shipcity FROM orders

                              WHERE shipcountry= $P{p_shipcountry} ORDER BY shipcity

After creating these two input controls in JasperSoft, add the input controls in report by editing the report.

Add an input control in JasperReport Server

Now we have created the parameterized report in iReport and we have also created the input control parameter in JasperServer, after this we have to add this input control (which we just created in previous post) add with report.

To add a input control in report of JasperServer, Edit the report and select Controls & Resources button and click to Add Input Control…,

Then you will see Locate Input control page, Select an Input Control from the repository radio button and click on Browse button and chose the Input control.

In this screen, you can set the Display mode of the Input control, I suggest you to select “In Page” and submit the report.

Create an Input control in JasperServer

After creating the parameterized report in iReport, we are going to learn how to create an input control for a parameterized report in JasperServer.

Once you click Input Control, you will see a Create Input Control page, in this page you need to select the type of input control from drop down list, list contain so many types like Single Value, Single Select List of Values (radio), Multi Select List of Values, Single- select Query, and Multi-select Query. For the report which we developed in previous post we are going to create single-select query input control, all the query based input control such as single-select query fetch the data from database and shows as input control values in JasperServer.

Select Types- Single-select Query (radio)

            Prompt Text (required) – Select Year (Prompt for parameter)

            Parameter Name (required)-  p_year  (It’s case sensitive, it should be same as iReport parameter name )

Next, Enter Query Name then Next, select Do not link a data source and Next, In this step we have to define a query for input control.

                        SELECT DISTINCT year(ORDERDATE) year FROM ORDERS

Then Next and set Parameters Value:-

Value column- year (column of a query)

Visible column- year (column of a query) and click “add” and submit.

Parameterizing the report

Now I am going to parametrize the report so we can filter the records at the run time of the report, suppose we want to filter the data by year and we want to see the chart for only a singal year,  we can achive this by follwing these simple steps:-

Open the report in which you want to add paramter(I am using the same report which we had developed in the previous post) and edit the query.

Click on the “New paramter” button  and add parameter name and value expression, in the Default Value Expression section we can set the default value for paramter.

Add the Where clause in the query

select count(*) orders,

       year(ORDERDATE) year,

       MONTHNAME(ORDERDATE) month,

       CONCAT(MONTHNAME(ORDERDATE) ,

       right(year(ORDERDATE),2))monthyear

from orders

where year(ORDERDATE)=$P{p_year}

group by year(ORDERDATE), MONTH(ORDERDATE)

order by year(ORDERDATE), MONTH(ORDERDATE)

And click on the preview tab, now you should get the prompt for parameter , you can enter the years or use default value.

As you can see in the final report output, report has only year 1996 data.