Defining Template SQL

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. Note the following rules when working with drill-through Template SQL:

Note:

This procedure is optional.

  To define Template SQL:

  1. From the Report Contents tab of the drill-through report editor, click the Template SQL button.

  2. 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.

  3. Edit or write the SQL you require for this drill-through report.

  4. Click Update User-defined SQL to save your user-defined SQL without exiting the dialog box.

  5. Click Validate to validate the syntax in User-defined SQL.

  6. 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.

  7. When errors are corrected, click OK to return to the drill-through report editor.

  8. To complete the remaining tasks in the Report Contents tab of the drill-through report editor, see the following topics: