Joining BI Queries
Two or more queries can be joined together to create a query that meets the combined criteria of the selected queries. The results of the joined queries can be composed in one of the following ways:
•all the records that meet the criteria of each of the queries (Add option)
•only the records that meet the criteria of all of the queries (Keep Only option)
•the records that meet the criteria of one query, minus the records that meet the criteria of the other queries (Remove)
When joining queries, only the primary query can be unsaved. All of the other queries must be saved to be available for joining.
One of the queries is called the primary list and the others are referred to based on the order in which they are joined to the primary list (#2, #3, #4). The primary list is the query that is already open when you select the other queries to join to the primary. This is the query for which an extract record will be created and, in the case of a Remove extract, this is the query from which the records from the other queries will be subtracted.
When adding more than one additional query to the primary list, the results are dependent on the order in which the queries were added.
When you complete an extract involving multiple customers, an extract record is created only for the primary extract’s record.
To join BI queries:
1.Open Business Intelligence and Reports from the Desktop.
2.Select the Business Intelligence tab.
The Business Intelligence|Search page appears.
3.Search for and select the business intelligence (BI) query that you want to work with.
The Business Intelligence|Results page appears.
4.Click 'Add Criteria' in the saved list criteria section of the page.
A new row is added to the saved list criteria section.
5.Select the query that you want to join with the current query from the 'Search ID' dropdown.
6.Select how you want the results from the two extracts combined from the 'Type' dropdown:
•Add: Records that meet the criteria of any of the extracts; if a record meets the criteria of more than one extract, it is still only displayed once.
•Keep Only: Only the records that meet the criteria of all of the extracts.
•Remove: The records that meet the criteria of the primary extract, minus the records that meet the criteria of the other extract(s).
7.For each query that you want to join to the current query, repeat the process from Step 4.
8.If you want to extract the results to:
•Microsoft Excel:
a.Select CSV (text/csv) from the 'Extract Template' dropdown.
b.Click 'Extract'.
c.Complete the prompts to view or save the file.
•PDF:
a.Select the Report PDF (Print) (text/avdocument) from the 'Extract Template' dropdown.
b.Click 'Extract'.
c.Complete the prompts to view or save the file.
•XML:
a.Select the XML (text/xml) from the 'Extract Template' dropdown.
b.Click 'Extract'.
9.If you want to save your changes, expand the save lists and extracts section of the Business Intelligence|Results page and save the BI query.
Examples of Combined Extract Data
Extracting Data Using Two Extracts Together
Extracting Data Using Three Extracts Together
Extracting Data Using Combinations of Two and Three Extracts
