Lesson: Creating Queries

  To create a query using Query Designer:

1. Select Essbase, and then Query Designer.

   Essbase displays the Query Designer welcome panel.

2. In the navigation panel, select [Book1]Sheet1, right-click, and select New, and then Query.

   The layout panel of Query Designer is displayed.

3. Define the worksheet layout by dragging the dimension tiles in the properties panel 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).

   

4. In the navigation panel, select the Measures dimension by selecting the Measures icon.

   Alternatively, double-click the Measures tile in the layout panel.

   The member select properties panel, where you can select a member from the Measures dimension, is displayed.

5. In Members, select Profit, right-click, and select Add to Selection Rules.

   Profit is displayed in the Selection Rules list box.

6. Select members of the Year dimension as follows:

   1. In the navigation panel, click the Year icon.

      The member select properties panel for the Year dimension is displayed.

   2. In Members, 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.

   

7. Select members of the Scenario dimension as follows:

   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 properties panel.

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

      Actual is added to the Selection Rules list box.

   3. In the same manner, add Budget to Selection Rules.

8. Select members of the Product dimension as follows:

   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 properties 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 Selection Rules, select product code 100, right-click, and then, from the shortcut menu, choose Select, and then Children.

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

   5. In Selection Rules, select product code 400, right-click, and choose Select, and then Descendants.

      All Descendants is displayed next to 400 in the Selection Rules list box.

      

   6. To view the list of all product codes to be retrieved into the worksheet, select an entry in Selection Rules (for example, 200), right-click, and select Preview.

      The Member Selection Preview dialog box is displayed with the selected product dimensions listed.

   7. Click Close.

9. Select members of the Market dimension:

   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 properties panel.

   2. In Members, select East, right-click, and select View by, and then Generation.

   3. To pick the second generation of the Market dimension, in Member, 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 to be retrieved into the worksheet, in Selection Rules, select Region, right-click, and select Preview.

      East, West, South, and Central, which are members of generation two in Market, are displayed in the Member Selection Preview dialog box.

   5. Click Close, or press Alt+C.

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