Problem - Webi merging a variable with a dimension

[Former Member]

Hi All,
 
I'am new here and also a newbie in Webi.
 
I need to merge a variable with a dimension. After some research, i found, it is not possible. may be you have an idee how can resolve my problem:
 
I have 2 queries :
 
Query1:
Person, Parent, HouseId
 
A    A    1
B    A    2
C    B    Null
 
 
Query2:
HouseId, Number, Street
 
1    11   s1
2    22   s2
 
 
I want to build one report (HouseId, Number and Street for a Person, if the person has no House get the one of the Parent):
 
Person, Parent, HouseId         Number,   Street
A    A    1                                  11        s1
B    A    2                                   22       s2
C    B    2(HouseId from parent B) 22       s2
 
I merged the diemsnions HouseId from the queries but still only have data if the HouseId is not null.
 
So I created a new Variable

Code:

HouseId_Merged =  If IsNull([HouseId]) then (HouseId In [Parent]) else [HouseId]

and it works for HouseId.
 
But i can't get the Number and Street for the Person C.
 
 
Thank you for any suggestion

[Former Member]

Hi,

Can you please expain below:

I want to build one report (HouseId, Number and Street for a Person, if the person has no House get the one of the Parent):

what is your exact requirement .

Please provide clearly.

Thanks,
Swapnil

[Former Member]

Hi Swapnil,

My requirement is to get a Report, where all data (House data) is available for all Persons, in my query the houseid (data) is missing for some persons, in this case i want to display the house data of the parent (which is available).

Thanks,

Tomi

[Former Member]

Tomi, the best way that I can think of doing this is to push it back to the database or universe level. Ideally use in preference order:

Materialised view, view, derived table.

I'm suggesting this because it will be easier for you to build, the dba will be best placed to tune it if you push it back to the database. It also means that you then have a solution that's far easier to deploy in other reports should you need to.

[Former Member]

Hi Mark,

Thanks for the suggestion, I can't do it by myself and change the universe. I thought there is a possibilty to get it in webi.

best regards

Tomi

[Former Member]

Tomi,

As far as I can tell, you're looking at the equivalent of CASE expression or COALESCE/ISNULL/NVL type logic at a SQL level.
You're saying for query 1 is - present the house ID but if it's null then get me a house ID from a different row.

As has been previously said, you cannot merge a variable object with a universe object, so you'll need a universe object to be created. Using details won't work because you can obviously have more than one child for a parent. To achieve the merge that you want, you need to provide a universe fix - if HouseID is the merging dimension, then you need to know all values on both sides of the merge.

[Former Member]

Mark,

thank you for the explanation. so we will fix it in the universe.

Tomi

[Former Member]

No problem. I understand that there will be something that might work for you in the future. There is talk of the introduction of universe-level variables (the SAP Idea was originally put in due to complex percentage calculations having to be cut and pasted every time because they are a report level solution). Obviously doesn't help you at the moment but it is something that could give you a cleaner fix longer term and certainly offer more functionality for us.

Good luck - if you need any more help, just ask.

[Former Member]

Did you try applying this formula on query 1 and then merging the 2 queries?

-Ankush

[Former Member]

Hi Ankush,

Thank you for the replay. do you mean in the universe ? I can't change the query or define new Dimension in the universe.

Thanks

Tomi

[amitrathi239]

Hi,

try with below steps.

Merge the HouseId from Both query.

3rd column :HouseId==If IsNull([HouseId ]) Then Previous(([HouseId ]) Where ([Parent]=Previous([Person]))) Else [Query 1].[HouseId ]

Create  detail variable "Var Number"=[Number]   (Associate dimension is  HouseId)

Var Number =If IsNull([Var Number]) Then Previous(([Var Number])Where ([Parent]=Previous([Person]))) Else[Var Number]

Create  detail variable " Var Street"=[Street]     (Associate dimension is  HouseId)

Var Street=If IsNull([Var Street]) Then Previous(([Var Street])Where ([Parent]=Previous([Person]))) Else [Var Street]

Amit

[Former Member]

Hi Amit,

Thank you for the replay, but that works, only if the Previous row contains the Parent as Peson (like in my example) but the data in my report is not sorted this way and i have no criteria to sort it so.

Best regards

Tomi