BusinessObjects Board

Aggregate Issue - Sum

building reports webintelligence
#1

Hi All,

I have a question on placing the aggregate function at different parts of the report.
As shown in the attachment, I am just selecting the dimension ‘COST’. That gives me 8 rows of data. When I insert sum on that column I get proper sum value. But if I give the same formula in a blank cell i.e Sum([COST]) then I am finding the discrepancy in the sum value.

I did go through the concepts of input/output contexts but it did not help me as I am not using any context. I am just selecting the coulmn COST.

I am missing a basic concept here. Can you please help me understand the concepts.

MSR_BO (BOB member since 2009-05-01)

#2

Maybe COST should be defined in the universe as a measure rather than a dimension. Then the Select can be defined as

Sum([COST])

and projected when aggregated with Sum.

charlie :us: (BOB member since 2002-08-20)

#3

Is there a difference between creating a variable COST_VAR and declare this as Measure at report level and converting the Dimension COST to measure at universe level?

MSR_BO (BOB member since 2009-05-01)

#4

The SQL will be different. An object defined in the universe as a measure and with a SQL aggregate function like SUM in the SELECT will have a GROUP BY clause, thereby returning fewer rows (and better performance). Including a projection function will enable dynamic roll-up when, for example, a dimension is removed from the report. Here is a link to required reading on the subject.

charlie :us: (BOB member since 2002-08-20)

#5

Please, see also this post.

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

#6

I do not have access to Universe in order to change the object COST to measure from dimension. Is there a way to achieve this at report level?

MSR_BO (BOB member since 2009-05-01)

#7

Yes. Can be done at report level.
It happened with me as well…

Could you pleas let me know the time filter used in the query from which the sum dim is pulled?

Also let me know if there are more than queries at the report level? If yes, then let me know the time filter for all the queries…

i can suggest you a solution based on the information you are going to provide.

-Vikram

vikramvasisht (BOB member since 2008-02-18)

#8

I do not have any filters defined. I am just pulling the onject COST, doing a sum on it which gives me 25,064. But when I place the same formula =sum([COST]) it is giving me 100,257 as the value.

Attached is the snapshot of the report for yur reference.

MSR_BO (BOB member since 2009-05-01)

#9

this type of problem generally happens if you are using
filters at query level
Or
filters at report level.
Or
If you don’t have any time filters then it could be the other objects/measures used at query level along with the cost object are playing their part.

vikramvasisht (BOB member since 2008-02-18)