This post on the MSDN forum:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2064643&SiteID=1
…reminded me of something interesting I found out a few months ago. It seems that whereas you can’t mix regular and calculated measures in a set used in the SCOPE statement, you can rewrite the assignment to avoid using SCOPE and do a direct assignment instead. So, for example, if you were trying to assign to a regular measure and a calculated measure using a SCOPE statement like this:
SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});
this=1;
END SCOPE;
You would get the following error:
A set has been encountered that cannot contain calculated members.
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.
You could rewrite the assignment as follows using two SCOPEs:
SCOPE([Measures].[RegularMeasure]);
this=1;
END SCOPE;
SCOPE([Measures].[CalculatedMeasure]);
this=1;
END SCOPE;
…but this is clearly a pain, as you’re duplicating your assignment logic. What you can do instead is this:
({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;
Not as easy to read as using SCOPE, I know, especially if you’re doing something complex, but at least it works! Now I wonder why SCOPE has this problem? Probably something worth opening an issue on Connect about…
Chris,
I couldn\’t agree more about this. As you mentioned, it is especially annoying if doing something complex. For example, lots of cell assignments within the single scope statement. When adding a new calculated measure that requires these assignments, it\’s nice to specify it in one place (the scope statement) and have it inherit the logic. I have even found myself doing the cardinal sin of "copy and paste reuse" by creating another scope statement for the new measure in order to reuse (ahem, copy and paste) the assignments. I\’ve seen this contribute considerably to MDX-script unmanageability (I suppose it keeps us consultants in a job when no one else can figure out what on earth our MDX is doing).
If there is a sound technical reason for disallowing both base and calculated measures in a single scope statement, it would be nice to at least allow reuse of logic. Maybe something in between code snippets and using statements would suffice. If we could at least put a placeholder at the start of the cell assignments and another at the end, we could then refer back to it from subsequent scope statements. I’m sure this would be very easy for the AS team to implement as it would effectively just copy and paste the assignments as required when executing the MDX script.
Christian
I read your blog but I want to know from you how can I understand it better?
Greetings Chris,I\’m just curious if you\’ve found any more on this topic – mostly why it\’s an issue at all.Thanks,Todd
No, I haven\’t actually – I\’ve asked but never had a clear answer. Strange. I also came across a scenario where using this technique to scope across multiple measures lead to much worse performance than separate SCOPEs, but never found out why.
Hi Chriss,
Firstly i have question about my error.
i write scope and calculate as you said, but i get same error. his measure is hidden and when client login with their user, it give same error.
And who is own this topic, i see same issue there: http://dbaspot.com/sqlserver-olap/376341-measures-calculated-members-scope-statement.html
Can you post the exact code you’re trying to use?
Actually , it is a calculated measure, but for hide it from client , i created a new named calculation in DSV then i create scope with this code
SCOPE({[Measures].[CPC GBP]});
this=[Measures].[Total Spent GBP]/[Measures].[Clicks];
end scope;
finally i add it in denied memeber set that in role >{[Measures].[Spent], [Measures].[Spent GBP],[Measures].[CPC GBP]}
it is workin for first 2 measures because they are normal measure, but last one is calculate member.
FYI > If i login with current user , scope working and i get [CPC GBP] value true, but when i select a role then browse cube, it say
i.e:
MdxScript(Cube1) (line, col) A set has been encountered that cannot contain calculated members.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Cube1) (line, col) The END SCOPE statement does not match the opening SCOPE statement.
[Measures].[CPC GBP] not fount
OK, I think I know what’s going on here. The problem could be that the definition of the calculation refers to a measure that is secured, so SSAS thinks it’s a syntax error. This post here shows how you can stop this error happening on calculated members when they refer to measures that may not be allowed: http://cwebbbi.wordpress.com/2008/01/22/dimension-security-tips-tricks-and-problems/
i followed this step that on this site : http://adamjorgensen.wordpress.com/2010/02/21/applying-security-to-calculated-measures/
I don’ t create measure , Firstly i create a named calculation name is > [CPC GBP] then i use this for vlue of this measure, SCOPE({[Measures].[CPC GBP]});
this=[Measures].[Total Spent GBP]/[Measures].[Clicks];
end scope;
This code working good if don’t select any role.
But when i loggin with a role, it giva error. In the each role i Denied this measure ([CPC GBP])
Your example looking some different.
Yes, but I still think the error is coming from the MDX Script because it’s trying to scope on a measure that it can’t see. Have you tried using a named set inside your Scope statement in the way I suggested?
I think error is like you think
I have tried using a named set inside your issue but maybe use wrong because i didn’t understand well.
I am try all way that i see on the net.
Hi ,
There are any control to calculate measure or not(or visible=false) with MDX when client login?
You need to use Perspectives for that, or (I guess) the Personalization Extensions (http://msdn.microsoft.com/en-us/library/cc627355(v=sql.110).aspx).
It will take long time , Maybe we can solve problem with named set , maybe i use wrong.
how can i Set ScriptErrorHandlingMode to IgnoreAll ?
It’s a property on the cube itself – click on the cube in the BIDS Cube Editor and you’ll see it. However I strongly recommend you don’t use it because it means that you won’t see any errors at all, even ones you don’t want to ignore.
Ok I understood
Hello Chris,
I have posted a question in the Microsoft forums w.r.t Scope and calculated measures with a multi select option and here is the link
http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e0a4975f-8f19-40d0-90ff-4fcca1d301c1
Kindly request you let me know if this is a bug in SSAS or is it something that I need to re-design my scope statements.
Please let me know if there are any solutions.