Scripting

Scripting is done with a variant of VBScript (which is not VBA or VB.NET) and is set in two places. The first is at the Report or Section level, the second directly on a control in its Text property. If the script is placed in a control’s Text property, make sure that the control’s Calculated property is set to True or the script will not be evaluated:

Where a script is placed is very important, particularly when using aggregating functions. Report sections are usually created in order, so some care has to be taken when writing scripts to ensure that certain values are available when the script referencing them is called.

A report has these properties to place scripts (click in the gray area of a report to select the whole report instead of a single control):

Whereas a section (such as Detail) only has these properties:

As the “On” in the name might imply, each represents an event that triggers the firing of the script assigned to it. For example, the OnOpen event for a report is triggered by the report being launched (by pressing the Generate Report button). In the OnOpen property global variables can be defined for use later, but the report has only just launched and therefore cannot do conditional formatting.

For conditional formatting a script needs to be set in the section that a control resides in (Grouped Header, Detail, etc.). This can be done in either the OnPrint or the OnFormat events. The difference between the two is that OnPrint represents when the underlying data set (i.e. the query) has returned values whereas OnFormat represents when the report’s controls have been populated with values.

The other location a script can be set is in the Text property of a control. This property is used to manipulate the text value that a control will display. For example, text can be added to a field value to make a more dynamic label. An example is shown below. A KPI percentage comes in and the label around it is formed through string manipulation and the Round function which rounds a number to a given number of decimals. Note that if extra text is not being added via script and the value is purely numeric the decimals on a number can be more easily manipulated with the Format property of a control.

Most of the commonly used available functions can be found by opening the script editor and looking through the categories: