It’s my 30th birthday today so before I disappear down the pub to buy everyone else a pint (which is the bizarre tradition at my workplace) I thought I’d mark the day with a quick blog entry…
The MemberValue property is something I saw in BOL quite a while ago, but didn’t really understand what it was for. It wasn’t until George, Siva et al started discussing it for ‘MDX Solutions’ that I really grasped why it is so useful: it’s for those occasions when you want to tie a value other than a caption to a member, and don’t want to go to the hassle of creating a separate attribute for that value. So, for example, imagine all of your Products have a weight attribute (eg 10Kg, 15Kg, 80Kg) and you want to run queries like a) Show me Sales for products which weigh 10Kg, and b) Show me sales broken down by weight for products which weigh more than 30Kg. In AS2K you might have ended up doing this by having two separate member properties for weight: one which contained a user-friendly string, such as "10Kg", which you might create a virtual dimension from or display in a query and a second, containing just the numeric value such as "10", to make filtering easier (although you’d still have had to cast it to an integer before you could do this). In AS2005 all you need to do is tie the user-friendly description to the NameColumn property and the filter-friendly value to the ValueColumn property (which, incidentally, is typed).The MemberValue property of a member then allows you to get this value back in MDX. Here’s an example from AdventureWorks, showing how the calendar year member has a name like "CY2001" but you can still get at the year as a smallint without the "CY" bit in front:
WITH
MEMBER MEASURES.DEMO AS [Date].[Calendar].CURRENTMEMBER.MEMBERVALUESELECT
{[Measures].[Internet Sales Amount], MEASURES.DEMO} ON 0,FILTER
([Date].[Calendar].[Calendar Year].
MEMBERS, [Date].[Calendar].
CURRENTMEMBER.MEMBERVALUE>2001.5)ON
1FROM
[Adventure Works]Take a look at Period in the Dimension Editor in Visual Studio to see exactly how it all works. If you don’t define anything in the ValueColumn, MemberValue returns either the key if there is no name binding or if the key and the caption are bound to the same column, or failing that the caption of the member.
Cheers, Chris, hope you downed enough pints to make MDX crystal clear to all concerned!This .MemberValue Property discussion made me recall your June 30th Blog thread on "Measure Expressions", to which Mosha had responded that MemberValue() was not a function. But this recent MSDN paper on DrillThrough in AS 2005 mentions the MemberValue() function in the context of the RETURN clause of a DrillThrough:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp>>Enabling Drillthrough in Analysis Services 2005…Dimension AttributesWhen a dimension attribute is specified in the RETURN clause, the drillthrough result column will contain the member name of that attribute for that detail level record. Attributes are actually complex entities containing multiple scalar values. So the RETURN clause supports functions that can extract these scalar values. Key(attr, N): Returns the Nth part of the composite key of the member. Name(attr): Returns the name of the member. MemberValue(attr): Returns the value of the member. This is the default. Caption(attr): Returns the caption of the member. UniqueName(attr): Returns the unique name of the member. CustomRollup(attr): Returns the custom rollup expression of the member. CustomRollupProperties(attr): Returns the custom rollup properties of the member. UnaryOperator(attr): Returns the unary operator of the member. …>> – Deepak
Hello Chris,Lately I have been working with MDX and currently I have been a problem with the next calculated member creating a "LOCAL CUBE":Aggregate( {[Time].[Fiscal Weeks].&[F2005].&[53].&[06/27/2005], [Time].[Fiscal Weeks].&[F2005].&[53].&[06/28/2005]} ,([Time Special Periods].&[0]) )the special time periods is a member across all the fact table.Everything works weell, however problem came out with the NULL values, If at least one of the members on the list is NULL the calculated member is not created.I have tried with non empty, nonemptycrossjoin filtering etc… but nothing worked.do you have any idea how I could modify this MDX expression to avoid NULL values.Thanks,