Totalling a field in a Conditionally Suppressed Section on a Report

I was recently asked how can you total a field in a report when that field is in a conditionally suppressed section.  You might ask why should this be difficult.  The reason for the complexity is this....

Suppressing a section just stops it being displayed at the last moment and does not exclude the section from other processing by the report writer.

The table below demonstrates an example with three rows for data in a body section with a summed value in the footer section.  In the report layout, there is a calculated field which returns an empty value for the second row of data based on a given business rule.  You can use this calculated field in the Tools >> Section Options window to suppress the body section using the Suppress When Field Is Empty option and the Calculated Field: drop down list.

Section	Suppressed	Data Field
Body - Record 1	No	1
Body - Record 2	Yes	2
Body - Record 3	No	3
Footer Summed	-	6

The report output would look something like:

Record 1: Value: 1
Record 3: Value: 3
-----------------------------
Total Value:        6

As you can see 1 + 3 does not equal the total of 6. This is because even though we cannot see Record 2, it is still included in the summed value in the footer section.

To resolve this situation, we need to create a conditional calculated field which will have a conditional expression checking if the calculated field used for suppressing the body is its empty (depending on data type) and then having a zero value of the true case and the actual field data for the false case.

For example:   Currency Conditional Calculated Field

Conditional Expression: (c) Suppress Body = 0
True Case: 0.00000
False Case: Table.Field

The effect of this is shown in the updated table below:

Section	Suppressed	Data Field	Calc Field
Body - Record 1	No	1	1
Body - Record 2	Yes	2	0
Body - Record 3	No	3	3
Footer Summed	-	6	4

Once you have created the calculated field, use this field in the footer when you create your subtotal.

The report output would now look something like:

Record 1: Value: 1
Record 3: Value: 3
-----------------------------
Total Value:        4

This correctly shows a total of 4.  Even though Record 2 is still included in the summing, it was assigned a zero value in the calculated field and so is not included.  Problem solved.

A point to discuss is the difference between a restriction and a suppression.  If you use a restriction in the report definition window the restricted data is removed and never included in the report.  This means that techniques like the one above are not required.  Using a suppression, means that the data is included in the report, but prevented from printing. 

So why not use a restriction every time.  The issue here is that restrictions can only work with the table data and do not have the ability for complex logic using conditional calculated fields, report writer functions or legends.  There are times where it is not possible to achieve what is desired using only restrictions.

Note: If you use Visual Basic for Applications (VBA) to suppress a section, you will need to use similar logic in your VBA code to total up the data from the non-suppress sections, so that you can have the correct total value displayed on the report. 

Hope you find this helpful.

David