Something interesting to note regarding how scoped assignments behave with multiselect…
On the Adventure Works cube, add the following code to the MDX Script:
CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[Calendar Year].MEMBERS);
THIS = 2;
END SCOPE;
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[All Periods]);
THIS = 3;
END SCOPE;
Then run the following query:
SELECT [Measures].[TESTCALC] ON 0
FROM [Adventure Works]
WHERE([Date].[Calendar Year].&[2001])
It returns the value 2 as you would expect. Now run the following query where there is a set in the Where clause, giving a multiselect on 2001 and 2002:
SELECT [Measures].[TESTCALC] ON 0
FROM [Adventure Works]
WHERE({[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002]})
It returns the value 1 – which, strangely, is the value of the original TESTCALC calculated measure before any of the scoped assignments were applied, even though it would seem that the two scoped assignments between them should cover the cells returned by this query.
Now delete the code you added to the MDX Script and add the following:
CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].MEMBERS);
THIS = 4;
END SCOPE;
When you try to run the two queries above you get the value 4 returned in both cases. When I first saw this I thought the results returned by two scenarios were inconsistent and that there was something buggy going on in the first one, but Jeffrey told me there is a specific rule in the engine that dictates this behaviour, so it’s how it’s intended to work – as a result, it’s something that needs to be understood and allowed for in any scoped assignments you write.
I’ve just tested it and of course
SELECT [Measures].[TESTCALC] ON 0,
[Date].[Calendar Year].Members on 1
FROM [Adventure Works]
returns the expected result (3 for [All Periods] and 2 for individual years). I would really like to know how the “specific rule” is defined and why this can be “intended behaviour”
I too would like to understand why its the intended behaviour.
-Saurav
Chris,
I recreated your experiment and I see similar results but with a couple of exceptions noted below. I ‘d like to know the rules that are in effect here if anyone can offer an explanation. We are struggling with a couple of our scoped measures and this might help explain the results we are seeing.
Thanks,
Paul
CREATE MEMBER CURRENTCUBE.[Measures].[TESTCALC] AS 1;
SCOPE([Measures].[TESTCALC], [Time].[Fisca].[Year].Members);
THIS = 2;
END SCOPE;
SCOPE([Measures].[TESTCALC], [Time].[Fiscal].[All]);
THIS = 3;
END SCOPE;
SELECT
{[Measures].[TESTCALC]} ON COLUMNS,
{[Time].[Fiscal].[All]
,[Time].[Fiscal].[Year].[FY2010]
,[Time].[Fiscal].[Period].[FY2010-APR]} ON ROWS
FROM [Test Cube]
As expected, results in:
All 3
FY2010 2
FY2010-APR 1
Each of these queries also create results with values from the scope statements:
SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube] WHERE {[Time].[Fiscal].[All]}
Result: 3
SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube] WHERE {[Time].[Fiscal].[Year].[FY2010]}
Result: 2
SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube] WHERE {[Time].[Fiscal].[Period].[FY2010-APR]}
Result: 1
Using a multi-member WHERE clause causes the unscoped value to appears:
SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube]
WHERE {[Time].Fiscal].[Year].[FY2010], [Time].[Fiscal].[Year].[FY2011]}
Result: 1
Using Excel 2007, I get a different answer. Looking at Excel’s MDX, I see a subquery. This query returns the “all” scope value of 3.
SELECT FROM
(SELECT
({[Time].[Fiscal].[Year].&[2010],[Time].[Fiscal].[Year].&[2011]}) ON COLUMNS
FROM [Test Cube]) WHERE ([Measures].[TESTCALC])
Result: 3
With a subselect I would expect to get the All Member value back – a subselect doesn’t alter the currentmember, only a where clause does. Also, are you using a user hierarchy (as opposed to an attribute hierarchy) for your tests?
Hi Chris,
This is in reference to the post http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e0a4975f-8f19-40d0-90ff-4fcca1d301c1
Thanks for your help and I was able to solve the issue. Here is the modified code
CREATE MEMBER CURRENTCUBE.[Measures].[SalesAmount]
AS
null,
FORMAT_STRING = “0”,
VISIBLE = 1 ;
/* When the channel is at all Level and when a region is selected show region data */
SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],[In Market Region].[InMarketRegionAttr].members);
[Measures].[SalesAmount]=[Measures].[Re Sales Amount];
END SCOPE;
/* When the channel is at all Level and when all region show channel data */
SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],[In Market Region].[InMarketRegionAttr].[All Regions]);
[Measures].[SalesAmount]=[Measures].[Ch Sales Amount];
END SCOPE;
CREATE MEMBER CURRENTCUBE.[Measures].[TotalAmount]
AS
[Measures].[SalesAmount],
FORMAT_STRING = “0”,
VISIBLE = 1 ;
However I do find one scenario , If I change the Scope statements i.e. If I add the second scope statement at the first place and when I select multiple regions, I am encountering the same issue. However If I first write the scope of region members and then add a scope for all region member then it works fine.
Please let me know why the behaviour. I have no clue why this is happening.
Thanks once again !
It’s hard to say – I’d probably need to spend about an hour looking at your cube and attribute relationships doing some tests before I could be sure why that was happening.
Hi Chris,
I watched your video “fun with scope assignment on SQLbits.com. It is very helpful, I have better understand the scope statement than before.
But I have hard time to understand the problem below.
The first scope statement works just fine; in the second statement I change to use data range to replace the calendar year, it return empty. I cannot figure out why. Can you please help?
scope([Date].[Calendar].[Calendar Year].&[2011]);
scope([Measures].[Most Frequent Price1]);
This=topcount(
[Price].[Price].[Price].MEMBERS,
1,
[Measures].[Invoice Transaction Count]
).Item(0).ITEM(0).MemberValue;
end scope;
end scope;
scope([Date].[Calendar].[Date].&[20110101]:[Date].[Calendar].[Date].&[20111231]);
scope([Measures].[Most Frequent Price1]);
This=topcount(
[Price].[Price].[Price].MEMBERS,
1,
[Measures].[Invoice Transaction Count]
).Item(0).ITEM(0).MemberValue;
end scope
end scope
thanks
Hua
Is [Date].[Calendar] a user hierarchy? Can you try scoping on the attribute hierarchy used to build that level instead?
Chris,
I reviewed the video, found my problem.
thank you for your reply and your video!
I am running into the same situation and I am in a bind.
I tried to use an IF to see if there are more than one member like this:
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[Calendar Year].MEMBERS);
THIS = 2;
If (count(existing [Date].[Calendar Year].[Calendar Year].MEMBERS)>1)
Then
THIS = 5;
End if;
END SCOPE;
The IF works only if there is one member, but NOT if there are more than one members in the WHERE clause of the query!
Each leaf has a different value for a particular measure – that works. But when more than one leaf is selected, I want it to default to specific number (the end user controls what leaves are selected). This is for one measure only. All the other measures aggregate normally.
Any help is appreciated!
Hi BJ, you are getting confused between IF and IIF() here (which is understandable). IF is a way of defining a scoped assignment, which means that it is evaluated when the MDX Script is executed (usually after processing) and not at query time. What’s more, you are scoping at the year level, which means that there will only ever be one year existing. If you want to detect whether more than one year is selected in a query then I suggest that you use Mosha’s method for detecting multiselects rather than trying to use a scoped assignment: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx
Crhis, thanks for the explanation for the “IF”. It makes sense.
Also thanks for the link to Mosha’s blog – it works. Someone else had posted a link to that blog that did not work.
Here is my true problem:
I have a dimension, and for each of the nodes and leaves, Measures.Benchmark gives the correct data.
However, when multiple leaves are selected, I need the Measures.Benchmark for the parent node, and not an aggregated measure of the selected leaves.
This measure is used in several calculations including KPIs, so it makes sense to set the value in a SCOPE statement.
thanks
BJ