Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Dynamic MDX Date Script

Answered Dynamic MDX Date Script

  • Thursday, February 14, 2013 9:42 PM
     
     
    Sign In to Vote
    0

    Hello-

    I'm a total newbie to MDX and really the whole Microsoft BI platform but am working my way through it.  Anyway, I have some SSAS cubes in which we're trying to pull the last 8 days of activity into a SSRS report.  In addition, the desire is to make this parameter based so that if the end user wanted to change the range, he/she would be able to.

    The first snippet of code is the system generated code:

     SELECT NON EMPTY { [Measures].[Patient Days] } ON COLUMNS,
     NON EMPTY { ([Time].[Apply Date].[Apply Date].ALLMEMBERS
     * [Location].[Location Short Name].[Location Short Name].ALLMEMBERS
     * [Version].[Version Name].[Version Name].ALLMEMBERS
     * [Payor].[Hierarchy].[Payor Name].ALLMEMBERS ) }
     DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
     FROM
     ( SELECT ( [Time].[Apply Date].&[2013-01-01T00:00:00] : [Time].[Apply Date].&[2013-01-07T00:00:00] ) ON COLUMNS
     FROM ( SELECT ( { [Version].[Version Name].&[Budget], [Version].[Version Name].&[Actual], [Version].[Version Name].&[Variance] } ) ON COLUMNS
     FROM [CL_Census]))
     CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    I've been trying to make the bolded line above a dynamic date range.  I have replaced the above bolded line with the line below but have NOT even got past the first part (start) of the date range even. 

    ( SELECT ( "[Time].[Apply Date].&["+Format(Today(), "yyyy-MM-dd")+"T00:00:00]": [Time].[Apply Date].&[2013-01-07T00:00:00] )  

    RESULT:

    Executing the query ...
    Query (9, 44) The '[Today]' function does not exist.
    Execution complete

    Thanks so much in advance for any assistance you can provide to me!

    Rich

     

All Replies

  • Thursday, February 14, 2013 11:10 PM
     
     
    Sign In to Vote
    0

    following MDX will get the last 7 days from the parameter which you passed from ssrs report. you have to study LAG () for your future refrence.

    WITH
      MEMBER [Measures].[Sum Of Last 7 Days] AS
        Sum
        (

    {
              [Date].[Calendar].CurrentMember.Lag(6)
            :

    [Date].[Calendar].CurrentMember
          }
         ,[Measures].[Internet Sales Amount]
        )
    SELECT
      {
        [Measures].[Internet Sales Amount]

    ,[Measures].[Sum Of Last 7 Days]
      } ON COLUMNS
    FROM [Adventure
    Works]
    WHERE
      [Date].[Calendar].[Date].&[20070827];

                     
     
  • Friday, February 15, 2013 3:01 PM
     
     
    Sign In to Vote
    0

    Thanks for your quick reply!

    I'm not sure from there how to then transform my current query whereas I need to format the lag date(s) to the acceptable string format, with the time syntax (00:00:00) required by SSAS?

    Thanks again for your help!!!

    Rich

     
  • Saturday, February 16, 2013 12:26 AM
     
     Answered Has Code
    Sign In to Vote
    1
     
    SELECT NON EMPTY { [Measures].[Patient Days] } ON COLUMNS, 
 NON EMPTY { ([Time].[Apply Date].[Apply Date].ALLMEMBERS 
 * [Location].[Location Short Name].[Location Short Name].ALLMEMBERS 
 * [Version].[Version Name].[Version Name].ALLMEMBERS 
 * [Payor].[Hierarchy].[Payor Name].ALLMEMBERS ) } 
 DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
 FROM 
 ( SELECT ( [Time].[Apply Date].LAG(6) ) ON COLUMNS
  FROM ( SELECT ( { [Version].[Version Name].&[Budget], [Version].[Version Name].&[Actual], [Version].[Version Name].&[Variance] } ) ON COLUMNS
  FROM [CL_Census])) 
 CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    • Marked As Answer by Scullpa Wednesday, February 20, 2013 2:10 PM
    •  
     
  • Wednesday, February 20, 2013 9:06 AM
    Moderator
     
     
    Sign In to Vote
    0

    ( SELECT ( "[Time].[Apply Date].&["+Format(Today(), "yyyy-MM-dd")+"T00:00:00]": [Time].[Apply Date].&[2013-01-07T00:00:00] )  

    RESULT:

    Executing the query ...
    Query (9, 44) The '[Today]' function does not exist.
    Execution complete

    Hi Rich,

    This issue might be caused by the "Today()" function in your MDX query. In a SSRS report, if we have two date parameters and want to pass the date values to filter data, please refer to the following expression:
    STRTOMEMBER("[Time].[Short Date].&["+@Time_Start+"]") : STRTOMEMBER("[Time].[Short Date].&["+@Time_End+"]")

    If you want to format date parameter value, here is a similar thread about this topic for your reference. Please see:
    http://social.technet.microsoft.com/Forums/en/sqlanalysisservices/thread/50f76c6e-008f-4dae-921f-be6601962fa7

    Regards,

    Elvis Long
    TechNet Community Support

     
  • Wednesday, February 20, 2013 2:11 PM
     
     
    Sign In to Vote
    0

    This did it! 

    Thanks Zaim as well as everyone else who assisted me with this!  It's greatly appreciated!!!

    Rich