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.
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:
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).
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.
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.
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:
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!