Schedule a Maximo report with date parameters

Posted by on in Uncategorized
  • Font size: Larger Smaller
  • Hits: 1849
  • 0 Comments
  • Subscribe to this entry
  • Print

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 theinventory_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: [2]

 

 

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 [3]

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 stringand 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. [4]

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) 

And with a date value (the dates entered were 2013–12–01 and 2013–12–05):

select po.ponum, po.description, po.orderdate from po where po.siteid='BEDFORD' and po.orderdate >= { ts '2013-12-01 00:00:00.000' } and po.orderdate <= { ts '2013-12-05 23:59:59.998' } 

The latter SQL statement shows how Maximo and BIRT passed the given date value and transformed it to a full date/time stamp using the MXReportSqlFormat.getStartDayTimestampFunction and theMXReportSqlFormat.getEndDayTimestampFunction functions.

You download the example report here: Sample_PO_List.rptdesign.

Got any questions? Feel free to hit me up on Twitter at @MyGeekDaddy.


  1. In the example for the Multi Context example, I’m assuming the date selection method is being added to a new report.  ↩

  2. If you’re modifying an existing report, you could just deselect the ‘required’ checkbox for a given date parameter.  ↩

  3. If modifying an existing report, the variables should already be listed.  ↩

  4. Technically it passes the 1st of the next month, which is why the SQL selection uses “<” rather than “<=”.  ↩

  5. This report fits the Type 3 definition (see pg 28), so make sure to check the box ‘Use Where Clause’.  ↩

0
Jason has been in the engineering and reliability field for over 20 years. He currently leads capital expenditure and reliability projects for a large food manufacturer located in southern Minnesota. Jason has been working with the IBM Maximo application for over 10 years, focusing on business process development and reporting
Comments are not available for public users. Please login first to view / add comments.