You are here: BI Publisher BI Publisher Data Model : Working with Multiple Data Sets

BI Publisher Data Model : Working with Multiple Data Sets

Rate this item
(4 votes)

This article is in continuation to our last article Adding Parameters and List of Values

Make a copy of OrderDM as OrderDetailDM  by Clicking the ‘Save As’  icon.

Add a new SQL Data set using following SQL query for the Order Items data.

 

Update the Group G1 => ORDERITEM from the structure tab. 

Navigate to structure tab and click the output section to view the XML Structure.  There is no relationship between ORDERS and ORDERITEM Groups in XML.  These are Multipart Unrelated Data Sets


In real time scenario, we often need a master detail or parent child relationship between two data sets.  This relationship defined through data link between two data sets, where one dataset defined as master/parent and other as details/child data set and this is referred as Multipart Related Data Sets.

There are two ways to define data link. Elements Level Link and Group Level Link.

Element Level Links: The element level link creates the join on columns of one query to column of other query. This is preferred option of defining the master details relationship.

In this example, Orders is master and OrderItems is detail data set. Both are linked with OrderID. There are multiple Orders items for a given Order. 

Select click the >> icon on ORDER_ID element of ORDERS Dataset. This will open the action windows. Click on Create Link action, This will popup the Create Link box, select the ORDER_ID1 element from the list and click OK to complete the operation

This will create data link on OrderID column between Orders and OrderItems. Hover the mouse on i icon to view the relationship.


Navigate to structure tab and click the output section to view the XML Structure. The OrderItem appears one level down the Orders



View the sample XML to verify the master detail relationship. There are multiple ORDERITM nodes for one ORDER node.

 

Creating Subgroups

This is another way to regroup the data at higher level or add the required hierarchy to flat data.

Look into sql query from Orders data set.  It lists out the orders for the Customers. There is 1.M relationship between Customers and Orders. 

Lets add the higher group on Customer level but before this we need to add proper order by clause to sort the data on CUSTOMER_ID AND ORDER_ID.

Select the ORDER data set from the Diagram pane and click the  icon at CUSTOMER_ID element level. Click the Group by action link form the action popup. This will add a new Group with CUSTOMER_ID element at higher level.


Click the >> icon at NAME element level, from the action menu, click on Move selected element to Parent Group.

This moves the Customer Name to Customer Group. Update the Tag name from the Structure tab to make it more readable.

 


Observe the new hierarchy. There three level.


==>CUSTOMERS

  ============> ORDER

 =====================> ORDERITEM

 

Generate the Sample XML and review the XML hierarchy.



Thats all for now. will few more features about the Data Model in next article.

Happy Reportng.

This article is part of BI Publisher for beginners series. You will see many more articles on Data Model design, Template desing, Administration, Web Services, Scheduler. The contents are picked-up from BI Publisher Training documents from  Adiva consulting. If you have any question regarding these articles or training program, please contact This e-mail address is being protected from spambots. You need JavaScript enabled to view it .

Read 21877 times

Leave a comment

Make sure you enter the (*) required information where indicated.
Basic HTML code is allowed.