SUMMARY

This Tech Note details the procedure to identify and clear APPCRASH events that appear in the Windows Event Viewer for System Platform components. This can impact any component of System Platform and look like the following:

• Windows Error Reporting: Event Name: APPCRASH P1: GDIWebServer.exe
• Windows Error Reporting: Event Name: APPCRASH P1: AELicServer.EXE
• Windows Error Reporting: Event Name: APPCRASH P1: aahIDAS.EXE
• Windows Error Reporting: Event Name: APPCRASH P1: aahStorage.exe

This issue is caused when AVEVA services that are running rely on other AVEVA services that have been stopped. For example, when performing a software install, such as a Service Pack or Patch upgrade,  “Stop Services” is selected to allow the installation to continue.  If some AVEVA services stay on “Running” with other AVEVA services on “Stopped”, the running services may generate the APPCRASH errors.

These APPCRASH events repeat in the same pattern along with other Windows related APPCRASH events. It is also worth noting that at times excessively large logs error reports can continue to report into Event Viewer because the log files failed to “report”, usually in instances where the machine is not connected to the internet. However, the projects/applications can still be developed and ran at runtime, data values continue to come in, alarms can be acknowledged, etc. The system continues to run as expected. Additionally, LogViewer in OCMC/SMC does not report any errors associated with the APPCRASH events.

APPLIES TO

• Application Server
• InTouch HMI
• Historian Server
• Historian Clients
• License Server/Manager

PROCEDURE

To correct the issue, perform a Disk Cleanup and clear the Windows Error Reporting Queue.

1. Open Windows Explorer.  Right-click on the C: drive and select Properties.

2. Under the General tab, select Disk Cleanup.

3. Check Windows error repots and feedback diagnostics, the click OK.

4. APPCRASH events should stop appearing in the Windows Event Viewer logs.

SUMMARY

AVEVA Historian Client makes some of its applications and functionality available as .NET controls that can be imported into your Galaxy or InTouch application to be used as a Client Control.  Imported Client Controls can then be embedded into symbols for use in your InTouch application.

The AVEVA Historian Client Trend application can be embedded in an Industrial Graphic by using the Historian Client Trend Control (aaTrendControl).  Through the use of custom properties and scripting, you can customize the appearance and behavior of this control, such as whether or not the Tag Picker or Time Selector panels are enabled, the default duration of the displayed trend, and which tags to automatically add to the trend when the symbol is shown.

This tech note will demonstrate how to build a Historian Client Trend symbol that can be customized by modifying the values of custom properties when it is used.

APPLIES TO

• AVEVA InTouch versions 10.0 and later

PROCEDURE

Import the Historian Client Trend Control object

Depending on the software version you are using, it may be necessary to import the Historian Client Trend .NET control into your Galaxy or InTouch application.  If your Galaxy already includes the aaTrendControl object in your Graphic Toolbox, you can skip ahead to Create the Trend Symbol.

1. In the IDE, select Galaxy > Import > Client Control. If you are using stand alone InTouch, select File > Import > Client Control.

2. In the browser window, navigate to C:\Program Files (x86)\Common Files\ArchestrA. Select the aaHistClientTrendControl.dll file and click Open.

3. Once imported, the aaTrendControl object will appear in the Graphic Toolbox.

Create the Trend Symbol

4. Create a new Industrial Graphic symbol and give it an appropriate name (we have used HistClientTrend). Open the symbol in the symbol editor.

5. Select Edit > Embed Industrial Graphic  In the browser window, locate and select the aaTrendControl object and click OK.  Click on the canvas to embed the object in your symbol.

6. Rename the embedded element to Trend. This is the name we will use to refer to this control in the scripting.

Create the Custom Properties

In this section, you will create a set of Custom Properties that you can use to configure the behavior and appearance of the trend control when you use it.

7. Right-click the canvas and select Custom Properties. In the Custom Properties window, add the following properties:

8. Click OK to close the Custom Properties window

Apply Bindings to .NET Properties

Next, you will bind certain properties of the Trend control to some of the Custom Properties you just created.

9. Double-click the Trend element to open the Edit Animations window.
10. Select Data Binding and enter the references for the properties listed below:

11. Click OK go close the Edit Animations window.

Add Scripting to Automatically Add Tags to Trend

Finally, you will add an On Show script that will automatically connect the Trend control to your Historian Server and add up to 8 tags to the display.

12. Right-click the canvas and select Scripts to open the Edit Scripts window.

13. Select Predefined Scripts. Set the Trigger Type to On Show and enter the following script:

NOTE:  The “Trend” reference in the script is the name of the embedded control (which we set to “Trend” in Step 6).  If you gave the control element a different name, substitute “Trend” in the above script with the name of your control.

14. Click OK to close the script editor.
15. Save and Close the symbol.

Use in Runtime

Now that your symbol has be built, you can embed it into an InTouch window or another Industrial Graphic and customize it using the custom properties.  In our example, we will embed the graphic directly into an InTouch window.

16. Open your InTouch application in WindowMaker.
17. Create a new window and place your trend symbol (HistClientTrend in our example) into the window.

18. Right-click the symbol and select Industrial Graphic <Symbol Name> -> Edit Symbol Properties to open the Custom Properties window.

19. Set the custom properties as desired.

NOTE:  The AddAnyTag function used in the script checks to verify the tag exists before adding it, so and unneeded “Tag” properties can be left blank.

20. Switch to runtime to test.

SUMMARY

This Tech Note provides examples of how to derive different SQL Server dates such as the first day of the month, the first day of the week etc. These examples may become useful while developing Wonderware Historian reports (for instance: “average flow month to date”, “total yield this quarter” etc.).

The examples below will make use of the following Transact-SQL functions:

• GETDATE() -“ this function returns current SQL Server’s date and time
• DATEDIFF() -“ this function calculates the amount of time between two dates; it consumes the time interval as a parameter which can be expressed in hours, days, weeks, months, years etc.
• DATEADD() -“ this function calculates a date by taking an interval of time and adding it to a date, it consumes the time interval defined as provided above

Note: The last midnight can be returned using the following command:

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

Upcoming midnight

SELECT CONVERT(DATETIME, CONVERT(INT, GETDATE()))

Monday of the current week

SELECT DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)

If you don’t want Sunday to be the first day of the week, you need to use a different method. This example sets Monday as the first day of the week.

SET DATEFIRST 1
SELECT DATEADD(day, 1 – DATEPART(weekday, getdate()),
DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

If you want to change the example above to calculate a different first day of the week you can adjust the SET DATEFIRST command above to the appropriate value (e.g. “SET DATEFIRST 3” for Wednesday).

Yesterday start

SELECT DATEADD(day, –1, DATEDIFF(day, 0, GETDATE()))

Section 2 – Month-Based Transact-SQL Examples

First day of the current month

SELECT DATEADD(month, DATEDIFF(month,0,GETDATE()), 0)

End of prior month

SELECT DATEADD(ms, –3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

Last day of current month

SELECT DATEADD(ms, –3, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1, 0))

First Monday of the current month

SELECT DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6
– DATEPART(day, GETDATE()), GETDATE())), 0)

Section 3 – Quarter-Based Transact-SQL Examples

First day of the current quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

Last day of the current quarter

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE())+1, –1)

Section 4 – Year-Based Transact-SQL Examples

First day of the current year

SELECT DATEADD(year, DATEDIFF(year, 0, getdate()), 0)

Last day of the current year

SELECT DATEADD(ms, –3, DATEADD(yy, DATEDIFF(yy, 0, getdate())+1, 0))

Last day of prior prior year

SELECT DATEADD(ms, –3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))

NOTE: If you need to calculate dates relative to an arbitrary date different from today, you need to change the GETDATE() function call with appropriate reference to the arbitrary date. For example, if you wanted to calculate the Last day of the year from two years ago, you could write GETDATE()-365.

