MDX: How to access currently selected dimension members?

0
votes

I'm trying to create a measure in MDX to get the first day in the selected period (I have a YMD date dimension). I'm using EXISTING function to get the selected members. It's working fine if I only select one dimension member in Excel pivot table filter. However, as soon as I select multiple members (Example: 2012 & 2013 together), the EXISTING function is not working as expected.

I've created another measure to debug and see what is going on. The measure is defined as:

SetToStr(EXISTING([Date].[Date YMD].[Year].members))

If I have only one dimension member selected, this works fine, I get this back:

{[Date].[Date YMD].[All].[2013]}

However, as soon as I select 2012 and 2013 together, I get a list of all dimension members back:

{[Date].[Date YMD].[All].[N/A],[Date].[Date YMD].[All].[2007],[Date].[Date YMD].[All].[2008],[Date].[Date YMD].[All].[2009],[Date].[Date YMD].[All].[2010],[Date].[Date YMD].[All].[2011],[Date].[Date YMD].[All].[2012],[Date].[Date YMD].[All].[2013],[Date].[Date YMD].[All].[2014]}

The EXISTING function seems to work only when a single member is selected?

--

Update:

Maybe I was not clear enough in the original post. The problem I'm facing is getting the first and last date member if the date dimension is being filtered (in an Excel pivot table filter) and multiple date members are selected in the filter (example: when years 2012 & 2013 are selected together).

I've tried using the solution from here: http://bimic.blogspot.com/2011/07/mdx-rewrite-query-with-currentmember.html, but to no success.

I've created 2 measures now:

  1. First Day Single:
HEAD(
DESCENDANTS(
    [Date].[Date YMD].CURRENTMEMBER,
    [Date].[Date YMD].[Day]
    ),
1
).ITEM(0).member_value
  1. First Day Multiple Years
MIN(EXISTING [Date].[Date YMD].[Year].members, [Measures].[First Day Single])

Unfortunately I can't include a screenshot directly. You can see it on this link: http://social.msdn.microsoft.com/Forums/getfile/446659

As you can see, the measures work when a single year is selected in the pivot table filter, but don't work when you select more than one year.

2
ssasmdx
What tools are you using to add this new measure? Are you writing this MDX via SQL-Server Management Studio, or using Excel with a connection to your cube? – whytheq
I'm using OLAP Pivottables extensions. – saso
Doesn't the first day depend on the current granularity? Say if you have 2 years selected 2012, and 2013, it will select the first day of the first year - which is 1st Jan 2012. Isn't that correct? – Mez
Hi Stephen! Yes, I expected to get back the first day of the first year (e.g. 1.1.2012), but this is not the case. The code I've posted does not work if multiple years are selected. Take a look at the screenshot: social.msdn.microsoft.com/Forums/getfile/446659 – saso
@StephenBorg that is what we expect but not the observed behaviour – whytheq

2 Answers

0
votes

There is a workaround for your problem described here: http://sqlblog.com/blogs/mosha/archive/2007/09/26/how-to-detect-subselect-inside-mdx-calculations-aka-multiselect-in-excel-2007.aspx. But, as the writer (one of the developers of SSAS) writes, " the solution is neither elegant nor efficient". Anyway, it needs another measure group to be added to the cube, and a stored procedure to be written.

3
votes

In my cube and via management studio I can write a script like this to create measures that return numeric values based on the first day and last day of each month:

WITH 
SET [Last12Months] AS
    TAIL ( 
        [Date].[Date - Calendar Month].[Calendar Month].members, 
        12)
MEMBER [Measures].[FirstDay] AS
    HEAD(
        DESCENDANTS(
            [Date].[Date - Calendar Month].CURRENTMEMBER,
            [Date].[Date - Calendar Month].[Calendar Day]
            ),
        1
        ).ITEM(0).member_value
MEMBER [Measures].[LastDay] AS
    TAIL(
        DESCENDANTS(
            [Date].[Date - Calendar Month].CURRENTMEMBER,
            [Date].[Date - Calendar Month].[Calendar Day]
            ),
        1
        ).ITEM(0).member_value
SELECT
    {[Measures].[FirstDay],[Measures].[LastDay]} ON 0,
    [Last12Months] ON 1
FROM [MyCube]

We use Office 2010 but using the OLAP Pivottables extensions add-in I can add the following two measures to my pivottable:

1.[Measures].[FirstDay]

HEAD(
    DESCENDANTS(
        [Date].[Date - Calendar Month].CURRENTMEMBER,
        [Date].[Date - Calendar Month].[Calendar Day]
        ),
    1
    ).ITEM(0).member_value

2.[Measures].[LastDay]

TAIL(
    DESCENDANTS(
        [Date].[Date - Calendar Month].CURRENTMEMBER,
        [Date].[Date - Calendar Month].[Calendar Day]
        ),
    1
    ).ITEM(0).member_value

Now whatever I use in Rows I get the correct answer from the pivot:

enter image description here

EDIT

If I manipulate the pivot so that our date dimension is in the pivot's filter and then go for multi-select of 2013 and 2014 it seems that the `mdx' which excel is using is the following:

WITH 
MEMBER [Measures].[FirstDay] as
 HEAD (
  DESCENDANTS ( [Date].[Date - Calendar Month].CURRENTMEMBER, [Date].[Date - Calendar Month].[Calendar Day] )
  , 1
 ).ITEM( 0 ).member_value
MEMBER [Measures].[LastDay] as
 TAIL (
  DESCENDANTS ( [Date].[Date - Calendar Month].CURRENTMEMBER, [Date].[Date - Calendar Month].[Calendar Day] )
  , 1
 ).ITEM( 0 ).member_value
SELECT
  { [Measures].[FirstDay], [Measures].[LastDay] }  ON COLUMNS
FROM 
   (
   SELECT 
     ({ [Date].[Date - Calendar Month].[Calendar Year].&[2012], 
        [Date].[Date - Calendar Month].[Calendar Year].&[2013] }) ON COLUMNS
   FROM [OurCube]
   )

I think the context of the function CURRENTMEMBER in the custom measures of this script will be lost because of the sub-select.