● “Sorting Data” on page 99
● “Retrieving Data into Asymmetric Reports” on page 101
● “Drilling Down to a Sample of Members” on page 103
● “Working with Formatted Worksheets” on page 104
● “Preserving Formulas When Retrieving Data” on page 109
● “Retrieving a Range of Data” on page 112
● “Retrieving Data by Using a Function” on page 114
● “Retrieving Dynamic Calculation Members” on page 117
● “Specifying the Latest Time Period for Dynamic Time Series” on page 119
● “Using Free-Form Reporting to Retrieve Data” on page 122
● “Retrieving Data Using Visual Explorer” on page 130
Remember that you can perform common data retrieval tasks in any of the following ways:
● Selecting commands from the Essbase menu on the spreadsheet application menu bar
● Clicking the appropriate buttons on the Essbase toolbar
● Double-clicking the primary or the right-mouse button in the appropriate cell (for Retrieve, Zoom In, and Zoom Out commands only, and the Linked Objects command, if you have enabled that option).
Filtering Data
Despite the ease and speed with which you can navigate through large Essbase databases, it is not practical to use the capabilities of the spreadsheet application to filter and sort very large databases; however, Essbase includes powerful data filtering and sorting capabilities.
In Chapter 3, you learned to use Essbase Query Designer to define a dimensional layout and to select members to view. Essbase Query Designer also provides a powerful tool to define conditional retrievals.
Note:
If you skipped the tutorial in Chapter 3, follow the steps in “Creating Queries Using Essbase Query Designer” on page 67 to create and save the Basic1 query.
➤ To become familiar with the capabilities of Essbase Query Designer, work with the query, Basic1, that you saved in Chapter 3, and perform the following steps:
1 Select Essbase > Query Designer.
The query information panel of Essbase Query Designer is displayed.
2 In the navigation panel, select [Book1]Sheet1.
3 Right-click and select Open Query.
The Open Query dialog box is displayed.
4 From the location that you specified in Chapter 3, select the Basic1 file.
5 Click OK.
The member selection, displayed in the properties panel, remains unaltered from the last Essbase Query Designer session.
6 From the navigation panel, select Data Filtering.
Essbase displays the data filter settings in the data filter panel, as shown in Figure 88. The filter controls the number of data rows that are retrieved. The number is based on the column criteria that you define. You can define data filtering criteria on data values that reside in one or more columns of the view.
Figure 88 Data Filter Panel
The data filtering panel contains the following items:
● A check box for ranking a specified number of top rows or a specified number of bottom rows of data.
You can select the highest or lowest rows. Your selection is based on previously selected row dimension members. When using the top or bottom criterion, you specify the number of rows, such as “top 10.” The default is the top 25 rows.
Performing Advanced Retrieval Tasks 95
● A “Dimension being ranked” drop-down list box to specify the dimension to which ranking should be applied.
● A “Column used for ranking” drop-down list box to specify the data column on which data values are based.
● A “Data Restrictions” list box to specify standard data comparison operations, such as greater than, less than, and equal to.
You can apply the comparison operator to data values, including negative data values, in one or more data columns; you can even apply criteria to compare values between two columns.
● Buttons for OR and AND operators.
If you define more than one criterion for a column, you can use these operators to link the criteria.
7 Select the Top check box, and enter a value of 30 in the Rows text box.
When you apply the query, Essbase retrieves the top thirty rows of the dimension.
8 From the Dimension being ranked drop-down list, select Product.
Product is the dimension to which ranking should be applied.
9 From the Column used for ranking drop-down list, select Qtr1, Actual.
Qtr1, Actual is the column on which data values are based.
10 In the navigation panel, select the Data Filtering icon. Right-click and select Apply Query.
Your query results should look like Figure 89.
Figure 89 Data Filtering Query Results
You can further filter the data output by specifying data comparison operations in the Data Restrictions list box.
11 In the navigation panel, select the Data Filtering icon.
The data filters that you specified are displayed in the properties panel.
12 In the Data Restrictions list box, double-click.
The data restriction settings are displayed in the properties panel.
13 Select the A value of option and type 500 in the value text box.
Observe that the “is” option in the Data drop-down list box changed to =.
14 Click the down arrow of the Data drop-down list box and select <=.
15 Click the down arrow of the Column used for filter drop-down list box and select Qtr1, Actual.
16 In the navigation panel, select the Data Filtering icon, right-click, and select Apply Query.
Notice that the query results now reflect only Actual and Budget data that are less than or equal to 500.
17 In the navigation panel, select the Data Filtering icon to display the data filter setting in the properties panel.
18 In the Data Restrictions list box, select Qtr1, Actual <= 500, right-click, and select New Data Restriction.
19 In the Data drop-down list box, click the down arrow and select <.
20 Select the option, the data values in, and from the drop-down list, select Qtr2, Actual.
21 Under Combined With Other Restrictions, select the Or option.
22 In the navigation panel, click the Data Filtering icon to access the data filter panel.
23 In the Data Restrictions list box, double-click to create a new data restriction.
24 In the Data drop-down list box, click the down arrow and select is not.
25 Click the button for the #Missing Value option.
This option instructs Essbase to discard data that have #Missing values.
26 In the Column used for filter drop-down list, select Qtr1, Actual.
27 Under Combined With Other Restrictions, select the And option.
28 In the navigation panel, click the Data Filtering icon to access the data filter panel.
The data restrictions should be displayed as shown in Figure 90:
Performing Advanced Retrieval Tasks 97
Figure 90 Data Filtering
29 Select the Data Filtering icon, right-click, and select Apply Query.
Essbase retrieves data for all the quarters. Notice that the retrieved data for Qtr1, Actual is less than or equal to 500 or is less than Qtr2, Actual. The results should be displayed as shown in Figure 91:
Figure 91 Data Filtering Results
If you wanted to delete all data restrictions, select the Data Filtering icon in the navigation panel, right-click, and select Delete All Data Restrictions. Alternatively, select any data restriction in the Data Restriction box, right-click, and select Delete All Data Restrictions.
To delete a particular data restriction, select the data restriction in the query outline, right-click, and select Delete Data Restriction. Alternatively, select the data restriction in the Data Restriction box, right-click, and select Delete Data Restriction.