WEBI report : Problem with 1:N relationship???

[Former Member]

Hi All,

Actually I got one scenario while working on WEBI report.

First of all my table structure and data is as follows :

Customer No	Product ID	Order ID	Credit Limit	Total Amount
1	P1	O1	10000	200
1	P2	O2	10000	300
2	P3	O3	20000	500
2	P3	O4	20000	400
3	P4	O5	30000	600
3	P5	O6	30000	700
4	P6	O7	40000	800

Now, I have two data Providers :

In DP 1 , I am taking Customer No,Product ID,Credit Limit

& in DP 2, I am taking Customer No,Order ID,Total Amount.

Now I would like to see output as shown in table above.

How can I solve the issue with considering above scenario only.

I have already tried following methods :

Method 1: Merge Customer No object , then create the detail for Order ID object but I am getting #multivalue error because of 1 : N cardinality.

1 customer having more then 1 order and also more then 1 products.

So, Please let me know the logic\method how anyone can achieve this in WEBI.

[tanisha_gupta20 · Accepted solution]

Hi Swapnil,

I am not sure if you have managed to get a solution to this problem. But here is my take on it.

When we create a detail objects for a dimension, it asks us to associate it with a dimension. Assuming you have put the merged Customer ID as the associated dimension, there always is a 1:1 relationship between the detail object and its associated dimension. So, with the present approach you can not resolve the #MULTIVALUE error however by dragging Order ID in DP 2, you can make it a merged dimension and that should be able to resolve the issue. Is it not possible for you to drag Order ID in the DP 2?

If you have done it in a different way then it would be great if you could share your findings.

Regards,

Tanisha

[Former Member · Accepted solution]

Yes Tanisha you are right ,actually the same thing I have think before few days.

Actually that was not part of requriement but I got stuck with one thing and that was if there are 1:N relationship between objects then in that case how can we create a detail objects because that will any ways get us #multivalue error.

Thanks a lot Tanisha for thinking on my thread (since its very old - dec 2014)

Thanks,

Swapnil

[former_member635186 · Accepted solution]

Hello Tanisha, I'm really new to webi and didn't quite understood the part "however by dragging Order ID in DP 2, you can make it a merged dimension and that should be able to resolve the issue. Is it not possible for you to drag Order ID in the DP 2?"

What should I do after merging Customer ID?

[Former Member]

Can anyone provide me the solution if possible

[Former Member]

HI,

this might help

http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/12/10/merging-queries-with-a...

Cheers,

RObgerio

[Former Member]

Hi,

No that is not a solution for my problem.

Actually I got only one common dimension between 2 data providers and that is Customer No.

But the link you have provided has all the dimension common.

I think now you will get my problem!!

[Former Member]

IT works the same,

simply merge on customer id, drop the dimensions from the n relationship, concatenate merged Customer No and Product Id  [conc] = FormatNumber([Customer Id];"0000")+[Product Id] create detail Order No with associated dimension = [conc].

DRop[merged Customer No], [Product Id], [Order Ni Det] and the measures.

CHeers,

ROgerio

[Former Member]

Hi ,

Thanks for your attempt.

But your solution will not work in following scenario :

Let's say Customer C1, having same product P1 ordered two different day i.e. Order ID O1,O2

So now you will again get #multivalue error.

[Former Member]

YOur talking about an M x N relationship then ?

[Former Member]

Yes.. It is...

Please share a solution if you know same..

[former_member183424]

Is there any specific reason to mark the discussion as Assumed Answered immediately after creating discussion (also without any reply) ?

Have you found the solution ? If yes, then you can share it to others.

[Former Member]

No.. Actually this scenario was asked by someone in one of the interview I faced .

So I have marked it as Assumed Answer.

But I don't know the answer.

Please provide answer if any one knows