Spreadsheet Add-in User's Guide for Excel

Performing Advanced Retrieval Tasks

The tutorial in "A Basic Essbase Tutorial" describes how to perform basic data retrieval and navigation tasks in Essbase Spreadsheet Add-in. This section walks you through performing the following advanced retrieval tasks:

Remember that you can perform common data retrieval tasks in any of the following ways:

Filtering Data

Despite the ease and speed with which you can navigate through large Hyperion Essbase databases, it is not practical to use the capabilities of Excel to filter and sort very large databases; however, the Essbase server includes powerful data filtering and sorting capabilities. In "A Basic Essbase Tutorial", you learned to use Essbase Query Designer to define a dimensional layout and to select members to view. Essbase Query Designer also provides a powerful, yet easy-to-use tool to define conditional retrievals.

To become familiar with the capabilities of Hyperion Essbase Query Designer, work with the query, Basic1, that you saved in "A Basic Essbase Tutorial", and perform the following steps:

Note:
If you skipped the tutorial in "A Basic Essbase Tutorial", follow the steps in "Creating Queries Using Essbase Query Designer" to create and save the Basic1 query.
  1. Select Essbase > Query Designer.

    Essbase displays the query information panel of Essbase Query Designer.

  2. From the location that you specified in "A Basic Essbase Tutorial", select the Basic1 file.
  3. Click OK.

    The member selection, displayed in the properties panel, remains unaltered from the last Essbase Query Designer session.

  4. From the navigation panel, select Data Filtering.

    Essbase displays the data filter settings in the data filter panel. The filter controls the number of data rows that are retrieved. The number is based on the column criteria that you define. You can define data filtering criteria on data values that reside in one or more columns of the view.

    Figure 88. Data Filter Panel


  5. The data filtering panel contains the following items:
  6. Click the Top check box, and enter a value of 30 in the Rows text box.

    When you apply the query, Essbase retrieves the top thirty rows of the dimension.

  7. From the Dimension list box, select Product. Product is the dimension to which ranking should be applied.
  8. From the Column list box, select Qtr1, Actual. Qtr1, Actual is the column on which data values are based.
  9. In the navigation panel, select the Data Filtering icon. Right-click and select Apply Query.

    Your query results should look as shown below.

    Figure 89. Data Filtering Query Results


    You can further filter your data output by specifying data comparison operations in the Data Restrictions box.

  10. In the navigation panel, select the Data Filtering icon.

    The data filters that you specified are displayed in the properties panel.

  11. In the Data Restrictions box, double-click.

    The data restriction settings are displayed in the properties panel.

  12. Select the Value option and type 500 in the Value box.

    Observe that the is option in the Data list box changed to =.

  13. Click the down arrow of the Data list box and select <=.
  14. Select Qtr1, Actual in the Column list box.
  15. In the navigation panel, select the Data Filtering icon. Right-click, and select Apply Query.

    Notice that the query results now reflect only Actual and Budget data that are less than or equal to 500.

  16. In the navigation panel, select the Data Filtering icon to display the data filter setting in the properties panel.
  17. In the Data Restrictions box, select Qtr1, Actual <= 500, right-click and select New Data Restriction.
  18. In the Data box, click the down arrow and select <.
  19. In the data values list box, select Qtr2, Actual. Under Combined With Other Restrictions, click the Or button.
  20. In the navigation panel, click the Data Filtering icon to access the data filter panel.
  21. In the Data Restrictions box, double click to create a new data restriction.
  22. In the Data box, click the down arrow and select Is Not.
  23. Click the button for the #Missing Value option.

    This option instructs Essbase to discard data that have #Missing values.

  24. In the Column list box, select Qtr1, Actual. Under Combined With Other Restrictions, select the And button.

    The data restrictions should display as follows:

    Figure 90. Data Filtering


  25. Select the Data Filtering icon, right-click, and select Apply Query.

    Essbase retrieves data for all the quarters. Notice that the retrieved data for Qtr1, Actual is less than or equal to 500 or is less than Qtr2, Actual. The results should display as follows:

    Figure 91. Data Filtering Results


Note:
To delete all data restrictions, select the Data Filtering icon in the navigation panel, right-click, and select Delete All Data Restrictions. Alternatively, select any data restriction in the Data Restriction box, right-click, and select Delete All Data Restrictions.
Note:
To delete a particular data restriction, select the data restriction in the query outline, right-click, and select Delete Data Restriction. Alternatively, select the data restriction in the Data Restriction box, right-click, and select Delete Data Restriction.

Sorting Data

