Linking Columns in Webi Reports Without Merging Dimensions

Published by Jesse Russo at

Working Around the Conventional use of Linking Columns in Webi Reports - Without Merging Dimensions.       

Conventional use of Webi reports requires you to merge all dimensions from multiple queries in order to view them in the same report block.  Unfortunately, this is not always possible since the queries may not contain the same data.  However, there is a workaround if both queries have at least one object in common.  Typically this should be an object that uniquely identifies each row or set of rows that should be aggregated.

In the sample data example below, we have 5 transactions with the same tx_id, but different detail types and different matching transaction id’s. 

  • The first row has a detail type of 1 which is a charge. 
  • The last four rows represent payments.

 Screen Shot 2017-04-11 at 4.08.26 PM.png

In the report block, we want to see only the payment transactions, but we do want the post date from the charge (the first row) in the columns.  In other words, we want the Webi Report to look like this:

 Screen Shot 2017-04-11 at 4.10.17 PM.png

In order to make this report work, we can create two queries:

     1.     The first query gets the columns for the payment transactions and has filters on transaction detail types 20 and 21 (as well as
              the date filter).

 Screen Shot 2017-04-11 at 4.12.19 PM.png

2.     The second query gets the columns for the original charge transaction.  For its filter criteria, we look for any transaction id’s
         that would have been returned in the first query (with the use of a subquery) and we limit the results to transaction detail types = 1.    
         Since the original charges may have occurred during a different time frame, we don’t put a date filter on them.

Screen Shot 2017-04-11 at 4.15.12 PM.png 
When we return the data, we cannot immediately put all the columns in the same table block because the dimensions have not been merged.  BUT we did not return the same dimensions in each query because they won’t represent the same data (i.e. the post date for the payments will not be the same as the post date for the charges).  So we have to approach this differently.  This is an advanced technique, but is very handy.

First, we must merge the transaction id’s between the two queries because they do represent the same data.  Remember, all of the rows had a common transaction id.

Screen Shot 2017-04-11 at 4.16.57 PM.png

Then we can put all the payment columns into the report as they are.  To get the charge columns to “play nice”, we have to tell Webi how to associate them with the payment data.  To do this, we create “detail” variables for each of the charge columns.  When we create a “detail variable”, we tell Webi which column it is a detail of.  Since we merged Transaction ID between the two queries, this is the object that will work.  Here is an example:

 Screen Shot 2017-04-11 at 4.18.24 PM.png

In this example, we only pulled one additional column in the Original Charges query, but if you pulled more, you would simply repeat this process for each column in the second query that you need to associate with the first query.  Then you will be able to put them all in the same table.  Note – you will not have to do this for any measure objects.

 ___________________________________________________________

 And Voila – you have a report! 
_______________________________________________

 Screen Shot 2017-04-11 at 4.19.42 PM.png

    
 
 
Jesse Russo

Jesse Russo

Jesse Russo, a Senior Consultant, Fusion Consulting, has 17 years of experience in Business Intelligence and Data Warehousing with a focus on SAP Business Objects Solutions. Her areas of expertise include Data Architecture, Solution Architecture, SSIS ETL, Universe Development, and Report Development in WebIntelligence and Crystal Reports. She also has experience in BOBJ Server Administration, Dashboard Development and Project Management.