I have country, state, city parameters and users would be using either country or country, State or country, City or State, City or all 3 of the combinations. Could you please help achieve this functionality. Thanks

hello, can you clarify what you are trying to do?

These are the parameters used for the report. Business users want this functionality of viewing the report either by country, state or city or any of the combinations. Based on the parameters a user selects the unused parameter should freeze. For ex: Country parameter is used then state and city should freeze. Thanks

so basically any combination? search by country search by state search by city search country and state search by country and city search by state and city search by country, state, and city You cannot dynamically disable parameters, and I dont understand how you want that to work if you could. If you "freeze" the state and city parameters after a country is selected, what if they still wanted to use one of those parameters? However in your query you can ignore any of those parameters when they are left blank: (in the parameter settings, check the "allow blank" option for each parameter) where (state = @state or @state is null) and (country = @country or @country is null) and (city = @city or @city is null) so if the user wants to search by country and state but not city, just leave the city parameter blank.

Thanks for the reply. Even that solution does not work. Instead I am going for If Else block. Let me know if you have a better workaround. Thanks

Hi, I have a report that has to filter by following parameter combinations. search by country, search by state, search by city, search country and state, search by country and city, search by state and city, search by country, state, and city in doing so when country is 'USA' I have to give user the option of choosing either state or country rest its country and city option. How do I get this done? Thanks

Hi ione721, From my testing in Reporting Services, if a parameter has been specified Available Values, we must select a value in them in order to view the report. In other words, we cannot omit a parameter with available values by default. However, in your requirement, you need to allow users just select 1 or 2 parameters and omit the rest to filter the report. To achieve this, one workaround is to make some changes on the datasets. For example, if the Country parameter has been specified Available Values from a dataset with the query like SELECT CountryID, CountryName from Country, we need to modify the query to SELECT '-1' AS CountryID, 'All' AS CountryName UNION ALL SELECT CountryID, CountryName FROM Country After this change, the Country parameter will display an addition “All” select item, users can select it to ignore the Country selection. Similarly, we can also modify the datasets of State parameter and City parameter. Since the addition record doesn’t exist in the database, we also need to modify the dataset for the report. If the query originally is like SELECT * FROM TABLE WHERE CountryID=@Country AND StateID=@State AND CityID=@city We can modify it to SELECT * FROM TABLE WHERE (@Country=-1 OR CountryID=@Country) AND (@State=-1 OR StateID=@State) AND (@City=-1 OR CityID=@city) Then the query will omit the parameter which is specified All If anything unclear, please feel free to ask. Thanks, Tony Chain Please remember to mark the replies as answers if they help and unmark them if they provide no help

Thank u all for ur response. I did this I am just defining just the parameters in ssrs report parameters not creating the datasets for Country, State, City. In report Preview they show up as blanks so the user will have the flexibility to type in Country, State, City per his requirement. This worked for me. Let me if u need an explanation in detail. Thanks