Design Drill Through Reports

Database managers can create a drill through report definitions by creating the column mappings and then defining one or more drillable regions.

• General Considerations for Designing Drill Through Reports
• Define Column Mappings for Drill Through Reports
• Define Drillable Regions for Drill Through Reports
• Implement Parameters for Drill Through Reports

General Considerations for Designing Drill Through Reports

The drill through report you create is associated with a cube in the Scripts section of the database inspector.

Every drill through report definition must include a column mapping and a drillable region. Optionally, you can define a mapping for runtime parameters, if there is a parameterized query in the Datasource that supplies external source data connectivity for your drill through reports.

To create a drill through report definition, you must be a Database Manager or higher.

You can create drill through reports that pull data from an external source system (RDBMS) or from a file.

Note:

If the Datasource for the drill through report is connected to Oracle Database, you can check the Use Temporary Tables option to improve performance for queries that have a large number of values in the SQL “IN” clause.

You can also design drill through reports to access Web URLs.

Define Column Mappings for Drill Through Reports

The column mapping defines which external source columns should be included in the report, which Essbase dimensions those columns map to, and (optionally) a generation/level filter condition indicating how much depth of access to provide.

To understand column mappings in depth, see Drill Through Report Definition.

Define column mappings:

1. On the Applications page, expand the application.

2. From the Actions menu, to the right of the cube name, click Inspect.

3. In the application inspector, select the Scripts tab.

4. Select Drill Through Reports.

5. Click Create and select Datasource from the drop down menu.

6. In the Name field, add a name for the report.

7. In the Datasource field, select the Datasource you want to use.

8. Create column mappings:

   1. Select the columns from the external Datasource that you want to include in the report.

   2. Select the dimension to which you want to map each column.

   3. Select a filter conditions for the mappings; for example, Level 0, Generation, or None (for dimension mapping).

      
      
      
      
      

9. When you are finished, click Drillable Regions. You cannot save the drill through report definition without defining a region, as described in Define Drillable Regions for Drill Through Reports.

Define Drillable Regions for Drill Through Reports

Define one or more drillable regions in the Essbase cube from which you can access (“drill through to”) the external source of data.

1. After you complete the column mappings, click Drillable Regions.

2. Click the plus sign and choose one of the following methods:
   • Double click in the empty row and define the region using calculation syntax: member names and member set functions.
   • Click the pencil icon to open the Drillable Region Selector, select the member you want to add, right click, and choose from the menu options to define the region.
     
     
     
     

3. Click Save. A message is displayed indicating if the drill through report was updated successfully.

   
   

   

   
   

Drillable Region Examples

The following examples show drillable regions in Smart View, highlighted in blue.

Example 1

To define a drillable region at the top of the Market, Year, and Scenario dimensions, the member Sales, and all level 1 members of the product dimension, use:

Market,Year,Scenario,Sales,@LEVMBRS(Product,1)

When you drill into a Smart View sheet, the resulting grid looks like this:

Example 2

To define a drillable region for the descendants of Market, use:

@DESCENDANTS(Market)

When you drill into a Smart View sheet, the resulting grid looks like this:

Example 3

To define a drillable region for the members of generation 3 in the Product dimension, at the top of the Market, Year, Scenario dimensions and the member, Sales, use:

Market,Year,Scenario,Sales,@GENMBRS(Product,3)

When you drill into a Smart View sheet, the resulting grid looks like this:

Implement Parameters for Drill Through Reports

To make drill through reports even more flexible, you can design drill through to implement parameters.

If you are a database manager or higher, you can implement parameterized Datasource queries in drill through reports.

Prerequisite: an application manager or higher has provided you access to a Datasource that uses a fixed (default) value, a substitution variable, or an external user-defined function as a parameter.

Essbase discerns the value of parameters at runtime, inserting their current value into the query that Essbase generates when a drill through is performed.

In the drill through report definition, you can optionally add dimension, generation, and level mapping for runtime parameters. This enables you to further customize the results of drill through reports based on the current variable context.

If you want Essbase to dynamically build drill through reports based on variables, follow this workflow:

1. Implement Parameters for Datasources in the underlying Datasource query. This must be done by an application manager or higher.

2. Create a Drill Through Report Definition associated with the Datasource.

3. Optionally, provide runtime customizations to parameter use, within the drill through report definition. An example is provided below.

4. Test and validate expected behavior by running drill through reports and Debug Drill Through using Essbase Server Platform Log.

Runtime Parameters and User Defined Functions

The following example use cases are based on prerequisite assumptions:

• A user defined function, getMonths, is defined in the external source system. The function returns a comma-separated list of months.

• The Datasource used for the drill through report definition is defined using a query that calls the getMonths function, as follows:

  select * from SampleBasic where month in (getMonths(?))

Level 0 (Recursive) Mapping of Runtime Parameter

In the drill through report definition, when the runtime parameter is bound to Year at Level 0,

then if a Smart View user drills through on Qtr1 of the Year dimension, the drill through report will include Jan, Feb, Mar. If a Smart View user drills through on Year, the drill through report will include Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec.

Generation Mapping of Runtime Parameter

In the drill through report definition, when the runtime parameter is bound to Year at the Quarter generation,

then if a Smart View user drills through on Year dimension member, the drill through report will include Qtr1, Qtr2, Qtr3, Qtr4.

Note:

It is invalid to set a runtime parameter's Dimension/Generation Binding that overlaps with a column mapping. The generated query in this case is always a null set.