How to set specific values to parameters in SSRS. RRS feed

  • Question

  • Hi

    I want to give specific values to my parameters used in my SSRS report.

    Ex:  order no as a parameter should give a drop down list containing 1,2,3,4,5 and based on the selection of the order no the report should get populated.



    Wednesday, August 21, 2013 5:25 PM


All replies

  • You can define a query in the report that is driven by a parameter. Lets say we have Parameter 1: Customer Number and Parameter 2: Invoice Number. Create a query that filters the invoice results based on the customer number.


    SELECT customenumber FROM CUSTOMER

    Query 2

    SELECT invoice number FROM INVOICES where customer number = @customernumber

    When defining the Report Parameter Properties from the second parameter, select the Available Values. Choose Get Values from a query.  Use the query that was filtered by the first parameter.

    Joe Fedak

    Wednesday, August 21, 2013 6:41 PM
  • Hi Ajit,

    From the description, I understand that you would like to select the parameter values from a drop-down list to filter the report. In this case, I suggest to refer to the steps below:

    1.Select the Parameters in the Report Data region and add a parameter named “order_no".
    2.Check the “Allow multiple values” checkbox.

    3.Click the Available Values tab, select the “Get values from a query” option and select the field Order for the Label field and Value field. (assuming there is a filed named Order in the dataset).

    4. Click the Default Values tab, select the “Get values from a query” option and select the field Order for the Value field.
    5. Right click the tablix and select Tablix Properties.

    6. Select Filters tab and add one filter as follows:
    Value: [@order_no]
    Moreover, if you want to create the cascading parameter which the list of values for one parameter depends on the value chosen in another parameter to filter the report, please refer to the reply Joe posts. Additionally, you can see more detailed information about creating parameters below:

    • Tutorial: Adding Parameters to a Report (SSRS)

    • How to: Add Cascading Parameters to a Report (Reporting Services)

    Heidi Duan


    Thursday, August 22, 2013 9:56 AM
  • Hi All,

    I guess i asked the question in a wrong way. I simply wanted to give specific values to my parameterized stored proc .

    I added a parameter with specific values(both in the lable and values fields)and then filtered my report based on the values selected from the parameter.



    Monday, August 26, 2013 10:02 AM
    • Edited by SathyanarrayananS Monday, August 26, 2013 2:05 PM formatting
    • Proposed as answer by Heidi-Duan Wednesday, August 28, 2013 1:14 AM
    • Marked as answer by Mike Yin Thursday, August 29, 2013 3:41 PM
    Monday, August 26, 2013 2:05 PM