OLAP Spreadsheet Add-in User's Guide for Excel

Using Drill-Through

Drill-Through consists of the following tasks:

• Accessing Drill-Through from Hyperion Essbase Spreadsheet Add-in
• Selecting Drill-Through Reports to View or Customize
• Selecting and Ordering Columns
• Sorting Data
• Filtering Data

The following sections describe each of these tasks and step you through a live working Drill-Through session.

Accessing Drill-Through from the Spreadsheet

From Hyperion Essbase Spreadsheet Add-in, you can access detail-level Drill-Through reports that are based on the member intersections of Hyperion Essbase data cells in the sheet. Each Drill-Through report has been predefined by an administrator at your organization; that is, each Drill-Through report is already set up to retrieve specific columns from the relational source, sort data in these columns in specific ways, and so forth. Using the Drill-Through Wizard, you can customize these predefined Drill-Through reports to retrieve only the data you want, displayed in a specific way.

To access the predefined Drill-Through report, double-click a Drill-Through cell in the spreadsheet (or select a range of cells and choose Essbase > Linked Objects). To help identify which cells in the sheet are associated with Drill-Through reports, you can set styles for cells tagged as Drill-Through. When you double-click a Drill-Through cell, Hyperion Essbase displays the Linked Objects Browser dialog box, which displays a Drill-Through report entry. A single cell can have multiple reports associated with it. The Linked Objects Browser dialog box also displays entries for linked partitions and other linked object types (for example, cell notes and application files). After you view or customize the Drill-Through report, the Hyperion Essbase retrieves data from the relational source and displays the results in a new spreadsheet.

Before starting the Drill-Through tutorial, you will:

1. Open a sample Excel file that contains the appropriate member intersections from the sample database for the Drill-Through report. This file, Dtreport.xls, is provided as part of the default Hyperion Essbase installation
2. Set a style for data cells that have Drill-Through reports associated with them.

To access the sample file and sample database:

1. Start Excel.
2. Choose File > Open and open the Dtreport.xls file from the Essbase\Client\Sample directory.

   The sample file should look like this:

   Figure 181. Sample Excel File for Drill-Through
   

   The sample file shows data for specific members of a Hyperion Essbase database. From this view, a predefined Drill-Through report exists for all member intersections in the sheet. Using Drill-Through, you can access this report and customize it so that the Integration Server retrieves only the data you need and displays the data in the desired format.

3. Select Essbase > Connect and connect to the appropriate sample database.

   Note:

   For information on the sample database, contact the person at your organization who installs Integration Server.

4. Select Essbase > Options and select the Style tab.
5. In the Data Cells option group, click the Integration Server Drill-Through check box and click Format.

   Hyperion Essbase displays the Font dialog box.

6. Select Bold Italic from the Font style list box.
7. Select Red from the Color drop-down list, and click OK.

   Note:

   Hyperion Essbase displays an example of the selected style in the Sample box.

   Figure 182. Sample Style for Integration Server Drill-Through Data Cells
   

8. Click OK to close the Essbase Options dialog box.
9. Select Essbase > Retrieve to display the new style in the spreadsheet.

   In this example, the sample Drill-Through report is associated with every member intersection in the sheet, so all cells are now displayed in red, bold, italic font.

   Figure 183. Sample Excel File with Drill-Through Style Applied
   

   To access the sample Drill-Through report from the Hyperion Essbase Spreadsheet Add-in:

10. Select any Drill-Through cell; for example, cell E4.

    Figure 184. Selecting a Drill-Through Cell
    

    Note:

    You can also select a continuous range of cells in the sheet to display all Drill-Through reports associated with cells you select. In this example, we have only one Drill-Through report attached to the range of cells.

11. Select Essbase > Linked Objects to open the Linked Objects Browser dialog box.

    Alternatively, select the Enable Linked Object Browsing check box in the Essbase Options dialog box (Global tab), which enables you to double-click a linked object cell to open the Linked Objects Browser dialog box. This option works only with single-cell selection. If you select a range of cells, use the Essbase > Linked Objects menu command.

    Figure 185. Linked Objects Browser Dialog Box with Drill-Through Entry Selected
    

12. Select the Drill-Through report entry and click View/Launch.

    Hyperion Essbase launches Drill-Through and displays the Select Drill-Through Report dialog box.

    Note:

    If the Integration Server is not running, Drill-Through does not launch properly. For more information, contact the Hyperion Essbase System Administrator.

    Figure 186. Select Drill-Through Report Dialog Box
    

13. If you are prompted with the Drill-Through Login dialog box to connect to the Integration Server and the relational data source, enter the appropriate connection information. The person at your organization who administers Integration Server and develops Drill-Through reports should provide you with this information.

    Note:

    If there is only one report available for the cells you select in the spreadsheet, and if that report is not designed to be customized, Drill-Through generates the report and immediately displays the results in the spreadsheet. The person at your organization who develops Drill-Through reports specifies whether you can customize a report and whether you need to log in to the Integration Server and relational data source.

14. Follow the steps in the next section, Selecting Drill-Through Reports to View or Customize to select a report to customize.

Selecting Drill-Through Reports to View or Customize

After you launch Drill-Through from the Linked Objects Browser dialog box, the Integration Server displays the Select Drill-Through Report dialog box if:

• More than one Drill-Through report exists for the cell or cell range that you select in the spreadsheet, or
• Only one report exists, but you have the option of customizing it using the Drill-Through Wizard.

  The Select Drill-Through Report dialog box displays the list of Drill-Through reports available for the cells you select in the spreadsheet. Depending on how a report is defined in OLAP Desktop Model interface, you may have access only to view, not customize, the report.
  

  The sample report used for this tutorial is the Promotion Media Mix for Cities report. You will use the Drill-Through Wizard to customize this sample report. To execute a predefined Drill-Through report without customizing it, you would: Select the report that you want to view from the Available Reports list box. Click Execute. The Integration Server retrieves the data from the relational source and displays the results in a new spreadsheet. The new sheet is added before the current sheet.

To customize the sample Drill-Through report:

1. Select the Promotion Media Mix for Cities report from the Available Reports list box.

   Figure 187. Selecting the Sample Drill-Through Report
   

2. Click Customize.

   Note:

   The Customize button may be enabled or disabled for any given report, depending on how the report was defined in OLAP Builder.

   The Integration Server displays the first screen of the Drill-Through Wizard.

   Figure 188. Drill-Through Wizard Dialog Box, Introductory Screen
   

3. Click Next to display the Select Columns and Display Order dialog box.

   Figure 189. Select Columns and Display Order Dialog Box
   

4. Follow the steps in the next section, Selecting and Ordering Columns, to select and order rows for the customized report.

Selecting and Ordering Columns

Using the Drill-Through Wizard, you can customize predefined Drill-Through reports. The first task in the Drill-Through Wizard is selecting and ordering columns to retrieve from the relational database. These columns contain detailed information that is not available in the Integration Server database.

In the Select Columns and Display Order dialog box, you can select which columns you want the Integration Server to retrieve from the relational data source. From this dialog box, you can also specify how the columns are displayed in the resulting report.

The Available Columns list box displays a list of columns available from the relational data source for this report (as defined in OLAP Desktop Model interface). The Selected Columns list box displays the columns from the Available Columns list box in expanded form. You can remove columns from the Selected Columns list box to exclude them from the Drill-Through report.

In this example, the columns from the Available Columns list box are selected for inclusion in the sample Promotion Media Mix for Cities report. These columns are displayed in expanded form in the Selected Columns list box. In this task, you will remove several of the selected columns from the Drill-Through report:

1. In the Selected Columns list box, select the TIME.PRINTMEDIA, TIME.TV, TIME.RADIO, and TIME.DIRECT columns.

   Hold down the Ctrl key to select multiple columns at once.

   Figure 190. Selecting Columns to Remove From the Drill-Through Report
   

2. Click
   
   to move the selected columns from the Selected Columns list box back to the Available Columns list box.

   To move a column from one list box to another, click
   
   or
   
   . To move all columns from one list box to another, click
   
   or
   
   .

3. To rearrange the display order of columns, select each PRODUCTS column (one at a time) in the Selected Columns list box and click Move Up twice to move each column up above the MARKETS columns.

   The customized report will display the columns in the Selected Columns list box in the order in which they appear. The PRODUCTS columns will appear as the first (left-most) columns, then the MARKETS columns, and so forth.

   Figure 191. Rearranging the Display Order of Columns
   

4. Click Next to display the Select Data Sort Order dialog box, and follow the steps in the following section, Ordering Data to further customize the report.

Ordering Data

In the Select Data Sort Order dialog box, you can select an ascending or descending sort order for the data in a column. Sort order determines the order in which rows will be displayed in the Drill-Through report. For example, you can sort the contents of a MARKETS.CITY column in ascending order, which presents the cities in alphabetical order in the Drill-Through report.

To define the sort order of rows in the Drill-Through report:

1. In the Available Columns list box, select the SALESINVACT.MARKETING column.

   The columns in the Available Columns list box are those that you selected in Selecting and Ordering Columns. The columns in the Column list box are those for which a sort order has already been defined in OLAP Builder.

   If a data sort order was selected when the report was created in OLAP Desktop Model interface, the Order By list box displays that selection. Otherwise, the default sort order is Ascending.

2. Click
   
   to move the SALESINVACT.MARKETING column to the Column list box so that you can define a sort order for the column.

   To move a column from one list box to another, click
   
   or
   
   . To move all columns from one list box to another, click
   
   or
   
   .

   Figure 192. Moving a Column to the Column List Box for Sorting
   

