2 objects from 2 Universes in 1 Table/Crosstab

[cassbella]

I need to display contents of 2 objects, each from a different Universe in a Table/Crosstab where the context of some value.
I need to have this and do a sum (grand total) on their values for the table.

illustration may explain my point clearer:
Aus NZ
company type a (Universe1) value value
company type b (Universe1) value value
company kind c (Universe2) value value
company kind d (Universe2) value value
Total value for a+b+c+d

any advise is greatly appreciated! thanks!

 

[Subhashab]

you have dataprovider 1 from universe 1 with object Company, country and value

Then you have another dataprovider 2 from universe 2 with object Company, country and value

then u have also linked Company from query 1 to company of query 2 and also country.(Using View Data Option)

then u made a crosstab by selecting company from query 1 and country from query 1 and a new variable object which is Value from query 1 + Value from query 2.

and voila u get the report u want.

 

[cassbella]

Hi Subhashab,

Thanks alot for your advice!
I managed to have the 2 objects from 2 Universes displayed in a column of the table.

However, the value is having problem.
1st object - order $
2nd object - profit $
How to have the value display corresponding to the object?
What I've here is only 1 type of value(i.e. order $) shown while the other object which do not involve with that value, is NA.
Hope you can help me with this.
Thanks!

 

[Subhashab]

Sorry can u clarify more on this may be with a sample report output.

 

[cassbella]

sample output i'm afraid inconvenient since it's company info.
hope my illustration helps:
Aus NZ
company type a (Universe1) order$ order$
company type b (Universe1) order$ order$
company kind c (Universe2) NA NA
company kind d (Universe2) NA NA
i.e. total for Aus = order$ + order$ + profit$ + profit$
(a) (b) (c) (d)

how to let profit$ show? (profit$ for c & d)

thanks for ur patience..

 

[Subhashab]

Ok so you have two measure objects Order$ and Profit$.
And Order$ is present in Query 1 while Profit$ is present in query2.
So in query 1 the values for Profit$ is null while in query 2 the values for Order$ is null

Now when you create a crosstab with objects arranged like below please confirm wether u obtain the result as below in the end. check in Slice and Dice for verification.

Country
-------------------
company(Query 1) | Order$ Profit$

Aus NZ
Order Profit Order Profit
company type a (Universe1) order$ order$
company type b (Universe1) order$ order$
company kind c (Universe2) profit$ profit$
company kind d (Universe2) profit$ profit$

where order$ and profit$ indicates the actual value corresponding to companies.

 

[cassbella]

Hi Subhashab,

the 2 measure objects are in 2 columns, can we have them in 1 column, corresponding correctly as illustrated below?

AU NZ
company type a(Universe1) order$ order$
company type b(Universe1) order$ order$
company kind c(Universe2) profit$ profit$
company kind d(Universe2) profit$ profit$

a big thank you!

 

[Subhashab]

Yes ,
by creating a new variable which with the follwinf formula

=if IsNull(order$) then profit$ else order$

and use this measure in the crosstab and make the other two measure hidden.

 

[cassbella]

Hi Subhashab,

it doesn't work..

after i created the variable with the formula and use it for the crosstab, below is what shown:
AU NZ
company type a(Universe1) NA NA
company type b(Universe1) NA NA


Universe2 objects disappear, with Universe1 objects values all NA..

hmm... what went wrong..?

 

[blom0344]

Sorry to jump on you, but you can - I suspect - only validate on the null function if there is a corresponding record to validate on... Joining datasets in BO causes the effect of an outer join between the datasets and a validation like:

=if IsNull(order$) then profit$ else order$

will only work if there is a record in the dataset where the IsNull validation can work on. There is none, so the validation will always be false, only showing the records with matching 'order$'. Remember that all logic is aimed at the datasets (cubes) and NOT at what is presented at report-level...........

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Subhashab]

blom is right the IsNull function might not work here.

then why do not you just add then together

= order$ + profit$

as there can be value only in either object for a row it will return the right value.

 

[blom0344]

Yep, that should do the trick.......... T. Blom
Information analyst
tbl@shimano-eu.com