BiLog: Relationships - Facebook vs Complex Maxrelationships with Subqueries
PamDenny 270000BXV7 Visits (5248)
Single, Married or It's Complicated. Those are a few of the eleven choices Facebook gives us to define our Relationship Status.
With V7 Reporting, we've made it easier to define your maxrelationships by offering you two clear maxrelationship types - simple and complex.
In that BiLog entry, it was noted that you should carefully review the maxrelationship you select when building your ROS. Today, I want to additionally highlight the business rules surrounding maxrelationships with subqueries for reproting, which are considered complex relationships.
Simple relationships are those with direct column to column relationships. An example of a simple relationship between ASSET and a child WORKORDER is assetnum=:assetnum and siteid=:siteid.
Complex relationships have sql statements that include filters and/or sub-selects. The key for QBR reporting is those complex relationships with sub-selects, also known as sub queries.
To highlight this, let's use the example of the delivered ROS, REP_WOPLANACT. This has a hierarchy of database objects shown here to the left, but we'll focus on the child object of Planned Labor, WPLABOR, and its grandchild object of LABOR. The child object of WPLABOR is joined ot the parent of WORKORDER by the maxrelationship shown below. Note the subselect highlighted in red - which classifies this as a complex relationship
where (wonum=:wonum or exists (select 1 from workorder w where w.parent=:wonum and w.istask=1 and wopl
Because this is a complex relationship with a subquery at the child object level, users can only create Detail QBR reports. If the user has a Summary QBR report type enabled, and selects attributes from the WPLABOR child table, they will receive a message similar to what is shown to the left - and they will have to use a detail report type to handle the subquery of the maxrelationship.
Additionally, maxrelationships with subqueries cannot be included at the grandchild level, as nested subqueries are not currently enabled. Continuing with our example where Labor is a grandchild object, its sql enabled thru its maxrelationship is 'where labo
Therefore, the key item to remember for QBR reporting, if you have a complex maxrelationship in your ROS which contains a subquery (1) your users will only be able to create Detail QBR reports with it and (2) these relationships cannot be created at the grandchild level
You can find more details on this in the Version 7 or 7.5 QBR Ad Hoc Reporting Guide, along with other details on creating ROS for your individual environment. To access this document,click here