Mercury reports (like any reports) rely on queries to retrieve data. Queries can take several forms. (Not all of these may be applicable to your database platform.)
The query is entirely responsible for gathering the desired data, including multi-database or multi-server queries.
Queries must return one or more tabular result-sets. (For pivot-reports, only the first result-set is used. Table-reports can use multiple result-sets to present data in a hierarchical or parent-child fashion.)
Scalar results, including return values, are ignored. In the case of stored procedures, output parameters are ignored.
The same query can be used by as many reports as you wish.
Stored procedures: We strongly recommend that your stored procedures be designed purely for data retrieval. No production data should be modified. If you do choose to have your stored procedures modify production data, please understand that Mercury cannot be responsible for those changes.
Parameters
Parameters are a great way to improve report performance and to focus the scope of the report to a limited amount of data. We encourage the judicious use of parameters. As a general guideline, avoid using more than seven parameters. Use date ranges and a parameter or two that limits a master value – such as customer/vendor/item ID.
Stored Procedures: stored procedures offer a set of parameters (or none) as determined by the author of the procedure. When creating a report based that uses a stored procedure for data retrieval, Mercury automatically detects stored procedure parameters and allows the report designer to specify alternate (friendlier?) captions to be presented to the user at run-time.
Views and other queries: unlike stored procedures, views and other queries do not have formal parameters. But Mercury lets the report designer designate any field(s) returned by the query as parameter fields, just as if they really were formal parameters.
Delimited Strings: Multi-valued parameters
A common reporting desire is to offer users the ability to specify a "set" of entities to be included in a report – a handful of companies, a list of vendors, part-numbers, employees, etc.
Multi-select Lookup-lists: in Mercury, the report definition can associate a multi-select lookup-list with any parameter. (For more information, see Admin Tools > Lookups in Mercury help.)
To support a multi-valued parameter, write your stored procedure to use a parameter consisting of a comma-delimited list of values. (Note: you're not restricted to a single such parameter. Your stored procedure might have multiple such parameters – say, a list of salespeople, a list of vendor-numbers, and a list of part-numbers.)
When a user runs a report that has a multi-select lookup associated with it, Mercury creates a delimited list of the user’s selections, which is then passed as a parameter to the stored procedure.
The stored procedure is responsible for handling the delimited string appropriately.
One common technique is to transform the delimited string list into a table of values suitable for use in a JOIN or subquery.
Many database platforms have a feature that supports this. For example, Microsoft SQL Server (starting with 2016) uses the STRING_SPLIT table-valued-function.
If your database doesn’t have a similar feature, you may be able to create your own. Please contact us if you need help.
Views and Other queries:
Any result-set column designated as a parameter can be associated with a multi-select Lookup. You don’t need to do anything special in your view definition or in your query; Mercury takes care of it automatically.
Result sets ("tables")
Pivot reports can only use one result set – the first one returned from the query. If more result sets are returned (which is only possible if the query is a stored procedure), the extra result sets are ignored.
Table reports can either be based on a single result set, or they can use multiple result sets in a hierarchical or master / detail arrangement. A report based on multiple result sets must be used with stored procedure. A view is able to return only a single result set.
For master / detail Table Reports:
Return one result-set ("table") for each level of data desired. You can have as many levels as you need. In practice, 4 levels is a reasonable limit.
Return result tables in hierarchy order. The top-level (master) should be returned first.
Columns that join result tables must be named identically, so your query must alias column-names as needed.
Linkage between result-sets is part of the report definition in Mercury. Each child table must link to the Master directly or indirectly through intermediate children. A result that cannot eventually link back to the Master is ignored.
Each child result record should link to exactly one parent record.
You can have a strict hierarchy, in which there's one result set at each level; or you can have multiple result sets at the same level (except for the top level).
Result-set columns: go big (or at least, bigger)
When designing your query, you must return all the columns (fields) that are required to be present on the report.
We suggest that you also consider adding additional fields that could be useful. These additional fields need not appear on the report but are still available to the user to drag on to the report if needed. Or they can be used to filter results, even if they don’t participate in the display.
Or perhaps you have two reports that have a significant number of fields in common. You could have a single stored procedure that returns everything needed by both reports. Each report layout would simply exclude what's not needed by making those fields invisible. (For more on report layouts, see Admin tools - reports
Result-set column order and naming
The order of data columns (fields) on a new Mercury report is by default the same as the column order from the query. But this order is easily changed as part of the report layout definition.
Similarly, column names on a new report default to the names returned by the query, but these can easily be given different names ("captions") in Mercury reports. So, if you prefer to use the native database column-names (which can sometimes be cryptic for an end-user), that's fine. If you want to use other names, that's fine too.
In other words: the author of the query isn’t subject to any naming or ordering constraints.
Location and Permissions
Queries can live on any server to which the user has access. Mercury must be able to make a connection to that server and execute the query. Depending on your database platform, you might wish to create a database “role” or “group” that has permission to the procedures and views, and make your users members of that group. (If you’re using a database-login to access your production data, then that database login can be made a role member.)
Reports that need data from multiple servers must do so linked servers or other mechanisms (such as ad-hoc remote queries) inside the stored procedure. It is the stored procedure, not Mercury itself, that handles multi-server queries. If your database platform supports linked servers, that arrangement tends to perform better than ad-hoc queries.