I am facing #multivalue error in my webi report.
I am using 2 queries
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.
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.account_number = table2.account_number -
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:
3. In the second column do not place Cost Center alone, insert it with a max function:
4. In the third column then isnert:
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.
#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
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.