To Sum or Not to Sum

Sorry for what is probably a straighforward question.

I have a report which contains a column for CURRENT and a column for PREVIOUS. A variable has been created to calculate the difference. It says SUM(Current-Previous). When I change the calculation to say
SUM(Current) - Sum(Previous) I get a different result. And to confuse me more, if I change the calculation to CURRENT-PREVIOUS I get another answer.

All of my measure have the SUM() function in their SQL definition. They all have SUM as their projection. So I thought the 3 formula variations above would give me the same answer. They are all sitting in the same part of the report and there are no specific contexts within the formula

The only herrings to through in are 1) CURRENT and PREVIOUS come from different data providers. 2) There are filters on the crosstab. MULTICUBE and NOFILTER spring to mind, but I still think the 3 formulae above should give me the same amswer.

Am I missing something fundamental here. I’m using 5.1.6. I would post the report, but it’s rather big.

Thanks in advance. :confused:


Grantie :uk: (BOB member since 2004-07-01)

Just to add more information. If I just select the CURRENT measure along with a dimension from the same data provider, when I place a sum on CURRENT I get one figure. If I check the AVOID DUPLICATE AGGREGATION in the table to see all the rows that have been returned by the query, I get another.

Tell me I’m having a thick day.


Grantie :uk: (BOB member since 2004-07-01)

Yep Paul this was my first thought. But yes they’re measures and yes the projection is set to SUM. Therefore I would expect the totals to match regardless of the dimensions I placed with them. But this isn’t the case.

If I drag the measure in on its own, I get one total. If I add a dimension from the same data provider to it, I get a different total. And so on. I’m at a loss now.


Grantie :uk: (BOB member since 2004-07-01)

Which dimensions are you bringing into the crosstab?
Which dimensions are linked across the data providers?
Which filters are you using (code/definition)?

Does it work as expected without any report filters?

When you use the Business Objects Reporter function MultiCube does it work as expected?


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

Hi Andreas

Thanks for joining in. Can I send you the report direct so that you can see? (Don’t want to post the data onto the forum).

On the first tab, I have simply got one dimension and the measure. The dimension is simply a category and the measure an amount that has been posted to this category. Both items are from the same DP. Look at the value for FXBL.

The second report tab simply has another dimension in it, again from the same DP and breaks down the posting by account. So one would expect the totals for each category to be the same. But they’re not. Again see FXBL

The 3rd report tab has the AVOID DUPPLICATE AGGREGATION ticked so that I can see the detailed rows. Again I expect to see the same totals but I see a different one for FXBL.

I have checked the measure and it definately has its projection set to SUM and it has SUM in its SQL definition.

I must be missing something fundamental here. If you can shed some light I would be eternally grateful. :wah:


Grantie :uk: (BOB member since 2004-07-01)