A script-enabled browser is required for this page to function properly.

About matrix reports

A matrix (cross-product) report is a cross-tabulation of four groups of data:

Paper Design view with a matrix report's output.

Thus, to create a matrix report, you need at least four groups in the data model: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels", and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.

A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.

With Reports Builder, you can create many different matrix reports. The four general types of matrix reports are simple matrix, nested matrix, multiquery matrix with break, and matrix break, but you are not confined to these designs.

For a detailed example, see the chapter "Building a Matrix Report" in the Oracle Reports Building Reports manual, available on the Oracle Technology Network Oracle Reports Documentation page (http://www.oracle.com/technology/documentation/reports.html).

Matrix data model

In building your matrix data model, you should consider the following:

Number of queries

Although matrix reports always require at least four groups, they can be built with any number of queries. If you build a matrix report with only one query (usually the most efficient structure), you must create at least three groups manually in addition to the one created by default. If you build a matrix report with multiple (three or more) queries, you still need to create at least one group. The figure below illustrates the two types of query structures that you can use for your matrix data model.

matrix data model

One-query matrix. One advantage to a one-query data model is that the resulting report is generally more efficient than a report based on a multiquery data model.

Multiquery matrix. You may consider using a multiquery data model, as it often has simple queries and can be easier to maintain than the equivalent one-query data model. In addition, a multiquery data model is required for some types of matrices (for example, some nested matrix reports).

Group structure

Matrix reports are built with four or more groups:

graphic depicting matrix

The next figure shows graphical representations of how groups are related for both single and multiquery data models.

matrix data model relationships

Notice that for each data model the cross product group is represented by the large rectangle, while the dimension groups are the smaller rectangles enclosed by it, and the cell group is shown outside of the cross product group.

Summary settings

Creating a summary for a matrix requires more information than creating a summary for other kinds of reports. When you create summary columns for your matrix, you need to indicate the following:

In Reports Builder, you specify this information by setting the Product Order property for your summary. All summaries that are owned by the cross product group require that a Product Order be specified. Suppose that you have a matrix report that looks something like the one below.

matrix report

This is a nested matrix report. Assume that group G_YEAR contains the YEAR column, G_DEPT contains the DEPTNO column, G_JOB contains the JOB column, and G_CROSS is the cross product group. To create the summary of salaries by job that appears at the bottom of the matrix for each job, you create a summary column in G_CROSS with the following property settings:

matrix settings

These settings specify that the summary should be calculated for each job in the G_JOB group and the summary should be reset to zero for each job in the G_JOB group.

To create the summary of salaries by year which appears at the right of the matrix directly underneath the boilerplate lines, you create a summary column in G_CROSS with the following property settings:

matrix settings

These settings specify that the summary should be calculated for each year in the G_YEAR group and the summary should be reset to zero for each year in the G_YEAR group. To create the summary of salaries by year and department that appears at the right of the matrix for each department row of the matrix that contains a value, you create a summary column in G_CROSS with the following property settings:

matrix settings

These settings specify that the summary should be calculated for each record of G_DEPT within each record of G_YEAR. G_DEPT comes after G_YEAR in the Product Order because, in this report, it changes more frequently than G_YEAR. If G_YEAR changed more frequently (that is, years were listed for each department), it would make more sense to have a Product Order of G_DEPT, G_YEAR.

This summary is reset to zero for each record in the G_DEPT group. When you have multiple groups listed in Product Order and you want a non-running summary, the reset group should be the same as the last group in Product Order. When you have multiple groups listed in the Product Order and you want a running summary, the reset group should be a group other than the last one in the Product Order.

Matrix layout

The figure below shows the objects that make up a simple, two-dimensional matrix. All of these objects are necessary for a matrix, except for the boilerplate field labels.

matrix layout

A matrix layout must consist of the following layout objects:

Note: When running a JSP-based Web report that includes a matrix object, the JSP <rw:include> tag expects the matrix object to format on only one page. However, if the cells in the across repeating frame expand, the column headers of the matrix may be forced onto a second page regardless of the page size. Therefore, these matrix headers will not appear in the report output. Additionally, in this situation, the paper layout does not display correctly as header fields move to subsequent pages where the cell values remain on the first page. To resolve this, it is important to carefully define in the Report Editor the space available to the across repeating frame to allow for displaying the largest values, then set the Vertical Elasticity property of the across repeating frame to Fixed.

Matrix object

The matrix object defines the intersection of at least two repeating frames. The repeating frames are the dimensions of the matrix and the matrix object contains the repeating frame that will hold the ”filler” or values of the cell group. You need one matrix object for each pair of intersecting repeating frames in the layout. One of the repeating frames must have the Print Direction property set to Down and the other must have the Print Direction property set to Across in order to form a matrix.

For more details, see About matrix objects.

Usage notes

You can improve the performance of matrix reports with:

Note: Cross-product groups always cause "fetching ahead." The reason for this is that to cross-tabulate the data in a cross-product group, Reports Builder must first fetch all of the data.

See also

About matrix objects

About matrix with group reports

About nested matrix reports

About layout defaulting

Creating a report

Creating a matrix object

Creating a matrix (cross-product) group

Creating a nested matrix report