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

Building Reports Using the Web Intelligence Java Report Panel 105
9
Filtering data using subqueries and
database ranking
Overview
”Filtering data retrieval using query filters” on page 83 explains how you restrict the data
returned by a query by adding query filters.This chapter explains how to work with more
advanced types of query filter.
This chapter covers the following advanced query filters:
• subqueries
• database rankings
Subqueries and database rankings allow you to create filters that are much more powerful
than standard query filters.
What is a subquery?
A subquery is a more flexible kind of query filter that allows you to restrict values in more
sophisticated ways than with an ordinary query filter.
Subqueries are more powerful than ordinary query filters for the following reasons:
• They allow you to compare the values of the object whose values are used to restrict
the query with values from other objects.
• They allow you to restrict the values returned by the subquery with a WHERE clause.
What can you do with subqueries?
Subqueries allow you to pose complex questions that are difficult or impossible to
formulate with simple query filters. For example: what is the list of customers and their
associated revenue where the customer purchased a service that had previously been
reserved (by any customer) in Q1 of 2003? The example ”Which customers bought a
service that had previously been reserved in Q1 of 2003, and how much revenue have
they generated?” on page 108 shows you how to answer this question using a subquery.
How do subqueries work?
Subqueries work by modifying the SQL that Web Intelligence generates to retrieve the
query data. Web Intelligence generates SQL containining a subquery that restricts the data
returned by an outer query. For more information on SQL subqueries, see any book on
SQL.