Depending on the Connection used for your report or lookup, there will be one or more of the following query-types available. (For more on Connections, see Admin > Connections.)
Web Service Query (aka “Query") (used by Business Central connections)
CodeUnit queries (used by Business Central connections)
When creating a report (see Admin > Reports) or a Lookup (see Admin > Lookups), you will:
- Tell Mercury the Connection to use.
- Choose a query-type.
- Select the specific query you need from a drop-down list. (Or in the case of a Statement, you would enter or paste the query.)
In the New-Report form, the Query-Type is part of the “Report Source” specification that follows the Connection.
In the Lookup form, the Query-Type is part of the “Data Source” specification that follows the Connection.
If you don’t see the specific query you need in the drop-down list, you may have to choose a different query-type and look at the list again. Whenever you select a query-type, the list is re-created. (This does not apply to Statement queries.)
Most queries are authored by database administrators or developers. If you don’t know what query is needed for a particular report, check with your IT department.
The most powerful query type – but also the most complex. Procedures are miniature “programs'' that live inside your database and are typically authored by a database administrator or developer.
A stored procedure specifies the parameters it uses (if any). Parameter values for stored procedures are required (cannot be blank) and will automatically appear in Mercury’s Parameters grid, both when a report definition is being created or edited by an admin-user (see Admin > Reports), or when the report is being run by a user.
A Lookup cannot use parameters. Therefore, any stored procedure that includes parameters cannot be the basis for a lookup.
A View also lives inside your database but is simpler to create and is less powerful than a stored procedure. Views do not explicitly specify parameters, but Mercury allows you to designate any field(s) in the View to be used as a “parameter” available to the end-user running the report. But unlike the parameters of a stored procedure, View parameters are optional – at runtime, the user doesn’t have to supply a value.
A Statement query-type consists of one or more database query statements (using standard SQL syntax). Unlike Procedures and Views, Statements don’t reside in your source database. They’re stored as a part of Mercury’s report-definition and must be written or pasted directly into the field located to the right of the pencil icon. Alternatively, click the pencil icon and Mercury provides a Statement Editor that makes it easier to enter, test and edit your query. If you’re familiar with SQL you can write queries yourself, or your IT developer can provide a query for you.
Statement Queries entered directly into Mercury can be managed using the Statement Editor.
Optional: click Format to apply typical formatting to your query – line breaks, indentation, and so on.
Optional: Test the query by clicking the Validate button. This gives you a chance to find errors before you actually try to run the query and retrieve data.
If Mercury returns the result “Valid” then proceed to the next step.
If Mercury returns the result “Invalid”, review the Statement and make the necessary corrections.
Complete the testing by clicking the Execute button. Results will appear in the frame on the right.