25.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.

25.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:

  1. Choose File > New > Report.

  2. Select Build a new report manually, then click OK.

25.3.2 Create a data model with a cross product and a data link

When you create a report with multiple queries, it is typically easier to create all of the queries with the Data Wizard first and then create the layouts with the Report Wizard.

To create the queries:

  1. In the Data Model view, choose Insert > Query to display the Data Wizard.

  2. If the Welcome page displays, click Next.

  3. On the Query page, type Q_Dept for the Query name, then click Next.

  4. On the Data Source page, click SQL Query, then click Next.

  5. On the Data page, enter the following SELECT statement:

    SELECT DISTINCT DEPTNO
FROM EMP

    Tip:

    If you click Query Builder, you can build the query without entering any code manually.

  6. 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 25.1, "Prerequisites for This Example" describes the sample schema requirements for this example.

  7. On the Groups page, click Next.

  8. Click Finish to display your first query in the Data Model view.

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

    SELECT DISTINCT JOB
FROM EMP

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

    SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

    Figure 25-3 Three query data model, queries unrelated

    Description of Figure 25-3 follows
    Description of "Figure 25-3 Three query data model, queries unrelated"

To create the cross product group:

  1. In the Data Model view, click the Cross Product tool in the tool palette.

  2. Drag a box around G_DEPTNO and G_JOB. When you release the mouse button, the cross product group is created. Ensure that it completely surrounds both groups.

Figure 25-4 Three query data model with cross product group

Description of Figure 25-4 follows
Description of "Figure 25-4 Three query data model with cross product group"

To add the data link:

  1. In the Data Model view, click the Data Link tool in the tool palette.

  2. Click and drag from the DEPTNO column in the G_DEPTNO group to the DEPTNO1 column in the G_DEPTNO1 group.

  3. Repeat steps 1 and 2, but this time drag the link between the JOB column in G_JOB and JOB1 in G_DEPTNO1.

Figure 25-5 Three query data model with cross product group and data links

Description of Figure 25-5 follows
Description of "Figure 25-5 Three query data model with cross product group and data links"

25.3.3 Create the layout with the Report Wizard

Once your data model is complete, you need to create a layout for the data objects to display in the report output.

To create the layout:

  1. In the Data Model view, right-click on the canvas, then choose Report Wizard.

  2. In the Report Wizard, on the Report Type page, select Create Paper Layout only.

  3. On the Style page, select Matrix.

  4. 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_DEPTNO should be Down, G_JOB should be Across, and G_DEPTNO1 should be Down.

    Figure 25-6 Groups page of the Report Wizard

    Description of Figure 25-6 follows
    Description of "Figure 25-6 Groups page of the Report Wizard"

  5. On the Fields page, ensure that only the following columns appear in the Displayed Fields list:

    • DEPTNO

    • JOB

    • SUM_SAL

    Figure 25-7 Fields page of the Report Wizard

    Description of Figure 25-7 follows
    Description of "Figure 25-7 Fields page of the Report Wizard"

  6. On the Labels page, delete the labels for all of the fields.

  7. On the Template page, make sure Beige is selected under Predefined Template, then click Finish to display your report output in the Paper Design view. It should look like this:

    Figure 25-8 Paper Design view for the matrix report

    Description of Figure 25-8 follows
    Description of "Figure 25-8 Paper Design view for the matrix report"