Joining data from two queries in a single report when all the objects are dimensions
Hi I recently worked out how to Join Data from two queries in a single report
Create a WEBI document with 2 queries where 1 reads data from the other
in my example I have 2 queries 1 is Rels and the other is Related Acc the Related Acc query is reading the account name for accounts that are the Rel Business Partner ID from the Rels Query
The result is two tables 1 of the accounts with the related accounts and the other of the related accounts IDs with their names. I want to join the second table to the first to provide name details fro the Rel Business Partner ID in the First table
The first step is to define the Merge criteria which is the Rel-Business Partner ID from the Rels query and the Business Partner ID for Filtering from the Related Acc query use Ctrl click to select these two dimensions then right click and select Merge
Next right click on the Merged dimension and select Edit Properties
Then change the name of the Merged dimension to make it obvious which is which
result is shown below
Next create a variable for the Organization name from the Related Acc query.
The important elements are to make the Qualification of the variable a detail not a dimension. When this detail variable type is used it allows you to specify what the associated dimension is in this case it is the MErged dimension Business Partner ID M. The Formula just reads the value of the field from the Related Acc query
finally include the new variable in the first table and the names will be displayed correctly
I hope this helps other people I have been wondering how to do this for ages. So simple in SQL but quite complex in WEBI.