none
Set First Returned Value as Default for Multivalued Paramter

    Question

  • I have a parameter that allows multiple values, and is populated by a call to a stored procedure.  In my report, I want the dropdown to have the first returned value selected by default.  In the Default Values section of the Parameter Properties, I selected the "Specify values" option and provided an expression similar to:

    =First(Fields!MyParamField.Value, "SP_ParamField")

    When I preview my report, I get an error saying that fields cannot be used in report parameter expressions.

    Setting the first value returned in the dataset as the default for a parameter seems like a natural thing to want to do.  What is the preferred method?


    - Mark Z.

    Wednesday, July 24, 2013 5:11 PM

Answers

  • Hi Mark,

    From your description, you want to use the first returned value of the dataset as the default values of the parameter, right? In this case, we can modify the data set query to return only record, and then use it as the default value.
    TOP (Transact-SQL)

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Mark Zudeck Thursday, July 25, 2013 2:20 PM
    Thursday, July 25, 2013 6:02 AM

All replies

  • why dont you apply rownum function to get only first row from the dataset and in parameter default value use the dataset.

    in your where clause should be like 

    Where rownum=1

    Wednesday, July 24, 2013 10:40 PM
  • Hi Mark,

    From your description, you want to use the first returned value of the dataset as the default values of the parameter, right? In this case, we can modify the data set query to return only record, and then use it as the default value.
    TOP (Transact-SQL)

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    • Marked as answer by Mark Zudeck Thursday, July 25, 2013 2:20 PM
    Thursday, July 25, 2013 6:02 AM
  • Thanks, Charlie.

    I ended up creating a separate dataset for the default value, which does as you described.


    - Mark Z.

    Thursday, July 25, 2013 2:22 PM