Additional Macro Features

Active Print Conditions

Active Macros offer a method of performing print conditions that require more complex calculations. It is possible to add a function to the Active Macros that will be called prior to each formatting on the region. The function name is composed of the region name + "_CanPrint()."

Note that the standard print condition can still be used and if it fails then the Active print condition will not be executed.

For example, assume you wanted to perform a database query to determine if the Detail region should print. You could add the following function to the macros to serve as the print condition:

Function Detail_CanPrint()
Dim myCursor
Set myCursor = CreateSQLCursor
myCursor.sql = "select count(*) nRecords from si_obj_dtl _ where si_id = '" & OJB_ID & "'"
myCursor.MoveNext
if myCursor.nRecords > 0 then
Detail_CanPrint = vbTrue
else
Detail_CanPrint = vbFalse
end if
End Function

This function declares an SQLCursor that then fetches the number of records from the imaginary table si_obj_dtl where the si_id is equal to the value in OBJ_ID (a report field). If the number of records is greater than 0 then the function returns vbTrue to indicate that the region should be printed.

Another use of the _CanPrint print condition is to execute macros that you would like to execute regardless of whether you want the region to format. CDD by default does not execute the macros of a region that is not going to be formatted. By performing the macros in the print condition rather than the regions subroutine and then returning true or false, you ensure that the code is executed, and the print condition enforced.

Active Script Drills

By defining a subroutine is the reports script that is composed of the item name + "_DoubleClick" you can perform operations when the field is double-clicked. A possible use of this would be to send e-mail to a person when their e-mail address is double-clicked in a report.

The following example will send mail using Microsoft Outlook:

Sub ID_DoubleClick
sMsg = "Would you like to send e-mail to " & EMPMSTR.NAME
If MsgBox(sMsg, vbYesNo, "Send E-Mail?") = vbYes Then
Call SendMail()
End If
End Sub
Function SendMail()
If Len(EMPMSTR.E_MAIL) <= 0 Then
MsgBox "No e-mail address for " & EMPMSTR.NAME
Exit Function
End If
Set oOutlook = CreateObject("Outlook.Application")
Set myItem = oOutlook.CreateItem(0)
myItem.Subject = "Meeting Request"
Set recips = myItem.Recipients
recips.Add(EMPMSTR.E_MAIL)
If Not recips.ResolveAll Then
For Each recip In recips
If Not recip.Resolved Then
MsgBox recip.Name
End If
Next
End If
'This is where the content of the message should be placed
myItem.Body = "Testing"
Set ins = myItem.GetInspector()
ins.Display (False)
End Function

Suppressing Default Comments in Subroutines

Use registry key "Hide Macro Default Comments" to suppress default comments being written into the Macro when a sub region is created.

Create registry key

Path: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\bitech\Report Processing Service
Name: Hide Macro Default Comments
Type: DWORD
Value: 1

To read comments to the subroutine and enter 0 in the registry key instead of 1.

Accessing the Active Macros of a Report's Query

Methods and data members declared in the macros of a reports information category can be accessed at runtime by prefixing the function or member with reserved word "Query." This is useful for writing functions that reside at the category level and can be used across multiple reports. It allows the category designer to write macros that make sense for the information category.

For example, suppose an information category has the following macros:

Sub OnEachRow
'This sub is called each time a new data row is fetched
End Sub
Function GetCSZ()
GetCSZ = EMPMSTR.CITY & ", " & EMPMSTR.STATE_CD & " " & EMPMSTR.POSTAL_CODE
End Function

A report based upon this information category could call the function GetCSZ by using the following syntax:

localCSZ = Query.GetCSZ

Automating Other Applications

Many applications support what is known as an Automation interface, which allows other applications to control the behavior of the application to accomplish some task. We saw an example of this above when CDD used Microsoft Outlook to send e-mail. In addition to Outlook, Microsoft Excel supports automation and can be automated using Active Macros to export data directly to an Excel spreadsheet. The following brief example will show how CDD can export directly to Excel.

The macro code below will create an instance of Microsoft Excel when the report is initialized. When each detail row is formatted the fixed asset id, description and purchase amount will be written out to the active Excel worksheet.

Dim excelApp
Dim rowIndex
rowIndex = 1
Sub Initialize
Set excelApp = CreateObject("Excel.Application")
If IsObject(excelApp) Then
excelApp.Workbooks.Add
excelApp.visible = vbTrue
End If
End Sub
Sub Detail
If IsObject(excelApp) Then
excelApp.Cells(rowIndex, "A") = FAIDNT.FAID
excelApp.Cells(rowIndex, "B") = FAIDNT.F_DESC
excelApp.Cells(rowIndex, "C") = FAIDNT.PURCHAMT
rowIndex = rowIndex + 1
End If
End Sub

Note: ExcelApp is checked to ensure that it is a valid object prior to writing values to the cells.
Most popular applications support automation and the documentation for the application is question should be consulted for information on how to manipulate the objects that are exposed.