Friday, 26 July 2013

Basic Chart Properties - 3 (Jasperreport charts)



In the previous post, we saw how to concatenate two fields, change the chart properties of any text field, static filed and charts properties such as color of chart, add background color form Property Pane in iReport but after this post, you will be able to add some more properties than the ones visible in the Property Pane.

How to add additional properties

To add additional properties, select the element on which you want to add the property and select the “Properties expression” in Property Pane.




Once you click on Properties expression click on “Add” button, you will get “Add/modify property” window.
.
Just add the Property name that you want to use as in the above screen shot in this report I am using tick interval property and trying to set the tick intervals for value axis.
net.sf.jasperreports.chart.range.axis.tick.interval

Property specifying the interval between 2 consecutive ticks on a range value axis.
If the  net.sf.jasperreports.chart.range.axis.tick.count property is set, this interval will be neglected.
If it is not set, the JFreeCharts engine will use its own defaults to instantiate the axis.
By default, it is not set.

List of Properties :-


A very large set of special properties are now available in the current version. Over the upgrade history, many new properties have enriched the list.
The full API's are available at http://jasperreports.sourceforge.net/config.reference.html
                                        http://jasperreports.sourceforge.net/api/index.html.



Sunday, 9 June 2013

Basic Chart Properties - 2 (Jasperreport charts)

By changing the chart properties, you can present the data in better format and make the chart more visualize. So in this post I will try to change some chart properties of the same chart that I had created in my previous post, these are some basic chart properties:-

               Chart before applying properties                                  Chart after applying properties


  • As you can see in first chart numbers are not there, to see the values of each column uncheck the Show Labels check box and check again after unchecking.
  • To change the colors of bars add the colors in Series Colors properties.
  •  You can also add the Category axis (X-axis) and Value axis (Y- Axis) labels in Category Axis Label Expression and Value Axis Label Expression property.
  •  To move the legends from bottom to top change the Legend Position to Top.
  •  X and Y Offset values are to decrease or increase the 3D effect.
  •   By adding text in to Title Expression, you can add the chart title.
  • To change the background color of chart, go to chart property and add color in Backcolor property and check the Opaque check box.



                                                

Basic Chart Properties - 1 (Jasperreport charts)

Chart properties:-
By changing the chart properties, you can present the data in better format and make the chart more visualise. So in this post I will try to change some chart properties of the same chart that I had created in my previous post, these are some basic chart properties:-

      Rotate category axis(X- axis) label :  -
      To rotate category axis, go to the chart property tab and change the
                  Category Axis Tick Label Rotation / Label rotation to            -50.0
      (You can change it according to your requirement)
       
       
              


        Convert data format in special format in iReport : -
       Sometimes you may need to change the date format to some other date format , in case if you
       are using date column in category axis and you want to show the dates in “MMM-yy” format.
       To change the date format of category axis dates, right click on chart, select chart data tab, and
       add this date format
                                  new SimpleDateFormat("MMM-yy").format($F{DataMonth})
       Where $F{DataMonth} will be date column

        Concatenate two columns in iReport :  
      This show you how to concatenate two data filed in iReport
                                            $F{Firstname} + “ - ” + $F{Lastname}
       However, both data should be same data format like if you want to concatenate string and
       integer data filed then first convert integer field to string function.
      To convert integer field to string field add .toString() function.
                                           $F{MONTH} +"-"+$F{YEAR}.toString()
For example:-

I used this in the Series expression of the chart.

Sunday, 19 May 2013

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.


Saturday, 4 May 2013

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.



Thursday, 2 May 2013

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.