Result from another query is a filtering option in the Query panel. It allows you to use the result of a first query as a filter criterion for a second query within the same Web Intelligence document.
You create two queries in your document:
- The first query runs and returns a list of values.
- The system uses this list as a filter to get data for your second query.
Example
Suppose you want a document that gives sales details for your five best-performing stores.
First, build a query to find your top five stores based on sales revenue. This query returns a list such as Store A, Store B, Store C, Store D, and Store E.
Next, build a second query to get detailed sales data, such as product, customer, and sale date.
In the second query, add a filter on the store name object. Set the operator to In List and choose the operand Results from another query. The system prompts you to select the store name object from the first query as the source for your list.
When you run the report, the system first finds the top five stores. Then, it uses that list to filter your detailed sales data. You see results for only those five stores.
Key Benefits
Using results from another query gives you several advantages:
- Dynamic reports: The filter is always current. If your top five stores change, the report updates the next time you refresh it. You do not need to change the filter by hand.
- Better performance: Running two simple queries is often faster than running one complex query.
- Easier to manage: Two focused queries are simpler to understand, build, and keep up than one large query.
Database Limits
There are limits, but they aren't set by Web Intelligence itself. The database system and server setup may cause these limits.
The most common limit is the size of the IN list in the database. When you use results from one query to filter another, Web Intelligence sends a SQL command with an IN list. Many databases limit how many items can be in this list. For example, some databases set the limit at 1,000 items. If your first query returns more values than the database allows, the second query fails and shows a database error.
Even if your database supports a large list, performance can still be a problem. Sending a list with thousands of values makes the query complex.
This can cause the report to:
- Run slowly.
- Use too much server memory.
- Stop running if it takes too long (a server timeout).
Let's Summarize What You've Learned
- Use Results from another query to filter one query with the output of another.
- This feature creates dynamic documents that automatically update when source data changes.
- Running two focused queries improves performance and simplifies report management.
- Be aware of database limits on the number of items in the IN list to avoid errors and slow performance.