Spreadsheet Add-in User's Guide for Excel

Creating Queries Using Essbase Query Designer

So far, you have seen how easy it is to retrieve data and navigate through Essbase Spreadsheet Add-in in an ad hoc fashion. Essbase also provides a query designer so that you can define a database query for retrieving dimensions and database members into the worksheet. Essbase Query Designer (EQD) replaces Retrieval Wizard, and is used to define queries in previous versions of Essbase Spreadsheet Add-in.

Before Hyperion Essbase actually retrieves data, Essbase Query Designer provides a series of panels so you can request the data that you want to view in the worksheet. It is particularly helpful when you know exactly which data you want to retrieve from the server. In addition, you can save a query and use it again.

The Essbase Query Designer window (Figure "Creating Queries") consists of the following panels:

• The navigation panel provides access to the various features in Essbase Query Designer. You can display all the dimensions used in a particular query and access the various properties of each dimension member.
• The hint panel provides a brief description of the feature that is selected from the navigation panel.
• The properties panel provides access to the following functions:
  • Layout: Design the layout of the spreadsheet report. To change the default layout, select a dimension tile and drag it to one of the other dimension boxes. To access the member select panel and to define a member for a query, double-click a dimension tile.
  • Member Select: Select members that you want to display in the rows of the spreadsheet report. To select a member, right-click the member, and select Add to Selection Rules. You can also double-click a member to add it to the selection rules.
  • Member Filter: Filter the member selection by attribute, generation name, level name, pattern string, or UDA.
  • Data Filter: Retrieve rows of data. The retrieval is based on the ranking of the rows within certain columns. Use this panel to access the data restriction panel.
  • Data Restriction: Filter data by comparing it to a fixed data value (including a negative value), a set of data values, or #Missing data values.
  • Data Sort: Sort rows in ascending or descending order. The sort is based on column data values.
  • Messages and Confirmation: Turn on and turn off certain messages from Essbase Query Designer.
  • Help: Access documentation about Essbase Query Designer.

  Note:

  Excel query functionality is not supported in Spreadsheet Add-in. Use the Essbase Query Designer to define database queries.

Creating and Changing Queries

To access any of the Hyperion Essbase Query Designer panels, select the appropriate feature listed in the navigation panel.

As you create a query or make changes to an existing query, the changes are reflected in the navigation panel. To view a dimension or a member of an open query, click on the specific dimension or member in the query outline that is displayed in the navigation panel. Selected members are displayed in the member selection panel on the right.

You can also revise an existing query in the member selection panel. For example, you can delete a member or add a member to the query by selecting a member in the navigation panel and making the appropriate changes in the properties panel.

Note:

Files created using the obsolete Retrieval Wizard feature can be opened in Essbase Query Designer. However, if a query contains more than two member filters per selection rule or more than two data restrictions, the member filters may be out of order. To ensure correct results, rearrange the member filters in the navigation panel, if necessary.

For complete information on Essbase Query Designer options, see the Essbase Spreadsheet Add-in online help.

Caution:
Manipulation of worksheets in VBA such as naming worksheets or moving worksheets may not work when EQD is running.

Creating Queries

To create a query using Essbase Query Designer:

1. Select Essbase > Query Designer.

   Essbase displays the Essbase Query Designer welcome panel.

   Figure 55. Essbase Query Designer Displaying Welcome Panel
   
   
   

2. To create a new query, select [Book1]Sheet1, right-click, and select New > Query.

   Hyperion Essbase displays the layout panel of Hyperion Essbase Query Designer.

3. Define the worksheet layout by dragging the dimension tiles as follows:
   1. Drag Market and Product to the Row location.
   2. Drag Measures to the Page location.
   3. Drag Scenario below Year (in the Column location).

      Figure 56. Changing the Worksheet Layout
      
      
      

4. To select the Measures dimension in the navigation panel, select the Measures icon. Alternatively, double-click the Measures tile in the layout panel.

   Essbase displays the member select panel, where you can select a member from the Measures dimension.

   Note:

   You can select only one member from the dimension in the Page location.

