The Advanced Options menu is available in Advanced Reports and CrossTab Reports by clicking on the Report Settings icon in the toolbar.

Note

If you do not have access to the Advanced Options menu or certain options within it, you may not have permission to do so. Please contact your administrator regarding permissions.

From the Advanced Options menu, the following items may be accessed:

• The Joins window, allowing advanced users to specify additional information about how the data categories within a report relate to each other.
• The Events window, allowing advanced users to apply event handlers for the report.
• The Show Generated SQL option, allowing advanced users to generate and view the constructed SQL that will be sent to the databases prior to execution.
• The Parameters window, allowing advanced users to create their own report-level parameters.

Joins

If a report has two or more data categories, then information will only appear if the categories match. Using the Joins window, however, you may specify information you want to display regardless of whether or not it only exists in one of the data categories.

For example, if a report has two data categories, Orders and Customers, then only customers who have made orders will appear. By selecting the appropriate checkbox you can include information on customers that have not made orders.

The following options are also available within the Joins window:

• To add a new join click the Add button.
• To edit a join click the Edit button.
• Restore the default joins by clicking the Recreate button.
• To remove a join click the Delete button.

Editing and Adding Joins

When either editing an existing join or adding a new join between two data categories that are not currently joined, the Report Join menu will appear.

In this menu you can modify the information of a join or create a new join for the report.

• A join condition — e.g., Customers.CustomerID = Orders.CustomerID — may be deleted from a join using the Delete button.
• A join condition may be added to a join using the Add Condition button.
• Join conditions may be adjusted using their respective Column dropdowns. Within each dropdown is a list of available fields that may be joined.

Caution

There is no restriction on what fields may be joined. Please use careful consideration when joining fields, as improper joins may result in unexpected data output.

Events

Event handlers may be applied to the report by advanced users for additional functionality.

The following options are available within the Events window:

• An event can be removed from a report using the Delete button.
• An event can be added to a report using the Add button.
• The event type can be selected using the Event dropdown.
• The event action can be selected using the Action dropdown.

Upon triggering the event — e.g., OnReportExecuteStart will be triggered at the start of the report's execution — the associated action will take place.

Note

For more information, please see the Server Events article.

Show Generated SQL

Advanced users may access the Execution SQL window via the Show Generated SQL option. This window displays the SQL statement that will be sent to the appropriate databases in order to execute the report.

The SQL statement shown in the Execution SQL window is the exact statement that will be sent to the databases. It is generated after applying sorts, filters, parameters, and other items that affect the constructed SQL.

Report-Level Parameters v2019.1.3+

Advanced users may also define their own report-level parameters. The Parameters window provides an interface for users to view existing non-hidden system-level parameters and create new parameters that are only present on the report.

To create a new report-level parameter:

1. Click the Add button located in the top-right corner.
2. Define a unique Name. This value will act as the parameter's identifier within the report.
3. Specify a Type for the parameter, the following are available:
   • String
   • Date
   • Integer
   • Decimal
   • Boolean
4. Enter a default or fixed Value. If left blank then the value will be interpreted as null.
5. Specify whether or not to Prompt the user for a value upon report execution.
6. Define the Prompt Text, the message that will appear when prompting the user for a value. A value for this field is required if the parameter is prompting.

Note

Report-level and system-level prompting parameters will both appear in the same prompt window upon report execution.

Report-level parameters can be reused throughout the report in the same manner as non-hidden system-level parameters. They may be used as cell values or within filters, sorts, and calculations. To utilize a parameter within a cell or formula, surround its Name with '@' symbols (@ParameterName@).

For example, the following formula outputs True if an employee's revenue is greater than or equal to the value of the Sales Quota parameter and False if otherwise:

{OrderDetails.UnitPrice}*{OrderDetails.Quantity} >= @SalesQuota@

Using Report-Level Parameters in Dashboards and Chained Reports

Like system-level parameters, report-level parameters may be used and set to prompt for values in Dashboards and Chained Reports when the associated Advanced Report is added as an existing report.

Legacy Dashboard pre-v2019.2 interface:

Within composite reports, however, these parameters can only take affect on the report-level and cannot be set to take affect on the Dashboard- or composite-level. This means that these parameters are only applicable on their associated Advanced Reports and cannot be modified to also apply to other reports or visualizations within a Dashboard or other composite report.