Playing with JasperReports - Part 2 Simple Report ( Service Summary by Service)

   Hello folks, here I gonna teach how to create a report using JasperSoft Studio, the Report I gonna create is Service Summary by Service, using Spectrum as datasource.

The first step is to plan your report, and for that try to answer these questions:

• What my user are going to provide as an Input, ie , initial date, end date, globall collection ?

In this tutorial i expect that the user provide the initial date, the end date and the services he want to exhibit on the report

• I need to make any query on the datasource to provide options for the user input?

Yes, I need to provide the list of services for the user.

• What data I want show to the user?

I want to show the service name, the percentage available and unavailable, and the outages cont

• The user Input provide any constrain to the data gathered?

Suppose that some outage ends later than the end date, for correctly calculate the percentage i must to truncate the outage end date, and do the same when the outage is ongoing

• How to gather the data ?

Here I'm going to use this doc for construct the query:

'reporting' Database - CA Spectrum - 10.2 and 10.2.1 - CA Technologies Documentation 

• First I need to gather the available Services in order to show to the user as an Input Control, and for that i gonna query the model table, here I want the Model Handle and the Model Name, and I'm going to show only the Model Name for the user and pass the model Handle for the report

SELECT reporting.model.model_h ModelHandle,
     reporting.model.model_name ModelName,
FROM reporting.model
WHERE 
      reporting.model.mtype_h = 0x1046f

• Assuming that I already have the ModelHandle I gonna use the following construction for the query:

SELECT reporting.sm_monitors.`monName`,
     reporting.sm_monitoroutages.`startTime`,
     reporting.sm_monitoroutages.`endTime`
FROM reporting.sm_monitoroutages
     INNER JOIN reporting.sm_monitormaps ON 
      reporting.sm_monitormaps.`monOutageID` = reporting.sm_monitoroutages.`monOutageID` 
     INNER JOIN reporting.sm_monitors ON 
      reporting.sm_monitors.`monitorMH` = reporting.sm_monitormaps.`monitorMH`

The second step is to create the Input Controls, and the query's.

Follow the steps on Playing with JasperReports - Part 1 Input Controls and create one query selecting the services and their model handles ( the first query showed on this report), and one InputControl with the following specifications:

• Type: Multi-Select Query
• Prompt Text: Services
• Parameter Name: MonitorMH
• Value Column: ModelHandle
• Visible Column: ModelName
• Data source: spectrum_ds

For space reason I'm not going to enter in details about how to set-up JasperSoft Studio i think it is best to dedicate another post for it.

In this stage  I recommend that you start one blank report created and with One Empty Record - Empty rows as data source, because it easier to create the report layout this way.

The jaspersoft studio might be that way:

Here we have one very important detail the report BANDS , Documentation support , and one think that you might know is

Now play a bit with the basic element and create your report layout,

And when you preview it became like this:

Now that the report layout is done let's collect the data, left click outside the report area, that gonna unselect whatever is selected, now on the lower left side right click on Parametes and Create Parameter

Now go to the lower right corner and configure the parameter

The Parameter name MUST be the same as the Input Control, now create 2 more Parameter, initDate and endDate. with Class java.sql.Timestamp, now when you preview your report the user questions will be there:

Now on the report edition left click out of the report and on the lower right corner scroll down a click on Edit query, filter and sort options

On the top select the Spectrum data source and we are ready to write our query

SELECT reporting.sm_monitoroutages.`startTime`,
     reporting.sm_monitoroutages.`endTime`,
     reporting.sm_monitors.`monName`
FROM reporting.sm_monitoroutages
     INNER JOIN reporting.sm_monitormaps ON 
      reporting.sm_monitormaps.`monOutageID` = reporting.sm_monitoroutages.`monOutageID` 
     INNER JOIN reporting.sm_monitors ON 
      reporting.sm_monitors.`monitorMH` = reporting.sm_monitormaps.`monitorMH`
WHERE
     $X{IN , reporting.sm_monitors.`monName` , MonitorMH}
     AND $X{BETWEEN ,  reporting.sm_monitoroutages.`startTime` , InitialDate , endDate}

Here we use the syntax $X of jasper for include operation on query based on parameter the first i select all the monitors the user chose and the second i select all the outages with statTime between initalDate and endDate

Click on Read Field and the click on the Sorting tab and Add the field monName, now click on OK

The next step is the creation of groups, on this report we are going to group based on the Monitor Name, to do that right click on the report title in Outline tab

Click on create Group, selecting the monName as Report Object and naming the group as Services

Uncheck Add Group Header.

On the next steps we're going to create some variables, to calculate the correct endTime, considering an ongoing outage and an outage that end after the report endDate, on OutLine click on Variables and create variable

Configure the Variable as the image bellow

And on expression use this:

($F{endTime}.equals( null )) || $F{endTime}.after( $P{endDate} ) ? $P{endDate} : $F{endTime}

Repeat the process to create the following variables:

With the variables ready its time to fill our report, in this tutorial I gonna use the group footer band, the Column Header Band, and delete the other bands, so the report design are going to look like this:

Right click on the Services Group Footer -> Arrange in container -> Horizontal Layout,do the same to the Column Header Band.

Drag and drop the 3 Static text element to the Column Header, they are going to be the table header, and fill with the header data

Drag and Drop the monName Field with no calculation function, and than one Text Field Element, and the Variable Service_Availability_perc to the Services Group Footer Band

Now double click on the Text Field and put the following expression:

$V{Service_Downtime_ms}/(24*3600*1000) + " d " + ($V{Service_Downtime_ms} - (24*3600*1000)*($V{Service_Downtime_ms}/(24*3600*1000)))/(3600*1000) + " h " +  ($V{Service_Downtime_ms} - (3600*1000)*($V{Service_Downtime_ms}/(3600*1000)))/(60*1000) + " m " +  ($V{Service_Downtime_ms} - (60*1000)*($V{Service_Downtime_ms}/(60*1000)))/(1000) + " s"

And your report is done

From here all you have to do is make your report prettier,  and in the next tutorial i'm gonna teach how to set up JasperSoft Studio, and publish the report. and I'm planning to create another tutorial teaching some tricks with Styles.

That's all for now, If you have any comment or suggestion please fell free to comment