Section 5 – Using These Examples in Historian Client Query

To use these examples in Historian Client Query application, set Query type to “History values” and modify the columns, criteria, retrieval, and other settings to your liking. You do not need to modify the time settings because we will change these in the SQL code. After the settings have been modified, click the SQL tab in the Results pane to see the SQL query that matches your retrieval options.

The code in the screen shot has been placed below for your convenience:

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT TagName, DateTime = convert(nvarchar, DateTime, 21), Value, vValue
FROM History
WHERE TagName IN (‘SysPerfCPUTotal’)
AND wwRetrievalMode = ‘Delta’
AND wwVersion = ‘Latest’
AND DateTime >= @StartDate
AND DateTime <= @EndDate

If we want to use the previous date examples, we can modify lines 4 and 5 according to what time we want to retrieve.

SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()

Changing the @StartDate and @EndDate variables yields the same results as changing the time settings in ActiveFactory. For example, if we want to get data ranging from last midnight until now, we would change the lines to:

SET @StartDate = CONVERT(datetime, FLOOR(CONVERT(float, GETDATE())))
SET @EndDate = GETDATE()

To get all data from last month, we would set the start date to the beginning of last month and the end date to the end of last month:

SET @StartDate = SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()) – 1, 0)
SET @EndDate = SELECT DATEADD(ms, –3, DATEADD(month, DATEDIFF(month, 0,
GETDATE()), 0))

SUMMARY

It may be necessary for a developer to create a report using the Historian Client Workbook add-in (a part of the AVEVA Historian Client tool set) for users to view tag data.  These reports can grow quite large and the Refresh Sheet function does not refresh new cell population.  Because of this, users would need to select and refresh every individual function in the workbook.  This document provides the procedures necessary to automate the Refresh Function on every function in the AVEVA Historian Client Workbook.

NOTE: The Workbook add-in is an add-on for Microsoft Excel. You must choose the 32-bit Microsoft Office add-ins during the installation of Historian Client (these can be added later through a “modify” operation). All Historian Client Microsoft Add-ins require 32-bit Office.

APPLIES TO

• AVEVA Historian Client – All versions

PROCEDURE

1. First open AVEVA Historian Client Workbook from Start->All Programs->Microsoft Excel

2. Next install the reference to AVEVA Historian Client Workbook, so that you will have an access to AVEVA Historian Client specific functions. You will need the “Developer” menu enabled in Excel (File -> Options -> Customize Ribbon -> Main Tabs). Once the Developer menu is enabled, go there and click “Visual Basic”

3. When the Visual Basic Editor opens, select Tools->References.

4. The References VBAProject window will open. Scroll down through the list until you find “ActiveFactoryWorkbook” and check the box. When finished press the OK button and close Microsoft Visual Basic – Historian Client Workbook window.

5. Next create your new report using Workbook Excel. Here you can automate the workbook by providing dynamic start and end times or even a hybrid of each. For example:

Start Date: =now()-1

End Date:  =now()

NOTE: Take note of where the first cell containing the function is located. For instance, in the example below there are two starting positions. You must use the R1C1 system (Row/Column). In the example below, we have one at cell R2C1 (A2) and one at R2C3 (C2).

6. Now that the report has been created and we have taken note of which cell the function begins, we will proceed by going to the Developer menu and selecting “Macros”.

7. The Macro window will appear. To create a new macro you will first need to enter a name for the macro (for the purpose of this document I chose to name the macro Button_Refresh). Once you have entered a name click Create.

NOTE: To have the macro run as soon as the report is opened name the macro Auto_Open (without quotes). You could also create two macros here, which would be:

1) Auto_Open to have the macro run as soon as the report is opened, and

2) Button_Refresh so the macro can be utilized at any point in time.

8. Within a new module you will now write the macro. When finished in the editor, close the “Microsoft Visual Basic” window.

