To create and add a new filter

Parent Previous Next

 

Report Application Server .NET SDK Developer Guide

To create and add a new filter

See Also 

 







New filters are created as a CrystalDecisions.ReportAppServer.DataDefModel.Filter object and added to a report with a new record, saved data, or group selection formula. There are two main ways to set the filter formula:

This property accepts a string value for the formula.

This property accepts a FilterItems collection containing FieldRangeFilterItem and OperatorFilterItem objects that represent the individual expressions and operators of a selection formula. The FilterItems collection is the most flexible way to select and change the fields, values, and operators in a formula.

The CrystalDecisions.ReportAppServer.Controllers.FilterController.Modify method is used to apply the new filter.

In this example, you create a new filter that selects all customer records in a particular region and country. The record selection formula represented by the filter is {Customer.Region} = "CA" AND {Customer.Country} = "USA" and is created using a FilterItems collection.

  1. Retrieve the DatabaseController object.

Visual Basic

Dim databaseController As DatabaseController = rcd.DatabaseController

C#

DatabaseController databaseController = rcd.DatabaseController;
  1. Find the database fields to filter on.

This example assumes that the report has a table called Customer that contains Region and Country fields. However, you can iterate through all database fields in the report, display them to a user through a UI, and allow the user to select the fields to filter on.

Visual Basic

