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 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.
(b) is determined by the admin-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.
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.)
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.
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 ...
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 ...
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 ...