Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculations tab SCOPE Expand / Collapse
Author
Message
Posted Sunday, February 17, 2013 3:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2014 12:05 AM
Points: 2, Visits: 12
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
Post #1421011
Posted Monday, February 18, 2013 6:34 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:03 AM
Points: 450, Visits: 842
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.
Post #1421398
Posted Monday, February 18, 2013 8:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 23, 2014 12:05 AM
Points: 2, Visits: 12
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!
Post #1421411
Posted Monday, February 18, 2013 8:21 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:03 AM
Points: 450, Visits: 842
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.
Post #1421413
Posted Tuesday, November 26, 2013 6:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 11:39 AM
Points: 14, Visits: 144
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.
Post #1517639
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse