Automatic generation of batch reports at the end of the batch using InTouch

Applies to

• ActiveFactory 9.1 and 9.2
• InTouch 9.0 and higher
• IndustrialSQL Server 8.0 or 9.0 deployed and running

Introduction

ActiveFactory 9.1 and 9.2 contains a component called WorkbookRunner which enables automatic generating of a report snapshot based off a predefined report template.
Examples of situations when this functionality may be desired are:

• Automatic generation of batch reports
• Automatic generation of snapshot on-demand reports

This Tech Note details the procedure to automatically generate batch reports in Wonderware InTouch. Also a VBScript program example is provided to illustrate report creation from the operating system level.

Procedure

Required elements of the procedure are:

1. Existing batch report template created for use with IndustrialWorkbook (see Wonderware PacWest Tech Note 14 for an example report).

2. InTouch script that will generate a report instance using ActiveFactory’s WorkbookRunner component.

3. Optional – Generating batch reports using VBScript program

Report template preparation

1. Create 2 folders on your hard drive, which the InTouch script provided below will depend upon:

• c:\Templates – which will contain the report template(s),
• c:\Reports – which will contain generated reports.

2. Prepare a batch report template similar to the one described in the Wonderware PacWest Tech Note 14 and open it in Excel.

3. Select the field that contains entered batch number (in the example shown in the Wonderware PacWest Tech Note 14 it’s cell B4 in the Reactor Report tab) and name it AFBindingBatchNumber using the Name Box in the upper left part of the Excel window (see the screenshot below for reference, the Name Box is highlighted in yellow).

Note: the name above consists of the keyword Binding and a custom label affixed (BatchNumber is this case).

4. Save the report template as c:\Templates\BatchReport.xls and close Excel.

InTouch script

1. Create the script described below using InTouch WindowMaker

Note: depending on the specific requirement, it can be a Data Change script, a Condition script or an Action script. In this case it’s a Data Change script that will trigger whenever batch number tag changes and generate a batch report instance for the batch that just finished (create a new data change script using Special Scripts Data Change menu item)

Script text:

DIM mBatchNumber AS MESSAGE;

{calculate batch number that just finished}
mBatchNumber = StringFromIntg(BatchNumber - 1, 10);

{create WorkbookRunner object instance}
OLE_CreateObject(%oRunner, "ArchestrA.HistClient.UI.aaHistClientWorkbookRunner");

{full syntax of the RunReport2 function}
{[Result=] aaHistClientWorkbookRunner.RunReport2(
    message inputFile, message outputFile,  message outputPrefix, integer outputFormat,
    message tagString, integer NSFolderKey, message nameSpace,    integer dateMode,
    message startDate, message endDate,     integer duration,     message customFilters);}

{report template}
InputFile = "C:\Templates\BatchReport.xls";

{report instance file name}
OutputFile = "C:\Reports\Batch_" + mBatchNumber;
{The name of the output file that will be generated, including the full path.
If this parameter is set to an empty string ( " " ),
then a file name will be generated automatically according to the following formula:
OutputFile = \\Input File path\OutputPrefix + InputFile + year + month+ day
+ _ + hour + minute + second}

OutputPrefix = "_";
OutputFormat = 1; {save as .htm}

