The Report Viewer and Filters on your Binding Source.
It is suprising how much information is on the net, and how little there is regarding your particular problem. For my particular problem, I could find no easy examples on how to make a binding source apply a filter when using the ReportViewer control in .NET 2005. It just didn’t work! Once I got back to basics, I realized that there is an easy solution.
Many people want to use the Microsoft Report Viewer control in either ASP.NET or WinForms in Local Mode. Local Mode allows you to generate a report without the need to use the whole Reporting Services server. For a standalone application (or for anyone who wants to avoid the headache of getting the Reporting Services Server installed and configured) the Report Viewer control will allow you to create a report and host it right in your application. The down side is that some features such as report parameters don’t work.
This changes how you create your report somewhat. If you only wanted to print information on the current user, you could send the users ID as a parameter and let that drive the filtering of data. When you run locally, you have to make sure that your data sources are already filtered before assigning them to the report. The report now wants you to send only data that is to be displayed. Any extra data will also be displayed!
I can hear you all now saying “No problem! The bindingsource control has a filter property on it. We will just set that and be done with it!” Unfortunately that doesn’t seemto work. After applying the appropriate filters on your binding source control, the report will still display incorrect/all information in the underlying dataset.
There are a few ways to get around this. One is to create a new dataset and copy all pertinent records, then set the reportviewer control to use this as its datasource. The problem with this is it can be a lot of extra work, expecially for something that should be “drag and drop”.
We could change how the dataset is filled initially, but this may not be what we are looking for and again can cause the lines of code to add up or require multiple data sets to be defined.
When you drop the reportviewer control onto your form, you can use the smart tag to define both the report file and the data sources. New Datasets, binding sources, and table adapters get added to your form automatically when you do so. By doing this, there is only one thing left to do. The binding source controls that were created reference the dataset in question but they reference a particular table in that dataset. To apply filters, you need to use the DefaultView property of a table (or your own custom view). This is ADO.NET 101 information, but the concept gets lost in all the connections between controls. If the binding source is bound directly to the data table, then you can’t apply filters as a data table doesn’t support them. The solution is to assign a different data source to the binding source control that does support filtering.
I have a button that generates the report. Assuming that all the datasets are previously populated with information, here is how I apply my filter and reassign my binding source. You don’t have to even touch the reportviewer control (other than to tell it to render) as it should already be looking to the binding source for its data.
Me.dataset.user_list.DefaultView.RowFilter = "[id]=" + cboCurrentUser.SelectedValue.ToString()
Me.user_listReportBindingSource.DataSource = Me.dataset.user_list.DefaultView
Me.ReportViewer1.RefreshReport()
We apply the filter to the appropriate table’s DefaultView which does support filtering, then assign that DefaultView as the data source for the binding adapter. At this point, we can simply tell the reportviewer control to refresh and it will use the correctly filtered data as expected.
I haven’t tested it, but once the bindingsource is using the DefaultView (which supports filters) we may be able to use the binding source filter property instead of the DefaultView filter property. It is the same number of steps though, and this was the first way I got it to work without writing books of code.
It would have been nice if MS had made the binding source point to the DefaultView as its data source by default rather than the data table and saved us the extra work. Maybe by the 2007 version they will have gotten the message. Regardless, this is a great control with great features. If you haven’t looked at it yet, you should.
Ray Pulsipher
Owner
Computer Magic And Software Design
