Selecting dimension's default member based on a member property

Suppose, I need to write an MDX statement that selects a certain month of my Time dimension based on a Month level member property, FLAG_LAST_MONTH, below are the two approaches:

SELECT {StrToMember(Date.CurrentMember.Properties("FLAG_LAST_MONTH"))} on rows,

{} on columns

from MyCube

or

Based on the property name: FLAG_LAST_MONTH, I assume that it has a value like "1" only for one month.  In that case, you could use Filter() to select the desired month member:

SELECT Filter([Date].[Month].Members,

[Date].CurrentMember.Properties("FLAG_LAST_MONTH") = "1") on rows

from MyCube

If you try to use the below query, you get an error, since Date.CurrentMember.Properties("FLAG_LAST_MONTH") - doesn't return a member,

SELECT {Date.CurrentMember.Properties("FLAG_LAST_MONTH")} on rows,
{Date.Month.Members} on columns
from MyCube

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=911884&SiteID=1

Time Dimension: How to set Default Member to Current Month

Question:

I want to set current Month as a Default member in my Time Dimension. So that every time i see my data it should display most current data.

Answer:

Here is an example using the Adventure Works cube.  You can add this to the cube script for the Adventure Works cube and it will default the day to the current date using the Now() function.  I had to use (Now() - 1000) to set the date back to 3/25/2004 due to the fact that the Adventure Works cube Date dimension ends at 8/31/2004, but I think you will get the idea.  The other thing to note here is that the [Date].[Date] attribute has a "ValueColumn" defined that is of type "Date".  This allows the filter statement to use a straight date vs. date comparison.

-- Now() = 12/19/2006

-- Now() - 1000 = 3/25/2004

ALTER CUBE CURRENTCUBE

UPDATE DIMENSION [Date],

DEFAULT_MEMBER = Tail(Filter([Date].[Date].Members,[Date].[Date].MemberValue < (Now() - 1000)),1)(0);

HTH,

Steve

---

Steve Pontello

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1036989&SiteID=1

Setting dynamic default member in dimension X based on the current member of dimensions Y

Is it possible to define a default member in dimension X based on the current member of dimensions Y.

E.g. I have 2 dimensions Cust (Customer)  and Ver (version).

Suppose I have the following facts:

Customer 1, version 1

Customer 1, version 2

Customer 2, version 1

Customer 2, version 2

Customer 2, version 3

The default member in the version dimension should be the highest version for this specific Customer.

Customer 1, version 2

Customer 2, version 3

I wrote the following MDX

(TAIL(NONEMPTY({[Ver].[Ver - Ver].children}, [Cust].[Cust - Cust].CurrentMember), 1)).Item(0)

However this returns the following error when I try to browse the cube from BIDS.

DefaultMember(Ver,Ver) (1, 46) The dimension '[Cust]' was not found in the cube when the string, [Cust].[Cust - Cust], was parsed.

When I connect from Excel, the default member is ignored and the ALL level is used.

Answer:

What I would do is to add a record into your version table called "Latest" or "Current" or something like that. Then I would setup this new member as the default member and add a script like the following to the cube.

SCOPE ([Ver].[Ver - Ver].[Current]);

   this = Aggregate(EXISTING [Cust].[Cust - Cust].Members

                         , TAIL(NONEMPTY({[Ver].[Ver - Ver].children} ), 1).Item(0)

                            )

END SCOPE;

This script finds all of the customers currently in context and then finds the last version for each one and aggregates them all together.

The problem with using .CurrentMember in a default member declaration is that .CurrentMember returns the member currently in context for a given query. The default member is established before any queries take place, so there is no .CurrentMember.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2394139&SiteID=1