none
Need a calculated member to use values outside the slicer dimension. Possible?

    Question

  • OK, I've been struggling with this and can't find a good way to do this and would appreciate anyone's input.

    We have a cube that tracks site registration information on a daily basis; the basic measure is the number of new members registered.

    We need to be able to display the number of new registrations per day (or week or month) as well as a running total of all registrations to date.

    My basic query looks like this.

    WITH MEMBER [Measures].[Total Registered] as SUM({NULL:[Date].[Week].currentmember},[Measures].[New Registered])
    SELECT ({[Measures].[New Registered],[Measures].[Total Registered]}) ON COLUMNS, 
           ({[Date].[Week].[Week]}) ON ROWS 
    FROM Analytics 

    This works, as long as I am wanting all dates. The problem occurs when I try to limit the dates I am considering.

    WITH MEMBER [Measures].[Total Registered] as SUM({NULL:[Date].[Week].currentmember},[Measures].[New Registered])
    SELECT ({[Measures].[New Registered],[Measures].[Total Registered]}) ON COLUMNS, 
           ({[Date].[Week].[Week]}) ON ROWS 
    FROM Analytics 
    WHERE ({[Date].[Date].&[2010-04-01T00:00:00] : [Date].[Date].&[2010-06-15T00:00:00]})

    This doesn't do exactly what I want. The running [Total Registered] in this case starts on 4/1 so it vastly under displays registrations.

     

    Now, our daily display is working; we use this:

    WITH MEMBER [Measures].[Total Registered] as SUM(NULL:[Date].[Date].currentmember,[Measures].[New Registered])
    SELECT   ({[Measures].[New Registered],[Measures].[Total Registered]}) ON COLUMNS, 
    NON EMPTY({[Date].[Date].&[2010-04-01T00:00:00] : [Date].[Date].&[2010-06-15T00:00:00]}) ON ROWS 
    FROM Analytics

    We originally tried to use the same format for the week/month display

    WITH MEMBER [Measures].[Total Registered] as SUM(NULL:[Date].[Week].currentmember,[Measures].[New Registered])
    SELECT   ({[Measures].[New Registered],[Measures].[Total Registered]}) ON COLUMNS, 
    NON EMPTY({[Date].[Week].&[2010-04-01T00:00:00] : [Date].[Week].&[2010-06-15T00:00:00]}) ON ROWS 
    FROM Analytics

    This works as long as the Start and End dates happen to land exactly on a week/month boundary but our requirement is to allow for any start/end date combo. (The query is being generated by a .NET package and is ultimately being displayed in graph form in a screen widget. End users will be able to enter any dates. I am tasked with coming up with an MDX "template" that the .NET routine will populate with the parameters provided by the user.)

    Sadly my limited MDX knowledge is failing me. Can anyone point me towards how to get around this (or come up with a better way of accomplishing it)? Thanks.

    Wednesday, June 15, 2011 9:33 PM

Answers

  • Something like the following might work:

    WITH MEMBER [Measures].[Total Registered] as SUM(NULL: TAIL(EXISTING [Date].[Date].[Date].members,1).item(0).item(0)
                                                                              ,[Measures].[New Registered])

    Basically it says to find all the days that existing in conjunction with the current context. So it will get all of the days that exist with the current year, month, week or day, then it gets the last member from that set of days and goes back to the start of time.


    http://darren.gosbell.com - please mark correct answers
    Thursday, June 16, 2011 1:55 AM

All replies

  • Something like the following might work:

    WITH MEMBER [Measures].[Total Registered] as SUM(NULL: TAIL(EXISTING [Date].[Date].[Date].members,1).item(0).item(0)
                                                                              ,[Measures].[New Registered])

    Basically it says to find all the days that existing in conjunction with the current context. So it will get all of the days that exist with the current year, month, week or day, then it gets the last member from that set of days and goes back to the start of time.


    http://darren.gosbell.com - please mark correct answers
    Thursday, June 16, 2011 1:55 AM
  • THANK YOU! That seems to have fixed it!

    I can do almost anything in straight SQL but still can't quite wrap my head around MDX. Thanks again!

    Thursday, June 16, 2011 1:36 PM