➤ To create a query using Essbase Query Designer:
1 Select Essbase > Query Designer.
Essbase displays the Essbase Query Designer welcome panel, as shown in Figure 54.
Figure 54 Essbase Query Designer Displaying Welcome Panel
2 In the navigation panel, select [Book1]Sheet1, right-click, and select New > Query.
The layout panel of Essbase Query Designer is displayed, as shown in Figure 55.
Figure 55 Essbase Query Designer Displaying Layout Panel
3 Define the worksheet layout by dragging the dimension tiles in the properties panel as follows:
a. Drag Market and Product to the Row location.
b. Drag Measures to the Page location.
c. Drag Scenario below Year (in the Column location).
Figure 56 shows the results of dragging the dimension tiles in the properties panel.
Creating Queries Using Essbase Query Designer 69
Figure 56 Changing the Worksheet Layout
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.
Note:
You can select only one member from the dimension in the Page location.
5 In the Members list box, 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 box.
Figure 57 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 Select members of the Year dimension as follows:
a. In the navigation panel, click the Year icon. Alternatively, double-click the Year tile in the layout panel.
The member select properties panel for the Year dimension is displayed.
b. In the Members list box, select Qtr1, right-click, and select Add to Selection Rules.
c. 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.
The result is shown in Figure 58.
Creating Queries Using Essbase Query Designer 71
Figure 58 Adding Members to the Selection Rules
7 Select members of the Scenario dimension as follows:
a. 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.
b. Select Actual, right-click, and select Add to Selection Rules.
Actual is added to the Selection Rules list box.
c. In the same manner, add Budget to the Selection Rules list box.
8 Select members of the Product dimension as follows:
a. 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.
b. Select product code 100, right-click, and select Add to Selection Rules.
c. Repeat the process for product codes 200, 300, and 400.
d. 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, as shown in Figure 59
e. 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
f. 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.
Essbase displays the Member Selection Preview dialog box, as shown in Figure 60, with the selected product dimensions listed.
Figure 60 Selected Members of Product Dimension
g. Click Close to close the Member Selection Preview dialog box.
9 Select members of the Market dimension as follows:
a. 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.
b. In the Members list box, select East, right-click, and select View by > Generation.
Creating Queries Using Essbase Query Designer 73
c. 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.
d. 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.
As shown in Figure 61, Essbase displays East, West, South, and Central, which are members of generation two in Market, in the Member Selection Preview dialog box.
Figure 61 Generation Name Selection
e. Click Close to close the Member Selection Preview dialog box.
You have now defined a basic query. The outline of the query is displayed in the navigation panel.
The next topic describes how to save this query.