CurrentMember and a set

I have an excel sheet that people are using with pivot tables. The users want to be able to select a set of dates, and get a current year measure, along with the corresponding last year measures on a day to day comparison (not day of year, or date vs. date, but a "monday this year vs. monday last year", which is 364 days ago). They want to be able to pick and choose which days go into the set, for instance they want 1/1/cy, 2/1/cy, 2/5/cy and the sum of a measure vs. 1/2/ly, 2/2/ly, and 2/6/ly same measure summed up.

I can do this for a single selection (using ([Calendar].[Date].CurrentMember.Lag.(364), [Measure]), but I don't know how to access a list of items in the calcluated measure when they select a set.

If I could write this in MDX it would be much easier, but they would like to have control over it and do it through Excel (who can blame them?).

Does anyone have any suggestions?

thank you in advance,

John Hennesey

Assuming that you're using AS 2005, which version of Excel is involved (2003 issues different multi-select MDX queries than 2007)?|||

Sorry I left that detail out. I am using Excel 2003.

Thanks in advance,

John

|||

Hi John,

With Excel 2003, you should be able to use the "Existing" approach, discussed in Mosha's blog:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

...

But obviously we wanted AS2005 to work well with existing client tools too. Therefore, AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

...

>>

So, in your scenario, the calculated measure MDX expression could be like:

Aggregate(Generate(Existing [Calendar].[Date].[Date], {[Calendar].[Date].Lag(364)}), [Measure])

|||

Awesome - thank you very much for the response. I will give it a shot. One question - when it iterates through the currently selected set, how will it know which ones are selected? Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember? If this is a dumb question, please forgive me - I'm still fairly new to MDX.

Once again, thanks!

John

|||

"when it iterates through the currently selected set, how will it know which ones are selected?" - Mosha's blog entry above explains this better than I could.

"Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember" - no, this is the [Date] level of the [Calendar].[Date] hierarchy, and is shorthand for [Calendar].[Date].[Date].Members, which you can use instead. This all applies if you're using AS 2005, of course.

|||Cool - I will dig into Mosha's blog. Thank you for your quick response!