Filter Data at the Data Source Level

• Sep 28, 2018
• 3 minutes to read

This tutorial illustrates how to filter data at the report data source level, as opposed to the report level. This approach is recommended when dealing with comparatively large data sources when the retrieval process is slow.

Create a Reporting Application

To get started with this tutorial, open an existing reporting application or create a new one from scratch. To learn how to create a reporting application on the platform of your choice, see Adding a Report to Your .NET Application.

The report created in this tutorial will be platform-agnostic, which means that you can use it later in applications created on any supported platform. See Store and Distribute Reports to learn more about storing and reusing reports.

Filter Data in the Report

1. To create a table report in this tutorial, start with a report that is bound to the Products table of the sample Northwind database (the nwind.mdb file included in the XtraReports installation). To learn more about binding a report to a data source, see Bind a Report to a Database.

2. In the Visual Studio designer, switch to the Field List, select the ProductName and UnitPrice fields, and drag-and-drop them onto the report’s Detail band.

   

3. In the Field List, create two report parameters of the type Number (decimal). These parameters will provide values for filtering criteria specifying minimum and maximum limits for the value of the UnitPrice data field. Set their Parameter.Description property to MinUnitPrice and MaxUnitPrice respectively.

   

4. To filter report data at the data source level based on the values of report parameters, you must provide an SQL query with query parameters, which are used to insert dynamically generated values into an SQL query before its execution.

   To add a new query parameter to the Products query, select the data source in the Report Explorer, expand its SqlDataSource.Queries collection property in the Properties window and click the ellipsis for the SqlQuery.Parameters property of the query.

   

5. In the invoked Query Parameters dialog, add two query parameters, which will specify the maximum and minimum price in the filter expression.

   For each query parameter that you want to map to a report parameter, activate the Expression check box, which allows the use of expressions to calculate the parameter value.

   Next, expand the drop-down list for the Value property and select the report parameter that you want to use.

   

6. To edit the query filter string, click the ellipsis for the query’s FilterString property.

   

   In the invoked Filter Editor, construct an expression where the parameter values are compared with the UnitPrice data field’s value, as shown below. To access the parameter, click the icon on the right. When it turns into a question mark, specify the query parameter and click OK.

   

   Alternatively, you can specify a filter expression when creating a query using the Query Builder. To invoke the Filter Editor at this stage, click the Filter… button.

Preview and Publish the Report

Your report is now ready to be generated. To view the result, switch to the Preview Tab, enter values for the parameters in the Parameters panel, and click Submit.