Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.sql