BiLog: ROS and Maxrelationships

Body

 As the building blocks to Version 7’s Ad hoc reporting tool, QBR (Query Based Reporting), Report Object Structures (ROS) enable you to select fields from multiple categories (objects) for your ad hoc reports.  Additionally, ROS enable you to visualize application categories and their parent/child relationships.  These categories are displayed via a category tree drilldown in the Select Tab of the QBR window in the top left hand side. 

 Depending on the products you are licensed for, you will receive over 20 ROS out of the box.  These ROS enable you an excellent starting point.  However, due to your unique data analysis requirements, you may need to modify the ROS or create new ones.  You may want to add or remove child objects, create new ROS for specific security groups, or create new ROS reflecting your unique Industry Solutions.

 As you go thru the process of creating or modifying ROS, a key item you will work with are maxrelationships.  Maxrelationships are sql statements which join the parent to the child (also known as source) object within the ROS.  Within the Object Structure application, a lookup is available to help you select the correct maxrelationship – but often multiple relationships are displayed.  How do you know which relationship to choose, and what do you do if you don’t find the correct relationship available?  

 Below is a listing of key items for you to consider when selecting the maxrelationship to be used within your ROS:

 1.  Make sure the individual creating and modifying the ROS is very familiar with the V7x database and database concepts.   Without this knowledge, the ROS can be setup incorrectly, leading to bad or misleading data in the Ad Hoc reports.

 2.  For more information on relationships between objects, review the V7 Entity Relationship Diagrams located here.

3.  Child Objects must be joined to their parents with relationships where the database sql is defined in its maxrelationship.  If a relationship exists between the parent and the child – but the database sql is null - that relationship can not be used in a ROS as it will give invalid results.

 4.  You may find it useful to create your own unique maxrelationships to be used in the ROS.  This can help you better manage and understand the relationships used in your QBR reports.  You can create these unique report relationships in the Database Configuration application.

 5.  If you create unique maxrelationships for ROS, make sure to take into consideration the following items which are not supported for use in ROS

 A.  Replacement variables such as :&username&

B.  Dates in bind variables, like …. and workdate between :startdate and :enddate

C.  Restrictions in utilizing maxrelationships with subqueries, which are (1) users can only create detail QBR reports and (2) these relationships can not be created at the grandchild level.

 For more details on modifying and creating ROS, download the V75 QBR Ad hoc reporting guide available here or the V71x QBR Ad hoc reporting guide available here