Calculations tab SCOPE

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » Calculations tab SCOPE

Calculations tab SCOPE

Rate Topic

Display Mode

Topic Options

Author

Message

graham.bath

Posted Sunday, February 17, 2013 3:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:53 PM
Points: 2, Visits: 7

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

davoscollective

Posted Monday, February 18, 2013 6:34 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 PM
Points: 413, Visits: 701

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

graham.bath

Posted Monday, February 18, 2013 8:10 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 7:53 PM
Points: 2, Visits: 7

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

davoscollective

Posted Monday, February 18, 2013 8:21 PM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:54 PM
Points: 413, Visits: 701

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

« Prev Topic | Next Topic »

Permissions