Using the data sort panel, you can sort the output from the Basic1 query in ascending or descending order.

  1. From the navigation panel, select the Data Sorting icon.

    The data sorting settings are displayed in the properties panel. You can specify data sorting criteria that affect the order in which the selected rows are retrieved in the data sorting panel.

    The data sorting panel contains the following items:

    Figure 92. Data Sorting Panel


  2. In the Column used for sort list box, double-click.

    The selection defaults to Qtr1, Actual. The sort order defaults to Ascending in the Ordering list box.

  3. Click Ascending. A down arrow is displayed next to Ascending.
  4. Click the down arrow. Descending is displayed below Ascending.
  5. In the Ordering list box, select Descending.

    Figure 93. Specifying Data Sorting Order


  6. In the next row of the Column used for sort list box, double click.

    The selection defaults to Qtr1, Actual.

  7. Click the down arrow and select Qtr1, Budget.

    Observe that the order in the Ordering list box has defaulted to Ascending.

  8. In the navigation panel, under Data Sorting, select Ascending, Qtr1, Budget. Right-click and select Delete Sorting Rule.

    The Ascending, Qtr1, Budget sorting rule is deleted from the query.

  9. In the navigation panel, select the Data Sorting icon. Right-click and select Apply Query.

    Essbase returns the results sorted in descending order for each quarter, as shown below:

    Figure 94. Result of Filtering and Sorting Data


    Note:
    The values that you are ranking and sorting must be the same. For example, you cannot specify Product in the Dimension Being Ranked box and Market in the Dimension Being Sorted box. If you specify different values, Hyperion Essbase Query Designer automatically changes both values to the last specified value.
  10. To close the worksheet, select File > Close. You do not need to save the worksheet.

Retrieving Data into Asymmetric Reports

When you retrieve data into a worksheet, the resulting report can be either symmetric or asymmetric. Symmetric reports are characterized by repeating identical groups of members. For example, Figure Figure 94 shows a symmetric report that contains Actual and Budget members nested below Year members (Qtr1, Qtr2, Qtr3, and Qtr4).

An asymmetric report is characterized by groups of nested members that differ by at least one member. There can be a difference in the number of members or in the names of members.

You can create asymmetric reports in one of the following ways:

If you retrieve data into an asymmetric report, Essbase must perform additional internal processing to maintain the asymmetric layout. This processing may increase the retrieval time on large reports. For more information regarding optimizing reports, see the Database Administrator's Guide.

As part of the default Essbase installation, Hyperion Essbase provides a sample file, Asymm.xls, that illustrates how to create asymmetric reports.

To view the sample file, Asymm.xls:

  1. Select File > Open.
  2. From the \Essbase\client\sample directory, open the Asymm.xls file.

    Depending on how software is installed on your PC, the file may not be available or may be located in a different directory. Contact the Essbase system administrator for more information.

    Figure 95. Asymmetric Report


    In Asymm.xls, row and column dimension groups are asymmetric. Thus, the nested member groups from the Product dimension differ in member content within respective markets. For example, Colas and Fruit Soda are included in East but not in West. In addition, Actual data is displayed for Qtr1, whereas Budget data is displayed for Qtr2, Qtr3, and Qtr4. Also notice that the sample file displays styles for members of the Scenario and Year dimensions.

  3. Leave the Asymm.xls file open for the next tutorial task.

Pivoting in Asymmetric Reports

When you pivot a group of members in an asymmetric report, Essbase keeps only unique members from dimensions that are not involved in the pivot.

To use the open Asymm.xls file to illustrate this point:

  1. Using the right-mouse button, drag East to the cell below Qtr1.

    Figure 96. Result of Pivoting in an Asymmetric Report


    Essbase combines the Product members into all unique members. For example, Root Beer, which is displayed twice in Figure Figure 95, is displayed only once in the current view. Colas, which is displayed in only one market in Figure Figure 95, now is displayed in East and West.

    Essbase also removes the blank line between Product row groups. A pivot action always eliminates any rows or columns in which all cells are empty.

  2. Close Asymm.xls without saving the changes.

Working with Formatted Worksheets

In addition to providing flexible, ad hoc retrievals, Essbase supports retrieving data into formatted worksheets. A worksheet can contain the following formats:

After you format and save a worksheet, you may want to retrieve and navigate through new data in the existing worksheet format. This section provides the following information on working with formatted worksheets:

Observing the Rules for Working with Formatted Worksheets

Observe the following rules when retrieving data into a formatted worksheet:

Rule 1

In the worksheet, no numeric cells can be located before the first Hyperion Essbase data cell. For example, in Figure Figure 97, the first Essbase data cell is B6. Neither any cell in rows 1 through 5 nor cell A6 can contain numeric values. Also, these cells cannot contain formulas that resolve to numeric values.

Rule 2

A cell that lies within a row or column of Essbase data cannot contain text or numeric values. For example, in Figure Figure 97, the cells in columns B, C, D, and F and rows 6 through 9 and 11 through 14 cannot contain any nondata text or numbers, for such values may be overwritten (or emptied) by the retrieved data. These cells can contain formulas, however, if Formula Preservation options are used. For more information on Formula Preservation options, see "Preserving Formulas When Retrieving Data".

Tip:
If you need to preserve text in a cell, define that text or value as an Excel formula, and use the Formula Preservation options.

Rule 3

The Pivot command is not available when the Retain on Retrieval check box is selected in the Essbase Options dialog box (Mode tab).

Rule 4

The Pivot command removes all cells that contain text other than database member names.

Retrieving Data into Formatted Worksheets

As part of the default Essbase installation, Essbase provides a sample file, P&l.xls, that illustrates how to retrieve data into a formatted worksheet. The P&l.xls sample file illustrates how to retrieve data into a worksheet that contains formatted text, formulas, and protected cells.

To view the P&l.xls worksheet:

  1. Select File > Open.
  2. From the \Essbase\client\sample directory, open the P&l.xls file.

    Depending on how software is installed on your machine, the file may not be available or may be located in a different directory. Contact the Essbase system administrator for more information.

    Figure 97. A Sample Formatted Worksheet


  3. Select Essbase > Options and select the Display tab.
  4. In the Cells option group, make sure that Adjust Columns is checked.
  5. Select the Mode tab.
  6. In the Formula Preservation group, select the Retain on Retrieval check box to enable Formula Preservation mode.
    Note:
    When Retain on Retrieval is enabled, there may be a slight delay in retrieval time.
  7. Click OK.

    By default, an Essbase retrieval overwrites Excel formula with data values in the retrieval area of a worksheet. The retrieval process also eliminates formulas in cells outside the retrieval area. However, the Retain on Retrieval option enables you to define retrievals that do not overwrite formulas in any area of the worksheet.

  8. Select Essbase > Retrieve to update the worksheet with the latest data values.

    Essbase determines that some text cells in the worksheet do not correspond to database member names. When Essbase is unable to resolve text in the worksheet, the following message is displayed:

    Figure 98. Essbase Unknown Member Message


    In this example, the first unknown member detected is Market: (in cell A1). Essbase does not recognize the colon (:) that follows Market in the cell. If you click Yes, Essbase displays the next unknown member; if you click No, Essbase continues with the retrieval.

    Note:
    If you work with formatted worksheets often, you may want to tell Essbase not to display this message. For more information, see "Pivoting Data on Formatted Worksheets".
  9. Click No to close the dialog box and continue with the retrieval.

    Essbase retrieves new data but retains the formatting and formulas in the worksheet.

    Figure 99. Result of Retrieving on a Formatted Worksheet


  10. Close P&l.xls without saving it.

Note:
This chapter's section on "Preserving Formulas When Retrieving Data" provides additional tutorial tasks that show you how to take advantage of all the Formula Preservation options.

Pivoting Data on Formatted Worksheets

With the Pivot command, you can produce ad hoc reports in both formatted and unformatted worksheets. However, a formatted worksheet may contain labels and formulas that make the result of a pivot operation ambiguous. The pivot is designed to compress and retain only the database elements represented in the worksheet. The worksheet also retains labels in areas that are not overwritten by pivoted data.

Note:
Essbase prevents pivot operations on worksheets that contain formulas when Formula Preservation mode is active.

Inv.xls, a sample file installed as part of the Essbase installation process, illustrates how to pivot data in a worksheet. It was saved with the Retain on Retrieval option disabled so that you can pivot on its worksheets.

To view the Inv.xls worksheet:

  1. Select File > Open.
  2. From the \Essbase\client\sample directory, open the Inv.xls file.

    Depending on how software is installed on your PC, the file may not be available or may be located in a different directory. Contact the Essbase system administrator for more information.

    Figure 100. Formatted Worksheet Before Pivoting


  3. Select Essbase > Options and select the Global tab.
  4. In the Display Messages group, clear the Display Unknown Members check box to avoid seeing the Essbase messages when working with formatted worksheets.
  5. Using the right-mouse button, drag Sales (in cell A5) to the cell nested below Jan (C4).

    Essbase pivots the Sales member group so that it is nested below the monthly members. However, during the pivot, Essbase retains only database elements. For example, all data for Stock to Sales and Adjusted for Audit is deleted during the pivot.

    Figure 101. Result of Pivoting on a Formatted Worksheet


  6. Close Inv.xls without saving it.

Preserving Formulas When Retrieving Data

In "Retrieving Data into Formatted Worksheets", you used the Retain on Retrieval option to preserve formatting and formulas in an existing worksheet. Collectively, the Formula Preservation options enable you to retain formulas during data retrievals, keep and remove only operations, and drill operations. In addition, Essbase can replicate formulas for additional members retrieved into the worksheet as part of a drilling operation. This section steps you through using all of the Formula Preservation options in a report that you create.

You must enable some options before you can enable other options. Keep in mind the following guidelines and restrictions when using the Formula Preservation options:

To preserve formulas when retrieving or retaining data:

  1. To open a new worksheet, select File > New or click the button.
  2. Select Essbase > Retrieve.
  3. In cell A2, drill down (double-click) on Year.
  4. Press and hold the Alt key and, in cell E1, drill down (double-click) on Scenario.
  5. Select cell G3 and enter the following formula in the cell: =B3/B$7*100.
    Note:
    The $ in front of the 7 anchors the formula to the Year member.

    Figure 102. Entering an Excel Formula into a Cell


  6. Press Enter.

    Excel calculates the formula that you entered in cell G3 and now reflects Qtr1 as a percentage of Year.

    Figure 103. Result of Excel Formula with Essbase Data


  7. Select Essbase > Options and select the Mode tab.
  8. In the Formula Preservation area, select the check boxes for Retain on Retrieval and Retain on Keep and Remove Only. Click OK.
  9. In cells D2, E2, and F2, respectively, select Variance, %Variance, and Scenario.
  10. Select Essbase > Remove Only.

    Essbase removes the selected columns but retains the Excel formula that you entered, keeping it with the retained dataset.

    Figure 104. Result of Removing Columns with Retain on Keep or Remove Only Enabled


  11. Select Essbase > Options and select the Mode tab.
  12. In the Formula Preservation area, click the box for Retain on Zooms and click OK.
  13. In cell A3, drill down (double-click) on Qtr1.

    Essbase drills down on Qtr1 and moves the formula down with the Qtr1 member.

    Figure 105. Result of Drilling Down with Retain on Zooms Enabled


  14. Select Essbase > FlashBack.
  15. Select Essbase > Options and select the Mode tab.
  16. In the Formula Preservation area, select the Formula Fill check box. Click OK.
  17. In cell A3, drill down (double-click) on Qtr1.

    Essbase drills down on Qtr1 and replicates the formula for each member of Qtr1 (Jan, Feb, and Mar). To view the replicated formulas, click in cells D3, D4, D5, and D6 and look at the new syntax in the Excel formula bar.

    Figure 106. Result of Drilling Down with Formula Fill Enabled


  18. Before moving on with the tutorial, complete each of the following actions:
    1. Select Essbase > Options and select the Mode tab.
    2. Clear all of the Formula Preservation options.
    3. Select File > Close to close the worksheet. You do not need to save the worksheet.

Retrieving a Range of Data

In a typical Excel worksheet, you can select a range of cells by dragging the mouse across the worksheet. You can also select a range of cells and tell Essbase to restrict the data retrieval to the selected range in the worksheet. Retrieving a range of data is particularly useful in the following situations:

As part of the default Essbase installation, Essbase provides a sample file, Profit.xls, that illustrates how to retrieve a range of data.

To view the Profit.xls file:

  1. Select File > Open.
  2. From the \Essbase\client\sample directory, open the Profit.xls file.

    Depending on how software is installed on your PC, the file may not be available or may be located in a different directory. Contact the Essbase system administrator for more information.

    Profit.xls has already selected a range of cells (B2 through F9) for you.

    Figure 107. Selected Range of Cells for Retrieval


  3. Select Essbase > Retrieve to update the selected range.

    Figure 108. Result of Retrieving First Range of Data


    Essbase updates only the data in the selected range of cells.

  4. Select cells B12 through G16.

    Figure 109. Selecting Cells for Retrieval


  5. Select Essbase > Retrieve once again to update the selected range.

    Essbase updates the data in the selected range.

    Figure 110. Result of Retrieving Second Range of Data


  6. Close Profit.xls without saving it.

Retrieving Data by Using a Function

The Essbase cell retrieve function, EssCell, retrieves a single database value into a worksheet cell. Enter an EssCell function directly into a worksheet or select an EssCell function from the Excel menu bar.

Note:
You must be connected to a database to use EssCell.

EssCell retrieves data when you perform an Essbase retrieval or when you recalculate a worksheet through Excel. As part of the default Essbase installation, Hyperion Essbase provides a sample file, Summary.xls. This file has EssCell functions already set in cells B16 and B17.

To view the Summary.xls file:

  1. Select File > Open.
  2. From the \Essbase\client\sample directory, open the Summary.xls file.

    Depending on how software is installed on your PC, the file may not be available or may be located in a different directory. Contact the Essbase system administrator for more information.

    Figure 111. Worksheet Containing EssCell Functions


    Cells B16 and B17 in Summary.xls contain the EssCell function. If you select either of these cells, you can view the syntax for the EssCell function in the Excel formula bar at the top of the worksheet.

    The EssCell function is defined in a cell as follows: 


    =EssCell(mbrList)

    mbrList is one of the following:

    For example, the syntax for the EssCell function in cell B16 in the Summary.xls file is the following: 


    =EssCell("Sales", A1, A2, B4, F5)

    When you open the worksheet, the values in these cells are #N/A. To update the values with the data in your database, you must perform a retrieval from Essbase.

  3. Select Essbase > Retrieve. Essbase calculates the EssCell functions in cells B16 and B17.

    Figure 112. Retrieval on a Worksheet Containing the EssCell Function


    Now update the EssCell functions to retrieve data for a different state.

  4. Change the contents of cell A2 from Texas to Florida.

    The values in cells B16 and B17 are updated as soon as you update the cell. The update occurs because Excel has recalculated the worksheet (if you have configured Excel to calculate changes automatically). The remaining data cells do not change. To completely update the worksheet, you must retrieve data from the server.

    Figure 113. Updating the EssCell Function


    Tip:
    If the worksheet contains many EssCell functions, change Excel to manual calculation mode. This change prevents the cell retrieve from calculating until you retrieve data or calculate the worksheet manually. For more information, see the Microsoft Excel documentation.
  5. Select Essbase > Retrieve to update the report.

    Essbase returns an error message if EssCell is unsuccessful. The following table lists messages that Essbase displays in the EssCell cell and explains the conditions that cause the messages:


    Message

    Reason

    #N/A

    The worksheet is not connected to a database.

    #VALUE!

    A member name in the list or reference is invalid.

    #NAME?

    A text name in the function does not contain double quotation marks.

  6. Close Summary.xls without saving it.

The sample file that you used for this tutorial task already had EssCell functions defined.

Note:
Tasks that you should not perform as part of the tutorial are shown in light-shaded boxes.

To enter your own EssCell function into a worksheet, you do the following:

  1. From the Excel menu bar, select Insert > Function.
  2. From the Function Category list, select Essbase Add-in.

The Excel Function Wizard steps you through defining the EssCell function.

Note:
For more information on EssCell functions, see the Essbase Spreadsheet Add-in online help.

Retrieving Dynamic Calculation Members

Dynamic calculation members are database members that are excluded from the batch calculation process, thus shortening the regular database calculation time . The Essbase application designer tags dynamic calculation members in the database outline so that Essbase knows not to calculate those members until a data retrieval requests them. This process is referred to as dynamic calculation. Dynamically calculating database members benefits the Essbase server in the following ways:

Database values that Essbase calculates dynamically take slightly longer to retrieve in Essbase Spreadsheet Add-in because Essbase must perform calculations before retrieving data into the worksheet. For more information on dynamic calculation, see the Database Administrator's Guide.

Tip:
Enable the Navigate Without Data feature while you arrange the spreadsheet report so that Essbase does not dynamically calculate the database when you are creating the report. For more information on the Navigate Without Data feature, see "Navigating Through the Worksheet Without Retrieving Data".

Because there may be a performance impact on retrieving data for dynamic calculation members, define visual cues, or styles, for these members so that you can identify them in Essbase Spreadsheet Add-in.

To illustrate how to use Dynamic Calculation members, use the Asymm.xls file.

To view the Asymm.xls file:

  1. Select File > Open.
  2. From the \Essbase\client\sample directory, open the Asymm.xls file.
  3. Drill up on Actual by double-clicking the right-mouse button in cell C3.
  4. In cell C3, drill down on Scenario to display all members of Scenario.

    Essbase displays only the Scenario members for Qtr1.

    Figure 114. Displaying Scenario Members


  5. Select Essbase > Options and select the Style tab.

    Note:
    You must be connected to the Sample Basic database to display the Style tab. For more information on connecting to a database, see "Connecting to a Database".
  6. In the Members group, scroll down until you see Dynamic Calculations.
  7. Select the Dynamic Calculations check box and click the Format button.
  8. In the Font style list, select Bold Italic.
  9. In the Color list, select Gray.
  10. Click OK and then select the Display tab.
  11. In the Cells option group, click Use Styles. Click OK.
  12. Select Essbase > Retrieve.

    Essbase displays the data and applies all the visual cues, or styles, that you set, including the newly set styles for dynamic calculation members and the styles you set in "A Basic Essbase Tutorial". You can tell that the columns for Variance and Variance% are dynamically calculated because Hyperion Essbase displays these members in gray, bold, italicized font. (Variance and Variance% also have a red background because that style was previously set for all members of the Scenario dimension.)

    Figure 115. Result of Retrieving Dynamic Calculation Members


    Note:
    Occasionally, other styles you have set may override a style for dynamic calculation members. For example, if a parent member is also a dynamic calculation member, and if you have styles set for both parent and dynamic calculations, the style for the parent member overrides the style for the dynamic calculation member. In this case, you need to remove the style for parent members in order to see the style for dynamic calculation members. For more information on the precedence of styles, see "Determining the Precedence of Overlapping Styles".
  13. Close the Asymm.xls file without saving it.

Specifying the Latest Time Period for Dynamic Time Series

Dynamic Time Series members are predefined database members that are used in dynamic, to-date reporting, such as year-to-date or month-to-date values. Dynamic Time Series members do not display as individual members in the database outline; instead, they correspond to a generation name that uses a key term for time, such as year, month, or day.

For example, in the Sample Basic database, a generation name called Quarter was created for generation 2 in the Year dimension. Generation 2 includes the members for Qtr1, Qtr2, Qtr3, and Qtr4. When the generation name Quarter was created, Essbase created a Dynamic Time Series member called Q-T-D, or quarter-to-date.

To take advantage of Dynamic Time Series in Essbase Spreadsheet Add-in, choose the latest time period for which you want data in a to-date calculation. The calculated value of the Dynamic Time Series member is derived when you define the latest time period to be reported. In the Sample Basic database, for example, the level 0 members of the Year dimension are the months of the year: Jan, Feb, Mar, and so on. If the current month is August, and you want to know the sales data for the quarter up to the current month, a quarter-to-date calculation gives you the sales data for the months of July and August.

To illustrate the concept of Dynamic Time Series:

  1. To open a new worksheet, select File > New or click the button.
  2. Select Essbase > Options and select the Zoom tab.
  3. In the Zoom In option group, select Next Level.
  4. Clear the Within Selected Group check box and click OK.
  5. Select Essbase > Retrieve.
  6. In cell B1, drill down (double-click) on Measures.
  7. In cell B2, click Year.
  8. Type Q-T-D in cell B2 and press Enter to enter a predefined Dynamic Time Series member (Q-T-D):

    Figure 116. Entering a Dynamic Time Series Member into a Worksheet


    Note:
    For a list of other possible Dynamic Time Series members, see the Essbase Spreadsheet Add-in online help.
  9. Select Essbase > Options and select the Display tab.
  10. In the Dynamic Time Series area, select the Latest Time Period check box.
  11. From the list box, select May. Click OK.

    Figure 117. Specifying Latest Time Period in a Dynamic Time Series


    Note:
    If you do not specify a latest time period, Essbase uses the first level 0 member (Jan) as the default.
  12. Select Essbase > Retrieve.
    Note:
    The Retrieve & Lock, Zoom In, and Zoom Out commands are not supported with Dynamic Time Series members.

    Essbase displays data for the Q-T-D member. The data values in the worksheet are the aggregated values for April and May, because May is the month you specified as the latest month in the quarter-to-date Dynamic Time Series.

    Figure 118. Result of Specifying Latest Time Period in a Dynamic Time Series


  13. To close the worksheet, select File > Close. You do not need to save the worksheet.

Note:
You can also create a report like the one shown above by simply typing the name of the Dynamic Time Series member followed by the name of latest time period in parentheses: for example, Q-T-D (May). If you are using free-form retrieval mode, you must enter the Dynamic Time Series member and the latest time period in separate, adjacent cells. You can also select a Dynamic Time Series member and a latest time period through Hyperion Essbase Query Designer or through Member Selection.
Note:
For more information on Dynamic Time Series, see the Essbase Spreadsheet Add-in online help.

Using Substitution Variables

The Essbase application designer uses substitution variables to define global variables to represent values that are specific to Essbase. For example, Latest can be a substitution variable representing the latest time period in a Dynamic Time Series. Using Essbase Application Manager, the application designer sets substitution variables and their corresponding values for a specific application. Essbase stores these variables and their values on the Essbase server. You can take advantage of the predefined substitution variables in Essbase Spreadsheet Add-in during Dynamic Time Series reporting

. For example, say the application designer sets a substitution variable on the server for the current month. The variable is called CurMnth and has a value of August. If you use the substitution variable in the worksheet, a retrieval returns values for August, because August is set as the current month on the server. If, at a later date, the application designer changes the value of the CurMnth substitution variable to October, a retrieval returns data for October.

The Sample Basic database you are using for this tutorial does not contain predefined substitution variables. If the application designer had set a substitution variable in the database you are using, you could enter a substitution variable directly into a worksheet. For example, you could open a blank worksheet and type member names as follows:

Figure 119. Entering a Substitution Variable in a Worksheet


Note:
Notice the substitution variable (CurMnth) in cell A2. When you enter a substitution variable directly into a cell, you must precede it with an ampersand (&).

In this example, a retrieval produces the following results:

Figure 120. Result of Retrieval on a Substitution Variable


Essbase queries the server for the value of the substitution variable CurMnth, which is August. Data is returned only for August.

Note:
If you save a worksheet containing a substitution variable as a template, make sure that you save the worksheet before you retrieve data. For example, if you save the worksheet shown in Figure Figure 120 as a template, each time you retrieve the template, August rather than the substitution variable CurMnth is displayed.

Using Free-Form Reporting to Retrieve Data

So far, you have learned how to retrieve Essbase data into a worksheet through ad hoc retrieval, Hyperion Essbase Query Designer queries, and Member Selection operations. In addition to these retrieval methods, Essbase supports free-form reporting. Free-form reporting enables you to tell Essbase specifically what you want to retrieve by simply typing data into the worksheet. Free-form reporting is especially useful when you are familiar with the dimensions and members in the database outline.

Essbase provides two different retrieval modes for free-form reporting:

In both retrieval modes, enter member names directly into the worksheet. The following sections describe the similarities and differences between the two modes.

Using Advanced Interpretation Mode

The Essbase server contains an advanced spreadsheet interpretation engine that scans a worksheet and interprets its content when fulfilling retrieval requests. When you construct a report by entering names directly into a worksheet in Advanced Interpretation retrieval mode, Essbase interprets the member names and creates a default view that is based on the location of the labels.

Keep in mind the following guidelines when you are working in Advanced Interpretation mode:

