Tag Archives: Maximo

Re-usable Date queries in Maximo

One of the things I try to re-enforce with Maximo users is striving to re-use their work, especially queries, as much as possible. A common task in Maximo is to find and sort information related to dates – order date, approval date, status date, etc. A common problem users come back to me is when they make a really useful query, but find that the query isn’t working as expected because the query was hard coded with a date range. Now when they try to use that query as part of their normal workflow, or in a QBR report, the information comes back less than useful. This post will go over some date related SQL statements that will make your Maximo queries even more powerful.

One of the common tasks users need to do is report, or list, a set of records based on certain date range, say the last 24h. A common frustration I see with users is trying to get the query to be just the past 24h, like this:

2012–01–01 00:00:00.000 to 2012–01–01 23:59:59.999.

But you know what is just as good? Adding 0.001 seconds to that query and run the date range from

2012–01–01 00:00:00.000 to 2012–01–02 00:00:00.000

Running the query from 12:00am to 12:00am gets you everything you’d want from 12:00am to 11:59pm. In the event you have a transaction that lands exactly on 00:00:00.000, go buy a lottery ticket, you are just that lucky. Maximo allows the use of several SQL functions, including the use of getdate(), dateadd, convert, and datediff. Using these date modifiers allows queries to now have rolling date ranges that will change as the calendar moves on. Here are some examples of rolling date queries you can use in Maximo.

Current Date to Midnight
Change current date to midnight of current date:

dateadd(day, 0, convert(varchar, getdate(), 101))

To change the day the query will be set to midnight to, just change the integer after the ‘day’ field. For example, to return a date of midnight yesterday, the date query would be:

dateadd(day, −1, convert(varchar, getdate(), 101))

And to get 7 days ago, just do:

dateadd(day, −7, convert(varchar, getdate(), 101))

Assuming today is 8/29/12, then the query above would return ‘2012–08–22 00:00:00.000’.

Current Date/Time to Just Date
Change the current date/time stamp to just a date format of mm/dd/yy

select convert(varchar, getdate(), 101)

Set First Day of Previous Month
Change the current date/time to yyyy-mm-dd 00:00:00.000 and have the date be the first day of the previous month. So if the current date is 4/21/11, this SQL statement would return 3/1/11.

dateadd(month,datediff(month,1,getdate())–1,0)

Set First Day of Current Month
Change the current date/time to yyyy-mm-dd 00:00:00.000 and have the date be the first day of the previous month. So if the current date is 4/21/11, this SQL statement would return 4/1/11.

dateadd(month,datediff(month,1,getdate())+0,0)

Just like the adjustment in the midnight date query, adjust the integer at the end of the query (….th,1,getdate())+1,0) will shift what first of the month that will be returned, this case it would return 5/1/11 based on the previous criteria.

Example query
The following query is used to show all the active purchase orders for a plant site in the last 24h.

(orderdate <= dateadd(day, 1, convert(varchar, getdate(), 101)) and historyflag = 0 and siteid = (select defsite from maxuser where userid= :user ) and orderdate >= dateadd(day, –1, convert(varchar, getdate(), 101)) )

So if this query is run at 8:43am on 6/12/12, the query will look between the following dates:

2012–06–11 00:00:00.000 to 2012–06–13 00:00:00.000

Now for some users this will be a little unsettling because it looks like the query is actually searching over 2 days, not just the last 24h. But remember, we’re treating 2012–06–13 00:00:00.000 = 2012–06–12 23:59:59.999. A lot of users will use a shortcut and use the following query for the last 24h by using getdate() and getdate()–1.

(orderdate <= getdate() and historyflag = 0 and siteid = (select defsite from maxuser where userid= :user ) and orderdate >= getdate()–1 )

So what happens if you run this report at 3:17pm for a quick report for the plant manager? The second query will definitely be fixed on just the last 24h, but it will also miss any PO created before 3:17pm yesterday. That’s why getting users to look at date filters based on midnight (00:00:00.000) to midnight is such a critical culture change.

References
Here are some references for additional details related to some of the funcitons used in the query statements.

MSDN SQL help for GETDATE

