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 2, you learned to use Query Designer to define a dimensional layout and to select members to view. Query Designer also provides a powerful tool to define conditional retrievals. Note:
If you skipped the tutorial in Chapter 2, follow the steps in “Creating Queries Using Query Designer” on page 69 to create and save the Basic1 query.
➤ To become familiar with the capabilities of Query Designer, work with the query, Basic1, that you saved in Chapter 2, and perform these steps:
1 Select Essbase, then Query Designer.
The query information panel of 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 2, select the Basic1 file.
5 Click OK.
The member selection, displayed in the properties panel, remains unaltered from the last Query Designer session.
6 From the navigation panel, select Data Filtering.
Essbase displays the data filter settings in the data filter panel (see Figure 84). 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
Figure 84 Data Filter Panel
The data filtering panel contains these options:
● Rank—Ranks 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.
● Dimension being ranked—Specifies the dimension to which ranking should be applied. ● Column used for ranking—Specifies the data column on which data values are based. ● Data Restrictions—Specifies 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.
● 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 Top, and in Rows, enter a value of 30 .
When you apply the query, Essbase retrieves the top thirty rows of the dimension.
8 From Dimension being ranked, select Product.
Product is the dimension to which ranking should be applied.
9 From Column used for ranking, select Qtr1, Actual.
Qtr1, Actual is the column on which data values are based.
10 In the navigation panel, select Data Filtering. Right-click and select Apply Query. Your query results should look like Figure 85.
Figure 85 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 Data Filtering.
The data filters that you specified are displayed in the properties panel.
12 In Data Restrictions, double-click.
The data restriction settings are displayed in the properties panel.
13 Select A value of, and enter 500 in value.
Observe that the “is” option in Data is changed to =.
14 Click the down arrow of Data, and select <=.
15 Click the down arrow of Column used for filter, and select Qtr1, Actual.
17 In the navigation panel, select Data Filtering to display the data filter setting in the properties panel.
18 In Data Restrictions, select Qtr1, Actual <= 500, right-click, and select New Data Restriction.
19 In Data, click the down arrow and select <.
20 Select the data values in, and select Qtr2, Actual.
21 Under Combined With Other Restrictions, select the Or option.
22 In the navigation panel, click Data Filtering to access the data filter panel.
23 In Data Restrictions, double-click to create a data restriction.
24 In Data, click the down arrow and select is not.
25 Click #Missing Value.
This option instructs Essbase to discard data that have #Missing values.
26 In Column used for filter, select Qtr1, Actual.
27 Under Combined With Other Restrictions, select the And option.
28 In the navigation panel, click Data Filtering to access the data filter panel. The data restrictions should be displayed as shown in Figure 86:
Figure 86 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 87:
Figure 87 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.