How to use dropdown and textbox field for one parameter in same report on SSRS

Question

I want to use two options.

1- If users want to use dropdown list for order they should use dropdown list parameter and textbox must be invalid or textbox must be set in dropdown value.

2 - Or if users want to type with keyboard manuelly for order, dropdown list must be invalid or dropdown must be show just type in textbox value. I added screenshot.

Do you have some suggestions? How can I do like this? My customers want to use dropdown list and type in manuelly in textbox for same parameter(@order parameter) on one report.

Thanks.

Answer 1

As far as I am aware it is not possible to disable one parameter input control or another. Parameters are 1 directional. You can have one based on the results of another but only in one direction. However, you should be able to produce something close enough to be satisfactory.

Here's a step by step report, based on the NorthWind database. You should be able to modify this to suit your database. If you want to copy this example exactly you'll first need to download and restore the NorthWind sample database if you don't already have it. You can find the database here. NorthWind database download on Codeplex

You can see the report design here, I'll explain each step.

1. Create a new, blank report
2. Create a datasource in your report and point it to the NorthWind database

3. Create a dataset called Order. This will be the final query containing the data that you want in the main body of the report. The query for this data set is.

   SELECT * FROM [Order Details Extended] WHERE OrderID = @ReportOrderID

4. Create another dataset called OrdersInDateRange. This will be the list of orders that you will see in your drop down list. It filters the available orders to only those that appear with a date range. Note It also contains a null entry so our final parameter can be edited without choosing anything from the list. The query looks like this.

   SELECT NULL AS OrderID UNION ALL SELECT DISTINCT OrderID FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate ORDER BY OrderID

This gets a list of all order IDs within a date range plus a NULL order id.

5. Create two new Parameters StartDate and EndDate and set them to be date time parameters.

6. You should now have 4 parameters, StartDate, EndDate, OrderID and ReportOrderID.

7. Edit the OrderID parameter, check the "Allow Null Value" option. Then set "Available Values" to a query and choose OrdersInDateRange as the dataset, choose OrderID as the Value and Label. Add a Default value of (Null). You need to set the default value as the final parameter (ReportOrderID) will not be enabled unless a value is selected in this parameter.

8. Edit the ReportOrderID parameter. Leave available values as none. For Default Values, choose "Specify Values" add a value, click the [fx] button and set the value to

   =Parameters!OrderID.Value

This will set this parameter to the value choosen in the drop down so the user can pick from a list but allows the user to type in an order ID manually too.

The report uses the ReportOrderID parameter to fetch data, the drop down list only exists as a helper.

9. Add a table and design your report layout
10. That's it.

Here's the finished report choosing from the list.

Here it again with nothing chosen in the list (the default value NULL is shown)