MDX for Named Set 'Current Month'

Question

Any help would be appreciated.  I am trying to create a named set for Current Month for a cube in SSAS 2005. 

 

 My Time Dimension hierarchy is as follows:

 

Year > Quarter > Month Number > Day of Month

 

I believe the MDX for Current Month is a variation of the following:

 

{StrToMember("[Activity Date].[Month Number].[" + Format(Now(), "mm") + "]")}

 

This results in the following error:

 

Error 1 The level '[36]' object was not found in the cube when the string, [Activity Date].[Month Number].[36], was parsed.  

 

Can anybody assist with what I might have wrong.  If additional details are necessary, please let me know.

 

Thanks....

 

KRS

 

 

 

Answer 1

Hi KRS,

 

I am getting pretty much the same thing.

 

The level '&[0]' object was not found in the cube when the string, [Time Financial].[Time Financial].&[0], was parsed.

 

OK we havent changed this cube at all and this started this morning 14 Sept 2007 after a scheduled inc update.

 

I processed the [Time Financial] dimension fully and could navigate it with no problem.

At first I was convinved there was some Calculated measure that was using [Time Financial].[Time Financial].&[0] directly. Search throught all of them with no luck.

Then did a full process again and all prcessed OK but got the same error right at the end.

 

Still trying to figure it out...

Answer 2

You're probably returning minutes.    Here's how I get months.  SELECT

{} ON 0

,Ancestor (StrToMember("[Date].[Calendar].[date].&["+ Cstr(Format(Now()-2383,'yyyyMMdd'))+ "]"),[DATE].[Calendar].[Month])

ON 1 FROM [Adventure Works];

//the -2383 puts the date back to 2005

---

Jim Barker