21
Building a Report that Suppresses Labels

Figure 21-1 Suppressed labels report output

Text description of the illustration suppress_fin.gif

The report you will build in this chapter is a master/detail report that fetches a master record that has no associated details. In the sample output above, notice how the field labels for Department 40 do not display because no detail records were found. In this chapter, you will learn how to suppress the detail information for a single record, but allow the other master/detail records to display.

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 chapter demonstrates how to prevent these orphan labels from displaying in your report.

Data Relationships

• This report uses a master/detail data model. You'll 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'll create a summary to count the number of times this formula column returns a value. In the layout, you'll 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

This chapter will show you how to build a report that does not display field labels when there are no detail records. Instead, text will display that tells the user that no details records were retrieved.

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.

Table 21-1 Features demonstrated in this example

Feature	Location
Manually create two queries and link them in the Data Model view.	Section 21.2, "Create the data model with two linked queries"
Add a formula column and a summary column in the Data Model view.	Section 21.3, "Create a formula column and a summary column"
Use the Report Wizard to create a simple layout.	Section 21.4, "Create the report layout"
Add a format trigger that suppresses labels when no detail records are retrieved.	Section 21.5, "Add a format trigger to suppress labels"
Create boilerplate text.that displays when no records are displayed.	Section 21.6, "Add text to display when no records display"

21.1 Prerequisites for this example

To build the example in this chapter, you must have access to the EMP and DEPT schema, which is provided by default with the Oracle9i database. The user ID and password for accessing this schema is scott/tiger.

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

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

2. In the Welcome or New Report dialog box, select Build a new report manually, then click OK.

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

4. In the SQL Query Statement field, 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.

5. Click OK.

   Note: If you are not already connected to a database, you will be prompted to connect to the database when you click 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.

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

7. Follow the steps above to create another query named Q_EMP, with the following code:

   SELECT DEPTNO, ENAME, JOB FROM EMP
   ORDER BY ENAME
   

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

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

Text description of the illustration suppress_dm.gif

10. Save your report as suppresslabels_<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. 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:

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

2. Click in the Q_EMP query, under the JOB column to create a formula column.

3. Double-click the new formula column object (CF_1) to display the Property Inspector, and set 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.

4. 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;
   

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

6. 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:

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

2. Click in the Q_DEPT query, under the LOC column to create a summary column.

3. Double-click the new summary column object (CS_1) to display the Property Inspector, and set 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

Text description of the illustration suppress_dmcols.gif

4. Save your report as suppresslabels_<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:

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 Group Above.

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

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

6. On the Labels page, make any desired changes to the labels.

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

Text description of the illustration suppress_prev.gif

8. Save your report as suppresslabels_<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:

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

Text description of the illustration suppress_on.gif

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

3. 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;
   

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

5. When there are no compiling errors, click Close.

6. 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:

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

2. Click the Text tool in the tool palette.

3. Draw a rectangle above the two fields F_ENAME and F_JOB to create a new boilerplate text object.

4. Click in the boilerplate text object, and type "No detail records retrieved."

5. Select the text, then choose Format > Font.

6. In the Font dialog box, choose Arial, then click OK.

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

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

Text description of the illustration suppress_bfw.gif

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

Text description of the illustration suppress_bfa.gif

Note: You'll 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.

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

11. Click the Fill Color tool in the tool palette, and change the fill color to light blue, so that it matches the template.

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

Text description of the illustration suppress_fin.gif

Note: Notice how the record for Department 40 shows the boilerplate text you added, and the field names for the record are suppressed.

13. 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 Reports Builder Online Help, which you can access in two ways:

• From the Oracle Technology Network (http://otn.oracle.com/products/reports/), click Documentation and navigate to the Reports Builder Online Help for the most recent, hosted online help.

• From Reports Builder, choose Help > Help Contents.

  Note: You can replace your Reports Builder online help with the most recent update by downloading the latest online help set available on the Oracle Technology Network (http://otn.oracle.com/docs/products/reports/). Instructions for replacing your help file are included in the readme.txt in the download file.