How to schedule a date parameter report

One of the challenges I typically have when creating new reports is making sure it can be used as many ways as possible. One of the toughest is giving a report the flexibility for a date range selection but also being able to use it as a scheduled report. This post will show one way to do both.

Normal Date Selection

Using an out of the box report with a date selection parameter, like in the inventory_transactions_adjustments.rptdesign, the date parameter is required and the Open Method sets a date selection based on the database type.

if(params["startdate"].value) {             
    if(maximoDataSet.isOracle()) {      
        params["where"]+= " and to_date(to_char(invtrans.transdate,'YYYYMMDD HH24:MI:SS'),'YYYYMMDD  HH24:MI:SS') >= "       
        + MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);      
    }       
    else if(maximoDataSet.isSQLServer()) {          
        params["where"]+= " and invtrans.transdate >= "      
        + MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);      
    }       
    else if(maximoDataSet.isDB2()) {            
        params["where"]+= " and timestamp(invtrans.transdate) >= "          
        + MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);      
    }       
}

While the Maximo user can select the date range for the information the report will return, it's difficult to get a regular update (e.g. a weekly summary) using the same report. In most cases I previously would have recreated the report so the date range was fixed to an agreed upon range so it could be scheduled to run on a recurring basis. Now I've got two reports, doing basically the same thing, which typically ends up with a phone call from a confused user asking which one to use.

Fall Back Date Selection

Looking at the conditional statement above, we can see the BIRT report uses if/then statements in the Open Method to determine which statement should be used, based on the SQL database environment. I use the same technique to get a fall back date selection in my enterprise reports. Below are the steps in a basic custom report to show a list of purchase orders, based on the record's order date.

Just like in other reports that use a date selection, the report needs to have a parameter to pass the date value to the selection query. [1] In my example there are two parameters in the report:

startdate
enddate

One of the differences when setting up these parameters is to not set them as required:

By not setting the parameter checkbox, Is Required, it will allow the report to be scheduled and pass a default date/time value to the parameter because the Maximo UI will not be triggered to have to have a date value on the Request Page.

The next step is to update the Open Method by adding two new variables

var start_date = new String();
var end_date = new String();

Then add the conditional statements for the date range selections:

if (params["startdate"].value) {
    start_date = " and po.orderdate >= " + MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]); }
    else {
    start_date = " and po.orderdate >= dateadd(month,datediff(month,1,getdate()) +0,0) "; }

if (params["enddate"].value) {
    end_date = " and po.orderdate <= " + MXReportSqlFormat.getEndDayTimestampFunction(params["enddate"]); }
    else {
    end_date = " and po.orderdate < dateadd(month,datediff(month,1,getdate()) +1,0) "; }

Using the StartDate statement as an example, what I've done is set the condifional statement to fall back to a default date range in the event a date was not entered when the report is run. So when the conditional statement if (params["enddate"].value) finds no value being passed from the startdate parameter, it will pass the SQL string and po.orderdate >= dateadd(month,datediff(month,1,getdate()) +0,0) to the report's selection query.

If a date value is given, so the parameter startdate has a value, the conditional statement will process the date value in the and po.orderdate >= " + MXReportSqlFormat.getStartDayTimestampFunction(params["startdate"]);. The same thing happens for the end date range value.

The last part is adding the two variables into the SQL selection query. Due to the way the conditional statements are written, I can just add the variables to the end of the SQL statement in the Open Method like this:

sqlText = "select po.ponum, po.description, po.orderdate from po"
+ " where" + params["where"]
+ start_date
+ end_date
;

Notice that I just added the variables to the end of the report. The wording used in the conditional statements allow the variables to be passed as additional and clauses to the SQL query.

So the full Open Method looks like this:

Registering Report

When you register the report in Maximo, Maximo will make some assumptions on how you want to have the report deployed. You'll need deselect the reqiured field for the date parameters and add the datelookup, similar to this:

Set the order sequence for each parameter and generate the request page. [5]

Running the report

Now when a user runs the report, they'll be presented with a normal request page.

Note the date fields are empty and they don't have required field star. If the user wants to select dates, they can use the calendar date lookups and run the report as most other date selection report. If the dates are left empty, the report automatically pulls in all records from the 1st of the month to the end of the month.

Looking at the debug log file, you can see the SQL selection when running the report without a date value in the parameters:

select po.ponum, po.description, po.orderdate 
from po 
where po.siteid='BEDFORD' 
and po.orderdate >= dateadd(month,datediff(month,1,getdate()) +0,0)  
and po.orderdate < dateadd(month,datediff(month,1,getdate()) +1,0)

Comments

Top