Display objects from two queries in one report

[Former Member]

Hello @ll,

how can I display several objects (defined as Dimension) from two or more queries in one report ?

For Example:

Query 1

+ ID Number

+ Date

Query 2

+ ID Number

+ Customer

+ Address

Although I bring together the ID Number in two queries it is not possible to display the objects Date, Customer, Address in one report. The error is "incompatible objects"

Please, can you help me to find a solution for this problem ?

[Former Member]

There are a few ways around this issue. You can either bring in date into the second query and merge on that, as long as it corresponds to the date in your first query.

You could define Customer and address in a formula as measure objects, since measures can be used across query if there is a link. Or if you still want them to behave like a dimension object, you could define the objects as Details, with the ID being their parent dimension.

[Former Member]

Merging data from more than one data source is one of the most powerful features of Web Intelligence, but you have to follow certain rules. First, you have to link common dimension objects. In your case, it's the ID Number that appears to be common across both data providers.

Next, you can include any measure objects that you want. It does not appear that you are using any measures, so that's not an issue.

Next, you can include unlinked dimension objects from exactly one data provider. So in your case, you could include either Date or Customer + Address. You cannot use both, as Web Intelligence assumes you would be creating a Cartesian Product. That's what causes the "incompatible objects" message. More on this in a moment.

Finally, you can include any detail objects that are associated with linked dimensions from step 1. Details are defined at the universe level, and there must be exactly one detail value for each parent dimension value. In other words, if you have an ID Number that has exactly one customer and address, then you're fine.

So how to solve the problem? You have a couple of choices. You can alter the universe so that Customer + Address are details of the ID Number. Or you can create variables that "demote" the dimension values to details on the report. To do this, create a new variable called Customer Detail (just as an example, it can be called anything really) and make sure that it has a detail qualification and the parent dimension is the ID Number. Do the same for the Address. At this point, you can include these two objects in your final report block. That's basically a longer version of this answer:

Or if you still want them to behave like a dimension object, you could define the objects as Details, with the ID being their parent dimension.

This other earlier answer is not correct:

One Webi document can have multiple queries and multiple reports but each report can only contain data from a single query.

You can have as many queries / data providers included in a single block as you want, as long as you follow the rules I outlined above.

[Former Member]

Hi Dave,

thanks for your detailed information.

Your suggestion to create a new variable as a detail object with parent link to ID Number works.

But when I have several Customer or Address for one ID Number it displays an error in the report: multiple values.

What can I do in this case ?

[Former Member]

If there are multiple values, then #MULTIVALUE is the proper result. If you have more than one customer / address per ID number then they are not valid as detail objects, nor will they provide the proper result when you merge only on the ID number. It's a data issue that cannot be solved on the report.

You can still display the data near each other by creating a sectioned report. Use the ID number as the section, then have one block of data with your dates in it and the other block of data with customer names and addresses. Unless you have a way to assign (link) a date to an address record, that's the best you can do. Anything else would be misleading.

[Former Member]

Hi Dave..

Thanks for such a detailed explanation..

Finally, you can include any detail objects that are associated with linked dimensions

With above point, We can conclude that Customer + Address +Date can be part of block, if we make them detail of Linked dimension (ID number)

Also will there be any scenarios where, We can not include unlinked dimension objects from exactly one data provider in the block..

Thanks in Advance !!

[Former Member]

Hello ThHein,

Are the queries against two different Universes or one? I assume two or else you could put all the data in one query. When you say you bring together the ID Numbers, do you mean you merged the ID Number dimensions? I am not sure what you are trying to do, but it may work if you pull a date into query 2 and merge that dimension also, or same with customer in query 1.

Can you provide more details?

Thanks

[Former Member]

One Webi document can have multiple queries and multiple reports but each report can only contain data from a single query.