The Advanced Options menu is available in Advanced Reports and CrossTab Reports.
Note: If you do not have access to the Advanced Option 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.
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.
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
As of v2019.1+, 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.