5. Select Profit, right-click, and select Add to Selection Rules. Alternatively, double-click Profit to add it to the selection rules.

   Profit is displayed in the selection rules list.

   Figure 57. Hyperion Essbase Query Designer Displaying the Member Select Panel
   
   
   

   Note:

   In Essbase Query Designer, after you make your selections, you do not need to confirm them; for example, you do not have to click OK. If you do not select members from any given dimension, Essbase uses the top member of the dimension.

6. To select members of the Year dimension, complete the following actions:
   1. In the navigation panel, click the Year icon. Alternatively, double-click the Year tile in the layout panel.

      Essbase displays the member select panel for the Year dimension.

   2. Select Qtr1, right-click, and select Add to Selection Rules.
   3. Add Qtr2, Qtr3, and Qtr4 to the selection rules in the same manner. Because Year is in a Column location, you can select one or more members.

   Figure 58. Adding Members to the Selection Rules
   
   
   

7. To select members of the Scenario dimension, complete the following actions:
   1. In the navigation panel, select Scenario. Alternatively, double-click the Scenario tile in the layout panel.

      The members of the Scenario dimension are displayed in the member select panel.

   2. Select Actual, right-click, and select Add to Selection Rules.

      Actual is added to the Selection Rules box.

   3. In the same manner, add Budget to the Selection Rules box.
8. To select members of the Product dimension, complete the following actions:
   1. In the navigation panel, select Product. Alternatively, double-click the Product tile in the layout panel.

      The members of the Product dimension are displayed in the member select panel.

   2. Select product code 100, right-click, and select Add to Selection Rules.
   3. Repeat the process for product codes 200, 300, and 400.
   4. In the Selection Rules list box, select product code 100, right-click, and then, from the popup menu, choose Select > Children.

      This action selects all children of 100. Essbase displays All Children next to 100 in the Selection Rules list box.

   5. In the Selection Rules list box, select product code 400, right-click, and choose Select > Descendants.

      Essbase displays All Descendants next to 400 in the Selection Rules list box.

      Figure 59. Selecting Members of Product
      
      
      

   6. To view the list of all product codes that will be retrieved into the worksheet, select any of the entries in the Selection Rules list box (for example, 200), right-click, and select Preview.

      Hyperion Essbase displays the Member Selection Preview dialog box.

      Figure 60. Selected Members of Product Dimension
      
      
      

   7. Click Close to close the Member Selection Preview dialog box.
9. To select members of the Market dimension, complete the following actions:
   1. In the navigation panel, select Market. Alternatively, double-click the Market tile in the layout panel.

      The members of the Market dimension are displayed in the member select panel.

   2. In the Member list box, select East, right-click, and select View by > Generation.
   3. To pick the second generation of the Market dimension, in the Member list box, select Region, right-click, and select Add to Selection Rules. Alternatively, double-click Region to add it to the selection rules.

      Region is displayed in the Selection Rules list box.

   4. To view the list of members that will be retrieved into the worksheet, in the Selection Rules list box, select Region, right-click, and select Preview.

      Essbase displays East, West, South, and Central in the Member Selection Preview dialog box.

      Figure 61. Generation Name Selection
      
      
      

   5. Click Close to close the Member Selection Preview dialog box.

   You have now defined a basic Essbase query. The outline of the query is displayed in the navigation panel.

10. In the navigation panel, select [Book1]Sheet1, Query1\, and then right-click and select Save Query.

    Essbase displays the Essbase Query Designer Save As Query dialog box. You can save your query to the server or to your own client machine. To save to the server, you must have a security level of database designer or higher. Contact the Essbase system administrator for more information.

11. Select Client.

    Figure 62. Essbase Query Designer Save As Query Dialog Box
    
    
    

12. Click the File System button.

    Essbase displays the Save As dialog box.

    Figure 63. Save As Dialog Box
    
    
    

