Hi, In one of my requirement in the report I have 3 filters like plant, category and customer. In the store procedure I have used split function to select multiple values for these 3 filters. when after executing the report , By Default report displays all the values but in the filters i have seen like for Customer: BOA,Chase,US Bank. Instead of that i need to see ALL default option in all the filters: like Customer:ALL,Category:ALL, Plant: ALL. how Can I achieve this? i am using SQL Server 2008 R2

Here's link: http://hardik-bhavsar.blogspot.com/2011/02/all-as-parameter-value-in-ssrs-oracle.html

Here's link: http://hardik-bhavsar.blogspot.com/2011/02/all-as-parameter-value-in-ssrs-oracle.html

Hi Hardik, 1) Create a new dataset to populate parameter values Ex: select distinct city from table1 where city is not null union select 'ALL' as city from dual order by 1 2) Modify your main dataset query Ex: where (city = :city or 'ALL' = :city) In the second point he told to modify the main dataset? Can you explain clearly... because what i did is i was using a store procedure, so in the store procedure iin where condition i have given like this "where Category IN (SELECT * FROM udfMultiValueParm(@Category,','))" modified: "where 'ALL' IN (SELECT * FROM udfMultiValueParm(@Category,','))" Is it correct because when i have used the second statement and execute i am not able to get data. can you help me out?

Try this: where (Category IN (SELECT * FROM udfMultiValueParm(@Category)) OR @Category = 'ALL') then go to the main dataset property --> parameters --> for (@category) parameter value expression --> =Join(Parameters!Category.Value,",")

After using your expression and given a default values as ALL,It works fine hardhik, thanks for the solution.

Hi Hardhik, Do you have any idea like in the filter there are about 100 values let say for the category we have 100 values , instead of scrolling down for the required option. can we have an option for search in the filter so the when we type certain value it will come up for example in the google website, when we type the value in the search bar in the drop down we will get the option..same as that...........can we do in the ssrs. If yes how can we achieve? thanks, aravind

It's a website future actually but you can try this method by using LIKE condition, see the example here. http://romiller.com/2008/07/29/searchable-dependant-parameters-in-ssrs/