{start and end date/time, not used here so it's OK to have them with static values}
StartDate = "5/1/2007 00:00:00";
EndDate = "5/1/2007 23:59:59"; {EndDate has to be some time greater than StartTime}
{StartDate = StringFromIntg($Month, 10) + "/" +
             StringFromIntg($Day,   10) + "/" +
             StringFromIntg($Year,  10) + " 00:00:00";}
{EndDate = StringFromIntg($Month,   10) + "/" +
           StringFromIntg($Day + 1, 10) + "/" +
           StringFromIntg($Year,    10) + " 23:59:59";}

{Custom Filters - this is how we pass batch number information}
CustomFilters = "BatchNumber=" + mBatchNumber;
{The format for the string is as follows: <name>=<value>.
To pass more than one name-value pair, join them with ampersands.
For example: <name>=<value>&<name>=<value>}

{make Excel visible only for testing/demo purposes, for production systems make it 0}
%oRunner.ExcelVisible = 1;  

{the core function that generates the report snapshot}
ResultString = %oRunner.RunReport2(InputFile, OutputFile, OutputPrefix, OutputFormat,
                                   "",        0,          "",           0,
                                   StartDate, EndDate,    0,            CustomFilters);

{release WorkbookRunner object from memory}
OLE_ReleaseObject(%oRunner);

The script requires the following InTouch tags to be created:

Also, the script assumes there is a tagname within the InTouch application called BatchNumber which holds the current batch number. For simplicity, we assume the batch number is an incremental integer number. The script will work as is with DemoApp1 demo application provided with InTouch 9.0 and 9.5.

Save the script and switch to WindowViewer to observe results.

Results

The script will trigger every time the value of BatchNumber tag changes and it will launch Excel and open c:\Templates\BatchReport.xls file. Initially you will see all values within the Excel’s spreadsheet as they were when the report template was initially saved. After the initial formula refresh, Excel will update BatchNumber in cell B4 and recalculate all formulas again, then it will save the report in the c:\Reports\Batch_ in HTML format.

The snapshot reports are html files that can be opened using a web browser (see the screenshot below).

They also preserve all Excel’s formulas so they can still be edited using Excel (to do so from the Windows Explorer level right click on the html file and select Edit from the right click context menu).

Optional – Generating batch reports using VBScript program

Batch reports may be run in a similar way using VBScript. Copy the following code and save it to a file named BatchReport.vbs.

' COMMON USAGE:  wscript BatchReport.vbs batchNumber
' SILENT USAGE:  wscript //B BatchReport.vbs batchNumber
' CONSOLE USAGE: cscript BatchReport.vbs batchNumber

' Create the needed variables
Dim args,       batchNumber,   oRunner,      inputFile, _
    outputFile, outputPrefix,  outputFormat, startDate, _
    endDate,    customFilters, resultString

' Get the command line arguments
Set args = WScript.Arguments

' Get the batch number from the arguments
batchNumber = CInt(args(0))

' Create the WorkbookRunner object instance
Set oRunner = CreateObject("ArchestrA.HistClient.UI.aaHistClientWorkbookRunner")

' Batch report template file
inputFile = "C:\Templates\BatchReport.xls"

' The name of the output file that will be generated, including the full path.
outputFile = "C:\Reports\Batch_" & batchNumber
' If this parameter is set to an empty string (""),
' then a file name will be generated automatically according to the following formula:
' outputFile = \\inputFile\outputPrefix & inputFile & year & month & day
' & _ & hour & minute & second

outputPrefix = "_"
outputFormat = 1 ' save as .htm

' start and end date/time. Not used here so it's OK to have them with static values
startDate = "5/1/2007 00:00:00"
endDate = "5/1/2007 23:59:59" ' endDate must be a time greater than startDate
' startDate = Month(Date) & "/" & Day(Date) & "/" & Year(Date) & "00:00:00"
' endDate   = Month(Date) & "/" & Day(Date) & "/" & Year(Date) & "23:59:59"

' This is how we pass batch number information
customFilters = "BatchNumber=" & batchNumber
' The format for the string is as follows: <name>=<value>.
' To pass more than one name-value pair, join them with ampersands.
' For example: <name>=<value>&<name>=<value>

' Make Excel visible only for testing/demo purposes. For production systems set it to 0
oRunner.ExcelVisible = 1

' The core function that generates the report snapshot
resultString = oRunner.RunReport2(inputFile, outputFile, outputPrefix, outputFormat, _
                                  "",        0,          "",           0, _
                                  startDate, endDate,    0,            customFilters)
' Full syntax of the RunReport2 function:
' [result =] aaHistClientWorkbookRunner.RunReport2(
'     String inputFile, String  outputFile,   String  outputPrefix, Integer outputFormat, 
'     String tagString, Integer NSFolderKey,  String  nameSpace,    Integer dateMode, 
'     String startDate, String  endDate,      Integer duration,     String  customFilters)

' Output the results
WScript.Echo(resultString)

' Release the WorkbookRunner object from memory
Set oRunner = Nothing

Now you may run the script from the command line according to the usage comments at the beginning of the code. You can also run it from any application or function that can access the command line. For example, with Transact-SQL you can use the built-in stored procedure xp_cmdshell (http://msdn.microsoft.com/en-us/library/ms175046.aspx):

DECLARE @batchNumber sysname, @command sysname
SET @batchNumber = '50'
SET @command = 'wscript //B BatchReport.vbs ' + @batchNumber
EXEC master..xp_cmdshell @command