A copy of the macro used in this Tech Note has been written below:

Sub Button_Refresh()

Worksheets(“Sheet1″).Activate
Application.Goto Reference:=”{function starting position}”
mnuRefreshSelection
Application.Goto Reference:=”{function starting position}”
mnuRefreshSelection

End Sub

9. Now that the macro has been written we will import a picture of a button that the users can click on to run the script. To do this first create an image of a button and save it somewhere on your system. Then select Insert->Picture->From Device.

10. Locate your button and click Insert.

11. Now that the button has been imported we need to assign the newly created macro to the button. To do this right-click on the button and select “Assign Macro”

12. Select your newly created macro and click OK. You now have a button in the AVEVA Historian Client Workbook that users can click on to refresh all of the functions you created in the report.

AVEVA Historian Client Workgroup Methods

The following methods execute Workbook menu commands

SUMMARY

It may be necessary for operators to repeatedly view trend information on specific tags.  This document describes the procedure how to open a pre-configured AVEVA Historian Client Trend graph when selecting an analog value displayed through an Industrial Graphic.

APPLIES TO

• AVEVA Application Server / InTouch
• AVEVA Historian Server
• AVEVA Historian Client

PROCEDURE

Part 1 – Creating and Saving a Historian Client Trend

1. Create a Historian Client trend. Navigate to Start -> AVEVA Historian -> Trend.  Configure your trend however you please and when finished select File -> Save As.

2. Navigate to your desired directory, and then proceed by selecting the Save button.

Part 2 – Associating an Action Script to an ArchestrA Graphic

3. Create a new Industrial Graphic in the Industrial Graphic Toolbox. Right-click the graphic name -> Open.  This will open the symbol in edit mode.

4. Select an element of the object that would be easy to select during Runtime. Right-click on the element and click Edit Animations.

5. In the upper left-hand corner click on the + button and select Action Scripts.

6. Use the StartApp function to call your configured trend and when finished click OK.

You may receive a message informing you that there was an issue found in the script because it doesn’t expect that file extension. If the warning box appears click Yes.

7. Make any additional changes as you please, and when finished click Save and Close.

8. Now when you switch into runtime your Industrial graphic has a button association that will open your pre-configured trend.

NOTE: The same procedure can be utilized to open other files like a Microsoft Excel Spreadsheet or Microsft Word.

SUMMARY

When experiencing problems connecting to an AVEVA Historian Server from an AVEVA Historian Client such as Trend, there are several steps that can be used to troubleshoot the problem. This tech note details the correct steps to take to initiate communication. Assume that there is a running Historian Server on your network which is able to accept client connections from other client nodes.

APPLIES TO:

• AVEVA Historian Server – all versions
• AVEVA Historian Client  – all versions

PROCEDURE

