Hi Friends, I have created a consolidated report which gives budget of a program on the basis of fiscal years(Right now report displays all years). Now I have a new requirement of adding a drop down box with available fiscal years. Once the year is selected the report should populate values for that peticular year only. I have figured out the way to use available values and report parameter to get the drop down.. But this drop down show duplicates as they come from the query that way. And how do I filter out the report based on the value selected? This report uses a Query to pull data from the database. Thanks a Bunch!!! Gopal Gopal

I know this should be posted in Reporting servicess section no one replied there, So I am posting it here...!!!! Hi Friends, I have created a consolidated report which gives budget of a program on the basis of fiscal years(Right now report displays all years). Now I have a new requirement of adding a drop down box with available fiscal years. Once the year is selected the report should populate values for that peticular year only. I have figured out the way to use available values and report parameter to get the drop down.. But this drop down show duplicates as they come from the query that way. And how do I filter out the report based on the value selected? This report uses a Query to pull data from the database. Thanks a Bunch!!! Gopal Gopal

Hi Gopal, Not sure if this is exactly what you looking for. But in the Fiscal Year Dataset along with Drop Down List values in the Query get the Start Date and End Date for the Fiscal Years. Once the user select the Fiscal Year from Drop Down , you have the Start Date and End Date associated with that Value. Use the Values to filter in the Main Reports Query Dataset. Hope thats solves your Purpose. Regards, PS

Hello Gopal, I think you do not have big chances to get your answer here, but I will try to help you before your post gets moved back to the SSRS Forum. A discovery phase now: What is your query that drives the drop-down; and What is the SSRS version you are using? As an attempt to help you ASAP, I am also shooting in the dark with providing the following links that I think can guide you to the solution with filtering the report (it is based on DataSets): http://www.mssqltips.com/tip.asp?tip=1897 and http://arcanecode.com/2010/07/12/adding-filter-parameters-to-sql-server-2008-reporting-services-reports/ Arthur My Blog

Hi Gopal, For populating the parameter's available values, A separate dataset has to be created otherthan the main dataset. Example: select countryID,country_Name from GeographyDetails In the MainDataset, the query should be like select Details from SalesFact where countryID = @CountryID Hope this helps you. Thanks M.MahendraPlease click the 'Mark as Answer' button if my Reply helped you to solve your problem! Thanks M.Mahendra

Hi Gopal, As M.Mahendra posted, a separate dataset has to be created for parameter's Available Values. For the dataset of parameter, we can select fields from a database table which is dedicated to stores Years like the example M.Mahendra posted. Or we can also select Years from the same database table of the main dataset, but please use DISTINCT in the query to avoid duplicated Years. After that Year parameter has been created correctly, we can use it to filter the report. Generally, we can add a filter to a dataset, data region, or group. Please choose the level as needed. For detailed steps, please read the article How to Add a Filter at: http://msdn.microsoft.com/en-us/library/ms156270.aspx For more information about Adding Parameters to Create a List of Available Values, please refer to: http://msdn.microsoft.com/en-us/library/aa337400(v=SQL.100).aspx Thanks, Tony ChainTony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Hi ArthurZ, Sorry for the delayed response. The query I am using is a complex query and cannot be posted here. I will need to edit it before putting it up here.. We are using report serviced 2008 R2 Gopal

Thank you friends I got this thing working. What did I do? 1. Created a saperate dataset to populate the available value of a parameter. 2. Set a default value 3. Added a filter on the dataset associated to the report data... Thanks you everyone for the help.. Gopal

Thank you friends I got this thing working. What did I do? 1. Created a saperate dataset to populate the available value of a parameter. 2. Set a default value 3. Added a filter on the dataset associated to the report data... Thank you everyone for the help.. Gopal