report-layout-types

Layout Types

The built-in report styles in Reports Builder are:

·         tabular reports

·         group above reports

·         group left reports

·         form-like reports

·         form letter reports

·         mailing label reports

·         matrix reports

Tabular

About tabular reports

A tabular report is the most basic type of report. Each column corresponds to a column selected from the database.

Creating a report

To create a single-query report:

1.      In the Object Navigator, click the Reports node, then click the Create button in the toolbar.

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

3.      Follow the Report Wizard to create your report, clicking Help for assistance on any tab page.

4.      Modify the resulting report output in the Web Source view or Paper Design view, or choose Tools > Report Wizard to re-enter the wizard.

To build a multi-user report:

1.      Create each query using the Data Wizard, clicking Help for assistance on any tab page

2.      Create a layout for the report

Group Above

About group above reports

A group above report contains multiple groups in its data model. It is a “master/detail” report, where there may be a lot of information in the master group. For every master group, the related values of the detail group(s) are fetched from the database and are displayed below the master information.

About groups

Groups are created to organize the columns in your report. Groups can do two things: separate a query’s data into sets, and filter a query’s data.

When you create a query, Reports Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually in the Data Model view or by using the Report Wizard to create a group above or group left report.

Create groups when you want to treat some columns differently than others. For example, you create groups to:

• produce subtotals (that is, totals at a more granular level).
• create breaks or cross products in your report output.

With the exception of cross-product groups, all user-created groups are called break groups.

Break groups

You create break groups to produce subtotals, print columns in a different direction, create breaks, and so on. A break group suppresses duplicate values in sequential records. For example, Reports Builder can select the department number for each record; however, the duplicate department numbers are not printed.

Cross-product groups

You create cross-product groups to perform mathematical cross products, which are generally used to create matrix reports.

Group filters

Filters enable you to conditionally remove records selected by your queries. Groups can have two types of filters:

• Reports Builder packaged filters:

• First, to display only the first n records for the group (for example, the first 5 records)
• Last, to display only the last n records for the group
• User-created filters, using PL/SQL.

Creating a break group

You can define break groups in the Report Wizard or create them manually.

To create a break group manually:

1.      In the Data Model view, drag the group that contains the column at which you want to break your report down from the query object about 2 inches (5 centimeters).

2.      Select the column that you want to use to divide your report (for example, if you want to group a list of employee data by department number, select the department number column) and drag it out of and above the group to create a new group. This is the break column.

3.      Double-click the new group object to display its Property Inspector, where you can set properties for the break group.

Group Left

About group left reports

A group left report also contains multiple groups in its data model, dividing the rows of a table based on a common value in one of the columns. Use this type of report to restrict a column from repeating the same value several times while values of related columns change. The data model for group above and group left reports is the same, but the layouts differ; group above reports display the master information at the top while group left reports display break columns to the side.

Creating a report

To create a single-query report:

1.      In the Object Navigator, click the Reports node, then click the Create button in the toolbar.

2.      In the New Report dialog box, click Use the Report Wizard, and then click OK.

3.      Follow the Report Wizard to create your report (type of layout), clicking Help for assistance on any tab page.

4.      Modify the resulting report output in the Web Source view or Paper Design view, or choose Tools > Report Wizard to re-enter the wizard.

To build a multi-user report:

1.      Create each query using the Data Wizard, clicking Help for assistance on any tab page

2.      Create a layout for the report

Form

About form-like reports

A form-like report displays one record per page, displaying field values to the right of field labels

Creating a report

To create a single-query report:

1.      In the Object Navigator, click the Reports node, then click the Create button in the toolbar.

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

3.      Follow the Report Wizard to create your report, clicking Help for assistance on any tab page.

4.      Modify the resulting report output in the Web Source view or Paper Design view, or choose Tools > Report Wizard to re-enter the wizard.

To build a multi-user report:

1.      Create each query using the Data Wizard, clicking Help for assistance on any tab page

2.      Create a layout for the report

Form Letter

About form letter reports

A form letter report contains database values embedded in boilerplate text (any text that you enter or import into a Report Editor.)

Creating a report

To create a single-query report:

1.      In the Object Navigator, click the Reports node, then click the Create button in the toolbar.

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

3.      Follow the Report Wizard to create your report, clicking Help for assistance on any tab page.

4.      Modify the resulting report output in the Web Source view or Paper Design view, or choose Tools > Report Wizard to re-enter the wizard.

To build a multi-user report:

1.      Create each query using the Data Wizard, clicking Help for assistance on any tab page

2.      Create a layout for the report

Mailing Label

