Advanced GridView Filtering
The DataGrid was nice in ASP.NET 1.0, but the GridView is a much friendlier control. One of the uses of the GridView is to build up reports from a data source. This may be a simple as setting up a SqlDataSource and binding it to the GridView and you have all you need, unfortunately things are rarely this simple. Reports need to be dynamic to show the data that your users are wanting. While the GridView has built in sorting, with
AJAX like abilities if configured properly, simply using another control as a filter usually will not do the trick.
Traditional Control Filtering
The normal route to get your GridView working with dynamic data is to filter the data source using a filter expression in the Select Command. The VS 2005 editor makes this a snap. When configuring the Select Statement for the data source click the “WHERE” button and add the control and parameters, often this is a dropdown list of values to filter from. This works nicely if you only have one parameter to work from.
True Dynamic Filtering
Suppose that if you want you users to build up a filter statement based on multiple parameters, but still want to show a dataset if nothing is selected. Here a method to do so. My struggle was getting a report that could filter using up to 9 parameters. The need was such that a query builder was used to get the filter statement, using multi-select list boxes for the values and drop downs for the “and/or” statements. I am not showing the query builder here, just too messy. However, once you build up the statements getting the filter applied is a snap.
Add an Init Event to your data source.
Protected Sub ReportSQL_Init(ByVal sender As Object, ByVal e As System.EventArgs)
Dim strSQL As String
strSQL = ‘Valid SQL WHERE Condition
If strSQL = “” Then Exit Sub
ReportSQL.FilterExpression = strSQL
End Sub
Possible Usage
I used this method on a pop-up page with a query builder. The query builder sends a valid WHERE condition to a datatable in a session object. The datatable is bound to another GridView that has checkboxes to combine the statements or apply the statement back to the parent. The values are all passed through session objects and the parent page refreshed based on my article here. The result is a fully customizable report that the user can create on a whim.