1. First, we must confirm basic network connectivity by using the ping command (which can be launched by going to Start button -> Run -> typing cmd -> then in the command window typing ping. You must know the node name or IP address of the Historian Server computer. Make sure that if you are pinging by node name that the IP address resolved is correct.

2. Next, we need to make sure that there aren’t any devices or software that could be blocking communication on specific TCP ports. If there are hardware firewalls or routers on the network, try bypassing them to see if the connection succeeds. Also, check for firewall software installed on the Windows OS, such as the Windows Firewall. The OS Configuration Utility will add the necessary exceptions to the Windows Firewall (but not other 3rd party firewall software). The OS Configuration Utility can be applied by installing a recent patch to AVEVA software.  If you’re not sure these exceptions have been added to the Windows Firewall and cannot download the OS Configuration Utility, the Windows Firewall can be temporarily deactivated manually by a local Administrator. Double-click the Windows Firewall in your Windows Control Panel, and turn it off:

3. If at this point there are still problem connecting to Historian Server, we need to check on the SQL client settings. First, we need to make sure the correct protocols are enabled. Click on Start -> Run type “cliconfg” in the Open: dialog and click on OK. This will start the SQL Server Client Network Utility. Make sure that TCP/IP is enabled and the default port under “Properties” is set to 1433:

Next, select the “Alias” tab and make sure that any configured aliases are correct. Specifically, make sure that the connection parameters reference the correct node name or IP address. Also, make sure the alias(es) are configured to use port 1433:

At this point we have confirmed network connectivity along with SQL client related networking options. If you are still having problems connecting it may be caused by other network or operating system related issues. Please contact Industrial Software Solutions Support at support@industrial-software.com for further assistance.

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

Applies to

• Wonderware IndustrialSQL Server/Historian 8.0 or 9.0 with all service packs and patches
• Wonderware ActiveFactory 9.1 with all patches and higher

Prerequisites

• Familiarity with Wonderware ActiveFactory, specifically IndustrialWorkbook

Introduction

This Tech Note describes the process of creating batch reports using IndustrialWorkbook Excel add-in (a part of ActiveFactory tool set). Batch reports don’t have determined time span. Time span needs to be determined based on a condition:
Retrieve time range when BatchNumber = actual batch number (if the batch number is an integer number) or BatchName = actual batch name (if the batch name is a string).

Therefore the way batch reports work is:

• In first step, the batch number is entered. Based on that parameter, batch start and batch end are returned.
• In the second step, the returned batch start and batch end times are used to query data for the overall batch report.

Our example is an Excel spreadsheet with 2 tabs:

• Reactor Report it will contain printable batch report with the actual data. It will also contain cell (in this example cell B4) in which the user will enter batch number for the report
• Report Parameters this tab will only contain internal report calculations

Description of batch reportA sample batch report will be created using 2 tags named ReactLevel and ReactTemp. The tags are included in an Wonderware InTouch’s demo application DemoApp1.

The report will contain the following elements:

• Report date and time (cell B2)
• Batch number or ID that the report is created for (cell B4) – this is the only field in the report that is going to be set by the user
• Batch start time (cell B6) and end time (cell B7) these two values will be retrieved from the IndustrialSQL Server database
• Batch duration (cell B8)
• Aggregated values for ReactTemp tag for the selected batch: minimum (cell A12), maximum (cell B12), average (cell C12), range (cell D12)
• Aggregated values for ReactLevel tag for the selected batch: minimum (cell A16), maximum (cell B16), average (cell C16), value (cell D16)
• History of values during the batch for ReactTemp and ReactLevel tags (cells A18:C18 and below)
• Chart for ReactTemp and ReactLevel tags position next to the table of values

The internal Report Parameters tab will contain the following elements:

• names of tags to be included in the history of values table in this example they are ReactTemp and ReactLevel (cells A1 and A2)
• text of query that calculates batch start time based on the batch number (cell B4)
• text of query that calculates batch end time based on the batch number (cell B6)

IndustrialWorkbook Excel add-inWhen using ActiveFactory 9.2 with Microsoft Office 2007, the IndustrialWorkbook add-in can be found under “Add-Ins” tab.

You can also enable ActiveFactory 9.2 to use the “ribbon bar” in Microsoft Excel 2007 and Word 2007. Wonderware PacWest Tech News

Procedure

Part 1: Look and feel of the report

1. On a system with ActiveFactory installed and configured against Wonderware IndustrialSQL Server 8.0 or 9.0, open Excel. This will create a new workbook.
2. Rename the first tab, Sheet1 to Reactor Report, rename the second tab, Sheet2 to Report Parameters, delete the third tab (right click on Sheet3 tab and select Delete from the context menu)

3. On the Report Parameters tab, in cells A1:A2 type the names of tags that will be included into the report’s history of values per batch. Optionally, type in Start Query and End Query labels in respectively cells A4 and A6 (cells B4 and B6 will contain queries retrieving batch start and batch end)

Note: you can include more than 2 tags in here (if you do so, keep adding tag names to the same column).

4. On the Reactor Report tab place report labels as shown on the screenshot below:

Note: all of the text elements above are plain text.

5. On the Reactor Report tab, type in the formula = NOW() in cell B2

6. Go to ActiveFactory menu -> Options -> Options and uncheck Display Heading box (since we’ve already typed in headings, we don’t need IndustrialWorkbook to enter them again).

7. Click OK to close the dialog box.

Part 2: Configuring connection to IndustrialSQL Historian

1. Configure your connection to the IndustrialSQL Server by going to ActiveFactory menu -> Connection Management (refer to product documentation for detailed instructions). In this example server name is VMGIS9.

Part 3: Formula calculating batch start time

Download visual companion for Part 3 (Windows Media Video format, screen resolution 1024×768, length: 3 min 50 sec)

At this point we create a query using ActiveFactory Query that will return a single value representing time when our batch number tag became the value entered in cell B4 in the Reactor Report tab.

1. Select cell E4 in the Reactor Report tab – this cell will contain a formula that will get created using one of the Query Wizards in a moment. After we’ve created the formula we’re going to modify it to take into account the actual Batch Number entered by a user in cell B4.

2. Select ActiveFactory menu -> Direct Query. The Direct Query window will appear on the screen.

3. Click the white-yellow icon (located next to the Servers drop-down list) to open ActiveFactory Query. ActiveFactory Query will appear on the screen.

4. Under Query Type select History Values

5. Under Tags select the Tag that contains the batch number (in this example it’s BatchNumber)

6. Clear all check boxes under Columns tab

7. Under the Time tab select Last 24 hours this is going to be the time in which the query will try to find the start time of the batch number entered by the user in cell B4.

Select 24 hours when using Wonderware ActiveFactory 9.2

8. Leave Format tabs with default options (Narrow format).

9. Under Criteria tab check Value, select ‘=’ (equal) operator and enter a sample batch number (983 in this example), select Quality as Good, and select Criteria Applicability as First True (we want to detect the very first moment of time when the batch number turned to the value in the query).

10. Under Retrieval select Delta mode.

If using Wonderware ActiveFactory 9.2 go to Main options tab and select Delta mode

11. Under Source tab select Latest Value and Extension tables.

12. Leave Order tab with default values.

13. At this point Results pane should populate with results under the Data tab showing the very first moment of time when the Batch Number became the Value entered.

In ActiveFactory 9.2:

14. We want our query to return just the time (without the value). So at this point, we’re going to further manually tweak the query. To do that switch from Data tab to SQL tab in the lower right pane

and carefully modify the query by removing the text “Value” from the SELECT portion of query text, so it should read like shown below:

Click on the Data tab to confirm that the query returns only DateTime:

15. Click OK in the main Query window, this will cause the query text to be pasted back to Excel’s Direct Query dialog box. Then click OK again to close the Direct Query dialog and come back to Excel’s main screen. The query text will paste into cell E4 and the result is retrieved into cell E6 in the Reactor Report tab.

16. At this point, we will want to remove the query text from the Reactor Report tab and move it to the Report Parameters tab. In order to do that:

• right click on cell ‘Reactor Report’!E4 (the query text)
• cut it to clipboard using context menu’s Cut command item (make sure you cut and not copy the cell)
• switch to Report Parameters sheet and select cell B4
• paste clipboard contents into cell B4 on the Report Parameters tab

17. Switch back to the Reactor Report tab, select cell E6 (the result), cut it to clipboard and paste it into cell B6 (still in the Reactor Report tab).

18. At this point we have a formula that finds batch start time for a fixed batch number (983 in this example). Our next step is to tie it dynamically to the batch number in cell B4 of the Reactor Report sheet. In order to do so change the formula located in the cell ‘Report Parameters’!B4 to look like this:

(changes to be made are indicated in red, the formula can be copied from the web page and pasted into cell B4)

=”SET NOCOUNT ON

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = DateAdd(hh,-24,GetDate())

SET @EndDate = GetDate()

SET NOCOUNT OFF

SELECT DateTime FROM v_AnalogHistory

WHERE TagName IN (‘BatchNumber’)

AND Value = ” & ‘Reactor Report’!$B$4 & “

AND Quality = 0

AND wwEdgeDetection = ‘LEADING’

AND wwVersion = ‘Latest’

AND wwRetrievalMode = ‘Delta’

AND wwRowCount = 100

AND DateTime >= @StartDate

AND DateTime <= @EndDate“

Note: The query may generate an error on the Reactor Report page in Batch Start cell (at B6) until you enter a batch number in the cell ‘Reactor Report’!B4.

Optional – query to calculate batch start time in case batch name is a string value

In the case of using string value for Batch Name our recommendation is to use 2 tags to manage batch start and end:

• a string type BatchName
• a discrete type tag that will indicate when actually the batch was processed (let’s call the tag ProcessRunning in this example)

In such situation we’d need to execute 2 queries:

• the first one will determine when the BatchName value changed
• the second one will determine when the process started running for the specified BatchName (ProcessRunning == 1)

So the first query could be executed internally within the Report Parameters tab and it could look 24 hours and locate the first time the tag “BatchName” equals the batch name provided by the user in the “Reactor Report” tab (marked in yellow are differences between the query above):

=”SET NOCOUNT ON

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = DateAdd(hh,-24,GetDate())

SET @EndDate = GetDate()

SET NOCOUNT OFF

SELECT DateTime FROM StringHistory

WHERE TagName IN (‘BatchName‘)

AND Value LIKE ‘” & ‘Reactor Report’!$B$4 & “‘

AND Quality = 0

AND wwEdgeDetection = ‘LEADING’

AND wwVersion = ‘Latest’

AND wwRetrievalMode = ‘Delta’

AND wwRowCount = 100

AND DateTime >= @StartDate

AND DateTime <= @EndDate“

Note the following changes above:

• table name changed to StringHistory
• LIKE operator (instead of = operator)
• different tag name queried (BatchName as opposed to BatchNumber)

The result of the query above could be placed in cell A5 in the Report Parameters tab.

The second query could use the time found from the 1st query (placed in ‘Report Parameters’!A5) and, from this start time, find the first time the tag ‘ProcessRunning’ is equal to the value 1:

=CONCATENATE(

“SET NOCOUNT ON

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime “,

“SET @StartDate = ‘”, TEXT(A5,”yyyymmdd hh:mm:ss”),

“‘ SET @EndDate = GetDate()

SET NOCOUNT OFF

SELECT TOP 1 DateTime

FROM History

WHERE TagName IN (‘ProcessRunning’)

AND Value = 1

AND Quality = 0 “,

” AND wwRetrievalMode = ‘Delta’

AND wwVersion = ‘Latest’

AND wwEdgeDetection = ‘LEADING’

AND DateTime >= @StartDate

AND DateTime <= @EndDate”)

The query above uses Excel’s function called CONCATENATE to append dynamic portions of the query (such as TEXT(A5,”yyyymmdd hh:mm:ss”)) to static portions of the query. The end result of it (which is batch start time) should be included in the cell ‘Reactor Report’!B6.

Part 4: Formula calculating batch end time

1. Copy the formula above from the cell ‘Report Parameters’!B4 to ‘Report Parameters’!B6,

then modify wwEdgeDetection = “LEADING” to read wwEdgeDetection = “TRAILING” – this will the query retrieve the batch end.

2. Switch to Reactor Report tab, copy the formula from the cell B6 (Batch Start) to clipboard and paste it into cell B7 (Batch End).

Then modify the formula to read =wwQuery(“VMGIS9”, ‘Report Parameters’!$B$6) replace VMGIS9 with the name of your IndustrialSQL Server.

3. Type in the formula  =B7-B6 in the cell B8 in the Reactor Report tab (this will calculate batch duration). At this point you may see errors in cells B6, B7 and B8, to eliminate them enter a valid batch number into cell B4.

4. If needed change formatting of B6 and B7 cells to Date, B8 to Time Interval only (indicated in the Excel’s Format Cells -> Category: Time -> Type  -> “37:30:55” – for further information check Excel’s documentation). After these change the spreadsheet should look like this:

Optional – query to calculate batch end time in case batch name is a string value

In the case of using string value for Batch Name our query will use the time we found in the previous step (testing ProcessRunning = 1) as the start time and will look for the time when ProcessRunning goes from 1 to 0.

=CONCATENATE(

“SET NOCOUNT ON

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime “,

“SET @StartDate = ‘”, TEXT(‘Reactor Report’!B6,”yyyymmdd hh:mm:ss”),

“‘ SET @EndDate = GetDate()

SET NOCOUNT OFF

SELECT TOP 1 DateTime

FROM History

WHERE TagName IN (‘ProcessRunning’)

AND Value = 0

AND Quality = 0 “,

” AND wwRetrievalMode = ‘Delta’

AND wwVersion = ‘Latest’

AND wwEdgeDetection = ‘LEADING’

AND DateTime >= @StartDate

AND DateTime <= @EndDate”)

Part 5: Aggregation Formulas

1. Select cell A12 and go to ActiveFactory menu -> Tag Values -> Aggregate Values. Aggregate Values wizard will appear on the screen.

2. In Step 1 of 4, select the cell that contains ReactTemp tagname (in our example it’s “Report Parameters”!$A$1) and hit Next.

3. In Step 2 leave all default settings (Select cell for output should state $A$12)

4. In Step 3 under Calculations tab select Minimum, leave all other settings at this step with default values.

5. At Step 4, select Absolute time option, and select fields “Reactor Report”!$B$6 and “Reactor Report”!$B$7 for Start Time and End Time respectively (see the screenshot below for reference).

then click Finish.

6. As the minimum value of ReactTemp is pasted into cell A13 on Reactor Report tab, move the formula from A13 to cell A12 within the same tab.

7. Copy the formula from A12 to cell B12 then modify the copied formula to read

=wwAggregate(“VMGIS9″, ‘Report Parameters’!$A$1,”Row0″,’Reactor Report’!$B$6,’Reactor Report’!$B$7,”MAX“,””)

In other words change the text “MIN” to “MAX”.

8. Copy cell B12 to C12 and D12 and respectively change “MAX” to “AVG” and “RNG”

9. Select cells A12 through D12 and copy them to clipboard, then select cell A16 and paste clipboard contents in there.

10. Then modify each cell, A16 through D16 individually,

changing the following reference ‘Report Parameters’!$A$1 to ‘Report Parameters’!$A$2

11. After these steps the Reactor Report tab should look like this:

Part 6: History Values Table for the Batch1. Select cell A19 in the Reactor Report tab, then go to ActiveFactory menu -> Tag Values -> History Values to retrieve historical values for ReactTemp and ReactLevel for the duration of the batch. A 4-step wizard window will appear on the screen.

2. In Step 1 select cells containing both ReactLevel and ReactTemp (“Report Parameters”!$A$1:$A$2).

3. In Step 2 accept output cell as $A$19

4. In Step 3 under Display Options tab uncheck Quality, under Resolution tab select Cyclic retrieval mode (assuming we want to retrieve 100 rows worth of data for the batch).

In ActiveFactory 9.2 uncheck wwRetrievalMode checkbox and go to Retrieval -> Main options tab to select Cyclic retrieval mode

5. Step 4 should show the same time options as set earlier. Results paste into the report after you click Finish. Result will be pasted into Reactor Report tab.

6. Using Excel features you may want to create charts based on the history or aggregate date (the screenshot shows a sample XY Scatter plot).

7. Type in batch number in cell B4 on the Reactor Report tab and observe the spreadsheet recalculate.