About mailing label reports

A mailing label report prints mailing labels in multiple columns on each page. Using the Report Wizard, you can specify the format for your mailing labels.

Creating a report

To create a single-query report:

1.      In the Object Navigator, click the Reports node, then click the Create button in the toolbar.

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

3.      Follow the Report Wizard to create your report, clicking Help for assistance on any tab page.

4.      Modify the resulting report output in the Web Source view or Paper Design view, or choose Tools > Report Wizard to re-enter the wizard.

To build a multi-user report:

1.      Create each query using the Data Wizard, clicking Help for assistance on any tab page

2.      Create a layout for the report

Matrix

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

• One group of data is displayed across the page.
• One group of data is displayed down the page.
• One group of data is the cross-product, which determines all possible locations where the across and down data relate and places a cell in those locations.
• One group of data is displayed as the “filler” of the cells.

Figure  Example matrix report

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.
Example Scenario
Suppose that you have a group named Group1 that contains a column called C_DEPTNO, which gets its values from the database column DEPTNO.  A group called Group2, contains column C_JOB, which gets its values from the database column JOB, and column C_DEPTNO1, which is used for linking to Group1′s query.  A group called Group3 contains a column called SUMSAL, which is a summary of the database column SAL.
     Job 
Analyst      Clerk        Manager
10                       $1300        $2450
Dept   20      $6000       $1900        $2975
30                       $ 950         $2850
In this example: 
The Vertical Repeating Frame is the repeating frame that contains Group2 (the job titles).
The Horizontal Repeating Frame is the repeating frame that contains Group1 (the department numbers).
The Cross Product Group is Group4 (the group that is the parent of Group1 and Group2).
If you need to build a more complex matrix, you can do so by adding more columns to Group1 and Group2.  For example, instead of having Group1 just contain department numbers, it could also contain the locations (LOC) of the departments. The matrix might then look something like the one below.
Job
Loc         Dept      Analyst      Clerk      Manager
New York    10                     $1300      $2450
Dallas          20      $6000    $1900      $2975
Chicago       30                    $ 950       $2850

Creating a Matrix Report

Sample matrix report output shown in below figure

Figure Matrix report output

Concepts

·         A matrix report looks like a grid. As shown by the example report above, it contains one row of labels, one column of labels, and information in a grid format that is related to both the row and column labels.

·         This sample matrix also contains three additions to the basic matrix: summaries have been added, zeroes replace non-existent values in the cells, and the cells themselves are surrounded by grid lines. Of the summaries, one sums the salaries by department, one sums them by job, and one sums them for the whole report.

·         Certain requirements exist for building matrix reports:

·         You must have at least four groups in your data model.

·         One group must be a cross product group.

·         At least two of the groups must be within the cross product group. These groups furnish the “labels” of the matrix report.

·         At least one group must be a “cell” group; that is, it must provide the information related to the labels. The values from this group fill the cells created by the matrix.

·         It contains four groups–one group supplies the vertical labels (department numbers) and one group supplies the horizontal labels (job identifiers). These two groups are the children of the third group, called the cross product group, which creates the grid. The fourth group provides the values that fill in the grid.

·         Matrix reports are different from tabular reports because the number of columns is not known in advance; that is, the number of columns in your report is not determined by the number of columns you specify in your SELECT statement plus the columns you create yourself. The number of columns in your report depends on the number of values contained in the columns providing the horizontal and vertical labels. Thus, the report would automatically be extended if a new job function, called RECEPTIONIST, was added to the underlying data tables.

·         The queries used to select data for these 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.

·         You can create matrix reports with any number of queries.

·         This report uses the matrix layout style. You’ll modify some default settings to ensure that the column and row labels display correctly. You’ll also modify some field widths to ensure that the fields fit across the page.

Matrix with Group

About matrix with group reports

A matrix with group report is a group above report with a separate matrix for each value of the master group. For example, for each year (master) in the report below there is a unique matrix that contains only that year’s departments and jobs. This means that a summary of each job category may not line up with the values it summarizes because the position of each job category in the matrix may vary for each year.
A multi-user matrix with group report is similar to a nested matrix report in that it has more than two dimensions. For example, in the following report, notice that for each year there is a nested list of related departments.

Figure Sample matrix with group and nested matrix report

The advantage of using multiple queries is that you get a real break, or master/detail relationship, for the nesting groups (for example, notice that in the multi-user example above, Year 80 shows only record 20; with a single query, Year 80 would show all records whether or not they contain data for Year 80). If you want to suppress detail records that do not contain data for a particular master record, you must use multiple queries.