HP Storage Essentials Storage Resource Management Report Optimizer Software 6.0 Building Reports Using the Web Intelligence Java Report Panel (August 2008)

Filtering data using subqueries and database ranking112
NOTE: The Add a database rankingbutton is disabled if your database does not
support ranking.
4. Select the ranking direction (Top or bottom.)
5. Type the number of records you want the ranking to return in the box next to
Top/Bottom.
NOTE: You can specify a prompt instead of a constant by clicking on the arrow
next to the number. When you select a prompt the user must enter the ranking
number when the query is run. For more information on prompts, see ”Building
prompt filters on data providers” on page 113.
6. Drag the dimension that provides the calculation context for the measure to the For
Each box.
This dimension is optional. To display the For Each box, click the arrow to the right of
the Based on measure.
7. Drag any dimensions that you want to include in the WHERE restriction to the area at
the bottom of the ranking. For more information on the WHERE restriction, see the
explanation under ”What is a subquery?” on page 105.
8. Click Run Query.
Example: Create a report that returns the top 10 employees based on salary, and
calculated by department.
1. Drag the Department, Employee Name, and Salary objects to the Report Objects pane
within the Query Panel.
2. Select the Employee Name object.
3. Click Database Rank on the toolbar.
Web Intelligence adds an outline database rank to the Query Filters pane. The ranking
dimension is Employee Name and the ranking measure is Salary.
4. Set the ranking direction to Top.
5. Set the ranking number to 10.
6. Click the arrow next to the Based on measure if the For Each box is not already
visible.
7. Set the For Each dimension to Department by dragging and dropping the dimension.
The ranking now looks like this:
8. Click Run Query.