Multivalue error when merging queries in WEBI report

[former_member334960]

Hi All,

I have two queries as below :

capture1.png

Query1 : ID,Resource, Actuals

Query2: ID, Division

Output : Division, Sum(Actuals)

I am trying to achieve an output where I can see sum of actuals for each division. I get multivalue error when doing it.

Following is what I tried:

Method1. Created merged dim on ID, then created a detail obj for Division(Division_DetailObj) and used Division_DetailObj and Actuals (Query1) in the output but get multivalue error.

Method2. Created a variable for Actuals (Actuals_Var) as sum(Actuals) and used Actuals_Var and Division(Query2) in the output but still ger multivalyue error.

Can someone pls help on this

Thanks.

Poojitha

[amitrathi239]

what is the datatype and object type (dimension/measure) of Actual object?

[former_member334960]

Actual Object is a Measure object coming from query 1

[amitrathi239]

Try with Force merge function in actual column.

=ForceMerge([Actual])

[former_member334960]

I have used the logic as suggested. Please see below.

capture1.png

Also the logic which we that I am using for "Division" is

=If(IsNull([Division_Trim_UserName]);[Division_Trim_Manager];[Division_Trim_UserName])

If division of username is blank then pick manager division,

[ayman_salem]

just create the merged Dim on ID. then greate a table with merged ID, Division, Actual.

after that, hide the column ID.

[former_member334960]

Bu hiding ID , the table will have duplicate division, I need the sum of actuals for each division.

There can be more than one actuals for each division, the values should be aggregated.

I used Sum(actuals) , but still the results are not correct.

[amitrathi239]

If actual is measure object then have you tried to drag division object along with actual in one table.it will work if ID is merged .

[former_member334960]

Hi Amit,

I have tried this , but the results are not as expected. Please see the attached image.

capture1.png

Thanks.