Use Special Characters in a Filter

Overview

In Reporting Intelligence, "#," "♪," and "♫" are considered special characters and cannot be used in filters for BC Web service data source connections. These special characters are designated for Reporting Intelligence.

Certain characters have specific meanings in queries and are considered to be special characters.

e.g.,

|   >   <   =    .  (   )   &

If you need to include any of these characters as part of filter value then you need to precede the filter with "@@".

The "@@" will pass the filter in as a literal string and be evaluated as intended.  If you have two periods in a filter string (e.g., "A.M."), the filter includes special characters and needs to be prefixed by "@@" in most versions of the Reporting Intelligence Excel add-in.

Examples

1. Single Filter

   If your were to filter by the customer named "John(Houston)" you would need to place the "@@" in front of "John(Houston)".  The resulting formula would look like:

   =NL("Rows","Customers","CustomerID","ContactName","@@John(Huston)"

   Since the name John(Houston) would often be located in an adjacent cell, here is the equivalent function using a cell reference:

   =NL("Rows","Customers","CustomerID","ContactName","@@"&C3

2. Multiple Filters

   If you needed to combine several filters that all include special characters, you would need to enclose each filter item in single quotes (') instead of using the "@@" prefix.

   To filter by customers named "John" or "Ben", you would use the logical OR (a vertical bar | ) operator.  Since there are no special characters, your function would look like this:

   =NL("Rows","Customers","CompanyName","CompanyName","John|Ben")

   If, however, the customers' names were "John A.M." or "Ben & Jerrys", you would need to enclose each name in single quotes (').

   The single quotes around each name are required because applying the "@@" prefix to the entire filter would cause the | to evaluate as a part of the filter instead of being evaluated as an operator - meaning your filter would be "John A.M|Ben & Jerrys", instead of "John A.M" or "Ben & Jerrys".

   The following demonstrates how to filter by multiple values with special characters:

   =NL("Rows","Customers","CustomerID","CompanyName","'John A.M'|'Ben & Jerrys'")

   {please note the single quotes around each of the filter values: ... ," ' John A.M ' | ' Ben & Jerrys ' " }

   Again, these names could be coming from cell references, so the equivalent formula with cell references is below:

   =NL("Rows","Customers","CustomerID","CompanyName","'"&C3&"'|'"&C4&"'")