OnFormat vs. OnBeforePrint in Active Reports

Recently I was asked to assist with an internal report for the Sales and Marketing Department. It might interest you to know that we use Shelby v.5 for our own data management purposes, just like our customers do. The report was put together in ShelbyQUERY and the Report Designer. The report was mostly working fine before I was looped into the project except that there were a few calculated values that were not working as expected. Specifically, the report needed some calculated variances of bound controls that had been aggregated using the SummaryType property of ddSMSubTotal.

In order to calculate the variances, I wrote a VBScript to pull the values for the aggregated Bound Controls in the GroupFooter1 section and to do the necessary math, returning the results of the calculation as the caption property of a label. I put the script to trigger during the OnFormat event, which is the only event I had ever used for Active Report scripts in the body of the document. The process seemed straightforward enough, except that the calculated values that were returned by the script were based on the last scalar value of the field, not the aggregated value.

I almost started down the path of creating script for the Detail section that would sum up the values by leveraging the caption of an invisible label as a place to hold a running sum. But before I did that, I took a step back and looked at other options. One of the options I researched was the event choice for when the script activates. I discovered that the OnBeforePrint event occurs right before the object values are committed to the canvas, but after the object values have been calculated.

That was the "Aha!" moment for me. I changed the script from occurring in the OnFormat event to the OnBeforePrint event. Voila'. The calculations came out based on the aggregate values, just as I wanted to happen. So I learned the following rules:

• Use OnFormat to work with bound controls before their values are aggregated.
• Use OnBeforePrint to work with bound controls after their values are aggregated.

If I am not working with aggregated values, I will still prefer the OnFormat event, if for no other reason than it is the default event option.

Incidentally, I also discovered in this process that the way to explicitly reference the value of a bound control is to use its DataValue property. In the past I have used the implicit reference by merely using the bound control's name to stand in for its value. The following two lines of code are functionally identical:

objValue = rpt.Sections("Detail").Controls.Item("Field1")

objValue = rpt.Sections("Detail").Controls.Item("Field1").DataValue

However, because using explicit references is generally better practice than using implicit ones, from this point forward I will give preference to including the DataValue property as part of any reference to the value of a bound control.