#multivalue error for detail object in webi report

[Former Member]

Hi,

I am facing #multivalue error in my webi report.

I am using 2 queries

Query 1

Account Number

Cost Center

Query 2

Account Number

Order Number

Now I want to display Account Namer,Cost Center and Order Number in the report.

So I merged Account Number from both the queries, and created a detailed object "Order" and associated it with [Query 1].[Account number].

But Order displays values for some accounts, and displays Nulls and #MULTIVALUE error for some accounts.

Please through some light on this issue.

I tried every possible solution from my side, but couldn't get it work. It is happening because there is a 1:N relationship between Account Number and Order. I can not use any aggregate function because it will give only the Max or Min value, but I want list all the value of for each Account Number. Avoid Duplicate Row Aggregation was also checked but it didnot help.

Thanks.

[Former Member]

A detail object must provide exactly one value for the parent (associated) dimension. In earlier versions of BusinessObjects this was not strictly enforced. It is much more strict now, and a #MULTIVALUE error will be displayed when this is not the case.

For example, any account number with more than one order will throw this error. Order number should be a dimension as it stands alone as a key. For any account number with more than one cost center the same will happen. Is an order associated with a cost center? If so, bring cost center in your second query and link those values.

Basically a report can "fix" data; you have to work with what you have.

[Former Member]

Hi,

#Multivalue means multiple value for that particular object is shown in one cell like or that single cell has number of values which can't be shown in single cell.

So, go to unvierse -

---

> right click and change it to default agreegation or sum.

Try with this..

if still it doesn't work, just post again, i will get you one more solution.

Edited by: akhanna1 on Feb 2, 2011 3:03 PM

[Former Member]

Hi Sh,,,,

Right click on Webi report choose format option>table>check the option Avoid duplicate rows.

Thanks & Regards,

Praveen

[Former Member]

Hi Shreya,

There are few things that you need to consider while displaying the data.

1 - Can an Account Number be associated to more than one Order Number? If yes, then your solution will not work and that's the reason you are seeing #MULTIVALUE (i.e. Multiple Order Numbers for the same account number)

2 - Why would you want to create two data providers? Would it not be possible for you to bring in all the 3 objects in the same data provider? The only reason I could think of is that they are not related

3 - Always make the lower granularity objects common across the data providers. I do not know about the relation between different entities of your database, but I would suggest something like

Query 1 - Order Number, Account Number

Query 2 - Order Number, Cost Center

and merge Order Number...and create detailed object for either Account Number / Cost Center. It all depends on the relation between the tables.

Hope the above pointers help you.

Regards,

Srivatsa

[Former Member]

Do you have access to the Universe?

Perhaps if you do, then you could add in the universe a derived table or link containing the relationship between the table used in query 1 and the table used in query 2. This way you could create in Webi only one query containing Account Number, Order Number and Cost Center, so you don't have to do anything extra in WebI.

Lets say a relationship like:

Table1 -

---

table1.account_number = table2.account_number -

---

Table2

[Former Member]

Hi,

I have the same problem.

What if I have an Olap Universe?

because with an Olap Universe I can't build an derived table or link.

thanks for your reply.

Mauricio

[Former Member]

Then do this steps in WeBi:

1. Merge Query1.Account with Query2.Account. An objects named Account is created.

2. Create a table, in the first column insert this:

 =[Account] 

3. In the second column do not place Cost Center alone, insert it with a max function:

 =Max([Cost Center]) 

4. In the third column then isnert:

 =[Order Number] 

[Former Member]

Thank you Erika! This solved my issue!

[Former Member]

hi erika i want databases like motor and resorts iland that type db's plz send me

[Former Member]

The #MULTIVALUE that I came across was solved by selecting 1 distinct value from the repeating values. The result was to add Max( ) to my formula, since Max only chooses one value and the values are repeating anyway.

Thank you Erika for the post.

[former_member142702]

You should associated the detail variable with the Query2.Order Number object.

Regards,

Stratos

[Former Member]

Hi Stratos,

Thanks for your help, but it did not help