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.
Regards
Louis
Hi,
I have been trying to merge the dimensions of common data type from different queries, it is working fine but only with the merged dimension and the comparising dimension from either of the query. Not able to add new dimension because it is giving Incompatible Object.Means it is allowing me to only add one object with the common object.
Example: i have taken Emp_Firstname and Job_Title from Employee and Job tables in Query 1
in the second query i have taken Emp_Firstname and Showroom_Name from Employeeand Showroom tables in Query 2.
I have merged the Emp_Firstname.
And when i am trying to drag Emp_Firstname along with Showroom_Name and Job_Title , it is giving incompatible object cannot drop.
But when I only take Emp_Firstname and either Job_Title or Showroom_Name it works fine.
Why cant I take all the three objects in the report?
Please give suggestions
Regards
Have you created a detail variable for your Showroom_Name or Job_Title and associated it with your merged dimension?
read the instructions above
“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”
Thanks it worked
Hi
As Andrew pointed out you have to create a new variable which is specified as type detail and not dimension. when you do this you can then specify the associated dimension field in the variable. This fixed the issue for me as per my original blog post. If you are still struggling after following the instructions then reply to the message with some more details and I will try and assist.
Regards
Louis
I have been looking for this solution for days. I was missing the trick to project the dimension as a detail. Thanks so much for the tip!
Hi Sam, I am glad this helped you
Regards
Louis
Hi Louis, I am having a problem with merged dimensions. It looks like it is case sensitive. So if I bring back data from two different databases it is not completing the match on all values because some of them are lowercase in one database and uppercase I the other. I can create a dimension variable using a lower function but it won’t let me use that in a merge.
Have you come across this issue?
Using BI4.1 SP5
Hi Andrew, sorry to intrude in this discussion but I think I may have a solution to your question.
Try this,
merge both dimensions ( the one with lower and the other with upper case) into [merged_dim],
now apply Upper or Lower to this variable , like Upper([merged_dim]), this will “normalize” the values forcing it to match.
I´ve discussed this here
How to merge dimensions where values from one Query has leading or trailing spaces.
Now, when defining your detail variables use [merged_dim] as the associated dimension.
Regards,
Rogerio
Hi Rogerio
thanks, I have got it working using your suggestion, but your description above is not quite right, but your link is correct. Above it should say
By the way, on “Properties-Document-Extend merged dimension values” needs to be unticked, otherwise you get #MULTIVALUE error for records that have a mismatched case on the merged value
thanks again, that seems to work
Andrew
Hi Andrew,
Thanks for the correction!!
You can either use Sum(measure 1)+Sum(measure 2),
assuming measure 1 comes from query1 and measure2 comes from query2.
Regards,
Rogerio
HI Andrew and Rogerio I have not experienced this issue, as all the merged dimensions I used had the same properties. It does makes sense that different cases would cause a problem. I personally do not like to join on criteria such as first Name Last Name and always like to use a key such as the ID which typically would not have a different case issues. but if you create a variable in your query that normalizes the case then doesn’t this fix the problem ?
Rogerio I think this is what you are suggesting but I did not completely understand your explanation.
Andrew if you try normalising the case and then following the merge approach outlined does this work ?
Regards
Louis
Hi,
What if both queries have many to many relationship?
For example.
Query 1 shows the following:
MRN Procedure Date
1220 DNA 01/18
1220 DNA 02/18
and Query 2 shows the following:
MRN Procedure Date
1220 Blood 02/17
1220 CT 03/17
I would like to show this at the end.
MRN Procedure Date Procedure Date
1220 DNA 01/18 Blood 2/17
1220 DNA 02/18 CT 03/17