BiLog: Where's the Beef? And Using Maximo Reporting Where in Maximo Applications
PamDenny 270000BXV7 Visits (3479)
Wendy's Restaurant Commercials. While many Bilog entries do touch on food and their relationships to reporting -- including chocolate buffets yoohoo drink products and brownies ….today’s Bilog will detail how to find the beef of the reporting where – and apply it back to Maximo applications.
The Maximo Where is one of the key attributes of reporting. It is the primary determinant of your report’s results as it defines the filters used to query the database. This same where clause is used as your users query on and take actions on each of the maximo applications.
In prior BiLogs, we’ve detailed how you can use the Maximo where to create reports but today, we’ll switch things up and go backwards. Let’s take a delivered V7 report – Work Order Pick – and show you how you can use its ‘where’ clause in a Maximo application to either get a listing of results – or to extend in an ad hoc report.
To do this, we first need to access the report sql contained in the report design file. You can do this by navigating to the report source at: <V7>
sqlText = "select distinct invr
+ " invr
+ " invbalances.binnum, invbalances.lotnum, invb
+ " from workorder, invreserve, invbalances, item, wpmaterial "
+ " where wpmaterial.wonum in (select wonum from workorder wo2 where (wo2.wonum = workorder.wonum or "
+ " (wo2.parent = workorder.wonum and istask = 1)) and wo2.siteid = workorder.siteid) "
+ " and wpmaterial.wonum = invreserve.wonum "
+ " and wpmaterial.itemnum = invreserve.itemnum "
+ " and wpmaterial.siteid = invreserve.siteid "
+ " and wpmaterial.location = invreserve.location "
+ " and wpma
+ " and wpmaterial.siteid = invbalances.siteid "
+ " and wpmaterial.itemnum = item.itemnum "
+ " and wpma
+ " and invreserve.itemnum = invbalances.itemnum "
+ " and invreserve.location = invb
+ " and invbalances.curbal > 0 "
+ " and " + params["where"]
+ " order by workorder.wonum, siteid, invr
To transfer this report’s where clause to the Work Order Tracking application, we’ll skinny down the sql to only use the portion starting from and ending before params["where"] statement as shown in blue. (This is finding the ‘beef’ portion of the sql)
Then, we’ll add the following condiments
1. Before: , wonum in (select workorder.wonum
2. After: )
Here is the complete query:
wonum in (select workorder.wonum from workorder, invreserve, invbalances, item, wpmaterial
where wpmaterial.wonum in (select wonum from workorder wo2 where (wo2.wonum = workorder.wonum or
(wo2.parent = workorder.wonum and istask = 1)) and wo2.siteid = workorder.siteid)
and wpmaterial.wonum = invreserve.wonum
and wpmaterial.itemnum = invreserve.itemnum
and wpmaterial.siteid = invreserve.siteid
and wpmaterial.location = invreserve.location
and wpmaterial.siteid = invbalances.siteid
and wpmaterial.itemnum = item.itemnum
and invreserve.itemnum = invbalances.itemnum
and invreserve.location = invb
and invbalances.curbal > 0 )
Now that you have this reporting where, you can copy it directly in the Work Order Tracking application. To do this, open up the application, clear any current queries you have and copy it directly in the Advanced Search > Where Clause dialog. Depending on your data, you may or may not see results.
You can then take the where one more step by creating your own ‘Work Order Pick’ Ad hoc report which has the unique fields you want. To do this, click on the ‘Create Report’ icon and make sure you have the field ‘Apply the Current Query and Filter from the Application’ enabled on the Select Tab. This enables the where clause you have entered to be applied to the ad hoc report.
After you do this, select the unique database fields you want in your individual report – and you have quickly created a work order pick ad hoc report reflecting your individual needs.
Now that you've found the beef...is it time for lunch?