Reports

You can generate a wide range of custom reports. You can use all the standard, familiar Excel tools and techniques to access the Historian archives and build reports and charts of all types to fit your specific needs. You can use the sample reports included with Historian almost as is — just change the tags to fit your application. As an alternative, use the setup worksheets as a starting point and adapt them to your particular situation.

Defining Reports: You can define a report so that Excel recalculates the worksheet whenever the contents of specific cells, such as start times or dates, change. In this way, the report generates a dynamic snapshot of process performance, updated regularly in real time. You can also manually initiate recalculation at anytime.
Building Dynamic Reports: The primary rule to follow in building a dynamic report is to use formulas with cell references that contain variable information rather than fixed data, so that recalculation produces new data each time it occurs. You then initiate recalculation by changing certain inputs manually or automatically.
Sharing Reports: You can share any Excel reports you develop with the Historian Excel Add-In as you would any other Excel workbook. For each client using the worksheets, set up the Excel Add-In for Historian.

Using the Sample Reports

The Historian application includes three typical sample reports that demonstrate the power and ease-of-use of the Excel Add-In. Use them directly in your application or modify them to fit your requirements.

The three sample Excel reports are built using tags from the Simulation collector. You must create an instance of the Simulation collector and start it in order for these reports to work. The Historian Batch Report Sample.xls file also uses Batch ID and Product ID tags from the Simulation collector. These are Simulation Collector points that are configured to store string data types.

To ensure that the sample reports work correctly, you must add the string tags. These are the last five tags in the tag collector list. Add the string tags in Historian Administrator by browsing the Simulation collector and adding all of the tags by selecting the Add All Tags check box. Alternatively, you can run the Add Tags to Simulation Collector.bat batch file in the Historian\Server directory of the machine that has the Simulation collector.

In addition, when you create an instance of the Simulation collector, it prompts you for the number of simulation tags it should create (but you must still add the tags for collection using one of the two methods above). The default is 1000. Do not enter a value less than 30.

When opening a sample Excel report, you may receive a message prompting you to update all linked information in the workbook or keep the existing information. It is recommended that you select No (that is, keep the existing information). The links will be automatically updated for your worksheet. Save your worksheet after the links have been updated.

Historian Statistical Analysis Sample Report

For a specific duration, this report calculates a number of statistical properties of a tag, such as the average, maximum, minimum, standard deviation, 2 sigma and 3 sigma control limits, and correlation coefficients for other tags. It displays charts of various types for several of these variables.

The chart at the lower left is a plot of the main variable vs. time with sigma control limits indicated by the straight lines. The two charts to the right are scatter diagrams that show the correlation between the main variable and two other variables. The chart at the top right is a histogram of data values of the main variable that shows how the data points are distributed.

The following figure shows the worksheet associated with the sample report that contains the data used to generate the report.

Daily Performance Sample Report

This sample report shows how the measured values and selected statistical properties of specified tags have varied in the last 24 hours. This sample is an example of a typical daily performance report in an industrial plant.

The report shown in the following image is a collection of chart plots of the data displayed in the report of the previous image.

The following figure shows the worksheet used to set up the Daily Sample Report. Edit the worksheet to adapt this report to your application.

Batch Sample Report

This is an example of a report that might be used with a batch type of industrial process. The table at the top of the report shows the batch identification, the start and end times, product name, and computed statistics for several process variables. The charts show how selected process parameters varied during the batch cycle.

This is the configuration worksheet used to generate the report shown in the previous image. Modify this worksheet to adapt it to your requirements.

Troubleshooting the Excel Add-In Sample Reports

If you follow the recommended installation procedures, you should not have any difficulty in running the sample reports. If you do encounter any problems, they are likely to relate to the locations of files and the links to those files.

When opening a sample Excel report, you may receive a message prompting you to update all linked information in the workbook or keep the existing information. We recommend that you select No (that is, keep the existing information). The links will be automatically updated for your worksheet. Save your worksheet after the links have been updated.

For problems in the worksheets themselves, refer to Excel online Help for assistance.

Running a Report Using Visual Basic

The following Visual Basic example shows you how to create a hidden instance of Microsoft Excel, open a preconfigured Historian report in that instance, and then print the report to the default printer. To use the example, you must modify the path of the .XLA and .XLS files. The paths that you need to edit are in bold font in the following example.

To use this example, you must have the privileges to run the collector as a Windows service and a default printer must be installed. If Historian security is enabled, you must be a member of the iH Readers group. Tag-level security can override this privilege.

You can trigger this example to run on an event basis or on a polled basis. Most likely, you would run this example on an event basis. However, you can run it on a polled basis using Windows Task Scheduler.

Sub CreateExcelObjects()
Dim xlApp As Excel.Application Dim wkbNewBook As Excel.Workbook Dim wksSheet As Excel.Worksheet Dim strBookName As String
' Create new hidden instance of Excel. Set xlApp = New Excel.Application
' Open the preconfigured Historian Excel Add-in report.
Workbooks.Open "C:\Program Files\Microsoft Office\Office11\Library\iHistorian.xla" 
Set wkbNewBook = Workbooks.Open("c:\testih.xls", 0, False)
'xlApp.Visible = True
With wkbNewBook
For Each wksSheet In .Worksheets
Select Case wksSheet.Name Case "tag1" wksSheet.Select
.RefreshAll
.PrintOut End Select Next wksSheet
.Close False
End With
Set wkbNewBook = Nothing xlApp.Quit
Set xlApp = Nothing
End Sub

Array Formulas for the Historian Excel Add-In

In Excel, an array formula is a data request that inputs a set of parameters and returns results. The Historian Excel Add-In uses the following array formulas:

