Introduction to Multiple Queries

Difficulty Level: Very High

Please note that using multiple queries is a very advanced report writing technique and is entirely unnecessary for most reports. Please be sure that you've gained enough experience writing single query reports before attempting this on your own.

We would be happy to help you through your first multiple queries report. Come to our Open Labs!

Definition

Multiple queries in a single report makes the report retrieve information from the Data Warehouse multiple times.

Normally, a report will fetch data from the database only once. Your actions in the Query Panel of a report assemble together to create a single query that contains the commands to retrieve the desired information. Clicking the "Refresh" button initiates the retrieval process by sending those commands to the database and displaying the answer to you in your report.

Reports that use multiple queries complete the retrieval process multiple times: once for each query.

There are several ways to use multiple queries in a single report, all of which are described below:

Descriptions

Subqueries

Diagram showing how subqueries work

A subquery is a type of query filter: the results of your subquery will narrow the results of your main query.

Uses: Subqueries are helpful if you need to define a population or a set of rows by criteria that you don't want to have to apply to the whole report.

Limitations: This can only work inside a single data source (universe or excel file) and your subquery will only limit one field of your main query.

Multiple Queries

Diagram showing how multiple queries work

Using Multiple Queries asks InfoView to complete two completely separate report queries which you will have to conjoin manually in your report. The two result sets are conjoined on the vertical axis: you are adding additional columns (fields) to your report.

Uses: Multiple Queries are helpful if you need fields (objects) from multiple data sources (universes/excel files) or from multiple facets of a single dataset.

Limitations: This the most complex and work-intensive way of using multiple queries.

Multiple Queries used as Subqueries

Diagram showing how subqueries work

Multiple Queries can be used like subqueries, where the results of one query limit a previous query

Uses: This can be helpful if the subquery must be based on a different data source (universe/excel file)

Limitations: The subordinate query must return fewer than 1,000 rows, otherwise this method will not work.

Union Queries

Diagram showing how union queries work

Using Union Queries asks InfoView to complete two completely separate report queries which will stack on top of each other in your report. InfoView will retrieve two sets of rows with different filters, possibly even different objects, but the results are automatically presented in a single, unified table.

Uses: Union queries can be useful if you want to stack the information from two separate columns on top of each other to make one column with twice as many rows. This can be especially useful if the data in the universe can't be otherwise combined together.

Limitations: Both queries must use the same data source (universe/excel file).

Intersection Queries

Diagram showing how intersection queries work

Intersection Queries use the same mechanic as union queries, but instead of adding extra rows, the report will show only those rows that are retrieved in both queries.

Uses: This technique is only needed in very specialized situations, for example when you are aiming for extreme precision and/or you are comparing two facets of the same data.

Limitations: Both queries must use the same data source (universe/excel file).

Minus Queries

Diagram showing how minus queries work

Minus Queries use the same mechanic as union queries, but instead of adding extra rows, the report will show the results of the first query minus the rows returned in the second query.

Uses: This technique is only needed in very specialized situations, for example when you are aiming to reduce rows by criteria that is very specific but could interfere with the filters on your first query.

Limitations: Both queries must use the same data source (universe/excel file).