Dim tables As Tables = databaseController.Database.Tables
Dim customerTable As Table = DirectCast(tables.FindTableByAlias("Customer"), Table)
Dim fields As Fields = customerTable.DataFields
Dim regionField As Field = DirectCast(fields.FindField("{Customer.Region}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS), Field)
Dim countryField As Field = DirectCast(fields.FindField("{Customer.Country}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS), Field)

C#

Tables tables = databaseController.Database.Tables;
Table customerTable = (Table)tables.FindTableByAlias("Customer");
Fields fields = customerTable.DataFields;
Field regionField = (Field)fields.FindField("{Customer.Region}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS);
Field countryField = (Field)fields.FindField("{Customer.Country}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS);
  1. Create a new FieldRangeFilterItem object that represents the primitive expression for filtering by customer region.

The filter expression to assemble is {Customer.Region} = "CA".

    1. Instantiate the new FieldRangeFilterItem object.

Visual Basic

Dim regionFieldFilterItem As FieldRangeFilterItem = New FieldRangeFilterItemClass()

C#

FieldRangeFilterItem regionFieldFilterItem = new FieldRangeFilterItemClass();
    1. Use the RangeField property to set the database field representing customer region as the field to filter on.

Visual Basic

regionFieldFilterItem.RangeField = regionField

C#

regionFieldFilterItem.RangeField = regionField;
    1. Use the Operation property to set an equality operator between the field and value of the expression.

Visual Basic

regionFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual

C#

regionFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual;
    1. Create a new Values collection, and add a constant value representing the country to filter on.

Visual Basic

Dim regionValues As Values = New ValuesClass()
Dim regionValue As New ConstantValue()
regionValue.Value = "CA"
regionValues.Add(regionValue)

C#

Values regionValues = new ValuesClass();
ConstantValue regionValue = new ConstantValueClass();
regionValue.Value = "CA";
regionValues.Add(regionValue);
    1. Use the Values property to set the value of the region to filter on.

Visual Basic

regionFieldFilterItem.Values = regionValues

C#

regionFieldFilterItem.Values = regionValues;
  1. Create a new FieldRangeFilterItem object that represents the primitive expression for filtering by customer country.

The filter expression to assemble is {Customer.Country} = "USA".

Visual Basic

Dim countryFieldFilterItem As FieldRangeFilterItem = New FieldRangeFilterItemClass()
countryFieldFilterItem.RangeField = countryField
countryFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual
Dim countryValues As Values = New ValuesClass()
Dim countryValue As New ConstantValue()
countryValue.Value = "USA"
countryValues.Add(countryValue)
countryFieldFilterItem.Values = countryValues

C#

FieldRangeFilterItem countryFieldFilterItem = new FieldRangeFilterItemClass();
countryFieldFilterItem.RangeField = countryField;
countryFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual;
Values countryValues = new ValuesClass();
ConstantValue countryValue = new ConstantValueClass();
countryValue.Value = "USA";
countryValues.Add(countryValue);
countryFieldFilterItem.Values = countryValues;
  1. Create a new OperatorFilterItem object that represents an "AND" operator between the region and country field filters.

Visual Basic

Dim operatorFilterItem As OperatorFilterItem = New OperatorFilterItemClass()
OperatorFilterItem.Operator = "AND"

C#

OperatorFilterItem operatorFilterItem = new OperatorFilterItemClass();
operatorFilterItem.Operator = "AND";
  1. Create a new FilterItems collection and add the FieldRangeItem and OperatorFilterItem objects in the correct order to represent the desired record selection formula.

The position of items in the FilterItems collection represents the order of operations in the formula. Therefore, you must add the region filter expression first, followed by the "AND" operator, and finally the country filter expression.

Visual Basic

Dim filterItems As FilterItems = New FilterItemsClass()
FilterItems.Add(regionFieldFilterItem)
FilterItems.Add(operatorFilterItem)
FilterItems.Add(countryFieldFilterItem)

C#

FilterItems filterItems = new FilterItemsClass();
filterItems.Add(regionFieldFilterItem);
filterItems.Add(operatorFilterItem);
filterItems.Add(countryFieldFilterItem);
  1. Create a new Filter object to represent the record filter.

Visual Basic

Dim newRecordFilter As Filter = New FilterClass()
newRecordFilter.Name = "Region and Country record selection filter"

C#

Filter newRecordFilter = new FilterClass();
newRecordFilter.Name = "Region and Country record selection filter"
  1. Use the FilterItems property to set the newly created FilterItems object to the filter.

Visual Basic

newRecordFilter.FilterItems = filterItems

C#

newRecordFilter.FilterItems = filterItems;
  1. Retrieve the DataDefController object.

Visual Basic

Dim dataDefController As DataDefController = rcd.DataDefController

C#

DataDefController dataDefController = rcd.DataDefController;
  1. Retrieve the FilterController object representing record selection.

Visual Basic

Dim recordFilterController As FilterController = dataDefController.RecordFilterController

C#

FilterController recordFilterController = dataDefController.RecordFilterController;
  1. Call the Modify method to apply the newly created record filter to the report.

Visual Basic

recordFilterController.Modify(newRecordFilter)

C#

recordFilterController.Modify(newRecordFilter);

The following code creates a new filter that selects all customer records in a particular region and country, and applies the filter to the report. The record selection formula represented by the filter is {Customer.Region} = "CA" AND {Customer.Country} = "USA"

Visual Basic

Private Sub CreateNewRecordFilter(ByVal rcd As ISCDReportClientDocument)
 Dim databaseController As DatabaseController = rcd.DatabaseController
 
 Dim tables As Tables = databaseController.Database.Tables
 Dim customerTable As Table = DirectCast(tables.FindTableByAlias("Customer"), Table)
 Dim fields As Fields = customerTable.DataFields
 Dim regionField As Field = DirectCast(fields.FindField("{Customer.Region}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS), Field)
 Dim countryField As Field = DirectCast(fields.FindField("{Customer.Country}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS), Field)
 Dim regionFieldFilterItem As FieldRangeFilterItem = New FieldRangeFilterItemClass()
 regionFieldFilterItem.RangeField = regionField
 regionFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual
 Dim regionValues As Values = New ValuesClass()
 Dim regionValue As New ConstantValue()
 regionValue.Value = "CA"
 regionValues.Add(regionValue)
 regionFieldFilterItem.Values = regionValues
 Dim countryFieldFilterItem As FieldRangeFilterItem = New FieldRangeFilterItemClass()
 countryFieldFilterItem.RangeField = countryField
 countryFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual
 Dim countryValues As Values = New ValuesClass()
 Dim countryValue As New ConstantValue()
 countryValue.Value = "USA"
 countryValues.Add(countryValue)
 countryFieldFilterItem.Values = countryValues
 Dim operatorFilterItem As New OperatorFilterItem()
 operatorFilterItem.[Operator] = "AND"
 Dim filterItems As FilterItems = New FilterItemsClass()
 filterItems.Add(regionFieldFilterItem)
 filterItems.Add(operatorFilterItem)
 filterItems.Add(countryFieldFilterItem)
 Dim newRecordFilter As Filter = New FilterClass()
 newRecordFilter.Name = "Region and Country record selection filter"
 
 newRecordFilter.FilterItems = filterItems
 Dim dataDefController As DataDefController = rcd.DataDefController
 
 Dim recordFilterController As FilterController = dataDefController.RecordFilterController
 
 recordFilterController.Modify(newRecordFilter)
End Sub

C#

private void CreateNewRecordFilter(ISCDReportClientDocument rcd)
{
 DatabaseController databaseController = rcd.DatabaseController;
 Tables tables = databaseController.Database.Tables; 
 Table customerTable = (Table)tables.FindTableByAlias("Customer");
 Fields fields = customerTable.DataFields;
 Field regionField = (Field)fields.FindField("{Customer.Region}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS);
 Field countryField = (Field)fields.FindField("{Customer.Country}", CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula, CeLocale.ceLocaleEnglishUS);
 FieldRangeFilterItem regionFieldFilterItem = new FieldRangeFilterItemClass();
 regionFieldFilterItem.RangeField = regionField;
 regionFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual;
 Values regionValues = new ValuesClass();
 ConstantValue regionValue = new ConstantValue();
 regionValue.Value = "CA";
 regionValues.Add(regionValue);
 regionFieldFilterItem.Values = regionValues;
 FieldRangeFilterItem countryFieldFilterItem = new FieldRangeFilterItemClass();
 countryFieldFilterItem.RangeField = countryField;
 countryFieldFilterItem.Operation = CrSelectionOperationEnum.crSelectionOperationEqual;
 Values countryValues = new ValuesClass();
 ConstantValue countryValue = new ConstantValue();
 countryValue.Value = "USA";
 countryValues.Add(countryValue);
 countryFieldFilterItem.Values = countryValues;
 OperatorFilterItem operatorFilterItem = new OperatorFilterItem();
 operatorFilterItem.Operator = "AND";
 FilterItems filterItems = new FilterItemsClass(); 
 filterItems.Add(regionFieldFilterItem);
 filterItems.Add(operatorFilterItem);
 filterItems.Add(countryFieldFilterItem);
 Filter newRecordFilter = new FilterClass();
 newRecordFilter.Name = "Region and Country record selection filter";
 newRecordFilter.FilterItems = filterItems;
 DataDefController dataDefController = rcd.DataDefController;
 FilterController recordFilterController = dataDefController.RecordFilterController;
 recordFilterController.Modify(newRecordFilter);
}

This list includes the namespaces used by the sample code:

See Also

© 2021 SAP AG. All rights reserved.

http://www.sap.com/sapbusinessobjects/

Support services

http://service.sap.com/bosap-support/

Created with the Personal Edition of HelpNDoc: Easy to use tool to create HTML Help files and Help web sites