COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.

Column Grand Totals

Is there a slick way to add grand totals to my report? so far, in my report table I have the following sections

TableHeader1

TableHeader2

Group1

Footer1

I added one of the same fields from my Group1 to Footer1....but it's not summing correctly. I have this in my footer right below one of my Group Fields:

=SUM(Round(((Fields!FeeGoal_AZ.Value) / Fields!FeeSchedule.Value) * 100))

From the expression it looks like you are summing up percentage values? Is this really what you want?

Maybe you are actually looking for the average percentage value?
E.g.
=Avg(Fields!FeeGoal_AZ.Value / Fields!FeeSchedule.Value * 100)

-- Robert

|||

sorry, just ignore that one. Try this...this is actually numbers:

=SUM(Fields!PostedAmount.Value)

when I put that in my footer, it isn't summing my Group field above it in my Group1 correctly all the way down that colum...

|||

Rob, can you help me!! need your input since you're the only one who seems to have any knowledge on this. I am very desperate here! This can't be that hard:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=235554&SiteID=1

|||totals are accurate...I just needed to add SUM() to my group row fields and then I saw the light...My grand total was acting fine, it was just my row items that weren't showing currect Sums. I assumed that since the SSRS 2005 report had a group, that it would sum up it's rows automatically but that's not the case, you still need to use SUM() around your fields in your group.