Hi there,

I am using the following MDX to default to the current month.

tail(filter([FiscalYear].[Month].members, not
isempty([FiscalYear].currentmember)),1).item(0)


The issue i have however, is if there is an incorrect transaction in the system for a date in the future, or of there is budget data, this MDX will not work. How can I use MDX to somehow point to the Current month from the System date?

Is there a way to do this?

e.g. Where system month = dimension time period

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....e9dbd7cbd61333
*****************************************

Hi,
I think u can use month(now())as a calculated member to get the current
month.

Neo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

A combination of StrToMember and calling "VBA!CStr(Month(Now()))" may
help you out.

Steve

Thanks Neo...I have tried using this function, however it returns an integer as in 11 for November...I need to be able to match this to a member in my Dimension....i.e. November 2004

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....rver-olap/4563
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....a79a840135c2b0
*****************************************

I have tried using it as:

StrToMember(VBA!CStr(Month(Now())))

But it errors...sorry but my MDX is far from good.

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....rver-olap/4563
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....bb4aa05f8db0bf
*****************************************

I have tried using it as:

StrToMember(VBA!CStr(Month(Now())))

But it errors...sorry but my MDX is far from good.

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....rver-olap/4563
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....e85e512f4629d3
*****************************************

The exact syntax depends on the structure of your date dimension. Here's
a working example for the Foodmart Sales cube. It selects the current
calendar month (11), but subtracts 7 from the current year, to select
1997 data:

{StrToMember("[Time].["
+ CStr(Year(Now())-7) + "].[Q"
+ CStr(CInt((Month(Now())-1)/3)+1) + "].["
+ CStr(Month(Now())) + "]")} on rows
from Sales

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Is this code used in a calculated member?

*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum....rver-olap/4563
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse....c72ccd5f5e97f1
*****************************************

This is an MDX query, which can be run in the MDX Sample App that is
installed with Analysis Manager. But the StrToMember() section can
certainly be used elsewhere.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Smith,
try the MDX below

strtomember("Time.[" + IIf(Month(Now()) = 1,
LTrim(RTrim(Str(Year(Now()) - 1))), LTrim(RTrim(Str(Year(Now()))))) +
"].&[" + IIf(Month(Now()) = 1, LTrim(RTrim(Str(12))),
LTrim(RTrim(Str(Month(Now()) - 1)))) + "]")

the above MDX would give you previous month from the current month
which you can change according to your need

Regards
Prasanna

"Jared Smith via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote