OLAP Spreadsheet Add-in User's Guide for Excel

Getting Acquainted with Hyperion Essbase Spreadsheet Add-in

This section provides the following information to help you get acquainted with Hyperion Essbase Spreadsheet Add-in:

Adding Hyperion Essbase Spreadsheet Add-in
Starting Hyperion Essbase Spreadsheet Add-in
Installing the Hyperion Essbase Toolbar
Using the Hyperion Essbase Toolbar
Accessing Online Help
Enabling Mouse Actions

Adding Hyperion Essbase Spreadsheet Add-in

If you manually update your environment settings or if you have removed Hyperion Essbase Spreadsheet Add-in from the MicrosoftExcel environment, you must use the Excel facility to add Hyperion Essbase Spreadsheet Add-in into Excel. The facility adds the Hyperion Essbase menu to the Excel menu bar and points Excel to the Hyperion Essbase Spreadsheet Add-in file in the \essbase\bin directory. This file is on the hard drive of your PC, if that is where you installed Hyperion Essbase Spreadsheet Add-in, or on the network drive if that is where you set up your PC operating environment to run Hyperion Essbase Spreadsheet Add-in.
Note: essexcln.xll is the add-in file for Excel 97 and Excel 2000. Adding Hyperion Essbase Spreadsheet Add-in into Excel 97 and Excel 2000 modifies the Windows 95, Windows 98, or Windows NT Registry. The .ini file and Registry point to where the Hyperion Essbase Spreadsheet Add-in file is installed.

To add Hyperion Essbase Spreadsheet Add-in to Excel:

In Excel, select Tools > Add-Ins.
Excel displays the Add-Ins dialog box.
Click the Browse button.
Excel displays the Browse dialog box.
Select the file essexcln.xll in the bin directory where you installed Hyperion Essbase Spreadsheet Add-in on your PC hard drive (if you installed Hyperion Essbase Spreadsheet Add-in there) or in the bin directory where the Hyperion Essbase system administrator installed Hyperion Essbase Spreadsheet Add-in on a network drive (if you set up your PC operating environment to run Hyperion Essbase Spreadsheet Add-in there).
Click OK twice to close the dialog boxes and start Hyperion Essbase Spreadsheet Add-in.
The Hyperion Essbase startup screen is displayed.
If you do not see the Hyperion Essbase startup screen or if your system does not list the Hyperion Essbase Spreadsheet Add-In menu options, see the Hyperion Essbase Installation Guide for troubleshooting information.

Starting Hyperion Essbase Spreadsheet Add-in

Because Hyperion Essbase Spreadsheet Add-in software for Excel

is implemented as an Excel add-in, you must start Excel from Windows to use Hyperion Essbase Spreadsheet Add-in software.

You must open a worksheet before attempting to connect to the Hyperion Essbase server. An attempt to connect to the Hyperion Essbase server without opening a worksheet results in an error message.

To begin a Hyperion Essbase session:

Start Excel.
After the Excel startup screen disappears, the Hyperion Essbase startup screen is displayed.
The Hyperion Essbase menu should be displayed in the Excel menu bar. If you do not see the Hyperion Essbase menu, you may need to use the Excel add-in facility to add Hyperion Essbase Spreadsheet Add-in into Excel. For more information, see Adding Hyperion Essbase Spreadsheet Add-in.
The Hyperion Essbase toolbar should also be visible. If you do not see the Hyperion Essbase toolbar, see Installing the Hyperion Essbase Toolbar and Adding Hyperion Essbase Spreadsheet Add-in.
From the Excel menu bar, select Essbase to open the menu.

Figure 5. The Essbase Menu

Note:

If the Excel status bar is not displayed, you can display it by selecting View > Status Bar.

Installing the Hyperion Essbase Toolbar

Hyperion Essbase Spreadsheet Add-in provides a convenient toolbar that displays buttons for accessing most of the common Hyperion Essbase commands without having to open the Essbase menu. Prior to using the Hyperion Essbase toolbar, you must install the toolbar by opening an Excel file that is provided as part of the default Hyperion Essbase installation. For more information on using the toolbar, see Using the Hyperion Essbase Toolbar.

To install the Hyperion Essbase toolbar:

Start Excel.
Select File > Open.

From the \essbase\client\sampledirectory, open the esstoolb.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 Hyperion Essbase system administrator for more information.

A blank worksheet opens.

Figure 6. esstoolb.xls File

Note: If you are using Excel 97, two dialog boxes may be displayed when you open the esstoolb.xls file. If so, click the Enable Macros button on the first dialog box, and click OK on the second dialog box.

Select File > Close to close the esstoolb.xls file. You do not need to modify or save the file.

You should not have to perform the toolbar installation procedure again unless you delete the Hyperion Essbase toolbar from the Toolbars dialog box in Excel. If you delete the toolbar, perform the installation procedure again.
Note: If you have toolbars turned off in Excel, you do not see the Hyperion Essbase toolbar immediately. You must first enable toolbars. For more information, see Using the Hyperion Essbase Toolbar.

