IDT 4.0 - Measure/#Multivalue error

[Former Member]

Hi,

I have a measure in the IDT with the follwing syntax in the select part:

CASE WHEN CODE IN ('A','B','C') THEN @Select(Measures\Total) ELSE 0 END

When I drag this measure onto a webi document with a dimension it throws #multivalue error

Any help is appreciated

Thanks

Veena M

[Former Member · Accepted solution]

If this is defined as a measure then it should have a aggregation layer defined ... try with the below code

SUM(CASE WHEN CODE IN ('A','B','C') THEN @Select(Measures\Total) ELSE 0 END)

[Former Member]

Hi Veena,

Seems like for single value of CODE i.e. 'A','B' and 'C' there are multiple values returned by 'Total' measure.

In order to fix this, you need to use aggregation functions like sum( ),avg( ), min() or max () as per your requirement.

In order to check what values are coming, go to Block Properties --> Display and check "Avoid Duplicate row aggregation" option.

This will return the multivalues in form of multiple rows and you can then analyse your data.

Regards,

Yuvraj

[CdnConnection]

Veena,

     You need add Database aggregation so get a proper GROUP BY in you SQL that is being generated.  You should also add Projected Aggregation, see sample below

What is the SQL for @Select(Measures\Total))

TRY

CASE WHEN CODE IN ('A','B','C') THEN  SUM(@Select(Measures\Total)) ELSE 0 END

Regards,

Ajay

[Former Member]

Ajay,

Just some advice for you

The case statement that you've given will still return multiple rows - the sum should be round the whole case statement rather than just the @Select statement.

You would end up with a total from the @Select plus however many 0s for the number of rows where the code is not A, B or C.

While the projection set as sum will still give you the correct answer in webi, you would be bringing back far more rows that you intended to.

Regards,

Mark