To construct a free-form report in Advanced Interpretation retrieval mode:

  1. To open a new worksheet, select File > New or click the button.
  2. Select Essbase > Options and select the Mode tab.
  3. In the Retrieval option group, select Advanced Interpretation (the default setting) and click OK.
  4. Enter member names and data as in the following illustration.
    Note:
    If a member name consists of a number, such as 100, you must precede the member name with a single quotation mark (for example, '100). This rule also applies to member names with spaces between words.

    Figure 121. Creating a New Free-Form Report


  5. Select Essbase > Retrieve or double-click in a data cell.

    Essbase retrieves data for the members you entered into the free-form report and implements the Use Aliases option that you set in the Essbase Options dialog box in the beginning of this chapter.

    Figure 122. Retrieving Data into a Free-Form Report


Now define a free-form report that does not contain all of the dimensions from the database. In Advanced Interpretation mode, you may need to enter a dummy data value, such as 0, into the first Essbase data cell to tell Essbase where the data starts in the worksheet. Be sure to use a numeric value as the dummy value.

For example:

  1. Select Essbase > FlashBack.
  2. Delete cells A1, B1, and C1.
  3. In cell B4, type 0 in cell B4 to provide Essbase with a data cell reference point.

    Figure 123. Defining a Free-Form Report Without All Dimensions


  4. Select Essbase > Retrieve.

    Essbase adds the dimensions that were omitted from the free-form report to the worksheet and retrieves data.

    Figure 124. Retrieval in a Free-Form Report Without all Dimensions


  5. Select File > Close to close the worksheet. You do not need to save the worksheet.

Using Free-Form Mode

Similar to Advanced Interpretation mode, Free-Form mode enables you to enter member names into any location in a worksheet and then interprets the contents of the worksheet when fulfilling the retrieval request. In addition, with Free-Form mode, you can use Essbase report script commands to retrieve data into a worksheet.

Report script commands are most useful for defining member references that can bring back the most current member information dynamically. For example, if you need to create a report that shows every product, including the products added since the last retrieval, standard retrieval mode reflects changes only when you zoom in on the product. If you use the report script command <IDESCENDANTS, Essbase retrieves all descendants of a specified member, including the specified member. For more information on the syntax of report script commands and on guidelines for developing reports, see the Technical Reference in the docs directory and the Database Administrator's Guide

. Keep in mind the following guidelines when working in Free-Form retrieval mode:

To construct a free-form report in Free-Form retrieval mode:

  1. To open a new worksheet, select File > New or click the button.
  2. Select Essbase > Options and select the Mode tab.
  3. In the Retrieval option group, select Free Form.
  4. Select the Display tab.
  5. In the Cells option group, select Auto Sort Rows. Click OK.
  6. Enter the member names into the worksheet as shown in Figure Figure 125:

    Figure 125. Typing Member Names In a Free-Form Report


  7. Select Essbase > Retrieve.

    Essbase retrieves data for the members and creates a default view according to the location of the labels. Note that three members were pivoted from row groups to column groups.

    Figure 126. Result of Retrieving in Free-Form Retrieval Mode


  8. Select File > Close to close the worksheet. You do not need to save the worksheet.

Now create a report by entering member names and a report script command:

  1. To open a new worksheet, select File > New or click the button.
  2. Enter the member names into the blank worksheet as shown in Figure Figure 127.

    Figure 127. Typing Member Names in a Free-Form Report


  3. In cell A3, type <IDESCENDANTS Product. Press Enter.

    Figure 128. Typing a Report Script Command in a Free-Form Report


  4. Select Essbase > Retrieve.

    Essbase retrieves data into the worksheet for all descendants of Product and for the members you entered in the worksheet.

    Figure 129. Result of a Retrieve with a Report Script Command


    Note:
    When Essbase completes the retrieval, the report script command is overwritten by the data it returns. You can use FlashBack to restore the previous view in Free-Form mode.
  5. Select File > Close to close the worksheet. You do not need to save the worksheet.

Using Attributes in Free-Form Reporting

Essbase enables you to retrieve data selectively by specifying attributes that are associated with a base dimension. For example, in the Sample Basic database, the Product base dimension is associated with attributes such as packaging and size. You can enter an attribute name into the worksheet to retrieve data that is associated with that attribute.

To use attributes in a free-form report:

  1. To open a new worksheet, select File > New or click the button.
  2. Enter member names as shown in the Figure Figure 130.

    Caffeinated is an attribute dimension associated with the Product base dimension. Bottle is a level 0 member of the Pkg_Type attribute dimension. The Pkg_Type dimension is associated with the Product base dimension. A level 0 member is the lowest level member in a dimension.

    Figure 130. Using Attributes in Free-Form Reports


  3. Click in any empty cell and select Essbase > Retrieve or double-click in an empty data cell.

    Essbase retrieves information on profits for the first quarter of the year for all members of the Product base dimension that are associated with both the level 0 attribute members of the Caffeinated attribute dimension (Caffeinated_True and Caffeinated_False) and the level 0 member Bottle of the Pkg_Type attribute dimension. The results should display as shown below:

    Figure 131. Result of Using Attributes in Free-Form Reports


  4. You can drill down to data on the level 0 attribute members of the Caffeinated attribute dimension. The results should display as shown below:

    Figure 132. Drilling Down on Attributes in Free-Form Reports


  5. To drill down further for data on profits for the first quarter for all members of the East base dimension, double-click on cell E1. The results should display as shown below:

    Figure 133. Result of Drilling Down on East


Entering Generation and Level Names

In addition to entering database member names into a free-form report, you can enter generation or level names directly into a worksheet to retrieve specific members. The Essbase application designer defines generation and level names for database dimensions in the database outline. You have two options for discovering what generation and level names are defined in the database:

To enter generation and level names directly into a free-form report:

  1. To open a new worksheet, select File > New or click the button.
  2. Select Essbase > Options and select the Mode tab.
  3. In the Retrieval option group, select Advanced Interpretation. Click OK.
  4. Enter member names as shown in Figure Figure 134:

    Figure 134. Entering Member Names in a Free-Form Report


  5. Enter a generation name as shown in Figure Figure 135:

    Family is a generation name in the Product dimension. The name is already defined in the Sample Basic database.

    Figure 135. Entering a Generation Name in a Free-Form Report


  6. Select Essbase > Retrieve.

    Essbase retrieves data for the member and generation names that you entered. The Family generation name expands to its individual members.

    Figure 136. Result of Free-Form Retrieval with Generation Name


  7. Change Year to a level name (Lev0,Year) as shown in the following illustration.

    Figure 137. Entering a Level Name in a Free-Form Report


    Note:
    Be sure not to insert a space between the comma after 0 and the word Year. Generation and level names that are entered directly into a worksheet must be precise.
  8. Select Essbase > Retrieve.

    Essbase retrieves data for the level 0 members of the Year dimension, which are the individual months (Jan, Feb, Mar, and so forth).

    Figure 138. Result of Free-Form Retrieval with Level Name


  9. Select File > Close to close the worksheet. You do not need to save the worksheet.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]