Spreadsheet Add-in User's Guide for Excel

Formatting the Worksheet

Essbase provides you with various ways to customize the worksheet view. For example, you may want to apply visual cues, or styles, to certain member names or to data cells in the worksheet, or you may want to display alternative names, or aliases, for member names. This section steps you through the following formatting procedures:

• "Formatting Text and Cells"
• "Displaying Aliases for Member Names"
• "Displaying Both Member Names and Aliases"
• "Repeating Member Labels"

This section of the tutorial starts with a new worksheet.

Formatting Text and Cells

In a spreadsheet report, you may have many hierarchical levels of database information displayed. By defining and applying visual cues, or styles, to the text and cells in the worksheet, you can easily keep track of specific database members, dimensions, and cell functions. Styles are a great way to help view and distinguish data in Essbase Spreadsheet Add-in.

Note:

Keep in mind that applying styles requires additional processing during a retrieval request. For more information, see "Removing Styles".

This section of the tutorial describes the following:

• "Applying Styles to Parent Members"
• "Applying Styles to Dimension Members"
• "Applying Styles to Data Cells"
• "Determining the Precedence of Overlapping Styles"
• "Removing Styles"

Note:

For a discussion of the relationships among Essbase database members, see "Database Outlines".

Applying Styles to Parent Members

Each dimension in a database may contain a large number of hierarchical levels. As you view data in the worksheet, you may not be familiar with all the hierarchical levels of the database outline. To indicate which members have underlying children, you can apply formatting styles to parent members, including those with attributes.

To apply styles to parent members:

1. To open a new worksheet, select File > New or click the icon.
2. Select Essbase > Retrieve.

   Note:

   You should still be connected to the Sample Basic database. If you are not connected, follow the steps in "Connecting to a Database".

3. In cell A2, drill down (double-click) on Year.
4. Select Essbase > Options.
5. In the Essbase Options dialog box, select the Style tab.

   Note:

   The Style tab is available only when you are connected to a database.

   Essbase displays the Style tab.

   Figure 45. Essbase Options Dialog Box, Style Tab
   
   
   

   In the Members area, you can define styles for various types of database members, such as parent, child, and shared members.

6. In the Members group box, select the Parent check box.

   Clicking this box defines a font and color style for parent member names. Essbase defines a default color of navy for all parent members. You can select a font format by clicking the Format button to the right of the Members box and using the Font dialog box.

7. Click the Format button.

   Essbase displays the Font dialog box.

   Figure 46. Font Dialog Box
   
   
   

8. In the Font style list box, select Bold and then click OK.

   Note:

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

9. Click OK again.

   Even though you have defined styles, they are not enabled until you select the Use Styles check box from the Essbase Options dialog box and refresh the worksheet.

10. Select Essbase > Options, and select the Display tab.
11. In the Cells option group, select the Use Styles check box to enable your styles, and then click OK.
12. Select Essbase > Retrieve to refresh the worksheet and apply the styles.

    Essbase displays parent member names in bold, navy font.

13. In cell A2, drill down (double-click) on Qtr1.

    Essbase displays Jan, Feb, and Mar in a regular font, because these members do not have underlying children.

    Figure 47. Styles Applied to Parent Members
    
    
    

Applying Styles to Dimension Members

In addition to applying styles to parent members (as you did in the previous section), you can also apply styles to members of a dimension in a database. Applying styles to dimensions makes it easy to view the various dimension members in Essbase Spreadsheet Add-in.

To apply styles to dimensions:

1. Select Essbase > Options and select the Style tab.
2. In the Dimensions group box, select Year.
3. Select the Cell Border check box to create a border around each cell that contains a member from the selected dimension.
4. From the Background Color list box, select Yellow.

   Figure 48. Selecting a Background Color from the Style Tab
   
   
   

5. Click the Format button that is to the right of the Dimensions list. Essbase displays the Font dialog box.
6. From the Font style list box, select Bold, and then click OK.

   Note:

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

7. From the list of dimensions, select the Measures dimension and, from the Background Color list box, select Fuschia.
8. From the list of dimensions, select Product, and clear the Cell Border check box.
9. From the Background Color list box, select Aqua.
10. Scroll down the list of dimensions, and select Market.
11. Select the Cell Border check box, and click the Format button that is to the right of the Dimensions list.

    When the Font dialog box is displayed, from the Font style list box select Italic, and then click OK.

12. From the list of dimensions, select Scenario, and from the Background Color list box, select Red.
13. Click the Format button, and from the Color list box, select White.
14. Click OK twice to return to the worksheet.

    Note:

    When you define styles, your choices are saved to the Windows Registry on your local machine. You can define one set of styles per database.

15. In cell D1, drill down (double-click) on Market.
16. Press and hold the Alt key and drill down (double-click) on Scenario (in cell E1).
17. Select Essbase > Retrieve to refresh the worksheet.

    Essbase redisplays the worksheet and implements the newly defined styles. For example, members of the Scenario dimension are displayed with a red background.

    Figure 49. Dimensions with Styles Applied
    
    
    

Applying Styles to Data Cells

You can apply styles to data cells, such as read-only cells, read/write cells, linked object cells, and Essbase Integration Server drill-through cells to distinguish them from other cells in the worksheet. The Sample Basic database that you are using for this tutorial does not contain data cells with any of these characteristics. In the advanced tutorial presented in "An Advanced Essbase Tutorial", you attach a linked reporting object to a data cell and apply a style to the cell.

Note:

You cannot perform the actions in light-shaded boxes.

Determining the Precedence of Overlapping Styles

The only way you can apply a background color to data is to define a style for dimensions. If dimension styles are defined, and the Use Styles setting is turned on, members of a dimension always have the background color defined for their dimension.

The text styles that you can apply to members, dimensions, and data cells have a hierarchy that determines which characteristics are applied. Member styles are at the top of that hierarchy. Thus, member styles are always applied (as long as styles are turned on). Note that in Figure Figure 49, the Qtr1 label in cell B6 is in bold, navy font, and has a yellow background. The navy font comes from the style defined for parent members, and the yellow background comes from the style defined for Year.

Essbase uses the following order of precedence when applying multiple text styles:

• Linked object cells
• Integration Server Drill-Through cells
• Read-only cells
• Read/write cells
• Parent member cells
• Child member cells
• Shared member cells
• Cells containing formulas
• Dynamic calculation member cells
• Attribute cells
• Dimension cells

If you want to see a child member style, make sure that the parent member style is turned off. If you want to see a shared member style, make sure that both parent and child member styles are turned off.

Removing Styles

Styles can be very helpful tools for keeping track of data in Essbase Spreadsheet Add-in. Applying styles, however, involves additional processing time during a retrieval request. This additional processing has a slight impact on the speed of Essbase retrievals.

If you do not want to apply styles to the worksheet view, you can clear them. You can also turn off styles so they are not displayed when you refresh the view (by selecting Essbase > Retrieve, for example). So that the worksheet matches the illustrations presented in the following tasks, do not remove styles if you are stepping through the tutorial.

Note:

If you have styles applied to the worksheet and you execute the FlashBack command, these styles are temporarily removed from the current view. The styles are re-applied whenever you initiate a retrieval. You cannot perform the actions in the light-shaded boxes.

Note:

If you turn styles off without clearing them from the worksheet, the styles remain in the current worksheet view when you refresh the view. The styles remain to avoid removing any styles that you may apply to individual cells using native worksheet formatting options.

Displaying Aliases for Member Names

Aliases are alternate names for database members. You can create reports that use the database member name, which is often a stock number or a product code, or an alias name, which can be more descriptive. Aliases are defined by the Essbase application designer. Each database can contain one or more alias tables

. For example, members of Product in the Sample Basic database are defined as codes, such as 100 and 200. A descriptive alias for each member of Product, such as Colas and Root Beer, is defined in an alias table. In some cases, alias names may vary depending on the combination of other database members. For example, a Product member may have a different alias for each market in which it is sold. For more information, see the Essbase Spreadsheet Add-in online help or the Database Administrator's Guide.

To display the alias of a member rather than its database name:

1. In cell C2, double-click the secondary mouse button to drill up on Actual.
2. Press and hold the Alt key and drill down (double-click) on Product (in cell D1).
3. Select Essbase > Options, and select the Display tab.
4. In the Aliases option group, select the Use Aliases check box to display member aliases.
5. Select Default from the Alias list box.

   Figure 50. Enabling Aliases in the Essbase Options Display Tab
   
   
   

6. Click OK.
7. Select Essbase > Retrieve to refresh the worksheet and display the alias names.

   Essbase changes the Product codes (100, 200, and so forth) to their predefined aliases (Colas, Root Beer, Cream Soda, and so forth). In the Sample Basic database, Product is the only dimension with predefined aliases.

   Figure 51. Result of Displaying Aliases
   
   
   

   Notice that Essbase is still displaying the styles that you created and applied in the previous sections.

Displaying Both Member Names and Aliases

In addition to displaying aliases for database members, you can also tell Essbase to display both aliases and database member names in Essbase Spreadsheet Add-in.

To display the name and alias of a member:

1. In cell B8, double-click the secondary mouse button to drill up on Qtr2.
2. In cell C2, select Colas, and then select Essbase > Pivot.
3. In cell C3. select Year, and then select Essbase > Pivot.
4. Select Essbase > Options, and select the Display tab.
5. In the Aliases option group, select the check box for Use Both Member Names and Aliases for Row Dimensions.

   Be sure that Use Aliases is already checked.

6. Click OK to return to the worksheet, and select Essbase > Retrieve.

   Essbase displays both member names and their aliases for row dimensions. Because the only row dimension in this example that has preassigned aliases is Product, only the Product members display their aliases. Region members simply repeat the member name instead of displaying an alias.

   Figure 52. Result of Displaying Both Member Names and Aliases
   
   
   

Repeating Member Labels

By default, Essbase displays member labels only once for each nested row and column group. If you are connected to a large database when using Essbase Spreadsheet Add-in, you may have to scroll down or across the worksheet to see additional data rows and columns. In some cases, as you scroll down or across, member labels may disappear from view. Essbase provides a feature for repeating member labels in each row or column cell that represents a data point so that you can always see a member label in the worksheet view.

To repeat member labels down and across the worksheet:

1. Select Essbase > Options, and select the Display tab.
2. In the Aliases option group, clear the Use Both Member Names and Aliases for Row Dimensions check box.
3. In the Cells option group, select the Repeat Member Labels check box, and then click OK.

   Figure 53. Enabling the Repeat Member Labels Option
   
   
   

4. In cell E1, drill down (double-click) on Year.

   Essbase displays a member label in every column and row cell. For the Sample Basic database that you are using for this tutorial, repeating member labels is probably not necessary because the database is relatively small. This feature is particularly helpful for keeping track of member labels when scrolling through large worksheets.

   Figure 54. Result of Repeating Member Labels
   
   
   

   Note:

   Even if you clear the Repeat Member Labels check box in the Essbase Options dialog box, Essbase retains the repeated member labels in the worksheet view. To remove the repeated labels, you need to do one of the following:

   • Clear the check box and open a new worksheet
   • Clear the check box and pivot the row group to a column group and then pivot it back to a row group (or vice versa)
   • Select Essbase > FlashBack and clear the check box
5. Before returning to the tutorial, complete the following actions:
   1. Select Essbase > Options, and select the Display tab.
   2. In the Cells option group, clear the Repeat Member Labels check box, and then click OK.
   3. Select File > Close to close the worksheet. You do not need to save the worksheet.