26.3 Create a Multiple-Query Matrix

You can build a matrix report with multiple queries in the data model. A multiple-query data model is typically easier to conceptualize and code than a single-query, but the single-query data model typically performs better.

26.3.1 Create a new report manually

In this case, it is easier to create the data model and layout separately. Hence, we will create an empty report first, then add the queries, and then create the layouts.

To create a blank report:

Choose File > New > Report.

Select Build a new report manually, then click OK.

26.3.2 Create a data model with a cross product and data links

When you create a matrix report with multiple queries, it is typically easier to create all of the queries with the Data Wizard first and then create the cross product group and the necessary links in the Data Model view.

On the Data page, enter the following SELECT statement in the Data Source definition field:

SELECT DISTINCT TO_CHAR (HIREDATE, 'YY') YEAR
FROM EMP

Note:

You can enter this query in any of the following ways:

Copy and paste the code from the provided text file called nested3_code.txt into the Data Source definition field.

Click Query Builder to build the query without entering any code manually.

Type the code in the Data Source definition field.

Click Next.

Note:

If you are not already connected to a database, you will be prompted to connect to the database when you click Query Builder or Next. Ensure that you connect to a database that has the appropriate schema for this example. Section 26.1, "Prerequisites for This Example" describes the sample schema requirements for this example.

On the Groups page, click Next.

Click Finish to display the data model for your report in the Data Model view.

Repeat the steps above for a second query, but this time name your query Q_Dept and use the following SELECT statement:

SELECT DISTINCT DEPTNO
FROM EMP

Note:

You can enter these queries in any of the following ways:

Copy and paste the code from the provided text file called nested3_code.txt into the Data Source definition field.

Click Query Builder to build the query without entering any code manually.

Type the code in the Data Source definition field.

Again, repeat the steps above for a third query, but this time name your query Q_Job and use the following SELECT statement:

SELECT DISTINCT JOB
FROM EMP

Again, repeat the steps above for a fourth query, but this time name your query Q_Salary and use the following SELECT statement:

On the Groups page, ensure that all of the groups from your data model appear in the Displayed Groups list. G_1 should be Matrix, G_YEAR and G_DEPTNO should be Down, G_JOB should be Across, and G_YEAR1 should be Down.