ihSearchTags
(pServer,pTagMask,pDescriptionMask,pCollector,pArraySize,pSort,pRowCol,Parameters())

ihQueryData
(pServer-,pTagName,pStartTime,pEndTime,pSamplingMode,pCalculationMode,pSamplingInterval,pNumberOfSamples,pDirection,pFilterTag,pFilterMode,pFilterComparisonMo ())

ihQueryData3
(pServer,pTagName,pStartTime,pEndTime,pSamplingMode,pCalculationMode,pSamplingInterval,pNumberOfSamples,pDirection,pFilterTag,pFilterMode,pFilterComparisonMo ())

ihQueryMessages
(pServer,pTopic,pStartTime,pEndTime,pSearchText,pArraySize,pSort,pRowCol,Parameters()) 

ihListArchives
(pServer,pArchiveNameMask,pArraySize,pSort,pRowCol,Parameters())

ihListCollectors
(pServer,pCollectorNameMask,pArraySize,pSort,pRowCol,Parameters())

When inserting an array formula, you cannot overwrite part of the range of another array formula in your worksheet. The range includes cells without data displayed. An error message appears if you try to do so. Reselect a different output range to insert the formula.

Array Formula Parameters

The following table describes the parameters for the array formulas for the add-in.


Parameter	Description
`pArchiveNameMask`	A search mask you can use to browse the archivers. Use standard Windows wildcard characters.
`pArraySize`	The number of cells that the array spans.
`pCalculationMode`	The type of the calculation mode.
`pCollector`	The collector or collector mask that you want to query.
`pCollectorNameMask`	A search mask for browsing collectors. Use standard Windows wildcard characters.
`pDescriptionMask`	A search mask for browsing tag descriptions. Use standard Windows wildcard characters.
`pDirection`	The direction (forward/backward from the start time) of data sampling from the archive.
`pEndTime`	The end time used to refine your query.
`pFilterComparisonMode`	The type of comparison to be made on the filter comparison value: `Equal`: Filter condition is True when the `FilterTag` is equal to the comparison value. `EqualFirst`: Filter condition is True when the `FilterTag` is equal to the first comparison value. `EqualLast`: Filter condition is True when the `FilterTag` is equal to the last comparison value. `NotEqual`: Filter condition is True when the `FilterTag` is NOT equal to the comparison value. `LessThan`: Filter condition is True when the `FilterTag` is less than the comparison value. `GreaterThan`: Filter condition is True when the `FilterTag` is greater than the comparison value. `LessThanEqual`: Filter condition is True when the `FilterTag` is less than or equal to the comparison value. `GreaterThanEqual`: Filter condition is True when the `FilterTag` is greater than or equal to the comparison value. `AllBitsSet`: Filter condition is True when the binary value of the `FilterTag` is equal to all the bits in the condition. It is represented as `^` to be used in Filter Expression. `AnyBitSet`: Filter condition is True when the binary value of the `FilterTag` is equal to any of the bits in the condition. It is represented as `~` to be used in Filter Expression. `AnyBitNotSet`: Filter condition is True when the binary value of the `FilterTag` is not equal to any one of the bits in the condition. It is represented as `!~` to be used in Filter Expression. `AllBitsNotSet`: Filter condition is True when the binary value of theFilterTag is not equal to all the bits in the condition. It is represented as `!^` to be used in Filter Expression.
`pFilterComparisonValue`	The value to compare the filter tag with when applying the appropriate filter to the DataRecordset query (to determine the appropriate filter times).
`pFilterExpression`	An expression that includes multiple filter conditions. The type of conditions used are: `AND` condition `OR` condition Combination of both `AND` and `OR` You can use a filter expression instead of `FilterTag`, `FilterComparisonMode` and `FilterValue` parameters. While using `FilterExpression`, the expression is passed within single quotes, and for complex expressions, enclose the conditions in parentheses. There is no maximum length for a filter expression.
`pFilterMode`	The type of the time filter: `ExactTime`: Retrieves data for the exact times that the filter condition is True (only True). `BeforeTime`: Retrieves data from the time of the last False filter condition up until the time of the True condition (False until True). `AfterTime`: Retrieves data from the time of the True filter condition up until the time of the next False condition (True until False). `BeforeAndAfterTime`: Retrieves data from the time of the last False filter condition up until the time of next False condition (While True). The `FilterMode`: Defines how time periods before and after transitions in the filter condition should be handled. For example, `AfterTime` indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and leading up to the timestamp of the archive value that triggered the False condition.
`pFilterTag`	The single tagname used when applying the filter criteria.
`pNumberOfSamples`	Number of samples from the archive to retrieve. Samples will be evenly spaced within the time range defined by start time and end time for most sampling modes. For the `RawByNumber` sampling mode, the `NumberOfSamples` column determines the maximum number of values to retrieve. For the `RawByTime` sampling mode, the `NumberOfSamples` is ignored.
`pRowCol`	The sorting criteria used: 0 for columns and 1 for rows.
`pSamplingInterval`	For non-raw sampled data, this column represents a positive integer for the time interval (in milliseconds) between returned samples.
`pSamplingMode`	The type of the sampling mode used by the query.
`pSearchText`	The text or mask that you want to search for in the message.
`pServer`	Name of the server from which you are retrieving data. If you are running Excel on the same server from which you are retrieving data, you need not enter a string, as the default server is used.
`pSort`	The sorting criteria used for the rows or columns: 0 for descending and 1 for ascending.
`pStartTime`	The start time used to refine your query.
`pTagMask`	A search mask for browsing tagnames. Use standard Windows wildcard characters.
`pTagName`	The tagname or tagname mask that you want to query.
`pTopic`	The message topic: Connections Configuration General Services Performance Security
`Parameters()`	Output display of the array formula. This field can include be one or more parameters.