Bi-directional Parameter issue in Report Builder

Question

I have two task in hand to achieve in a single report.

1. Application URL filtering - One ListID will be passed in the URL to filter the report. I achieved this by having @id as parameter for my datasets. In this scenario I only had one parameter in place.

2. Reporting Server filtering - For users who don't have access to application/db, they can go to the Reporting server and Select a 'ListName' from a dropdown parameter(@ListName) that fills the List ID parameter. I achieved this scenario by cascading parameters and having two datasets one for independent dropdown parameter(ListName) another for dependent parameter(ListID).

But since I cannot filter the report based on ListName from the application, I need a way to achieve both in the same report. When I try to filter the report using '?id=123' from application URL it does not filter the report.

Answer 1

You don't need two parameters, you are only ever need to filter on ListID so this is the only parameter you need. You only use ListName to look up the ListID.

Let's say your dataset for the list parameter values looks like this:

SELECT ListID, ListName FROM Lists

Link this Dataset to your ListID parameter's Available Values.

Parameter objects have a Value property which is used to run the query and the Label property which is display the list names to the user. Map ListID to the Value and ListName to the Label.

Now the application can simply provide a list id to the report. However, when a user runs the report, they will see the list names. When they select one, the corresponding ListID will be provided as the report parameter Value.