How to set specific values to parameters in SSRS.

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.

Thanks

Ajit

Answer 1

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.

Query1

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

Answer 2

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:
Expression:[Order]
Operator:In
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)
http://technet.microsoft.com/en-us/library/aa337432(v=sql.105).aspx

• How to: Add Cascading Parameters to a Report (Reporting Services)
http://technet.microsoft.com/en-us/library/aa337169(v=sql.100).aspx

Regards,
Heidi Duan

 

Answer 3

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.

Thanks

Ajit

Answer 4

Hi ,

Try these links :

http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26/reporting-services-multi-value-parameter

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/passing-multivalue-parameter-in-stored-procedure-ssrs

---

sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.