26 Building a Nested Matrix Report

In this chapter, you will learn about nested matrix reports. By following the steps in this chapter, you can generate the report output shown in Figure 26-1.

Figure 26-1 Nested matrix report output

Description of "Figure 26-1 Nested matrix report output"

Concepts

• A nested matrix report is a matrix report which contains more than the usual two dimensions (across and down) of a simple matrix.

• You can create nested matrix reports with any number of queries. In Section 26.2, "Create a single-query matrix", you'll create the example nested matrix report using one query. In Section 26.3, "Create a multiple-query matrix", you'll use multiple queries. In Section 26.4, "Create a multiple-query matrix with a break", you will use not only group hierarchy to nest one dimension within another, but you'll also create a true parent/child relationship between the relevant groups by explicitly creating a new group and placing it within the cross product and above the other group. This will restrict the records displayed to only those for which data exists.

  
  

  Notes: In a nested matrix report, where you create a matrix query that has a child matrix query, the fields on which you join should be separated into a separate group in your master matrix. Otherwise, on some platforms, only the first group prints. The queries used to select data for those sample matrix reports are not intended as definitive examples of matrix queries. If you are concerned with performance issues, for example, there are alternate methods of querying data that can improve the performance of a matrix report. Refer to the Oracle Reports online Help for details.

  
  

• This example report uses a matrix with three dimensions, created by including three columns in the cross product. The cross product is capable of displaying every possible value for three dimensions: two down (YEAR and DEPTNO) and one across (JOB). This method does not include rows that have null values because there is a break group within the cross product group in the data model. As mentioned above, this chapter describes how to create a nested matrix with and without a break group inside of the cross product group so that you can see the difference in the output.

• The order in which the two down dimensions (YEAR and DEPTNO) will be displayed is based upon the order in which you position their corresponding groups within the cross product; that is, of YEAR and DEPTNO, you'll position YEAR to the left of DEPTNO. This "nests" DEPTNO inside of YEAR and causes all records for DEPTNO to appear for each value of YEAR, regardless of whether that department has any data for that year.

• For additional conceptual information, see Section 2.1.7, "About nested matrix reports".

Example Scenario

In this example, you will create a report that cross tabulates salaries by year and department, and by job function. The result is a matrix with job functions listed across the top, years and departments down the side, and sums of salaries in the cells. Thus, you can quickly determine the sum of all of the salaries for clerks in department 20 and compare that value to the one for all clerks in some other department.

As you build this example report, you will:

• Create a single-query matrix.

• Create a multiple-query matrix.

• Create a multiple-query matrix with a break within the cross product group.

• Format monetary values.

To see a sample nested matrix report, open the examples folder named nestedmatrix, then open any of the Oracle Reports examples named nested1b.rdf, nested3b.rdf, or nested4b_brk.rdf. For details on how to access them, see "Accessing the Example Reports" in the Preface.

26.1 Prerequisites for this example

To build the example in this chapter, you must have access to the SCOTT sample schema provided with the Oracle Database. If you do not know if you have access to this sample schema, contact your database administrator.

26.2 Create a single-query matrix

You can build a matrix report with a single-query in the data model. A single-query data model typically performs better than a multiple-query data model.

When you are creating a single-query report, such as this one, you can use the Report Wizard to create the data model and layout simultaneously.

To create a data model and layout:

1. Launch Reports Builder (or, if already open, choose File > New > Report).

2. In the Welcome or New Report dialog box, select Use the Report Wizard, then click OK.

3. If the Welcome page displays, click Next.

4. On the Report Type page, select Create Paper Layout Only, then click Next.

5. On the Style page, type a Title for your report, select Matrix, then click Next.

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

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

   SELECT TO_CHAR (HIREDATE, 'YY') YEAR, DEPTNO, JOB,
   SUM (SAL) FROM EMP
   GROUP BY TO_CHAR(HIREDATE, 'YY'), DEPTNO, JOB
   ORDER BY TO_CHAR(HIREDATE, 'YY'), DEPTNO, JOB
   
   

   
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called nested1b_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.

   
   
   
   

   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.

   
   

8. On the Rows page:

   • Click YEAR in the Available Fields list and click the right arrow (>) to move this field to the Matrix Row Fields list. YEAR should appear under Level 1.

     Figure 26-2 First matrix row in the Report Wizard

     
     Description of "Figure 26-2 First matrix row in the Report Wizard"
     
     

   • Click Level 1 in the Matrix Row Fields list. This step ensures that the next column added will be at Level 2 rather than Level 1.

   • Click DEPTNO in the Available Fields list and click the right arrow (>) to move this field to the Matrix Row Fields list. Notice that it is added under Level 2 rather than Level 1.

     Figure 26-3 Second matrix row in Report Wizard

     
     Description of "Figure 26-3 Second matrix row in Report Wizard"
     
     

   • Click Next.

