Web Intelligence report error. #MULTIVALUE error

Question

I've run into an issue with the WEBI report I am making. Here is the code in question:

=If([Group Change Task Status Desc] = "Open")
Then([Count of GCT])
ElseIf([Group Change Task Status Desc] = "Complete"  And [SH Open Date Actual Dt] >= [Current Date - 60] And [SH Open Date Actual Dt] <= [Current Date])
Then([Count of GCT])

This is very basic and straight forward code. Count the O's and count the C's when they fall between the two dates.

This is the error I'm getting:

Formula Evaluation - Error in 
dataset values : #MULTIVALUE 
Check formula definition in this 
dataset context

I've determined the error is because of this part of the code:

And [SH Open Date Actual Dt] >= [Current Date - 60] And [SH Open Date Actual Dt] <= [Current Date]

I've tried it without this line and I get no errors. But the obvious problem is that I get too much data counted for the C's.

I hope I provided enough info. Like I said, its pretty straight forward code. But this is my first time using WEBI and I need more experienced eyes on it.

Answer 1

The #MULTIVALUE error means, given the context (dimensions) in your block, the formula in your variable is returning two or more figures, and WebI does not know how to aggregate them (assuming that the variable you've created is a measure).

You'll notice that if you add more dimensions to your block, such as [Group Change Task Status Desc], [Current Date - 60] and [SH Open Date Actual Dt], the #MULTIVALUE error eventually disappears and the figures will appear instead.

You can try to solve this by manually specifying the aggregation to be used (e.g. SUM). Thus, if your variable is called [Count C and O], you'd add SUM() to it, resulting in SUM([Count C and O]).

There's also a section on #MULTIVALUE in the Web Intelligence manual Using functions, formulas and calculations in Web Intelligence which you can find here.