locked
mdx report datetime parameter issues RRS feed

  • Question

  • I have created an MDX report that uses below query:

     

    SELECT
      NON EMPTY
        {
          [Measures].[Anchor Production Duration]
         ,[Measures].[Duration]
         ,[Measures].[Current Work Standard]
         ,[Measures].[Loss Point Value]
         ,[Measures].[Efficiency Current Work Standard]
         ,[Measures].[Anchor EV Value]
         ,[Measures].[Plant Operating]
         ,[Measures].[Anchor CWS]
        } ON COLUMNS
     ,NONEMPTY
        ({
            [NewProduct].[Product Name].[Product Name].ALLMEMBERS*
            [AssetOrganizational].[PLANT NAME].[PLANT NAME].ALLMEMBERS*
            [ProcessParameter2].[PARAM NAME].[PARAM NAME].ALLMEMBERS*
            [DayOfShift].[DATE ID].[DATE ID].ALLMEMBERS*
            [AssetOrganizational].[ASSET NAME].[ASSET NAME].ALLMEMBERS
        },[Measures].[Anchor Production Duration])
      DIMENSION PROPERTIES
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME
       ON ROWS
    FROM
    (
      SELECT
    
          StrToMember
          (@StartDate
           ,CONSTRAINED
          )
    
        :
          StrToMember
          (@EndDate
           ,CONSTRAINED
          )
     
      ON COLUMNS
      FROM
      (
        SELECT
          StrToMember
          (@PlantId
           ,CONSTRAINED
          )
        ON COLUMNS
        FROM [FusionNewCube]
      )
    )

     

     

    The @StartDate and @EndDate parameters have following parameter value expressions:

    ="[DayOfShift].[DATE ID].&[" + Format(CDate(Parameters!StartDate.Value), "yyyy-MM-ddT00:00:00") + "]"

    ="[DayOfShift].[DATE ID].&[" + Format(CDate(Parameters!EndDate.Value), "yyyy-MM-ddT00:00:00") + "]"

    This works fine for me. Now the issue is that these above parameter expressions help me find measure values for the StartDate to EndDate range which is not inclusive of time.

    I want to query the cube with datetime values also.

    The cube also has another dimension which I understand, can satisfy this requirement. It is as below.

    [DayOfMinute].[Granularity].[MINUTE ID].&[2010D287HR12MIN45]

    Here, in above example, D287 refers to 287th day of the year(as per my understanding of this cube) . I have tried to use this dimension for my date parameter expression but in vain. I am not sure how to format the date expression for the  287th day. See below tried implementation which fails (have also tried a few other implementaions).

    ="[DayOfMinute].[Granularity].[MINUTE ID].&[" + Format(CDate(Parameters!StartDate.Value), "yyyyDdddHRhhMINmm") + "]"

    ="[DayOfMinute].[Granularity].[MINUTE ID].&[" + Format(CDate(Parameters!EndDate.Value), "yyyyDdddHRhhMINmm") + "]"

    I get the error as follows:The Restrictions imposed by the constrained flag in the STRTOMEMBER function were violated.

    I need to pass either date only, or datetime to @StartDate and @EndDate parameters depending on a flag setting in my UI. I am not sure if I can achieve this by using the same parameters for both of these different type of inputs. (I am using Calender Control in my SSRS 2008 report for @StartDate and @EndDate parameters. Depending on the flag setting on the UI, if flag is set for datetime values, I need to retrieve the datetime values, from relational database tables by calling a custom function, for dates specified by user in Calender control. These I will then pass to my mdx date parameters.) 
    Do I need to go for a linked server implementation where I can call the mdx query inside a SQL Stored procedure if it is not possible this way. 
    Please suggest a solution.
    Thanks



     

     


    • Edited by MIDS Saturday, September 3, 2011 4:25 PM improved readability
    Saturday, September 3, 2011 4:21 PM

Answers

  • The error "The Restrictions imposed by the constrained flag in the STRTOMEMBER function were violated." happens if the STRTOMEMBEr function donot resolve to the existing member.

    Try using the below expression to construct the required member. Replace "01-Sep-2011 11:12:23" with your startdate or endate parameters.

    ="[DayOfMinute].[Granularity].[MINUTE ID].&[" 
    + cstr(DATEPART("yyyy","01-Sep-2011 11:12:23"))  
    + "D"
    + cstr(DATEPART("y","01-Sep-2011 11:12:23"))  
    + "HR"
    + cstr(DATEPART("h","01-Sep-2011 11:12:23"))  
    + "MIN"
    + cstr(DATEPART("n","01-Sep-2011 11:12:23"))  
    + "]"
    

     

    • Marked as answer by Challen Fu Monday, September 12, 2011 8:38 AM
    Monday, September 5, 2011 10:40 AM