Hi All, Can anybody please suggest the best practice to achieve the following. Using SQL Server 2008 Reporting services and Parameters. 1. Created a dataset (Districts) which is having fields ID and Name. 2. Used both fields in the report, which is working fine. 3. I want to create 2 parameters on the report, a textbox for ID and a drop down list displaying district names. 4. I want to generate report for all the below scenarios. a) Entered only ID in the textbox. b) Selected only district name from the drop down list c) Entered both the data ID and district name. if i used "AND" and "OR", i'll not the accurate data for the above scenarios to work. Please suggest. Is there a way to take the parameter only if user entered.

Suggest you create a second dataset just pulling out distinct District Names then call this as the parameter like: select * from Districts where districtname = @district and ID = @id That should do it. Rob www.crmconsult.info Twitter:robertpeledie

Try creating your dataset as an expression based on the values of the ID and Name parameters. =IIF(!IsNothing(Parameters!DistrictID.Value) AND !IsNothing(Parameters!DistrictID), 'SELECT ... FROM ... WHERE DistrictID = @DistrictID AND DistrictName = @DistrictName', IIF(IsNothing(Parameters!DistrictID.Value) AND !IsNothing(Parameters!DistrictID), 'SELECT ... FROM ... WHERE DistrictName = @DistrictName', 'SELECT ... FROM ... WHERE DistrictID = @DistrictID' ) ) (syntax may not be 100% correct but you should get the idea).

Hello, You can achieve this by chnaging the parameter properties in the report and the report query too. In the report design: 1. Go to the parameter section and select the District parameter. 2. For the District parameter check "Allow blanks" checkbox. 3. For the District dataset all blank as a value. See the query below: SELECT DISTRINCT ID, Name FROM tbl_Districts UNION SELECT '' AS ID, 'Blank' AS Name 4. In the report for the default values select Non-quired and click on ok. 5. Now go to another parameter ID and for this just check "Allow Blanks" checkbox and click on ok. In the report query: Where clause should be like below WHERE (ISNULL(@District,'') = '' OR DistrictColumn = @District) AND (ISNULL(@ID,'') = '' OR IDColumn = @ID) Hopeits clear & helpful....Pavan Kokkula Infosys Technologies Limited.