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

1 comment:

Anonymous said...

Good day !.
You may , perhaps very interested to know how one can reach 2000 per day of income .
There is no need to invest much at first. You may commense to receive yields with as small sum of money as 20-100 dollars.

AimTrust is what you haven`t ever dreamt of such a chance to become rich
The company represents an offshore structure with advanced asset management technologies in production and delivery of pipes for oil and gas.

It is based in Panama with affiliates around the world.
Do you want to become an affluent person?
That`s your chance That`s what you really need!

I feel good, I started to take up real money with the help of this company,
and I invite you to do the same. It`s all about how to select a proper companion utilizes your savings in a right way - that`s it!.
I make 2G daily, and my first deposit was 1 grand only!
It`s easy to start , just click this link http://hybipuvuq.freecities.com/yhexyw.html
and go! Let`s take this option together to become rich

My Articles

Design

Cube structure optimization for MDX query performance in Analysis Services 2005 SP2: Tips for Parent Child Hierarchies usage

Fact table design for “State Workflow Analysis”: Analysis Services Dimensional modeling

Handling inter-dimensional members dependency and reducing cube sparsity using reference dimensions in Analysis Services 2005 SP2 : Cube design tip

Identifying intra-dimensional members relationship and reducing cube sparsity in Analysis Services 2005 SP2 : Cube design tip

Leaves() : An example to understand it for both regular hierarchies as well as parent child hierarchies

Aggregation design: useful tips

Level based attribute hierarchy: MDX query performance woes in SQL Server 2005 SP2: Is it fixed in post SP2 hotfix?

Parent child hierarchy to level base hierarchy conversion: hiding placeholder dimension members in client application

Trouble / Troubleshooting

Aggregate(), Sum() functions using calculated members does not work in Analysis Services 2005 SP2 (9.00.3042.00 version) but works in Analysis Services 2000 SP4

Analysis Services 2005 migration tool: Custom member formula issues in migrated database

Cube Partitions: Fact table not listing in Business Intelligence Development Studio in partition wizard

Analysis Services 2005: Many-to-Many relationship does not support unary operators with parent-child dimension

MDX

NextAnalytics and MDX : Part 1 - Swap Cells with Row Labels

Selecting dimension's default member based on a member property

Sorting members on member codes / member properties

Time Dimension: How to set Default Member to Current Month

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

ADOMD.NET

Code : utility code for converting cellset to a data table

Others

Google specialized search for Analysis Services and MDX web resources integrated in my blog

Art of reading MDX articles

MDX Expression Builder : Need for a tool making it easier for functional users to write MDX expressions, queries.

Blogroll