Using the Hyperion Essbase Toolbar

Hyperion Essbase Spreadsheet Add-in for Excel features a convenient toolbar that displays buttons for accessing most of the common Hyperion Essbase commands without having to open the Essbase menu. You can view a short description of a button in a pop-up on the toolbar by moving your cursor over the button.
Note: Before you can view the Hyperion Essbase toolbar, you must install it. See Installing the Hyperion Essbase Toolbar.

To view the Hyperion Essbase toolbar, select View > Toolbars > Essbase from the Excel menu bar. A check box must be displayed next to Essbase in the menu.
Note: If you click the Essbase check box and click Delete, you must reinstall the toolbar. See Installing the Hyperion Essbase Toolbar for instructions.

The toolbar provides the following buttons:

Table 1. Hyperion Essbase Toolbar Fm Variable:Table Continuation

Button Purpose Description

Connect Enables you to connect to a Hyperion Essbase server. When you click the Connect button on the toolbar, Hyperion Essbase displays the Hyperion Essbase System Login dialog box.

Navigate with or without data Toggles the Navigate Without Data feature, which tells Hyperion Essbase to retrieve or not to retrieve data when you perform navigational operations, such as pivot, zoom in, zoom out, keep only, and remove only. This button serves the same function as the Navigate Without Data check box in the Essbase Options dialog box (Global tab).

Retrieve Retrieves data into the active worksheet. A retrieve request places data at the beginning of the active worksheet. When you click the Retrieve button, if you are not already connected to a Hyperion Essbase server, the Hyperion EssbaseSystem Login dialog box is displayed. If you have mouse actions enabled, you can retrieve data by double-clicking the primary mouse button in any empty cell in the worksheet. To enable double-click support, select Essbase > Options, select the Global tab, and select the Enable Double-Clicking option. For more information, see Enabling Mouse Actions.

Keep only Retains only the selected member (the active cell) or member range in the worksheet. When you click the Keep Only button, all unselected members are removed from the worksheet.

Remove only Removes the selected member (the active cell) or member range from the worksheet. When you click the Remove Only button, all unselected members are retained in the worksheet.

Zoom in Retrieves and expands data from the Hyperion Essbase server according to the options specified in the Essbase Options dialog box. If you have mouse actions enabled, you can drill down on data by double-clicking the primary mouse button in the cell that contains the member you want to expand. To enable double-click support, select Essbase > Options, select the Global tab, and select the Enable Double-Clicking option. For more information, see Enabling Mouse Actions.

Zoom out Collapses the view according to the options specified in the Essbase Options dialog box in the cell that contains the member that you want to collapse. To enable double-click support for drilling up, select Essbase > Options, select the Global tab, and select the Enable Double-Clicking option.

Open Hyperion Essbase Query Designer Opens the Hyperion Essbase Query Designer, which makes it easy to define a page orientation for dimensions and selected database members. You can also save queries for later use. Hyperion Essbase Query Designer is a new feature designed to create report queries, view attributes, and perform calculations based on attributes.

FlashBack Restores the previous worksheet view.

Set options Enables you to set display, zoom, mode, style, and global options for the active worksheet to customize the behavior of Hyperion Essbase Spreadsheet Add-in software. When you click the Options button, Hyperion Essbase displays the Essbase Options dialog box.

Select members Enables you to select members from the multidimensional database outline. When you click the Member Selection button, Hyperion Essbase displays the Essbase Member Selection dialog box.

Attach linked objects Enables you to attach comments or files to data cells. When you click the linked objects button, Hyperion Essbase displays the linked objects browser dialog box.

Button	Purpose	Description
	Connect	Enables you to connect to a Hyperion Essbase server. When you click the Connect button on the toolbar, Hyperion Essbase displays the Hyperion Essbase System Login dialog box.
	Navigate with or without data	Toggles the Navigate Without Data feature, which tells Hyperion Essbase to retrieve or not to retrieve data when you perform navigational operations, such as pivot, zoom in, zoom out, keep only, and remove only. This button serves the same function as the Navigate Without Data check box in the Essbase Options dialog box (Global tab).
	Retrieve	Retrieves data into the active worksheet. A retrieve request places data at the beginning of the active worksheet. When you click the Retrieve button, if you are not already connected to a Hyperion Essbase server, the Hyperion EssbaseSystem Login dialog box is displayed. If you have mouse actions enabled, you can retrieve data by double-clicking the primary mouse button in any empty cell in the worksheet. To enable double-click support, select Essbase > Options, select the Global tab, and select the Enable Double-Clicking option. For more information, see Enabling Mouse Actions.
	Keep only	Retains only the selected member (the active cell) or member range in the worksheet. When you click the Keep Only button, all unselected members are removed from the worksheet.
	Remove only	Removes the selected member (the active cell) or member range from the worksheet. When you click the Remove Only button, all unselected members are retained in the worksheet.
	Zoom in	Retrieves and expands data from the Hyperion Essbase server according to the options specified in the Essbase Options dialog box. If you have mouse actions enabled, you can drill down on data by double-clicking the primary mouse button in the cell that contains the member you want to expand. To enable double-click support, select Essbase > Options, select the Global tab, and select the Enable Double-Clicking option. For more information, see Enabling Mouse Actions.
	Zoom out	Collapses the view according to the options specified in the Essbase Options dialog box in the cell that contains the member that you want to collapse. To enable double-click support for drilling up, select Essbase > Options, select the Global tab, and select the Enable Double-Clicking option.
	Open Hyperion Essbase Query Designer	Opens the Hyperion Essbase Query Designer, which makes it easy to define a page orientation for dimensions and selected database members. You can also save queries for later use. Hyperion Essbase Query Designer is a new feature designed to create report queries, view attributes, and perform calculations based on attributes.
	FlashBack	Restores the previous worksheet view.
	Set options	Enables you to set display, zoom, mode, style, and global options for the active worksheet to customize the behavior of Hyperion Essbase Spreadsheet Add-in software. When you click the Options button, Hyperion Essbase displays the Essbase Options dialog box.
	Select members	Enables you to select members from the multidimensional database outline. When you click the Member Selection button, Hyperion Essbase displays the Essbase Member Selection dialog box.
	Attach linked objects	Enables you to attach comments or files to data cells. When you click the linked objects button, Hyperion Essbase displays the linked objects browser dialog box.

