Filter Data at the Data Source Level
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 new report or open an existing one.
Bind your report to a required data source. See the Bind to Data section to learn more about providing data to reports.
Switch to the Field List and drop the required fields onto the report's Detail band.
Select the Data Source tab in the designer's ribbon bar.
Edit the filter applied to the data source by clicking on the Edit Filter button. This will open the filter editor dialog.
Alternatively, the data source could be selected by clicking the Select Source button, or in the Report Explorer.
In DataBox Mode the Sql Queries are listed. Expand its Queries collection property in the Property Grid and click the ellipsis for the Filter String property of the required query. The following image is an example of what multiple queries will look like in the Property Grid.
In the invoked Filter Editor, construct an expression where the data fields are compared with the required values as shown below.
Every filter condition consists of three parts:
- A data field name.
- Criteria operator, such as Equals, Is less than, Is between, etc.
A static operand value or another data field.
You can arrange specific conditions into groups with And, Or, Not And, and Not Or operators.
Clear a filter by clicking the Clear Filter button in the ribbon bar. This will remove the filter that is applied for the data source. Alternatively, the filter can also be removed by opening the Filter Editor Dialog and clicking the X icon next to each entry in the editor, or by deleting the filter string text in the Property Grid
Switch to Print Preview to see the result.