Filtering reports

In the Filters section in the Details tab, you can add filters and associated filters by completing the following steps:

1. Click the Select button to open the Add filters dialog, or type the required name of the attribute in the Add Filters field, select the required attribute, and skip to step 3 below.

2. Select the required attributes for your filters or associated filters by clicking on attribute names. An attribute can be selected as a filter more than once. You can search for attributes in the Find Attribute field.
   To filter a report using a specific instance (for example, a specific unique activity), add an attribute that has a name that starts with "(Specific)", or add a "(Specific)" associated attribute, and select the instance in the Enter (Specific) filter dialog.

   

   As of NexJ CRM 9.6, the list of filters you can select from is no longer determined by your field selections. Instead, you can select any available filter. For example, you can filter a Deals report using the Stage filter without having selected the Stage field. The Stage field will not show in the Details tab for the report.

   When you select an association or its attribute, an associated filter is created in the filters list in the  Details  tab that contains a sub-filter for the attribute. For more information, see Associated filters.
   
   

   Your selected filters and associated filters are displayed in the Details tab. Each filter or associated filter is assigned a number, which corresponds to order in which you added them.

3. Select the required values for your filters or select the required operators for your filters, and select or enter the required text or numeric values that apply to the operators. In the Enter filter dialog, do one of the following:

   • If you see a list of values you can select from in the Enter filter dialog, select the values you want to include.

   • If you see fields for an operator and a text or numeric field value in the Enter filter dialog, select the operator and specify the values.

     

     Available operators vary based on the attribute type. In addition to the operators supported in NexJ CRM, you can select the following negative operators that are specific to ad hoc reporting:

     Attribute type	Operator	Description
     String/text	does not start with	Search for records that do not start with the value for the specified text field.
     	does not end with	Search for records that do not end with the value for the specified text field.
     Date/timestamp	not between	Search for records in which the value for the specified field is not between the From and To field values that you specify. The search results include records with values that do not match both the "from" and "to" values.
     	does not equal today	Search for records in which the value for the specified field is not today's date, including the year.
     	not in the previous (days)	Search for records in which the value for the specified date field is not a date in the past within the specified number of calendar days.
     	not in the next (days)	Search for records in which the value for the specified date field is not a date in the future within the specified number of calendar days.
     Numeric	not between	Search for records in which the value for the specified field is not between the From and To field values that you specify. The search results include records with values that do not match both the "from" and "to" values.

     For information about operators supported in NexJ CRM, see Using operators.

     
     

     

     Before you can save a report, you must specify values for all selected filters.

     
     

4. Click Apply. Your filters and associated filters are highlighted in green in the Details tab to show they are ready for use. You can edit the filters and associated filters by clicking on the filters or sub-filters, and providing different filter criteria. If you add additional sub-filters to an associated filter, the sub-filters are evaluated as one sub-filter and the next sub-filter, and so on.
   
   You can change an associated filter so that it excludes data in the report that matches the filter criteria by clicking on the associated filter name in the filters list in the  Details  tab, and selecting the  WITHOUT  option in the Configure Filters dialog. For more information, see Associated filters.

Expanding the Configure Filters dialog

When working to configure multiple complex filters, the expanded Configure Filters dialog offers a more convenient UI that provides an ability to:

• Access the entire list of fields that may be used as filters.

• Add new filters while being able to see those that were configured earlier.

• See the chosen operators and values for all filters at once.

Click the Expand button

• Edit existing filters and associated filters. To edit associated filters, click the Edit button

  

  .

• Reorder the display of the filters without impacting the logic by clicking the up and down arrow buttons.

• Remove a filter by clicking the remove button

  

  for a filter.

• Remove all filters by clicking the Clear Filters button.

• Add additional filter values by clicking the Add Another Value link.

After you have saved your report, a textual version of the filters will display in the Filters section in the About tab in the Report Viewer.

Associated filters

By default, associated filter names start with WITH, which means that associated filters will include data in the report that matches the filter criteria for the filters. For example, the following associated filter in an Activities report will select activities that have "Tier A" contacts on the "For" list.

You can change an associated filter so that it excludes data in the report that matches the filter criteria by clicking on the associated filter name in the filters list in the  Details  tab, and selecting the  WITHOUT  option in the Configure Filters dialog. For example, the following associated filter for a Contacts report will exclude contacts that have associated activities that start in the last 90 days:

Custom logic

When you are creating a report and you select a report template or a blank report, the Custom Logic field will be blank, which means that all of your filters and associated filters will be added together. You can determine how filters and associated filters are evaluated by entering your own expression in the Custom Logic field. The following rules apply to expressions:

• Only the following elements are allowed as a part of the expression:

  • Numbers such as 1, 2, 3, 4, and so on, which are the order numbers for filters and associated filters.

  • The AND, OR, and NOT logical operators, which are case insensitive.

  • Parentheses, which are used to group filters and associated filters. Parentheses must match, which means there needs to be a corresponding closing parenthesis for each opening parenthesis. For example, (1 OR 2) AND NOT (3 OR 4) is valid.

• The following list shows the logical order of operations, which determines the order in which operators are evaluated:

  1. Parentheses

  2. NOT

  3. AND

  4. OR

• All filters and associated filters for a report must be used in the expression in order to save the report. For example, if you have three filters, the custom logic expression must refer to all three order numbers that represent the filters. 

• AND or OR logical operators require two operands that are the order numbers that represent individual filters or associated filters defined by parentheses. For example, 1 OR 2 and 1 OR (3 AND 4) are valid. OR 1 or 1 AND are not valid.

• The NOT logical operator can be specified in front of a single filter or associated filter. For example, NOT 1, NOT (1 OR 2), or NOT (1 OR NOT 2) are valid expressions. 1 NOT 2 is not valid.

• Parentheses can be used to group one or more filters and logical operators. For example, NOT (1) is the same as NOT 1, and (1 AND 2 OR 3) AND 4 is valid.
  The following shows an example of a custom logic expression that will not include data in a contacts report for contacts who have an address in Toronto or Montreal, and will include data for contacts that have addresses in other cities when their full name contains "Lamont":
  

  

The custom logic will display in the Filters section in the About tab in the Report Viewer.

To change the order of the filters and associated filters in the list in the Details tab, drag and drop filter chips or associated filter chips.