Pivot Reports - Parameter Comparison Operators

Pivot Reports - Parameter Comparison Operators

How are Comparison Operators Used?

Some reports, in addition to asking for parameter values, allows you to specify the type of comparison you want to use when selecting data records for your report.

For example, you might have a general ledger report that has a Transaction-date parameter.  You can choose dates “greater than” your value, “less than” your value, or even “between” two values.

Or you might be running a sales report that offers a multi-select lookup-list of products.  You could ask to see only a list of products that you have selected – or you could ask to see only products that are *not* in the set that you selected.

When are Comparison Operators Available?

The availability of comparison operators for a report depends on:

   (a)the type of database query that’s used to gather data for the report – this determines whether any comparison operators are allowed at all; and 

      (b) whether the associated parameter uses a multi-select lookup-list. This restricts the operators available. (For example, it doesn’t make sense to say you want to see territories “greater than”  East, Northwest, and AsiaPacific.)


(a) is determined by the author of the query - which could be an IT person.  We won’t bother with the details here.  If you’re interested, see Data retrieval guidelines or Admin tools - reports
(b) is determined by the Mercury user who created (or last edited) report in Mercury.  For more about creating and editing the reports, including the use of lookup-lists, see Admin tools - reports
Admin Users – the discussion that follows also applies to report definitions.  When choosing parameters for View-based or Statement-based reports, you also specify the initial Comparison Operator that appears in the “Enter Parameters” grid when a user runs the report. As explained below, the user can change the operator if desired.
Parameter-Entry Grids

The parameter-entry grid for a report that does NOT allow comparison operators has just two columns: a “Parameter Name”, which tells you what’s being requested, and a “Parameter Value” where you enter or select your value(s).

The parameter grid for a report that DOES allow operators has an extra column in the middle, which you use to choose the operator you want.  The middle column doesn’t have a title, but it displays a symbol telling you the operator in effect.

If you aren’t sure what an operator means, you can hover over it to display a tooltip, as shown here.

To change the operator, click on the operator symbol in the grid and a pick-list of available operators appears.

In the above example, the report will include only a store that “Is any of” the stores in the parameter value list.  (In practice, a multi-select Lookup is often used as an aid to choosing items for “Is any of” and “Is none of” operators.)

Comparison Operators 

     

Case Sensitivity: whether your comparisons are case-sensitive depends on your production database(s). 


If you don’t know what to expect, you can ask your IT department, or you can experiment with Mercury, running the same report multiple times but with different parameter values.  


For example, say your report has a Size parameter.  You could run the report three different times, varying the Size parameter value: “medium”, “Medium”, and “MEDIUM”.  If the results are different, it’s a clue that your IT department has made your production database case-sensitive.

Null values: some of the operators involve comparisons with “null” values in the database.

Many databases make a distinction between a “Null” value  and a “blank” value.


Null means no value was provided at all.  It’s not available or wasn’t specified when the data were entered into the database.


Blank is an actual value – it was specified, and it just happens to be blank or empty.


Your company’s database may or may not support Null values.  Even if it does, as a matter of internal policy your company may have chosen to disallow null values when saving data.  You may need to ask your IT department or do a little experimenting to find out how operators relating to Null or Blank values affect your report results.

Symbol 

Meaning 

Selects records where the data field’s value … 

 

Equals 

is equal to the parameter value 

 

Does-Not-Equal 

is not equal to the parameter value 

 

Greater 

is greater than your parameter value 

 

Greater-Or-Equal 

is greater than or equal to your parameter value 

 

Less 

is less than your parameter value 

 

Less-Or-Equal 

is less than or equal to your parameter value 

 

Between 

is between two parameter values that you provide. 

The values are separated by two consecutive periods. 

 

For example,  

 

 

 

 

For Between (and Not-Between), you can also pop open a box that lets you enter the from and to values separately, which is sometimes easier than working with the two-dot separator. 

 

 

 

Not-Between 

is NOT between two parameter values that you provide.  As in “Between”, separate the values with two dots (periods). 

 

AnyOf 

is any of a specific list of desired values. 

“AnyOf” and “NoneOf” are typically used in conjunction with a multi-select Lookup list. 

 

NoneOf 

is NOT any of a specific list of values (in other words, the parameter value(s) specify items to be excluded from the report, instead of included) 

  

IsNull 

is Null: unspecified / undefined / unavailable (see the blue tip box above) 

  

IsNotNull 

is specified (any existing value is OK, including blanks) 

 

IsNotBlank 

is specified and also NOT empty – no blanks 

 

Like 

contains the specified parameter value.   

 

You can include the wild-card “%” to match more than one part of the data field. 

 

If your parameter was associated with a “LastName” field in your data, then: 

 

  • A parameter value of k matches last names containing a “k”: Kendall, Clark, Walker, Mackintosh. 

 

  • A parameter value of k%n matches anything containing a “k” followed at some point by an “n” – it would match Kendall and Mackintosh, but not Clark or Walker. 

 

 

BeginsWith 

begins with the parameter value 

 

EndsWith 

ends with the parameter value


Here’s a parameter entry grid specifying three customers, luxury products only, for orders placed from Oct 1 2020 through Dec 31 2021.

Parameter Name 

 

Parameter Value 

Customer List 

 

ApexTreks; Cycle Crazy; Pedal Pushers 

Product Description 

 

Luxury 

Order Date 

 

10/01/2021..12/31/2021 


    • Related Articles

    • Table Reports - Parameter Comparison Operators

      How are Comparison Operators Used? Some reports, in addition to asking for parameter values, allows you to specify the type of comparison you want to use when selecting data records for your report. For example, you might have a general ledger report ...

    • Running Pivot Reports

      Pivot reports and Table reports are run in exactly the same way.  The following examples may show pivot-reports, table-reports, or a mixture - it doesn’t matter. Reports are run in one of two ways. From scratch: select the report, specify the ...

    • Printing Pivot Reports

      You can print a Mercury report in two different ways: Print-Preview - displays the report in a "Print Preview" window, so that you can see what it looks like and change your page layout before printing. Quick-Print  - immediately sends the report to ...

    • Reports

      Manage Reports The Manage Reports form lets you create and edit reports, assign User accesses, and perform other maintenance tasks. (Note: Mercury allows users who run the report to modify the layout and save as a Favorite, so you don't have to worry ...

    • Running Table Reports

      Pivot reports and Table reports are run in exactly the same way.  The following examples may show pivot-reports, table-reports, or a mixture - it doesn’t matter. Reports are run in one of two ways. Running from scratch: select the report, specify the ...