Data Filter Overview

  • 4 minutes to read
  • Filter a SQL Data Source

    Filter records at data source level using your data connection query if you are binding to a large data source and want to speed up the retrieval process.

  • Filter Data Using Report’s Settings

    Use the report’s settings demonstrated in this section if you want to load the entire dataset and filter it on the client.

  • Limit the Number of Records to Display

    Options described in this section allow you to emulate the Top N feature in a sorted report or increase the Print Preview performance by rendering only a subset of a report’s data.

  • Conditionally Change Element Visibility

    Use this technique to hide certain report elements from the Detail band when the corresponding data records meet the specified criteria.

Filter a SQL Data Source

You can filter data records before supplying them to a report when you create a new data-bound report or bind an existing one to a SqlDataSource.

You can also apply filtering in an existing data source by right-clicking it in the Report Explorer or Field List and selecting Manage Queries. In the Manage Queries window, click the required query’s ellipsis button.

filter-data-01

In the invoked Query Editor, click the Run Query Builder button and use the Query Builder to specify the filter criteria as shown in the following image:

filter-data-02

The filter string can reference query parameters that you can link to report parameters.

See Filter Data at the Data Source Level for detailed instructions on using query parameters.

Filter Data Using Report’s Settings

Use the report’s XtraReportBase.FilterString property to filter records at the report level. Locate this property using the report’s smart tag and click the corresponding ellipsis button to invoke the Filter Editor.

filter-data-report-smart-tag-filter-srting

You can filter detail report data individually in a master-detail report.

filter-data-03

In the invoked Filter String Editor, specify filter criteria.

filter-data-filter-srting-editor

Refer to Filter Data at the Report Level for a step-by-step tutorial. See Creating Criteria to learn more about filter criteria syntax.

Use Report Parameters to Filter Data

You can use report parameters to control how a filter string is applied to report data. For instance, create a filter string where the expression’s first part specifies whether to apply the filter. The following scenario is an equivalent to using the Iif() function within a filter string:

  1. Configure a parameter where users can specify to display all customers.

    parameters-multi-value-optional-settings

    Property Value
    Name filterByCompany
    Type Boolean
    Default Value Yes
  2. Configure a parameter where users can select specific customers.

    parameters-multi-value-optional-settings

    Property Value
    Name paramCompany
    Type String
    Select All Values Enabled

Use the following filter string for the report:

?filterByCompany Or [CustomerID] In (?paramCompany)

parameters-multi-value-empty-value

The resulting report displays data for all customers when the showAllCompanies parameter is set to Yes.

Tip

You can also use the filter string shown above to filter report data at the data source level. See the Specify Query Parameters topic for more information.

Limit the Number of Records to Display

You can filter records displayed in Print Preview using the ReportPrintOptions class.

  • Limiting the Number of Records

    Use the ReportPrintOptions.DetailCount option to define how many times to print the Detail band in Print Preview at runtime (similar to the TOP clause in SQL).

    The ReportPrintOptions.DetailCountAtDesignTime property enables you to limit the number of records a report shows at design time in Visual Studio. Click the report’s smart tag to access this setting as shown in the image below.

    filter-data-00

  • Printing on Empty Data Source

    Disable the ReportPrintOptions.PrintOnEmptyDataSource option to avoid generating a report when its data source is empty. You can use this setting in master-detail reports to hide the detail report if its data source contains no records.

    The ReportPrintOptions.DetailCountOnEmptyDataSource property allows you to specify how many times to print the Detail band when a report does not have a data source. You can use this property to create static reports that are not connected to a data source and display the same static content several times.

Conditionally Change Element Visibility

You can show or hide a specific report control in Print Preview based on a logical condition. For instance, the following example illustrates a document that displays the State field only if the Country value is “USA”.

filter-data-08-conditional-visibility

Select the target label on the report’s surface, switch to the Properties window’s Expressions tab and click the XRControl.Visible property’s ellipsis button.

filtering-control-visible-expression-tab

In the invoked Expression Editor, specify the expression that defines when a control should be visible.

filtering-control-visible-expression

Add these controls to the Panel and set its XRControl.CanShrink property to true which automatically adjusts the panel’s size to fit all the controls and prevent blank areas.

filter-data-07-panel-can-shrink