locked
dynamic default values for parameter RRS feed

  • Question

  • Hi , I am trying to write a query for the default values for month, and I am stuck in bringing out only the parameter values, the query I have , brings back the month on row and value on column, and I am trying to get the value out. and will ssrs accept it as a valid expression for a default value

    WITH 

    MEMBER
    [MEASURES].[PARAMETERVALUE]
    AS
    [Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME

    SELECT 
    {

    [MEASURES].[PARAMETERVALUE]

    }
    ON COLUMNS
    ,

    nonempty(

    [Account ].[Month Short Name].lastchild.prevmember,[Measures].[AccountV]
    )

    ON 
    rows
    FROM 
    [SCube]
    ;

    Thank you

    I tried using the exp

    =Parameters!MONTH.Value(0)

    so as to return the first parameter in the parameter dataset , but i have the error

    the expression that ref the parameter MONTH does not exists in the parameter collection


    • Edited by I4QR1A Wednesday, November 11, 2015 11:43 AM
    Wednesday, November 11, 2015 8:24 AM

Answers

  • Hi I4QR1A,

    According to your description, you want to specify dynamic default values for a parameter based on SSAS data source. Right?

    In Reporting Services, we can replace a default value of a parameter with a expression and concatenated string. One import thing is, you can't specify any member in the filter expression of Query Designer. Otherwise, if the generated dynamic value doesn't match the member in filter expression, the dataset will not return any data. Please refer to screenshots bleow:

    Regards,


    Simon Hou
    TechNet Community Support


    Thursday, November 12, 2015 8:29 AM

All replies

  • The way to do this is to create a second data set that just returns 1 row and use that for your default value

    If you want the first non-empty month on the rows you could use the HEAD() function, but normally with days you would want to use TAIL() to get the most recent date with data.

    eg.

    WITH 
    MEMBER
    [MEASURES].[PARAMETERVALUE]
    AS
    [Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME
    SELECT 
    {
    [MEASURES].[PARAMETERVALUE]
    }
    ON COLUMNS
    ,
    TAIL(
    nonempty(
    [Account ].[Month Short Name].lastchild.prevmember,[Measures].[AccountV]
    )
    )

    ON 
    rows
    FROM 
    [SCube]


    http://darren.gosbell.com - please mark correct answers

    Thursday, November 12, 2015 3:44 AM
  • Hi I4QR1A,

    According to your description, you want to specify dynamic default values for a parameter based on SSAS data source. Right?

    In Reporting Services, we can replace a default value of a parameter with a expression and concatenated string. One import thing is, you can't specify any member in the filter expression of Query Designer. Otherwise, if the generated dynamic value doesn't match the member in filter expression, the dataset will not return any data. Please refer to screenshots bleow:

    Regards,


    Simon Hou
    TechNet Community Support


    Thursday, November 12, 2015 8:29 AM