MSDN SQL help for DATEADD

MSDN SQL help for DATEDIFF

MSDN SQL help for CAST & CONVERT

 

 

Bonus SQL Statement
Use the following query to set the date to be just before midnight today, which will yield a 23:59:59.999 time stamp.

select dateadd(millisecond,–1, (dateadd(day, 1, convert(varchar, getdate(), 101))))

Implementing a Maximo 7.5.0.3 BIRT fix in 7.1.1.x

IBM released fix patch 7.5.0.3 for Maximo on 7/20/12. As part of that patch there has been a flurry of documentation released too. Of particular interest to report writers is the update to the BIRT engine used by Maximo, which has moved from 2.3.2 to 3.7.1 (see details here). One of the new technical documents released this week, ‘Update Report Utilities – Starting with Version 7.5.0.3‘, includes information related to pagination fixes can be automatically applied to Maximo 7.5.0.3 reports. But the best part is some of these fixes are retroactive back to 7.1.1.7.

When a user creates and saves a QBR report, Maximo internally creates the metadata of a BIRT rptdesign file into the Maximo database. Maximo administrators can extract the QBR report using the following command:

exportreport report [App Name] [Report Name]

So to extract the current copy of the WO Print report, an administrator would execute the following command in the ..\maximo\reports\birt\tools folder.

exportreport report wotrack woprint.rptdesign

IBM has a several good articles regarding this on the Maximo Reports Wiki.

In the PDF document previously mentioned, there is information related to a utility in Maximo 7.5.0.3 to automatically fix a pagination issue with long running list reports. This pagination fix now appears to have been in place in QBR report since 7.1.1.7. QBR reports created after version 7.1.1.7 now include the following code in the beforeFactory method.

if ( (reportContext.getParameterValue("usepagebreaks") == "false") || reportContext.getOutputFormat() == "pdf" ) {
    // Give each table in the report a name, and add the names to the list below, e.g. ["mainTable", "childTable1"]
    var tableListing = ["dataSet_report_id13#"];
    for each(var tableName in tableListing) {
        var table = reportContext.getReportRunnable().designHandle.getDesignHandle().findElement(tableName)
        if (table != null) {
            table.setProperty("pageBreakInterval", 0);
        }
    }
}

This code is what would be applied to all Maximo 7.5.0.3 reports automatically with the update utility, but this code can be applied to any report in Maximo 7.1.1.7, and later. Applying the code will result in the same pagination fixes mentioned in the PDF for 7.5.0.3. Simply copy the code above and paste it into the beforeFactory method on any list report that has experienced paging problems when converting from the HTML viewed when the report is run to the PDF that is saved to locally to your hard drive or emailed.

Feel free to drop me a tweet to @MyGeekDaddy if you have any questions.

Fixing my top 3 annoyances in Maximo BIRT Designer

As I’ve used BIRT more and more, there are things with BIRT that I’m finding I truly enjoy. Like the apparent magic of how parameters get passed from Maximo to the BIRT reports. I’ve also begun to see a few annoyances with BIRT too. These aren’t show stoppers, just minor things that I know I’ll have to fix when I either update an out of the box report or promote a QBR report to enterprise wide usage.

The top three annoyances I have in BIRT Designer are:

  1. Default images at the top of all reports and report templates.
  2. The alignment of the “Page of Total Pages” at the bottom of reports.
  3. How all reports default to right alignment and not left alignment.

Out of the box, BIRT for Maximo has a standard set of templates to use when creating reports. These templates save a lot of time when creating new reports (trust me, I tried to do it from scratch and it’s not fun). The default WO Print report looks like this:

Screen Shot 2012-07-21 at 3.54.38 PM

