I have a Date dimension called Accounting Month. I want to know what is the current month ; Please let me know what's the MDX for it

E.g. I want to write IIf [Accounting Period].Month=Jan2003 ,ABABAB,ccccc

There are multiple ways to handle this depending on what you actually need.
One way is to just create a member property on the month level of the
dimension and call it "CurrentMonth" for example. Then you could simply
have this MDX to identify the current month.

FILTER(AccountingMonth.month.members,
AccountingMonth.currentmember.properties("CurrentM onth") = 1)

Another alternative is to use the VBA Now() function and do some string
manipulation. Here's an example.

StrToMember("[AccountingMonth].[Month].[" + Format(Now(), "mmmm ") + "]")

You'll have to use the appropriate arguments for the format function based
on how you are formatting your dates. The easiest way to test this out is
to create a calculated member on the measures dimension that displays the
result of the format function.

Sean
--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.





"Narayan" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I am getting #ERR when I try the following MDX in the calculated Member
StrToMember("[Accounting Period].[Month].[" + Format(Now(), "mmmm ") + "]"

Please llet me know what the proper syntax should b

Thank

Here's an example of how to use the syntax from Foodmart (or for that matter
any cube).

with member [Measures].[Test] as 'Format(Now(),"mmmm")'

SELECT {[Measures].[Test]} on 0 from sales

My suggestion would be to try out the different arguments for the Format()
function and see what they
--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
return. You'll then need to construct the proper strings based on how your
dimensions members are named. You can then wrap that in a StrToMember()
call.









"Narayan" <anonymous (AT) discussions (DOT) microsoft.com> wrote