|Oracle® Fusion Middleware Oracle Reports User's Guide to Building Reports
11g Release 1 (11.1.1)
Part Number B32122-01
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.
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.
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:
In the Data Model view, choose Insert > Query to display the Data Wizard.
If the Welcome page displays, click Next.
On the Query page, type
Q_Dept for the Query name, then click Next.
On the Data Source page, select SQL Query, then click Next.
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
nested2_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.
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.
On the Totals page, click Next.
Click Finish to display the data model for your report output in the Data Model view.
Choose Insert > Query and follow the steps above to create another query named
Q_Job and use the following
SELECT DISTINCT JOB FROM EMP
Again, choose Insert > Query and follow the steps above to create a third query named
Q_Salary and use the following
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
To create the cross product group:
In the Data Model view, click the Cross Product tool in the tool palette.
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
To add the data links:
In the Data Model view, click the Data Link tool in the tool palette.
Click and drag from the YEAR column in the G_YEAR group to the YEAR1 column in the G_YEAR1 group.
Repeat steps 1 and 2, but this time drag the link between the DEPTNO column in G_DEPTNO and DEPTNO1 in G_YEAR1.
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
Optionally, move the data model objects around to appear like the above figure.
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:
In the Data Model view, right-click on the canvas, then choose Report Wizard.
In the Report Wizard, on the Report Type page, select Create Paper Layout only.
On the Style page, select Matrix.
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
On the Fields page, ensure that only the following columns appear in the Displayed Fields list:
Figure 26-15 Fields page of the Report Wizard
On the Labels page, change the labels and field widths as follows:
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