BusinessObjects Board

#MultiValue Error when Aggregating Measure

building reports webintelligence
#1

I have the following formula as variable [Actual Benefits] in my report:

=([Cost Baseline Calc]-[Cost / Case])*([Volume ])

The 3 measure variables in the above formula are defined as follows:

[Cost Baseline Calc] = [Cost Baseline]
[Cost / Case] = Sum[Cost]/Sum[Volume]
[Volume] = Sum[Volume]

These are all calculated at the lowest level which is by MSDRG, Ministry and Month/Year.

When I bring them into a table with MSDRG, Ministry and Month/Year, I get the correct values at the lowest level. However, when I try to aggregate by Month/Year, I get a #MultiValue Error.

I have tried to create a new variable with the following variations, but nothing is working:

=[Actual Benefits] in [Month/Year]
=[Actual Benefits] ForEach [Month/Year]
=[Actual Benefits] in ([MSDRG]; [Ministry]; [Month/Year]) in [Month/Year]

Any ideas would be greatly appreciated!

Magriffin (BOB member since 2013-02-26)

#2

Hi,

Try it as Sum([Actual Benefits]) and then verify.

Thanks,
Swapnil.

swap_bo (BOB member since 2009-05-01)

#3

I have tried that as well, but I still get MultiValue Error at the Month/Year level.

Magriffin (BOB member since 2013-02-26)

#4

Can you share a sample from your report ?

rgoulart :brazil: (BOB member since 2011-08-21)

#5

What is [Cost Baseline]? How is it calculated?

#6

[Cost Baseline] is simply a dimension in the Universe. It is a dollar value. I am converting to a variable in the front-end to make it a measure.

Magriffin (BOB member since 2013-02-26)

#7

Should “Cost Baseline” not be a MEASURE in the universe insead of a dimension?

And remember, a proper measure always has a SQL aggregation in addition to projection, both of which are defined in the universe, see also here: diff between Select SUM (Table. Field) & Select Table. F

In your Webi document you might also try:

= ( SUM ([Cost Baseline]) - SUM ([Cost] ) / SUM ([Volume])  ) * SUM ([Volume])

Andreas :de: (BOB member since 2002-06-20)

#8

In my opinion, cost baseline should be a dimension. Basically, the Cost Baseline relationship to Ministry+Msdrg is similar to a Cost field being an attribute of a product. E.g. Cost Baseline stays constant across Ministry+Msdrg combinations as a Cost would stay constant across a product (with possible changes over time of course).

It seems to be a problem with the aggregation. I can get the same baseline results if I use Average([cost baseline]) but once I aggregate to the period level, my numbers are off as it calculates after aggregation. Is there a way to calculate at the base level and sum up at the period level in the front end?

Magriffin (BOB member since 2013-02-26)

#9

E.g. Calculate before aggregation?

Magriffin (BOB member since 2013-02-26)