Accessing Online Help

Hyperion Essbase Spreadsheet Add-in includes a context-sensitive online help system. You access the Hyperion Essbase Spreadsheet Add-in online help in one of three ways. Which way you choose depends on the type of information that you need,

Access the entire online help system for browsing or searching for information. To access the entire help system, select Help > Essbase Help. After you access online help, you can browse or search through the system to view general information on Hyperion Essbase Spreadsheet Add-in, Essbase command descriptions, procedural information for completing tasks, Spreadsheet Toolkit macros, and Visual Basic for Applications (VBA) function descriptions.

Access context-specific information from dialog boxes in Hyperion Essbase Spreadsheet Add-in. Each dialog box in Hyperion Essbase Spreadsheet Add-in features a Help button that accesses online help topics that are specific to the particular dialog box. The buttons enable you to find the information you need without having to search through the entire help system.

Figure 7. Excel Help Button

Note: The Hyperion Essbase Spreadsheet Add-in online help also provides a comprehensive section on Hyperion Essbase Spreadsheet Toolkit, which enables you to customize and automate your use of Hyperion Essbase by using Excel macros and VBA functions.

Access information on a specific Hyperion Essbase Query Designer function. Access What's This? Help in the Hyperion Essbase Query Designer dialog box, and then click an item in the dialog box to access information on that item.

Figure 8. EQD Help Button

Enabling Mouse Actions

The following terms are used throughout this guide to describe mouse operations:

Primary mouse button and secondary mouse button describe the buttons on a two- or three-button mouse. Usually, right-handed users configure the left mouse button as the primary button and the right mouse button as the secondary mouse button. The primary mouse button is the one that you use to start Windows applications, such as Excel; the secondary mouse button is used for auxiliary operations. The term click refers to use of the primary mouse button. The term right-click refers to use of the secondary mouse button.
Select chooses the object that is under the cursor when you press and release the primary mouse button. You can select a worksheet cell, for example, by moving the cursor to the cell and pressing and releasing the primary mouse button.
Click (that is, both click and right-click) describes a quick press-and-release action on a command object. You can click a button, for example, to execute a command.
Double-click describes two quick press-and-release actions that are executed in rapid succession. You can double-click an application icon, for example, to start a Windows application.

Drag describes a press, hold, and move action. You place the cursor on an object, press a mouse button, hold the mouse button and move the object, and release the mouse button when you reach your goal. For example, you can highlight a range of cells in an Excel worksheet by dragging the cursor over the cells.

Note:

Hyperion Essbase uses a drag operation called a pivot. A pivot requires use of the secondary mouse button. To execute a pivot, you must press and hold the secondary, rather than the primary, mouse button while dragging the selection.

Hyperion Essbase offers enhanced mouse actions in Excel. You can use the mouse to do any of the following:

Retrieve data
Drill down and drill up on database members
Pivot (move or transpose) data rows and columns
Access linked reporting objects
Access linked partitions

To enable double-clicking to retrieve, drill down, and drill up on Hyperion Essbase data:

Select Essbase > Options.
In the Essbase Options dialog box, select the Global tab.
Select the Enable Double-Clicking check box.
When the Enable Double-Clicking check box is selected, you can retrieve and drill down to more detailed data (primary mouse button) and drill up to less detailed data (secondary mouse button). When the double-clicking option is enabled, the Excel in-cell editing feature is overridden.
Figure 9. Essbase Options Dialog Box--Global Tab
Click OK to return to the worksheet.

For information about setting the primary mouse button to display the Linked Objects Browser dialog box when you double-click a data cell, see the Hyperion Essbase Spreadsheet Add-in online help.

[ Top of Page | Previous Page | Next Page ]