Essbase Query Designer includes powerful data filtering and sorting capabilities. To become familiar with these capabilities, you use the Basic1 query that you created and saved in Basic Tutorial.
If you skipped the basic tutorial, follow the steps in Creating Queries Using Query Designer to create and save the Basic1 query. |
To filter and sort data:
From the location that you previously specified, select the Basic1 file.
The member selection, displayed in the properties panel, remains unaltered from the last Query Designer session.
From the navigation panel, select Data Filtering.
Essbase displays the data filter settings in the data filter panel. 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.
Select Top, and in Rows, enter a value of 30 .
When you apply the query, Essbase retrieves the top thirty rows of the dimension.
From Dimension being ranked, select Product.
Product is the dimension to which ranking should be applied.
In the navigation panel, select Data Filtering, right-click, and then select Apply Query.
You can further filter the data output by specifying data comparison operations in the Data Restrictions list box.
In the navigation panel, select Data Filtering.
The data filters that you specified are displayed in the properties panel.
In Data Restrictions, double-click.
The data restriction settings are displayed in the properties panel.
Click the down arrow of Column used for filter, and select Qtr1, Actual.
In the navigation panel, select Data Filtering, 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.
In the navigation panel, select Data Filtering to display the data filter setting in the properties panel.
In Data Restrictions, select Qtr1, Actual <= 500, right-click, and select New Data Restriction.
Under Combined With Other Restrictions, select the Or option.
In the navigation panel, click Data Filtering to access the data filter panel.
In Data Restrictions, double-click to create a data restriction.
This option instructs Essbase to discard data with #Missing values.
Under Combined With Other Restrictions, select the And option.
In the navigation panel, click Data Filtering to access the data filter panel.
Select the Data Filtering icon, right-click, and select Apply Query.
Essbase retrieves data for all quarters. Notice that the retrieved data for Qtr1, Actual is less than or equal to 500 or is less than Qtr2, Actual.