Creating an MDX Set available to Excel for last 3 months

• giles.clapham

  SSCrazy

  Points: 2204

  More actions

  January 9, 2013 at 9:42 am

  #272647

  Hello,

  I've created a Set that returns the previous 3 months in a dimension and I can successfully use it (i.e. I get the previous 3 months displayed individually), see below:

  Create Set [Cube].ThreeMonths as LastPeriods(3,StrToMember("[Take Date].[Month].&["+CStr(Year(Now()))+"-"+Right("00"+CStr(Month(Now())) ,2)+"-01T00:00:00]").lag(1))

  Select [Channel].[Channel Name].members on 0,

  ThreeMonths on 1

  from [Cube]

  That works fine, however (and I'm sure I've over looked something really simple) what I can't do is "save" that Set for use later on, or by other queries or and, most importantly for me, by people using Excel.

  In "Professional SQL Server Analysis Services 2005 with MDX" by Wrox it mentions they can be scoped Globally but I can't find out how, or is that a red herring?

• Mackers

  SSCrazy

  Points: 2935

  More actions

  January 9, 2013 at 9:52 am

  #1575330

  You need to add this to the calculation script of your cube

  CALCULATE;

  CREATE SET CURRENTCUBE.[ThreeMonths]

  AS

  LastPeriods(3,StrToMember("[Take Date].[Month].&["+CStr(Year(Now()))+"-"+Right("00"+CStr(Month(Now())) ,2)+"-01T00:00:00]").lag(1));

  Mack

• giles.clapham

  SSCrazy

  Points: 2204

  More actions

  January 9, 2013 at 10:33 am

  #1575344

  Works perfectly thanks, and now I know to look at that area I've found it in the book too!

  Thanks again.