3. Rearrange the display order of columns in the Column list box so that the columns are grouped as follows:
   1. Click MARKETS.CITY and click Move Up twice.
   2. Click SALESINVACT.MARKETING and click Move Up twice.
4. In the Column list box, double-click the SALESINVACT.SALES column to change the data sort order from Descending to Ascending so that SALES values are displayed in chronological order in the Drill-Through report.

   The Integration Server now sorts the columns it retrieves first by state (in alphabetical order), then by city (in alphabetical order), then by sales (in chronological order).

   Figure 193. Selecting the Data Sort Order
   

5. Click Next to display the Select Data Filters dialog box, and follow the steps in the following section, Filtering Data to further customize the report.

   To change the data sort order for multiple columns at one time:
   

   Hold down the Ctrl key and select the desired columns from the Column list box. Click Order By. The Integration Server displays the Order By dialog box. Order By Dialog Box Select Ascending or Descending and click OK to return to the Selecting Data Sort Order dialog box.

Filtering Data

You can apply filters to determine what the Integration Server retrieves for the Drill-Through report. For any given column, you may want to retrieve only data that meets certain conditions. For example, the MARKETS.CITY column in the sample database contains many cities. In the sample Drill-Through report, if you did not apply a filter to this list of cities, the Integration Server would retrieve all cities in the East from the relational source, because the sample Drill-Through report applies to the entire Eastern region. In this section, you will apply a filter to the MARKETS.CITY column so that only specific Eastern cities are included in the report.

To define a filter:

1. Select the MARKETS.CITY column from the Column list box.

   The columns in the Column list box are those that you selected in Selecting and Ordering Columns.

   Figure 194. Select Data Filters Dialog Box
   

   Note:

   If there is a filter already attached to the column, it is displayed in the Condition list box. The full string of the filter is displayed in the lower Condition text box.

2. With the MARKETS.CITY column selected, click Add.

   The Integration Server displays the Set Filter on Column dialog box.

   Figure 195. Set Filter on Column Dialog Box
   

3. Select the CITY column from the Column drop-down list box.

   The column displayed in the Column drop-down list box is the one that you selected in Filtering Data.

4. Select the in operator from the Operator drop-down list box.

   Note:

   For more information on filter operators, see the Integration Server Drill-Through online help.

5. Click the Browse
   
   button to open the Select Filter Values from the List dialog box, which lists all possible values for that column.

   The Integration Server displays the Select Filter Values from the List dialog box.

   Note:

   The Integration Server retrieves these values directly from the relational data source. If the relational data source contains many values, the Integration Server confirms if you want to view them all before it retrieves them from the data source.

6. Hold down the Ctrl key, select Andover, Boston, and Cape Cod, and click OK.

   You can select multiple values at one time only if you have selected In or Not In as the filter operator.

   Figure 196. Selecting Filter Values from the List
   

7. Select And from the Add Type option group.
8. Click Add to add the condition to the Filters list box.

   Note:

   For information on using multiple filter conditions, see the Integration Server Drill-Through online help.

   The Set Filter on Column dialog box should now look like this:

   Figure 197. Defining a Filter for a Column
   

   The filter defined above causes only Andover, Boston, and Cape Cod data to show in the Drill-Through report.

9. Click OK to return to the Select Data Filters dialog box.

   Figure 198. Result of Defining a Filter for a Column
   

   Note:

   You can also create a filter by typing the filter conditions directly into the Filters list box. For more information, see the Integration Server Drill-Through online help. To delete a filter, select the filter and click Clear. To delete all filters, click Clear All.

10. Click Finish.

    The Integration Server generates the customized Drill-Through report and displays the results in a new spreadsheet. The new sheet is added to the workbook before the current sheet.

    Figure 199. Customized Drill-Through Report
    

In this sample, the customized Drill-Through report reflects the specifications that you set using the Drill-Through Wizard:

• The TIME.PRINTMEDIA, TIME.TV, TIME.RADIO, and TIME.DIRECT columns are excluded.
• The columns are displayed across the sheet in the order that you specified in the Selected Columns list box in the Select Columns and Display Order dialog box.
• The STATE, CITY, SALES, MARKETING, FAMILYDESC, and PRODDESC columns are sorted in ascending order, starting with the STATE column and proceeding to the PRODDESC column. For example, the Integration Server first sorts the STATE column in ascending order. Because there is only one state represented in this column (Massachusetts), the Integration Server moves on to the CITY column and sorts its contents in ascending (alphabetical) order. Within the SALES column, the values are sorted in ascending (chronological) order within each city. This process continues until all specified columns are sorted first by CITY and then in ascending order within CITY.
• The only cities retrieved from the relational source are Andover, Boston, and Cape Cod, as you specified in the filtering part of the Drill-Through Wizard.