The report looks great, right? Want to change the default logo images? No problem, IBM has a great KB doc on how to change the default images (IBM: Change Default Logo). But what if you want to reclaim that space? Here are the steps I take to get that space back.

  1. Open the report you want to alter in BIRT Designer. The changes shown below were made on the default woprint.rptdesign file.

  2. Once the report opens, click on the ‘Master Page’ tab in BIRT Designer.
  3. When the Master Page opens, click on the outline around the images until you see the pop-up that says ‘Grid’.

    Screen Shot 2012-07-21 at 3.59.42 PM

  4. Click on Grid element pop-up and this will select the entire Grid element and all elements inside the Grid.

    Screen Shot 2012-07-21 at 4.55.43 PM

  5. Find the ‘Visibility’ property in the Property Editor and check the box ‘Hide Element’ to hide the entire Grid all the time. Just checking the box will make any selected elements hidden all the time.

    Screen Shot 2012-07-21 at 4.07.54 PM

  6. Click in the middle of the blank space of the report in the window above the Property editor to select the overall report. This will change the options available in the Property Editor.
  7. Now select the Margin tab in the Property Editor and change the Top Margin to 0.00 inches.

    Screen Shot 2012-07-21 at 4.20.53 PM

  8. Now when you preview the report, the top images are gone and the space is reclaimed for your report data.

Screen Shot 2012-07-21 at 4.26.37 PM

The next post will show how to fix the alignment of the page numbering at the bottom of reports.

Bonus Tip: Update your BIRT template files with these changes so when a new report is created, the images are already removed from the header.

One quick note, this post (and the next two) are in no way to disrespect Pam Denny and her team at IBM. Maximo and BIRT are such versatile applications that if these were fixed for me they’d probably annoy someone else. These types of settings can be fixed by the end user, which is what makes the Maximo framework so amazing. 

Have questions? Feel free to drop me a tweet at @MyGeekDaddy.

How to run BIRT reports against "no print" Maximo apps

In the course of administering Maximo, system administrators eventually need to run queries against Maximo databases for support tickets or other administrative functions. Most SQL query tools (e.g. SQL Query Analyzer) output those queries into simple text files. Wouldn’t be nice to have to just run a BIRT report for all the current values? Yep, just log into Maximo and go to System Properties and run a BIRT report on all the current property values.

Oh wait… System Properties doesn’t have the QBR tool and it doesn’t have any printing controls. Neither does Logging, Actions, and other nitty gritty data needs. So now what?

Here’s an idea on how to run a summary report, in this example the current Maximo system property values, from another application. This can be applied to almost any other system report you may need to run. 

Step 1: Break out your SQL query tool to make sure you have your query properly setup to collect all the information you want. In this example, to collect Maximo system property details, the following was used:

SELECT MAXPROP.PROPNAME, MAXPROP.DESCRIPTION, MAXPROPVALUE.PROPVALUE, MAXPROP.MAXIMODEFAULT, MAXPROP.ENCRYPTED, MAXPROP.GLOBALONLY, MAXPROP.INSTANCEONLY, MAXPROP.LIVEREFRESH, MAXPROP.SECURELEVEL, MAXPROP.USERDEFINED, MAXPROPVALUE.CHANGEBY, MAXPROPVALUE.CHANGEDATE FROM MAXPROP JOIN MAXPROPVALUE ON MAXPROPVALUE.PROPNAME=MAXPROP.PROPNAME

Step 2: Open up the BIRT Report Designer and start creating a new report. Click
File –> New –> Report and select a the template of your choice. Since the number of fields isn’t very extensive, the example report used the Portrait List Template. (Don’t have portrait templates? Download the BIRT portrait templates from IBM ISM Library – Portrait Templates)

Step 3: Create the Open and Fetch scripts to pull the data from Maximo.

Open script:

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select logger, logkey, loglevel, active, appenders, maxloggerid from maxlogger "
// Include the Maximo where clause
+ " where " + params["where"]
;

maximoDataSet.setQuery(sqlText);

Fetch script:

if (!maximoDataSet.fetch())
    return (false);

// Add a line for each output column
// The specific get method should match the data type of the output column.
row["name"] = maximoDataSet.getString("name");
row["parent"] = maximoDataSet.getString("parent");
row["child"] = maximoDataSet.getString("child");
row["whereclause"] = maximoDataSet.getString("whereclause");
row["cardinality"] = maximoDataSet.getString("cardinality");

return (true);

Step 4: Add the output columns to the dataset

image

