Defining the Relational Report Type and Specifying Drill-through Report Columns

  To define a relational drill-through report type and specify the report columns and their order:

  1. Select the Report Contents tab of the drill-through report editor.

  2. From Drill-through Report Type, select Relational.

  3. Add the columns that will appear in the drill-through report.

    Note:

    All columns in the report must be from the same relational source.

    1. Click Add next to the report grid of the Report Contents tab to add a drill-through column to the report.

      A drill-through column is the external database column that Essbase Studio retrieves when a spreadsheet user double-clicks an intersection level. Columns contain detail information that is not available in the Essbase database; for example, a list of store managers.

    2. In Select Column, navigate to the column you want to include in the drill-through report, select it, and click OK.

      The column you selected is displayed in the report grid.

    3. Repeat step 3.a and step 3.b for each column that you want to add to the drill-through report.

  4. Optional: To display a column name other than the name of the column that you selected in step 3:

    1. In the report grid, select the column to work with and then click in the cell under the Display Name column heading to activate the cell.

    2. Type the column name that you want to display in the drill-through report.

    3. Repeat step 4.a and step 4.b for each column for which you want to change the display name in the drill-through report.

  5. Optional: Apply an aggregation function to drill-through columns.

    Use an aggregation function to perform consolidations on the column values that are returned in the drill-through report.

    To apply an aggregate function to a drill-through column:

    1. In the report grid, select the column to work with and then click in the cell under the Aggregate column heading to activate the drop-down list control.

    2. Select one of the following aggregate functions from the drop-down list:

      • Avg—Returns the average value of the column. Applies only to numeric column types.

      • Count—Returns the number of selected rows. Applies to all column types.

      • Min—Returns the minimum value of the column. Applies only to numeric column types.

      • Max—Returns the maximum value of the column. Applies only to numeric column types.

      • Sum—Returns the total sum of the column. Applies only to numeric column types.

    3. Repeat step 5.a and step 5.b for each column to which you want to apply an aggregate function.

  6. Optional: Define the sort order of a drill-through column.

    See Defining Sort Order for Drill-through Report Columns for more information.

  7. Optional: To delete columns from the drill-through report, select the column in the report grid and click Delete.

  8. Optional: To display duplicate records in the drill-through report, select the Show duplicate records check box.

  9. Optional: To base this report on SQL that you have written, select the Use user-defined SQL check box, and click the Template SQL button.

    See Defining Template SQL for more information.

    Alternatively, if you have saved user-defined SQL in the Template SQL dialog box, but do not want to use it, clear the “Use user-defined SQL” check box. Essbase Studio will automatically generate the SQL that is used to create a drill-through report, and your drill-through SQL is saved for future use.

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