Adding another query

Building several queries in the Query Panel is a core functionality of Web Intelligence.

The ability to centralize several data aspects in the same report is considered in many ways the heart of reporting and analysis.

There are many scenarios where we would be required to build more than one query; the main reasons are presented as follows:

• The report needs to present different queries with different filters that can't be unified into one query (will create contradiction in the filters)
• There are also several contexts in the universe that require separation between the queries

  Note

  Contexts are structures that can be created in universes when a business question can be asked in several different contexts. For example, in the Motors universe, we can analyze the customer data in the sales context (who bought a car) and we can analyze the customer data in the rental context (which customer rented a car).

  Contexts are also created when there are data areas in the universe that are isolated from each other since they can't connect directly through the table structure for business and technical reasons; for example, we have a monthly sales table and a daily sales table that are not connected directly in the universe and can't be defined in the same query.

• The data required is originated in more than one universe
• The data required is originated in different data types and structures (a universe and an Excel sheet)
• The query is using data structures from universes that are not connected (lack of joins)
• We want to create another query for quality assurance (QA) purposes (usually a lighter query in terms of the number of filters)

Getting ready

We are required to add a second query; this query will fetch a different aspect of data from the same universe and analyze the sales per customer and his or her geographical additional data. The reason we are using two separate queries based on the same universe is because they have different granular levels of data as well as they are relating to two different data subjects based on different query filters.

How to do it...

We will add a new query by clicking on the Add Query button located on the top-left Query Panel toolbar. We will drag the Category of Car, Maker, and Sales Revenue objects, and a query condition sales revenue greater than 1 million, as shown in the following screenshot:

The Universe prompt screen will be launched. Just as we chose a universe in the first query, we will pick the Motors universe again, as shown:

A second query tab will be created that will enable us to create the query. This query will deal with the customer data displaying the objects: Client Name, Client Town, Client Country, and Sales Revenue; and the query predefined filter US Clients, as shown in the following screenshot:

We will run the query. Before getting the results, an Add Query window will pop up, offering us three options. Have a look at the following screenshot:

The three options are as follows:

• Insert a table in a new report: This will insert the new query data in a new report tab
• Insert a table in the current report: This will insert the new query data in the current report beside the existing query's result table
• Include the result objects in the document without generating a table: The returned objects and data will not be presented, but will be available for dragging-and-dropping later if the user needs to do so

We will pick the second option and get the second query result next to the first query table, as shown:

How it works...

Creating another query is just as easy as creating the first one. Simply perform the same procedure one more time. After clicking on the Add Query button, we are walked through an already familiar universe screen to choose our next data provider.

Both of the reports are run and each query fetches its own dataset. As we have seen, the tables can be presented in the same report tab or in different report tabs.

When we are saving a Web Intelligence report, the query structure as well as the data in the presentation layer is saved.

There is no compulsion to combine the results of the different queries into one common table—it's all about the purpose of the report and the business user requirements. A business analyst might require a 360-degree report that displays a different aspect of the data in every tab of the report based on a different universe.

Note that when we refresh the report, we don't refresh the table and nor do we refresh the report tabs—it's the query that is being refreshed! As an outcome of the query refresh, all the visual components such as tables, charts, and cells are updated accordingly.

Tip

You can refresh several queries with the same prompt as long as the text in all the prompts is the same and the objects which are used in the prompts share the same data type.

See also

• For information on how to combine several queries from different universes, see Chapter 8, Merging Data