My report is suppose to return all rows when it initially loads, then allow the user to select dropdowns in the web app (not SSRS dropdowns) to filter the report. I've added a query parameter to the shared dataset so the reportviewer in the web app can set the Report Parameters based on the users dropdown selection. The report parameter allows blank values and nulls. I then mapped the Report Dataset Field to the Report Parameter. When i run the report, all rows get displayed but when I apply the drop downs, no filtering gets done. I tried adding a report filter but it seems to require an exact match. What I need is a conditional filter on the report, something like "if @Param is null or = "" then show all records in the datset, otherwise only show records where the dataset field = @Param... What am I missing? x

you can put this condtion in you SQL Like SELECT * FROM TableA WHERE ColumnA =@param1 OR @param is NULLGaurav Gupta http://sqlservermsbiblog.blogspot.com/

you can put this condtion in you SQL Like SELECT * FROM TableA WHERE ColumnA =@param1 OR @param is NULLGaurav Gupta http://sqlservermsbiblog.blogspot.com/

This condition is in my sql. The problem is that when I bind the report filter to the dataset, it returns no rows because the evaluation is '='. As I understand how the report/parameter/dataset works, when the report initially runs (with no filters anywhere,) all of the records get dumped into the dataset b/c I'm passing a Null value for my parameter @SiteNumber, and all of the records show on the report. When the user selects a Site Number in the dropdown in the web app, the param is passed to SSRS and is executed against the dataset (where SiteNumber = @SiteNumber.) The problem is that when I put a filter on the report or on the dataset, that logic is applied and since we're initially passing a Null value, what is being executed is 'Where SiteNumber = Null'. Since there are no Null site numbers, no records are being returned. Again, what am I missing?x

I suppose above qury should work In where clause its not WHERE COLUMNA IS NULL it is WHERE @param1 IS NULL So if param1 is null and we have "OR" condition, it will give me full data set (SELECT * FORM TableA) i am not sure i might be missing anything Gaurav Gupta http://sqlservermsbiblog.blogspot.com/

The problem is in the filter. The dataset is always correctly populated with all of the records. As an example, if the dataset contains 3 rows, Site Number 1, 2, and 3, the filter is looking for an exact match on one of those values. If I supply one of those values, the report shows that row. But on the initial load of the report I want to see all rows, so I need to pass a Null. In this case, the dataset will be populated with all three rows, but the filter will be requiring an exact match. Since we're passing a Null and none of the records will have a Null site number, no rows display. That is the problem. Thanks for your time and help. Much appreciated. x

Hi, Since you want to show all the records on the report when load the page, you could put the report’s initialization code without filter feature on Page_load event such as: protected void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) { DataTable dt = new DataTable(); ReportDataSource rd = new ReportDataSource("GetComparePerTraffic_ComparePerTraffic", dt); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(rd); ReportViewer1.LocalReport.ReportPath = GetGlobalResourceObject("IPVAResource", "CompareDailyAnyDayReportViewer1.ReportPath").ToString(); } } Of course, you could set a default value 'ALL'for your custom control, in your T-SQL, you need add some code behind the Where Condition, such as: Where Param=@param or @Param='ALL' In Code-behind, you should also set this value to the paramter like below: ReportParameter rp0 = new ReportParameter("Param", YourCustomControl.Text); ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rp0 }); ReportViewer1.LocalReport.Refresh(); Thanks, Challen Fu

Hi, Since you want to show all the records on the report when load the page, you could put the report’s initialization code without filter feature on Page_load event such as: protected void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) { DataTable dt = new DataTable(); ReportDataSource rd = new ReportDataSource("GetComparePerTraffic_ComparePerTraffic", dt); ReportViewer1.LocalReport.DataSources.Clear(); ReportViewer1.LocalReport.DataSources.Add(rd); ReportViewer1.LocalReport.ReportPath = GetGlobalResourceObject("IPVAResource", "CompareDailyAnyDayReportViewer1.ReportPath").ToString(); } } Of course, you could set a default value 'ALL'for your custom control, in your T-SQL, you need add some code behind the Where Condition, such as: Where Param=@param or @Param='ALL' In Code-behind, you should also set this value to the paramter like below: ReportParameter rp0 = new ReportParameter("Param", YourCustomControl.Text); ReportViewer1.LocalReport.SetParameters(new ReportParameter[] { rp0 }); ReportViewer1.LocalReport.Refresh(); Thanks, Challen Fu

Thanks for the suggestion. However I tried another approach and it seems to work well. In the stored procedure I added to the select "1 as Dummy" so the dataset would have a default column to filter on. Then in the report filter expression: "=IIf(Parameters!SiteNum.Value Is Nothing, Fields!Dummy.Value, Fields!SiteNum.Value)" and in the filter value: =IIf(Parameters!SiteNum.Value Is Nothing, 1, Parameters!SiteNum.Value). This way if the @SiteNum parameter is null it would filter on Dummy and get all rows, otherwise it will filter on the SiteNum parameter. Another issue has come up with regard to pagination. As I wrote before, the report must show all records on the initial load. When the report displays, the pagination and sorting from the reportviewer control does not work because it seems the report goes back to the database each time I try to page forward, and I end up seeing the same set of records. This doesn't seem right. I have a second report that has no parameters set from within the web application and paging works fine. In this report, the web application sets the param: ReportViewer1.ServerReport.SetParameters(New ReportParameter("SiteNum", SiteNum, True)) It seems that if there is a query or report parameter set from the web app, a trip to the database is done each time which effectively breaks reportviewer interactivity. Thanks again. x

Problem solved. I changed the parameter from query to a report parameter and that did the trick.x