Technical Blog Post
Abstract
Bilog: Poison Control and Understanding Subquery Complex Relationships in QBR
Body
Veterinary Poison Control for Humans – How can I help you? This wasn’t the phone call I had planned to make early this morning when I accidentally swallowed my dog's arthritis medicine - instead of my daily vitamins. The folks at Pfizer were extremely non-judgmental and assured me that many other ‘humans’ had made this same mistake. They encouraged me to drink extra fluids today, and to work on more clearly separating the dog’s medicines from the human vitamins.
As I was re-visiting this adventure on my drive into work – I thought of all the business rules within reporting – and how I may not have clearly defined them to you. And if you are unfamiliar with these business rules, you may ‘accidentally’ mis-use functionality and be frustrated by the outcome.
One example of these business rules is within QBR, or V7 Ad hoc reporting. Starting in V71.1.6, we introduced Detail reports. Detail reports enable the user to create complex reports of multiple database objects, thru the use of subreports. The enabler to this functionality are ROS are report object structures.
As described in this BiLlog, ROS join multiple database objects together via maxrelationships. And maxrelationships are simply sql statements which join the parent to the child (also known as source) object (ex. Where assetnum=:assetnum). One key business rule surrounding maxrelationships used in ROS – are if the maxrelationship contains a subquery. In these cases, you can only use Detail reports, not Summary Reports, to link child categories to the main category.
An example of a maxrelationship containing a subquery is in the out of the box ROS called REP_WOPLANACT. One of its children, WPLABOR has a maxrelationship with a subquery shown below.
(wonum=:wonum or exists (select 1 from workorder w where w.parent=:wonum and
w.istask=1 and wplabor.siteid=w.siteid and wplabor.wonum=w.wonum)) and wplabor.siteid=:siteid If you select a Summary report, and try to select fields from the WPLABOR Child, a message will display that the report type needs to be converted to a Detail report. Additionally, maxrelationships containing subqueries are not allowed at the grandchild level because nested Detail reports are not supported. If you try to do this, the message below will display.
This relationship includes one or more subqueries. A report can only contain this type of relationship between the main category and its children.
This information can also be found within V75 QBR available here in the section ‘Notes on QBR Relationships’ starting on page 90.
Clearly understanding the intended purpose of any product is key...which I was reminded of again...today!
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSLKT6","label":"IBM Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]
UID
ibm11134171