21 Building a Report that Suppresses Labels

In this chapter, you will learn about reports that suppress the display of field labels. By following the steps in this chapter, you can generate the report output shown in Figure 21-1. Notice how the field labels for Department 40 do not display because no detail records were found

Figure 21-1 Suppressed labels report output

Description of "Figure 21-1 Suppressed labels report output"

Concepts

A default master/detail report must print a detail label or field, even if there are neither fetched detail records nor values for user-created columns. This example demonstrates how to suppress the detail information for a single record, but allow the other master/detail records to display.

Data Relationships

This report uses a master/detail data model. You will also create a formula column in the detail group whose sole function is to return a value; this ensures the detail group contains at least one column that will always return a value each time a detail record is returned (as opposed to, for example, a null value).
You will create a summary to count the number of times this formula column returns a value. In the layout, you will create a format trigger that references the summary to determine if the detail labels should be displayed. Doing so provides a reliable method for determining the existence of detail records.

Layout

This report uses a Group Above layout style with modifications.

Example Scenario

In this example, you will build a report that does not display field labels when there are no detail records. Instead, text displays that notifies the user that no detail records were retrieved.

As you build this example report, you will:

Create the data model with two linked queries manually in the Data Model view.
Create a formula column and a summary column in the Data Model view.
Create the report layout using the Report Wizard.
Add a format trigger to suppress labels when no detail records are retrieved.
Add text to display when no records display.

To see a sample report that suppresses labels, open the examples folder named suppresslabels, then open the Oracle Reports example named suppressinglabels.rdf. For details on how to open it, see "Accessing the Example Reports" in the Preface.

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

21.2 Create the data model with two linked queries

The steps in this section will show you how to build a simple data model with two queries in a master/detail relationship.

To create a data model

Launch Reports Builder (or, if already open, choose File > New > Report).
In the Welcome or New Report dialog box, select Build a new report manually, then click OK.
In the Data Model view that displays, click the SQL tool in the tool palette, then click in an open area of the Data Model view to display the SQL Query Statement dialog box.

In the SQL Query Statement dialog box, type the first SELECT statement:

SELECT * FROM DEPT
ORDER BY DEPTNO

Note:

You can enter this query in any of the following ways:

Copy and paste the code from the provided text file called suppresslabels_code.txt into the SQL Query Statement field.
Click Query Builder to build the query without entering any code manually.
Type the code in the SQL Query Statement field.

Also note that if you have not installed the Pictures table into the sample schema, you will not be able to create this query.

Click OK.

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

When the query displays in the Data Model view, right-click the query name (Q_1), then choose Property Inspector from the pop-up menu to set the following property:
- Under General Information, set the Name property to Q_DEPT.
Follow the steps above to create another query named Q_EMP, with the following SELECT statement:
```
SELECT DEPTNO, ENAME, JOB FROM EMP
ORDER BY ENAME
```
In the Data Model view, click the Data Link tool in the tool palette.
Drag a link between DEPTNO in Q_DEPT and DEPTNO1 in Q_EMP. Your data model should now look like this:

Figure 21-2 Data Model view of the Suppress Labels report

Description of "Figure 21-2 Data Model view of the Suppress Labels report"
Save your report as suppressinglabels_your_initials.rdf.

21.3 Create a formula column and a summary column

The steps in this section will show you how to add a formula column to the Q_EMP (or detail) query you built in the previous section that will return a value every time a detail record is returned. You will then add a summary column to the Q_DEPT (or master) query that will count the number of times this formula column returns a value.

Before you create either of these columns, you may want to expand the size of your groups for better visibility. To do so, select the yellow group box. Click the bottom frame, then drag it down about 0.25 inches (0.5 centimeters). If you do this for both groups, you will have enough room to add your new columns.

21.3.1 Create a formula column in the detail query

This section will show you how to create a formula column that will return a single value.

To create a formula column:

In the Data Model view, click the Formula Column tool in the tool palette.
Click in the Q_EMP query, under the JOB column to create a formula column.
Double-click the new formula column object (CF_1) to display the Property Inspector, and set the following properties:
- Under General Information, set the Name property to DETAIL_VAL.
- Under Placeholder/Formula, click the PL/SQL Formula property field to display the PL/SQL Editor.
In the PL/SQL Editor, use the template to enter the following PL/SQL code:
```
function DETAIL_VALFormula return Number is
begin
     return(1);
end;
```
Click Compile.

Tip:
If you receive errors when compiling, compare your code against the code provided. You can also simply copy and paste the code from suppresslabels_code.txt.
When the code is compiled, click Close.

21.3.2 Create a summary column in the master query

The steps in this section will show you how to create and customize a summary column in the master query that will depend on the information returned by the formula column.

To create a summary column:

In the Data Model view, click the Summary Column tool in the tool palette.
Click in the Q_DEPT query, under the LOC column to create a summary column.
Double-click the new summary column object (CS_1) to display the Property Inspector, and set the following properties:
- Under General Information, set the Name property to COUNT_DETAIL.
- Under Summary, set the Function property to Count, set the Source property to DETAIL_VAL, and set the Reset At property to G_DEPTNO.
Your data model now contains both the formula and summary columns, and should look like this:

Figure 21-3 Data Model view with formula and summary columns

Description of "Figure 21-3 Data Model view with formula and summary columns"
Save your report as suppressinglabels_your_initials.rdf.

21.4 Create the report layout

In this section, you will create a default layout for your report. You will then add all the necessary layout objects for your checks.

