How to retrieve data from a detail variable of two different universes?

Hi everyone,

The situation is explained below, I need the Vendor Name in the report where Universe 1 is primary. But the data is from two different universes, how can i still retrieve the data without getting a #multisync error?

I created three Free-hand SQL queries to simulate your three universes.

image

Next, I merged Memo from Universe 1 and Universe 2 and Vendor Nr from Universe 2 and Universe 3.

The key to be able to show the Vendor Name from Universe 3 along with the Memo from Universe 1 is to create a variable for Vendor Name with the Qualification set to “Detail” and the Associated dimension to the “Vendor Nr” merged dimension.

Now you can create a table with your merged dimensions and the variable we just created.

By default you will get what is equivalent to an SQL inner join (only the matches from Universe 1 and Universe 3). We can simulate a SQL full join (all records from Universe 1 and Universe 3 regardless of match) by checking the “Show rows with empty dimension values” within the general format table properties. And with that setting checked we can simulate a left join (all records from Universe 1 and only those that match from Universe 3) by adding a “Memo Is not Null” filter and a right join (all records from Universe 3 and only those that match from Universe 1) by changing that filter to “Vendor Nr Is not Null”.

Hopefully, this is enough to get you started. There are lots of possibilities here and you will need to experiment to meet your objective.

Noel

1 Like