Problem merging data from 2 queries - incompatible object

building reports webintelligence
1

I’m building a report that uses 2 queries, each query pointing to a different datasource. They are merged using one common dimension: Store Number

Query 1 - Datasource A
Store Number
Store Name
State
Rep

Query 2 - Datasource B
Store Number
Month
Total Sales

I would like to create a crosstab report with Store Number, Store Name, State, and Rep in the rows along the left, Month in the columns along the top and then Total Sales as the summarized field.

However I can’t bring in the Month dimension to the report because it says that it’s an imcompatible object. Why is it incompatible if the 2 queries are already merged on the Store Number dimension?

edressen (BOB member since 2008-04-28)

2

It has been my experience that every dimension used in a report must be merged (unfortunately). You can bring measures from multiple queries without merging, but not dimensions.

REB01 :us: (BOB member since 2004-11-29)

3

Hi,

You can probably wirte a derived table query - only if the tables are in the same DB/Schema whatever and then try to do this cross tab report.

Or try this - create a variable “V_Month” as a detail object to one of the mearged dimensions and try using it in cross tab.

-Sija

sijasethu :india: (BOB member since 2009-01-07)

4

I think if you make Total Sales a detail instead of a dimension it will go into you cross tab without error.

Nniixx :australia: (BOB member since 2009-09-02)

5

Hi,

Please refer this link also.

Thanks,
Arun

arung :india: (BOB member since 2007-07-18)

6

Thanks - my problem is that I need to be able to bring in unlinked/unmerged dimensions from both data providers when I can only use unlinked dimensions from one data provider.

If I bring in the linked dimension store number into my report, along with Month and Total Sales from query 2, then everything is fine because I’m only using one unlinked dimension (‘Month’ from query 2). However if I then try to bring another unlinked dimension ‘Store Name’ from query 1, I can’t because Webi reads it as an imcompatible object since the report already has an unlinked dimension from query 2.

I would write a derived query to link the data providers at the universe level but the two data providers are in different databases on different servers, and as far as I know you can’t make a link across multiple servers in the same universe (although you can link multiple databases that are on the same server).

I think I’m going to have to use Crystal Reports to develop this report instead, which gives me more flexibility in linking data from multiple data providers.

edressen (BOB member since 2008-04-28)

7

Make the unlinked dimension from query 2 as detail object of merged dimension. Then you will be able to bring them together in a block.

Basically, create a variable and point it to unlinked dimension and qualify the variable as detail object to merged dimension.

After doing this you can bring the unmerged dimension of query 2 alongwith merged/unmerged dimensions of query1.

adbas (BOB member since 2010-01-12)