9. On the Columns page, click JOB and click the right arrow (>) to move this field to the Matrix Column Fields list. JOB should appear under Level 1, then click Next.

10. On the Cell page, click SUM_SAL and click the right arrow (>) to move this field to the Matrix Cell Fields list, then click Next.

11. On the Totals page, click Next.

12. On the Labels page, change the labels and field widths as follows, then click Next:

    Fields	Labels	Width
    YEAR	(no change)	4
    DEPTNO	(no change)	(no change)
    JOB	(no change)	10
    SUM_SAL	<none>	10

    
    

13. 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 26-4 Paper Design view for the nested matrix report

    
    Description of "Figure 26-4 Paper Design view for the nested matrix report"
    
    
    
    

    Note: You can set alignment and format monetary values directly in the Paper Design view. Simply click the item, then click the appropriate toolbar button (if you run the mouse over these buttons, hint text displays). If the Paper Design view and the Object Navigator are displayed side-by-side, notice that when you select an item in one, the selection is reflected in the other.

    
    

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:

1. Choose File > New > Report.

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

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_Year for the Query name, then click Next.

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

5. 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 nested4b_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.

   
   

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 26.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 the data model for your report in the Data Model view.

9. 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 nested4b_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.

   
   

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

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

    SELECT TO_CHAR(HIREDATE, 'YY') YEAR,  DEPTNO,  JOB,
    SUM(SAL) FROM EMP
    GROUP BY TO_CHAR(HIREDATE, 'YY'),  DEPTNO,  JOB
    ORDER BY TO_CHAR(HIREDATE, 'YY'),  DEPTNO,  JOB
    

    Figure 26-5 Data model with four queries

    
    Description of "Figure 26-5 Data model with four queries"
    
    
    
    

    Tip: The order of the queries is significant in this case. Q_Dept must appear to the right of or below Q_Year in order for the values of DEPTNO to be nested inside of the values of YEAR in the output.

    
    

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_YEAR, G_DEPTNO, and G_JOB. When you release the mouse button, the cross product group is created. Ensure that it completely surrounds all three groups.

Figure 26-6 Data Model with four queries and a cross product group

Description of "Figure 26-6 Data Model with four queries and a cross product group"

To add the data links:

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

2. Click and drag from the YEAR column in the G_YEAR group to the YEAR1 column in the G_YEAR1 group.

3. Repeat steps 1 and 2, but this time drag the link between the DEPTNO column in G_DEPTNO and DEPTNO1 in G_YEAR1.

4. Again, repeat steps 1 and 2, but this time drag the link between the JOB column in G_JOB and JOB1 in G_YEAR1.

   Figure 26-7 Nested matrix data model

   
   Description of "Figure 26-7 Nested matrix data model"
   
   

5. Optionally, move the data model objects around to appear like the above figure.

26.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_YEAR and G_DEPTNO should be Down, G_JOB should be Across, and G_YEAR1 should be Down.

   Figure 26-8 Groups page of the Report Wizard

   
   Description of "Figure 26-8 Groups page of the Report Wizard"
   
   

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

   • YEAR

   • DEPTNO

   • JOB

   • SUM_SAL

   Figure 26-9 Fields page of the Report Wizard

   
   Description of "Figure 26-9 Fields page of the Report Wizard"
   
   

6. On the Labels page, change the labels and field widths as follows:

   Fields	Labels	Width
   SUM_SAL	<none>	10
   YEAR	<none>	4
   JOB	<none>	10

   
   

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 26-10 Paper Design view for the nested matrix output

   
   Description of "Figure 26-10 Paper Design view for the nested matrix output"
   
   

26.4 Create a multiple-query matrix with a break

If you compare the single-query nested matrix output in Figure 26-4, "Paper Design view for the nested matrix report" to the multiple-query output in Figure 26-10, "Paper Design view for the nested matrix output", you notice that the multiple-query case displays all of the departments for every year while the single-query case does not. In the single-query case, only those departments that actually have values in their matrix cells are displayed in the output. To achieve a similar result with multiple queries, you need to have a parent/child relationship between the groups containing YEAR and DEPTNO.

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

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

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, select SQL Query, then click Next.

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

   SELECT TO_CHAR(HIREDATE, 'YY') YEAR, DEPTNO
   FROM EMP
   GROUP BY TO_CHAR(HIREDATE, 'YY'), DEPTNO
   ORDER BY TO_CHAR(HIREDATE, 'YY'), DEPTNO
   

   
   

   Note: You can enter this query in any of the following ways: Copy and paste the code from the provided text file called nested3b_brk_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.

   
   

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

   
   