Step 5: Add the data columns to the report and format to your specific style/standards.

image

Step 6: Test the report and make sure it runs in the Report Designer.

Step 7: Now that the report works and is ready to deploy, you need to make one subtle change. By default, BIRT reports for Maximo will check for parameters based on where the report is run from. Update the Open script and remark out the where clause statements. This will cause the report to ignore any parameters that get passed back to the report from Maximo.

maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();

var sqlText = new String();

// Add query to sqlText variable.
sqlText = "select logger, logkey, loglevel, active, appenders, maxloggerid from maxlogger "
// Include the Maximo where clause
//+ " where " + params["where"]
;

maximoDataSet.setQuery(sqlText);

NOTE: If you skip this step the report will run in Maximo, but it will return no data.

Step 8: Deploy the report to an application typically reserved for system admin access, for example Database Configuration.

Step 9: Run the report inside Maximo for final testing.

Voila! You can now run reports against applications that don’t natively support QBR or BIRT reports.

 

Bonus: Copy of BIRT report can be downloaded from here: System Property BIRT Report.

How to embed .js files into BIRT report for Maximo

One of the most unique features of BIRT is its ability to use other programming languages to do complex tasks within the report file. Take a look at the forums on BIRT-Exchange.com and you will see examples of people using PHP, java, javascript and more. This post will show how you can embed a javascript file (.js) into a Maximo BIRT report to do complex tasks.

One of the common tasks a report writer will get asked to do is make sure the report looks good. The goal is to have the information laid out cleanly so the end user will clearly see the information. A typical request is to transform a text string’s case, say from upper case to lower. That’s easy to do in BIRT, just apply a string transform on a dynamic text field like this:

BirtStr.toLower(row["description"])

Now the beauty of BIRT means it can also use the javascript method to apply a lower case string transform too. Like this:

row["description"].toLowerCase()

So another common request is to change a text string from upper or lower case to proper case (UPPER CASE –> Proper Case). Easy, just use a dynamic text field and apply a string transform again.

BirtStr.toProper(row["description"])

Guess what? BIRT doesn’t have a proper case text transform function. Using the string transform above will cause the report to error out and not show any information in that data row. A new string transform function, in an external javascript file, will need to be created to do the text transformation requested.

You’ll find plenty of examples searching the web on ‘javascript change string to proper case‘ for a javascript example to do a proper case text string transformation. The code that I’ll use was provided by Tuan on stackoverflow.com.

String.prototype.toProperCase = function () {
return this.replace(/\w\S*/g, function(txt){return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();});
};

Copy/paste the javascript into a text file and save it as ‘toProperCase.js’ in the Library folder of the BIRT Report Designer application. The path should similar to this:

x:\birt_232\reports\birt\libraries

Now open the report file that will use the javascript file in BIRT Report Designer. On the Outline tab, click on the report file name. This will bring up the general properties of the report design file.

image

The 6th tab under the ‘Property Editor’ tab lists ‘Resources’, click on this to open where the .js file is associated. 

image

Notice there is an option to add/include a JAR or javascript file. Click the ‘Add’ button for the javascript file and the ‘toProperCase.js’ file will be listed as an option. If it’s not listed, double check where the .js file was copied to from the steps above. Once attached, save your report file to update the file.

To apply the text string transformation function, just append the text transform to any value in a dynamic text field with .toProperCase(). For example:

image

An example of the text transform is shown below. Text in black has been left unchanged, while the text in blue has been applied the .toProperCase text transform.

image 
The next step is to get the .js file to work with the BIRT report inside Maximo. First the .js file has to be compressed into a zip file. This zipped file will be added as part of the import process into Maximo.

Next, log into Maximo and create the report object (skip this step if you’re going to re-import an existing report). Save the new report object and then click on Select Action –> Import Report. When you import the rptdesign file, you will also import the zipped .js flle as a resource file:

image 
Now click ‘Ok’ and generate the Request Page.

Run the report in Maximo and the extended javascript text transformation function will work on the report from inside Maximo too.

image

Good luck and have fun with this new option for your Maximo BIRT reports.

@MyGeekDaddy