Working with Reports | Historian 7.1 Documentation

About Working with Reports

Use the Historian Excel Add-In to 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.
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. Each client using the worksheets must have the Historian Excel Add-In installed and enabled inside Excel.

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.

This manual contains hypertext links that open the provided Historian Sample Reports directly in Excel. If you did not install the Excel Add-In, or if you have moved the Sample Reports from the Sample directory, or moved the Historian.chm file from its installed location, you will receive an error when you click on these links and will be unable to take advantage of this functionality.

The three sample Excel reports are built using tags from the Simulation Collector. You must have the Simulation Collector installed on a machine and collecting data to the Historian Server 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 the 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 first install 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 make this 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 (Yes) or keep the existing information (No). It is recommended that you select No and 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

This report calculates, for a specified time period, a number of statistical properties of a tag, such as 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 during the previous 24-hour period. This sample is an example of a typical daily performance report in an industrial plant.

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

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

Here 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.

Here is the configuration worksheet used to generate the report shown in the previous figure. Edit 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 (Yes) or keep the existing information (No). It is recommended that you select No and 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 Example

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 nmust modify the path of the .XLA and .XLS files. The paths that you need to edit are bolded in the following example.

To use this example, a user must have the privileges to run the collector as a Windows service in Windows and a default printer must be installed. If Historian security is enabled, the user must have iH Readers membership. 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 could run it on a polled basis through the 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 a result or list of 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 Historian Excel 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 calculation mode. See the Calculation Modes section in the Getting Started with Historian guide for a complete list.
`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 or 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` Filter Expression can be used instead of `FilterTag`, `FilterComparisonMode` and `FilterValue` parameters. While using `FilterExpression`, the expression is passed within single quotes and for complex expressions we write the conditions within a parenthesis. There is no maximum length for a filter expression.
`pFilterMode`	The type of 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 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 sampling mode used by the query. See the Sampling Modes section in the Getting Started with Historian guide for a complete list.
`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 that you are retrieving data from, you do not have to 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.

Editing an Array Formula in Excel

Click the cell in the spreadsheet that contains the array formula.
Click the formula bar at the top of the screen.
Edit the formula that appears in this bar.
To exit a formula array without editing it, press the Esc key.
Press Ctrl+Shift+Enter.
Select Save from the File menu.