Multiple Queries Part 1

Introduction

Note: For simplicity, this Guide assumes that you are making a report with two queries, but InfoView supports many more than just two.

Diagram showing how multiple queries work

With Multiple Queries, you are constructing two completely separate report queries which will return two completely separate results tables. Without further action on your part, your two sets of result objects cannot be used together in the same table: InfoView cannot know which rows match up together. Part of using multiple queries is adding in the logic that allows the different objects to be used in the same table.

Constructing a report with multiple queries has two parts:

  • Part 1: In the query panel, you'll need to create two separate queries with a little bit of forethought for the next steps.
  • Part 2: Once you have run your report and received two result tables, you'll need to add the logic that allows objects from the first query to be used with objects in the second query (or vice versa).

Because the two tables have to be manually connected, Multiple Queries is the most work-intensive way to use multiple queries. However, it also offers great flexibility because, unlike most other multiple-query techniques, you can work with multiple data-sources (universes/excel files).

Video

Step-by-Step Guide

Example: Class Buildings

The example shown in this guide uses two queries to show the age of the buildings where a particular class has taken place over the years. The information about classes will be pulled from the Course Review universe. The age of the building and other building information will be pulled from the Facilities universe.

Part 1: Query Panel

0. Before you start making your report, you're going to have to know what you want to achieve and a little bit about the data in the data set(s) that you're going to use. Specifically, you'll need to know how to match the data up from two different tables: what piece of information in both tables can you use to create that bridge?

If you're gathering data about people, you might be able to use Student or Employee ID numbers. For financial data, maybe you're looking at transaction date or FOAPAL.

Example: Class Buildings

I know that both the Course Review universe and the Facilities universe have building names in them. In Course Review, the field that contains this information is called the Building Code. In Facilities, the object is called the Building Name. I need to be sure to have both of these objects in their respective queries so that I can match up the rows in part 2.

Screenshot of InfoView showing both object names from my two queries

1. Start a new report. Pick your first data source and get to the Query Panel. (The order is not usually important).

2. Construct the query for your first result table.

Example: Class Buildings

I want to know a lot about the classes, but most importantly I need to make sure that the building name is included.

I've only added one limit on the report to have this run for a bygone year. Note that for most data-sets, you'll want to limit your selections much more than I have.

Screenshot of InfoView showing my first query

3. Click on the "add query" button and select "From Universe", then the second universe.

Note: If you're in Java Mode, the "add query" option includes "From Excel". See our Excel Guide for more information.

Screenshot of InfoView showing the location of the add query button

A second query will appear: In the lower left corner, you'll see your first query and that the second query tab is automatically selected. This second query is blank. You can toggle between them via the tabs.

Example: Class Buildings

Screenshot of InfoView showing the appearance of a blank second query

4. Complete your second query.

Note: If both of your queries are filtering on the same value via a prompt, you'll need to be very careful. Both queries need to have a filter with exact same prompt text in order to unify into one single prompt. The prompt text is the value that appears in the text box when you change your filter into a prompt. If the text is not the same, the field will present two separate prompts in the prompt window. Also: In report with multiple queries, it is not possible to change the prompt order via the query options (see the Query Options Guide for instructions for single queries).

Example: Class Buildings

I want to know the official Building ages, Building Names and official Building Code for all buildings at UCSC.

Note that the naming conventions between the two data sets are not intuitive: Building Code in the Course Review universe means something different than Building Code in the Facilities universe. This is typical: you'll need to know your universes.

Screenshot of InfoView showing my second query based on the facilities universe

5. If desired, rename your queries to be more understandable. Either complete this through the query options (see the Query Options Guide) or by right-clicking (command+click on a mac) on the query name and selecting "Rename".

Screenshot of InfoView showing how to rename a query

6. For more than two queries, complete steps 3, 4 and 5 until you have all of the queries that you want/need.

7. Run your report.

8. As soon as your results appear, don't forget to save your report!

9. Note that your results will appear as two completely separate, non-contiguous tables by default. To join them, see our Multiple Queries Part 2 Guide.

Example: Class Buildings

The default result tables for the two example queries are not contiguous (see below):

Screenshot of InfoView mutliple query example results