13. Select a location, in the File name text box, type Basic1 and then click Save.

    You will use the Basic1 query again in "An Advanced Essbase Tutorial"

    Figure 64. Hyperion Essbase Query Designer Displaying Query Information Panel
    
    
    

14. In the navigation panel, select [Book1]Sheet1, Basic1. then right-click, and select Apply Query.

    The result of the query is displayed in the worksheet.

    Figure 65. Results of a Essbase Query Designer Query
    
    
    

    Note:

    In the Display tab under Essbase > Options, if you select Use Styles and Use Sheet Options with Query Designer, the styles you selected for dimension members will be applied to the initial query results. If you do not select Use Sheet Options with Query Designer, even if you have selected Use Styles, they will not be applied to the initial query results. To apply styles, select Essbase > Retrieve. When Essbase returns the data to the worksheet, you are free to further investigate the data by performing Zoom, Keep Only, Remove Only, and Pivot operations.

Deleting Queries

You can delete a query only from the location where you saved that query. For example, if you save a query in the /essbase/client/sample directory, you can delete the query from within the sample directory. You cannot delete the query from within Hyperion Essbase Query Designer.

Viewing Messages and Confirmations

Hyperion Essbase Query Designer displays messages and confirmations about certain actions, such as moves and deletes, in the messages and confirmations panel.

To turn on or turn off messages and confirmations:

1. Select the Messages and Confirmations icon in the navigation panel.
2. To turn on (enable) a message, select the check box that is displayed next to that message.
3. To turn off (disable) a message, select the check box again (clear the check box).

   Figure 66. Messages and Confirmations Panel
   
   
   

Accessing Help

Access online help or the tutorial for Hyperion Essbase Query Designer by using the help panel. To access the help panel, in the navigation panel, select Help. For more information on a particular topic, click the Online Help button in the properties panel. To access the online tutorial, click the Tutorial button in the properties panel.

Figure 67. Essbase Query Designer Help Panel

Connecting to Multiple Databases from Essbase Query Designer

You can connect to several databases and create separate queries on each database from Essbase Query Designer .

To connect to multiple databases from Essbase Query Designer:

1. Logon to Essbase and connect to the server you want to access.
2. Select Essbase > Query Designer to open Essbase Query Designer.
3. Select [Book1]Sheet1, right-click, and select Connect.

   Essbase displays the Essbase System Login dialog box.

   Note:

   The book may be a number other than 1. For example, it may be [Book5], if four worksheets are already open.

4. Type your password, and click OK. Select Sample Basic, and click OK.
5. Select [Book1]Sheet2 (or Sheet3), right-click, and select Connect.

   Essbase displays the Essbase System Login dialog box.

6. Type your password, and click OK. Select Samppart Company, and click OK.

   Note:

   You are restricted to one connection per worksheet. The connection information is displayed in the query information panel of the Hyperion Essbase Query Designer only when you open an existing query or create a new query.

7. To create a new query based on Sample Basic, select <<c:[Book1]Sheet1, right click, and select New > Query.
8. To create a new query based on Samppart Company, select <<c:[Book1]Sheet2, right-click, and select New > Query.
9. To open an existing query, right click, and select Open Query.

You are now ready to proceed with the process of creating queries or opening existing queries.

Applying Worksheet Options to Essbase Query Designer Results

You can also apply any of the worksheet options you have previously set from the Essbase Options dialog box to the results of a query created in Essbase Query Designer.

To enable Essbase Query Designer to use your previously set worksheet options:

1. Select Essbase > Options.
2. In the Essbase Options dialog box, select the Display tab.
3. Select the Use Sheet Options with Query Designer check box, and select OK.
4. Select Essbase > Retrieve to refresh the worksheet.

   Essbase displays the results of the query you created in Essbase Query Designer and implements your previously set worksheet options. For example, note that aliases, instead of the numeric codes, are now displayed for the Product dimension.

   Figure 68. Results of Query with Options Applied
   
   
   

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