locked
Dashboard designer Chart Query Error - IIF funtion plus parameters RRS feed

  • Question

  • Hi there,

    I have the following MDX query in a Dashbord designer Chart:

    SELECT
    NON EMPTY { <<KPIParameter>> }
    ON COLUMNS,

    NON EMPTY { [Client].[Client].[Client].ALLMEMBERS }
    ON ROWS

    FROM [Fee Earner Fees]

    WHERE ( IIF(<<FeeEarnerScorecardParameter>> = 0,<<FeeEarnerParameter>>,<<FeeEarnerScorecardParameter>>) )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, FONT_FLAGS, FORE_COLOR, BACK_COLOR

     

    The KPIParamenter is linked to a scorecard.

    The FeeEarnerParameter is linked to a filter.

    FeeEarnerScorecardParameter is linked to the same scorecard. By default this parameter has 0 in its value. (I have defined that in the dashboard designer when I created the filter). So, if this parameter has 0, it means that the filter to be applied is FeeEarnerParameter, else it has to apply FeeEarnerScorecardParamenter.


    But when I deploy the dashboard I am getting a query error in the chart. So I think the problem is in the following: IIF(<<FeeEarnerScorecardParameter>> = 0,<<FeeEarnerParameter>>,<<FeeEarnerScorecardParameter>>)

     

    Any advice is welcome.

    Thanks in advance an best regards,

    Joss

    Wednesday, April 20, 2011 11:40 AM

Answers

  • I would move the IIF logic into a calculated member in the Query and pass that value into the WHERE clause.  I would need to know all of the values that are being set and used in the query to make sure that would work, not sure what values are being passed and what the final query would look like.  You can run a trace against your SSAS instance to see what is being passed, if anything, to the server.
    Dan English's BI Blog
    Friday, April 22, 2011 2:42 AM