February 17, 2013 at 3:32 pm
Hi, I'm wondering if anyone can help with some scope syntax in the calculations tab. I'm trying test the following:
/* Calculate the entire cube first.*/
CALCULATE;
/* This SCOPE statement defines the current subcube */
SCOPE([Geography].[State hierarchy].MEMBERS, [Measures].[Test], *);
/* This expression sets the value of the test measure */
THIS = [Measures].[Test] * 1000;
END SCOPE;
It dies at the semi-colon following the calculate command. What am I doing wrong?
I'm using SQL Server 2008 Standard Edition
Thanks
Graham
February 18, 2013 at 6:34 pm
Your syntax looks fine, assuming these exist:
[Geography].[State hierarchy].MEMBERS
[Measures].[Test]
I tried it on Adventureworks 2005 changing it slightly to match an existing Hierarchy and Measure and it debugs fine (by pressing F5 in script view under Calculations Tab):
Calculate ;
/* This SCOPE statement defines the current subcube */
SCOPE([Geography].[Geography].MEMBERS, [Measures].[Sales Amount], *);
/* This expression sets the value of the test measure */
THIS = [Measures].[Test] * 1000;
END SCOPE;
I wonder if your [Measures].[Test] measure is a calculation, or perhaps not available to the query parsers for some reason at the run time. In the script view the CALCULATE is the first statement and calculated members are done later on in the calculations script. If you are setting SCOPE before they are defined that might be your problem.
I tried modifying my Adventureworks version to use one of the calculated measures [Gross Profit] and the debug gave me an error that "the member '[Gross Profit]' was not found in the cube when the string, [Measures].[Gross Profit], was parsed."
This leads to an 'END SCOPE does not match the opening SCOPE' error which is symptom of an error in the syntax in the scope. Try your script again but use a Regular measure to see if that is the issue, run it in debug to see what the exact error is.
February 18, 2013 at 8:10 pm
Thanks for your help.
I had the following commented code before the syntax I posted previously.
/*
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*/
I removed this and my calculation now works!
February 18, 2013 at 8:21 pm
Glad that you sorted it out.
Strange that the only change you made to fix it was removing some comments, that shouldn't have made any difference.
Anyway, strange things happen.
November 26, 2013 at 6:20 am
Even stranger, I copied the comments out and pasted them back in and the syntax checked out. Subsequently, I was able to build the cube. Go figure.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply