April 2011

Tech Info

How To Suppress Repeating Detail Data In A Host Report

In report formatting, it’s common to need to suppress repeating detail— to print a value only if it changes. In many reports, you’ll see data repeat because of the many database files used by the underlying view. In the Host Report examples that follow, a customer has many orders. When the report prints, the customer number repeats for each order, which clutters the report. Let’s see just how easy it is to suppress the detail and clean up the report.

Before…

Here’s how the report looks before we suppress the customer number:

 After...

Now, here’s the same report with one customer number per group:

What’s The Trick?

The solution isn’t tricky, but we should review some background information first. This report uses the following view:

SELECT cusno, ordno, line#, quano
FROM sequelex/ordline, sequelex/ordhead
JOIN ordno.1=ordno.2
ORDER BY cusno, ordno, line#

The view is important to the solution because of the relationship between the fields in the ORDER BY clause and the formats (or level breaks) created in the Report Designer (and in the final report). You notice that when you create a report, each field in the ORDER BY clause generates a level break in the report. We can use these level breaks to control when a field value prints.

In the example view, the Report Writer evaluates the ORDER BY clause in reverse order and assigns a break level of 1 to line#, 2 to ordno, and 3 to cusno. (Note: You can see these assignments if you open the Report Writer Calculation Editor. They display there because you also can use them in calculated report fields.)

From the Design menu, select Insert\Expression. In the Break Level drop-down list, the ORDER BY fields are shown (in reverse order) with their break level numbers.

The Report Designer

To suppress the customer number (cusno) so that it only prints when the customer number changes, you must change the field’s properties and set the break level to the cusno break (3).

In the Report Designer, right-click the cusno field and select Properties from the drop-down menu.

In the Report Field Properties window, use the Break Level drop-down menu to select the CUSNO break.

Select the Print on Overflow option so that if the detail for a customer overflows the current page, the field prints on the first line of the next page.

Click OK to apply the change.

That’s all there is to it. Now, the report suppresses the repeating customer number as shown previously in the After... example. Try using this technique in your detail reports to clean them up and make them easier to use.

More Information

You can do much more with ViewPoint Host reports than we covered here. See the SEQUEL ViewPoint User Guide or the online help for more information about the ViewPoint Report Writer.

By Steven Smith, Technical Consultant