none
Can Execute Report But Fails in Query Builder

    Question

  • I'm running into an odd issue...

    I've inherited a report that was built using Visual Studio. It's going against an Analysis Services cube and the query is in MDX.

    For some reason, when I view the query in Query Builder and attempt to execute the query, it throws an error. I then cancel out of Query Builder and cancel any changes. Now if I actually run the report, it runs just fine. Users have verified that the results are correct.

    I've never seen this before! I've attached the query and the error message:

    WITH

    SET [Selected Discipline] AS STRTOSET(@Disciplines, CONSTRAINED)
    SET [Selected Department] AS STRTOSET(@Departments, CONSTRAINED)

    SET [Selected Departments] AS IIF(@Disciplines = @Departments, 
      FILTER(
        DESCENDANTS([Selected Discipline], [Employee].[DISC - DEPT - EMP].[Department], AFTER), 
        NOT INSTR([Employee].[DISC - DEPT - EMP].CurrentMember.UNIQUE_NAME, "Freelance")),
      DESCENDANTS([Selected Department], [Employee].[DISC - DEPT - EMP].[Department], AFTER))

    SET [TTM] AS { LastPeriods(12, StrToMember(@SelectedMonth, CONSTRAINED)) }

    SELECT 


      [Measures].[Employee Hours], 
      [Measures].[Employee Hours %] 
    } ON COLUMNS, 

    NON EMPTY CROSSJOIN(
    [Selected Departments],
    [Employee].[Employee Bill Rate].[Employee Bill Rate].ALLMEMBERS,
    [Employee].[Employee Level].[Employee Level].ALLMEMBERS,
    [TTM]) 

    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

    FROM [OLSON BI]

    WHERE (

    [Employee Hours Time Category].[Time Category].&[Client Facing],
    [Employee Hours Time Category].[Utilization Category].&[PRODUCTION])

    CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

    The error is:


    A. M. Robinson

    Saturday, March 29, 2014 9:42 PM

Answers

  • I was able to track down the problem:

    As it turns out, I was using STROTOSET and CONSTRAINED when the parameters do not allow multiple values.

    I made the change in the MDX and everything looks good! Still not sure how the original query works in the report but fails in Query Builder...go figure!

    Thanks anyway...!


    A. M. Robinson

    • Marked as answer by ansonee Sunday, March 30, 2014 1:21 PM
    Sunday, March 30, 2014 1:21 PM

All replies

  • I was able to track down the problem:

    As it turns out, I was using STROTOSET and CONSTRAINED when the parameters do not allow multiple values.

    I made the change in the MDX and everything looks good! Still not sure how the original query works in the report but fails in Query Builder...go figure!

    Thanks anyway...!


    A. M. Robinson

    • Marked as answer by ansonee Sunday, March 30, 2014 1:21 PM
    Sunday, March 30, 2014 1:21 PM
  • Hello,
     
    Glad to heard that the issue had been solved and thanks for your sharing. It will be very beneficial for other community members who have similar questions.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Monday, March 31, 2014 9:56 AM