7. On the Groups page, click YEAR in the Available Fields list and click the right arrow (>) to move this field to the Group Fields list, then click Next.

8. On the Totals page, click Next.

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

10. Choose Insert > Query and follow the steps above to create another query named Q_Job and use the following SELECT statement:

    SELECT DISTINCT JOB
    FROM EMP
    

11. Again, choose Insert > Query and follow the steps above to create a third query named Q_Salary and use the following SELECT statement:

    SELECT TO_CHAR(HIREDATE, 'YY') YEAR,  DEPTNO,  JOB,
    SUM(SAL) FROM EMP
    GROUP BY TO_CHAR(HIREDATE, 'YY'),  DEPTNO,  JOB
    ORDER BY TO_CHAR(HIREDATE, 'YY'),  DEPTNO,  JOB
    

    Figure 26-11 Data model with three queries

    
    Description of "Figure 26-11 Data model with three queries"
    
    

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_YEAR, G_DEPTNO, and G_JOB. When you release the mouse button, the cross product group is created. Ensure that it completely surrounds all three groups.

Figure 26-12 Data Model with three queries and a cross product group

Description of "Figure 26-12 Data Model with three queries and a cross product group"

To add the data links:

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

2. Click and drag from the YEAR column in the G_YEAR group to the YEAR1 column in the G_YEAR1 group.

3. Repeat steps 1 and 2, but this time drag the link between the DEPTNO column in G_DEPTNO and DEPTNO1 in G_YEAR1.

4. Again, repeat steps 1 and 2, but this time drag the link between the JOB column in G_JOB and JOB1 in G_YEAR1.

   Figure 26-13 Nested matrix data model

   
   Description of "Figure 26-13 Nested matrix data model"
   
   

5. Optionally, move the data model objects around to appear like the above figure.

26.4.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_YEAR and G_DEPTNO should be Down, G_JOB should be Across, and G_YEAR1 should be Down.

   Figure 26-14 Groups page of the Report Wizard

   
   Description of "Figure 26-14 Groups page of the Report Wizard"
   
   

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

   • YEAR

   • DEPTNO

   • JOB

   • SUM_SAL

   Figure 26-15 Fields page of the Report Wizard

   
   Description of "Figure 26-15 Fields page of the Report Wizard"
   
   

6. On the Labels page, change the labels and field widths as follows:

   Fields	Labels	Width
   SUM_SAL	<none>	10
   YEAR	<none>	4
   JOB	<none>	10

   
   

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 26-16 Paper Design view for the final nested matrix report output

   
   Description of "Figure 26-16 Paper Design view for the final nested matrix report output"
   
   

26.5 Format monetary values

To make your matrix report more readable, you should format the monetary values.

To format monetary values:

1. On the first page of the report, click one of the cell values. Notice that all of the values are immediately selected, indicating that you can change their properties simultaneously.

2. Click the Currency button in the toolbar. A currency symbol immediately appears next to all of the values.

3. Click the Add Decimal Place button in the toolbar twice. Two decimal places are added to the right of the decimal point.

4. Resize the fields. Click and drag the rightmost handle of the cell value under the SALESMAN label about 0.5 inches (1.5 centimeters) to the left. After you complete this operation, the department summaries from the second page should move onto the first page.

5. Shift-click the SALESMAN label.

6. Click the Align Right button in the toolbar.

7. Click in an open area of the Paper Design view to deselect all of the objects.

   Figure 26-17 Nested matrix report output with formatted monetary values

   
   Description of "Figure 26-17 Nested matrix report output with formatted monetary values"
   
   

8. Save your report as nested4b_brk_your_initials.rdf

26.6 Summary

Congratulations! You have successfully created three nested matrix reports. You now know how to:

• create a nested matrix report with a single-query data model.

• create a nested matrix report with a multiple-query data model.

• create a nested matrix report with multiple queries and a break within the cross product group.

• format monetary values.

For more information on any of the wizards, views, or properties used in this example, refer to the Oracle Reports online Help, which is available in Reports Builder or hosted on the Oracle Technology Network (OTN), as described in Section 3.1.1, "Using the Oracle Reports online Help".