CurrentMember failed with (Multiple Items) in the 'filter fields' because ofcourse CurrentMember can NOT be associated with a set !!
Is there a simple way to make this Multiple-Item-safe (in the filter) ?
for example this MDX-formula:
sum(
generate
(
ascendants([Posting Period].currentmember),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + {[Posting Period].currentmember}
,
[Measures].[VRD Mutation]
)Mosha posted a good discussion of this problem on his blog quite recently:
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx|||Interested
In short: use EXISTING-operator or exists-function OR remove CurrentMember statement !
But.... How about rewriting the ascendants-function (instead of descendants) into a multiselect-friendly-mdx ? (See my example above)|||
This problem raises the question of what you want to see when you select multiple members on Posting Period here - what do you want to do the cumulative sum on? I'm going to assume that you want to do the cumulative sum on the most recent selected Posting Period, and if this is correct then the following MDX should work:
sum(
generate
(
ascendants({tail(existing [Posting Period].[Day].Members,1) as myset}.item(0)),
iif(
[Posting Period].currentmember IS
[Posting Period].firstsibling,
{},
[Posting Period].firstsibling:
[Posting Period].prevmember
)
) + myset,
[Measures].[VRD Mutation]
)
Incidentally while looking at this problem, I've realised that the algorithm you're using here from Richard's site is not actually as efficient as it should be: in the scenario where you've selected a member which is the last member in its set of siblings it sums up the complete set of siblings instead of replacing the set with its parent. This effect is magnified when you have to deal with sets in the where clause in the way I've outlined, because I'm forcing everything down to the Month level - see this sample AdventureWorks query as an illustration:
WITH MEMBER MEASURES.TEST AS
GENERATE(
GENERATE(
ASCENDANTS(
{TAIL(EXISTING [Date].[Calendar].[Date].MEMBERS,1) AS MYSET}.ITEM(0)
)
,IIF( [Date].[Calendar].CURRENTMEMBER IS [Date].[Calendar].CURRENTMEMBER.FIRSTSIBLING,
{},
[Date].[Calendar].CURRENTMEMBER.FIRSTSIBLING : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER
)
) + MYSET
, [Date].[Calendar].CURRENTMEMBER.NAME, "; ")
SELECT MEASURES.TEST ON 0,
[Date].[Calendar].MEMBERS
ON 1
FROM [Adventure Works]
I don't think the other (admittedly more complex) approaches on my blog suffer from this problem, so you may want to use them instead.
HTH,
Chris
sql