Essbase Studio automatically generates the SQL that is used to create a drill-through report. The SQL statement identifies the OLAP intersection levels of the drill-through report and the columns being returned. You can override the SQL generated by Essbase Studio with your own SQL.
In the Template SQL dialog box, you define a template for drill-through SQL, referred to as “template SQL,” which specifies which parameters from the current reporting context need to be passed to the drill-through report as parameters. Template SQL can incorporate tables and columns from any defined data source connection, whether or not it is used or joined in a minischema.
Essbase Studio Template SQL uses the “$$” syntax as the variable delimiter. Text contained within the $$ syntax is replaced with actual column or data values during drill-through execution. However, you cannot use the $$ substitution variables in template SQL when the intersection level of the dimension is defined at Generation 1 and the dimension is built from a parent/child table.
Note: | This procedure is optional. |
To define Template SQL:
From the Report Contents tab of the drill-through report editor, click the Template SQL button.
Optional: To use standard SQL as a reference, from the Cube Schema drop-down list, select the cube schema on which you want to base your SQL, and click Get Standard SQL.
The SQL for this cube schema is displayed in the read-only Standard SQL text box and the editable User-defined SQL text box.
Edit or write the SQL you require for this drill-through report.
Click Update User-defined SQL to save your user-defined SQL without exiting the dialog box.
Note errors and make the appropriate corrections to the user-defined SQL.
Note: | When the SQL is correct, a message displays telling you the SQL is valid. |
When errors are corrected, click OK to return to the drill-through report editor.
To complete the remaining tasks in the Report Contents tab of the drill-through report editor, see the following topics: