The query on query feature allows content creators and power users the ability to filter an existing query with another adhoc query. This is a useful way of combining corporate data with either another corporate data source or a personal data source.
In the example below, I am creating a report that returns all of the business names along with the associated city, state, and business hours for animal shelters. This information is stored in an Microsoft SQL Server 2005 database.
In another Universe, again querying a Microsoft SQL Server database, I have an object that represents all of the cities within the United States. My goal, for the list of animal shelters that should be displayed in this report, is that I only want to see those shelters that are in the United States. I am unable to do this with my initial query as the data set does not include the Country in which my City exists. In order to filter my query using a query from another Universe, I need to do the following:
From the "Edit Query" panel, click on the "Add Query" button. This opens a dialog box that allows me to choose my new data source. For my example, I will choose the "World" Universe as the source for new query. Click "OK".
This opens a new query panel represented as a second tab next to your existing
query panel. Under the class "Cities", you will notice an object called "US Cities". This is the only object that I am going to include into this new query.
In order to use this second query as a filter for my first query, I need to first run both queries. To do so, simply click the "Run Queries" button. Once both queries have run successfully, you will then need to click on the "Edit Query" button to get back into the query definitions. Now, on query one, I will drag the "City" object from my Animal Shelters class into the "Query Filter" section of the query panel. As the filter type I am going to choose "Result from another query".
This shows me a dialog box with the current list of queries that I can use as filters within the existing session. I will select the US Cities query as this is the second query that I created. Click "OK".
Now, when I run the queries, my report will show me only the Animal Shelters from my first data source that exist within the listed cities (on US cities) from my second data source. You can see that both queries are represented in the "Data" pane to the left: "Query 1 - Animal Shelters" and "Query 6 - World".
There is obviously a lot more that I must do with this report to make it usable; however, this demonstrates the usefulness of the query on query functionality as a mechanism for report creators and analysts to combine multiple sources of information into a single information view.
1 Comment
