You can add single or multiple filters to a query to limit the information it returns. This standard method lets you combine filters in one query. You can use these combinations to make your queries more specific.
However, this technique only works with a single query. There are situations when you want to combine the results of two queries into a single one.
To do this, create a combined query in the Query panel. You can build combined queries using a single universe. Each query must include the same number of objects and use the same data types.
Methods of Combining Queries:
- Union combines results that appear in Query 1 or Query 2.
- Intersection combines results that appear in Query 1 and Query 2.
- Minus combines results that include everything in Query 1, except for what is also true in Query 2.
Combined Query Scenarios
A typical scenario for using a combined query is when you want to find the dates your customers made reservations or paid invoices.
This query uses two objects: Invoice Date and Reservation Date. Both objects represent dates, but their purposes are different. This difference makes the objects incompatible in a single query.
Differences between standard queries and combined queries:
| Query Type | Object Type | Result |
|---|---|---|
| Standard query. | Incompatible objects. | Two blocks of unsynchronized data. |
| Combined query. | Incompatible objects (for example, Invoice Date and Reservation Date). | A single column of data in a block (with a column header that reflects the object from the first query only). |
When you combine queries, the process is similar to using Boolean logical operators to combine conditions in a single query, but the results are not the same. The Intersection function works like the AND operator. The Union function works like the OR operator. The Minus function works like the NOT operator.
When you run a combined query, the column header shows the date object from only the first query. For example, if you combine queries for invoices and reservations, the column may be labeled "Invoice Date," but it can include both invoice dates and reservation dates. You may need to change the column header to make the data clear.
Advantages of Combined Queries
You may want to use combined queries instead of adding several filters to a single query for these reasons:
- Combined queries can be easier to build.
- You can set required filters even when Boolean logic does not work.
Combined queries may take longer to process because the system creates multiple queries.
Choose between query filters and combined queries based on how your data is structured.
Key Aspects for Use of Combined Queries
- You can only run combined queries when each query contains the same number of objects.
- When you use a combined query that returns data from more than one object in a column, such as with the Union operator, you must use objects of the same type: character, date, or number.
- After you run a combined query, only objects from the first query appear in the block.
- Combined query objects appear in the same column as the object from the first query.
- Union is the default operator for a combined query. It returns all values except duplicates.
- To use the Union All operator, you need to create a custom formula or variable in Web Intelligence.
For more information about this topic, please read SAP BusinessObjects Web Intelligence User's Guide
Let's Summarize What You've Learned
- Use this feature to combine results from multiple queries using Union, Intersection, or Minus operators.
- Ensure each combined query uses the same number and type of objects for accurate results.
- Combined queries help when standard filters or Boolean logic are not sufficient.
- The column header in combined queries reflects only the first query’s object, so adjust as needed for clarity.