21.4.1 Create the initial layout of your report

To create the initial 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 Group Above.
On the Groups page, click the following groups in the Available Groups list and click Down to specify the Print Direction and move them to the Displayed Groups list:
- G_DEPTNO
- G_DEPTNO1
On the Fields page:
- Click the double right arrows (>>) to move all of the fields to the Displayed Fields list.
- In the Displayed Fields list, click DEPTNO1 then click the left arrow (<) to move this field back to the Available Fields list.
- Do the same for the COUNT_DETAIL and DETAIL_VAL fields.
On the Labels page, make any desired changes to the labels.
On the Template page, select Predefined Template and click Blue, then click Finish to display your report output in the Paper Design view. It should look something like this:

Figure 21-4 Paper Design view for the suppress labels report

Description of "Figure 21-4 Paper Design view for the suppress labels report"
Save your report as suppressinglabels_your_initials.rdf.

21.5 Add a format trigger to suppress labels

The steps in this section will show you how to add a format trigger to your report that will prevent labels from being displayed for records will null values.

To create a format trigger:

In the Object Navigator, under the Paper Layout node, navigate to Main Section > Body > M_G_DEPTNO_GRPFR > R_G_DEPTNO > M_G_DEPTNO1_GRPFR > M_G_DEPTNO1_HDR. Or, use the Find field in the Object Navigator to find M_G_DEPTNO1_HDR.

Tip:

See the image below for an example of where the frame is located in your Object Navigator. When you select the frame name in the Object Navigator, the corresponding frame will be selected in the Paper Layout view, as well.

Figure 21-5 Navigating to M_G_DEPTNO1_HDR

Description of "Figure 21-5 Navigating to M_G_DEPTNO1_HDR "

Double-click the properties icon next to M_G_DEPTNO1_HDR to display the Property Inspector, and set the following properties:
- Under Advanced Layout, double-click the Format Trigger property field to display the PL/SQL Editor.

In the PL/SQL Editor, use the template to enter the following PL/SQL code:

function M_G_DEPTNO1_HDRFormatTrigger return boolean is
begin
  if :count_detail=0 then
     return (FALSE);
  else
     return (TRUE);
  end if;
end;

Click Compile.

Tip:
If you receive errors when compiling, compare your code against the code provided. You can also simply copy and paste the code from suppresslabels_code.txt.
When there are no compiling errors, click Close.
Save your report as suppresslabels_your_initials.rdf.

21.6 Add text to display when no records display

The steps in this section will show you how to add boilerplate text to your report layout that will display when no records are retrieved.

To add boilerplate text:

In the Paper Layout view, click the Confine Off and the Flex Off buttons in the toolbar.

Note:
To adjust the way the boilerplate objects display in your resulting report, you must turn off Confine and Flex modes before you create the objects.
Click the Text tool in the tool palette.
Draw a rectangle above the two fields F_ENAME and F_JOB to create a new boilerplate text object.
Click in the boilerplate text object, and type "No detail records retrieved."
Select the text, then choose Format > Font.
In the Font dialog box, choose Arial, then click OK.
While the Paper Layout view still displays, click the Object Navigator and position the two windows so that you can see them side-by-side.
In the Object Navigator, navigate to M_G_DEPTNO1_GRPFR, and select these objects using CTRL-click:
- R_G_DEPTNO1
  
  F_ENAME
- F_JOB
Your Object Navigator should look like this:

Figure 21-6 Selected objects in the Object Navigator

Description of "Figure 21-6 Selected objects in the Object Navigator"
In the Paper Layout view, click the Confine On button in the toolbar.

Choose Layout > Bring to Front.

Note:

If this menu option is greyed out, click the title bar of the Paper Layout view, but do not click on the canvas itself. This menu option is only available when the Paper Layout view is active.

By choosing this menu option, Reports Builder will display the records in front of the boilerplate text you just created. If there are no records, the boilerplate text will display.

The Object Navigator should now look like this:

Figure 21-7 Selected objects brought forward in the Object Navigator

Description of "Figure 21-7 Selected objects brought forward in the Object Navigator"

Note:

You will notice that the boilerplate text, B_1, is now located directly under the parent frame, M_G_DEPTNO1_GRPFR. This placement means that the records displayed by the objects in the repeating frame, R_G_DEPTNO1, will display in front of the boilerplate text. The boilerplate text, which says that no detail records were retrieved, only displays when no records are present.

Now, we need to change the fill color of the parent frame, so that you cannot see the boilerplate text behind the detail records.

In the Object Navigator, click M_G_DEPTNO1_GRPFR so that it is the only object selected. In the Paper Layout view, you should see this same frame selected.
Click the Fill Color tool in the tool palette, and change the fill color to light blue, so that it matches the template.
Click the Paper Design button in the toolbar to run and display your report in the Paper Design view. Your report should look something like this:

Figure 21-8 Final Paper Output of the Suppressing Labels Report

Description of "Figure 21-8 Final Paper Output of the Suppressing Labels Report"

Note:
Notice how the record for Department 40 shows the boilerplate text you added, and the field names for the record are suppressed.
Save your report as suppresslabels_your_initials.rdf.

21.7 Summary

Congratulations! You have successfully built a report that suppresses labels when no data is retrieved. You now know how to:

manually create and link two queries.
create a formula column and a summary column.
create a default layout in the Report Wizard.
create a format trigger that suppresses the field labels when no detail records are displayed.
added boilerplate text and manipulated the layout.

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