RENATO ALMEIDA DE OLIVEIRA

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

Blog Post created by RENATO ALMEIDA DE OLIVEIRA on Jul 22, 2017

   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.

 

JasperSoft Studio aren't supported for Spectrum, so do it by your own risk

       

 

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`

NOTE THAT THERE AREN'T ANY CONSTRAINS IN THIS QUERY BECAUSE LATER ON THE TUTORIAL I'M GOING TO EXPLAIN HOW TO USE INPUT CONTROLS ON QUERY'S

       

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

 

IT'S VERY IMPORTANT THAT THE PARAMETER NAME IS THE SAME THAT THE ONE INCLUDED ON THE REPORT

    

 

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

THERE ARE ONE DETAIL BAND FOR EACH ROW OF YOUR QUERY

    

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:

 

NAMEValue Class NameCalculationExpressionIncrement typeReset Type
Outage_Downtime_msjava.lang.LongNo Calc Func$V{endTimeCorrected}.getTime()-$F{startTime}.getTime()NoneReport
Service_Downtime_msjava.lang.LongSum$V{Outage_Downtime_ms}None[Group] Services
ReportTime_msjava.lang.LongNo Calc Func$P{endDate}.getTime()-$P{InitialDate}.getTime()NoneReport
Service_Availability_msjava.lang.LongNo Calc Func$V{ReportTime_ms}-$V{Service_Downtime_ms}None[Group] Services
Service_Availability_percjava.math.BigDecimalNo Calc Funcnew BigDecimal($V{Service_Availability_ms}.floatValue()*100/$V{ReportTime_ms}.floatValue()).setScale( 2, java.math.RoundingMode.HALF_UP )None[Group] Services

 

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

Outcomes