BusinessObjects Board

Percentage Calculation

building semantic layer
#1

Hi

Issue-Calculating Percentage in Universe
I have two measure objects which are summed up:
Summary Cal Qty and Generic Sample Qty and the percentage calculation should be (Summary Cal Qty/Generic Sample Qty )*100

I have used the following formula

((@Select(Qwx Summary Calculation Fact All\Summary Calc Qty)+ 0.0000 )/ @Select(Sample Size Fact\Generic Unit Sample Qty)) * 100

It gives me a multivalue error in my report in some rows

Note :The Measure Objects are set to sum in properties as the users want to sum them up

Any help would be appreciated

[Moderator Edit: Added code formatting - Andreas]

pilot (BOB member since 2010-01-27)

Simple % Measure in Universe
#2

In brief:
A) Make sure your two “base” measures (Summary Cal Qty and Generic Sample Qty) are using SUM in the SELECT box of Universe Designer as well! Only then will proper SQL code be generated such as:

SELECT SomeDimension, SUM(myMeasure)
FROM MyTables
GROUP BY SomeDimension

B) Make your calculated percentage measure a smart measure (a.k.a. database delegated measure).

Little note:
@Select does not tell us much here, we need to see the real underlying code!

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

#3

Thank you for the quick reply !!!

I believe smart measures are used in XIR3 and above versions and not in XIR2

Plz elaborate a little bit on database delegated Measures

Appreciate your response!!

pilot (BOB member since 2010-01-27)

#4

Have a look at this entry from Dave’s blog.

#5

Awesome …It worked !!!

Thanks a lot

pilot (BOB member since 2010-01-27)

#6

Smart measures/database delegated measure were introduced with XI R3+
In your case (prior to XI R3), you must calculate the percentage measure in the Webi document as a local variable, otherwise your percentage measure will not roll-up properly within the report!

Again, did you check your two “base” measures?
You MUST use SUM in the SELECT box, such as

SUM (myfactTable.Measure1)

That is in addition to SUM under properties!

See my other entry here explaining SQL aggregation and projection of measures.

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