SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculations tab SCOPE


Calculations tab SCOPE

Author
Message
graham.bath
graham.bath
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 26
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
davoscollective
davoscollective
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 1004
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.
graham.bath
graham.bath
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 26
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!
davoscollective
davoscollective
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 1004
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.
bteague
bteague
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 196
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search