I would say use PI Notifications, to deliver email messages with PI values to a group of users. Even with a piece of code from somebody else on the community, using PI ACE to do this would pretty much be writing everything from scratch as opposed to leveraging existing infrastructure...
point noted on ACE, we are already using Notifications to send small batches of values. We even have a sharepoint, however we have small groups of process engineers sitting off site and not on the same network that need to constantly monitor and analyse a section of the plant. Of course its for commissioning and this is not a long term solution, but their requirement for now has been put very clear to me, a daily spreadsheet in their ibox J . So bearing that in mind I’m trying to be as true to that goal as possible.
Jason I'm not sure that ACE is really the best tool for this. In order to update the values you will need to open the excel file, call an update on all the data, save the workbook and then send it. ACE can do this but I'm not sure this the approach I would follow.
What I've done in the past is to create an excel workbook and use a Macro to handle the updating of the data and sending the email. One thing I normally do is copy and paste the values to the worksheets that will be sent; I do this to prevent volitale function from causing problems with users that don't have datalink or who don't have access to our PI servers (like external vendors). Within Excel there are a couple of email methods that can be used; CDO (i.e. direct SMTP) and via Outlook. I've found the CDO method to be easier; the via outlook method gives problems with the outlook security.
For the schedule side I simply use the Windows scheduler. I admit that this is a bitty solution so I'm interested in hearing more about Steve's option of using PINS to send a structured workbook.
Steve: Where we use this is for external vendors that provide maintenance on certain equipment. They need the data for monitoring the operation of the equipment (not in real time) from a maintenance perspective. Because they outside of our networks they don't have access to our PI Servers and even if they did I would be sceptical about giving them access as their is data they don't need which is confidential.
Below is a VBA function that I have which will send the emails with attachements. You will notice that I use an excel sheet to maintain a log of all the mails sent; this is quite useful.
On Error GoTo EmailFailed
Dim iMsg As Object
Dim iConf As Object
Dim Flds As Variant
Dim fromAddress As String
Dim smtpServer As String
Dim smtpPort As Integer
Dim addressFormat As String
Dim subjectText As String
Dim bodyText As String
Dim destinationAddresses As String
Dim ccAddresses As String
Dim bccAddresses As String
smtpServer = Settings.Range("E11").Text
smtpPort = Settings.Range("E12").Text
fromAddress = Settings.Range("E13").Text
addressFormat = Settings.Range("E14").Text
subjectText = Replace(Settings.Range("E22").Text, "[PlantName]", ARFData.Range("curPlantName").Text)
bodyText = Settings.Range("E24").Text
bodyText = Replace(bodyText, "[Name]", fromAddress)
destinationAddresses = Settings.Range("E17").Text
ccAddresses = Settings.Range("E18").Text
bccAddresses = Settings.Range("E19").Text
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtpServer
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
'.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
If Settings.Range("K16").Text = "yes" Then
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Settings.Range("E49").Text
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Settings.Range("E50").Text
Dim rowsCount As Integer
rowsCount = Worksheets("Logs").Cells(Rows.Count, "B").End(xlUp).Row
Set .Configuration = iConf
.To = destinationAddresses
.CC = ccAddresses
.BCC = bccAddresses
.from = fromAddress
.Subject = subjectText
.TextBody = bodyText
.AddAttachment Worksheets("Logs").Cells(rowsCount, 2).Text
If Worksheets("Logs").Cells(rowsCount, 6).Text <> "" Then
.AddAttachment Worksheets("Logs").Cells(rowsCount, 6).Text
Worksheets("Logs").Cells(rowsCount, 4).Value = "Yes"
Worksheets("Logs").Cells(rowsCount, 5).Value = Format(Date, "yyyy-MM-dd") & " " & Format(Time, "HH:mm:ss")
Worksheets("Dashboard").Range("K4").Value = "Yes"
MsgBox "Email could not be sent. Please check that there is a valid Internet connection and that the email settings are correct"
Thanks Michael for the post, some good ideas above. I'll also intrested on hearing Steve's thoughts using PI infrastructure to autogen structured workbook.
I admit that this is a bitty solution so I'm interested in hearing more about Steve's option of using PINS to send a structured workbook.
I'll also intrested on hearing Steve's thoughts using PI infrastructure to autogen structured workbook
@Michael and Jason: I'm afraid there is a bit of misunderstanding here... I didn't say you could use PI Notifications to send an "autogen structured workbook" - I wrote you could use PI Notifications to "deliver email messages with PI values to a group of users". More specifically, I meant you could use PI Notifications to send emails to a group of user, which emails would contain PI values (whatever you under the "Content" tab...). Not use PI Notifications to send a DataLink-enabled Excel spreadsheet.
If the requirement is to deliver an actual spreadsheet, then maybe your solution (Excel + VBA + CDO) is the right one. Assuming that the core requirement was to deliver data by email to a group of people, I thought PI Notifications might have helped here. It really depends on how much data you need to send, and what type of data - whether it's current values or calculations using historical data.
Dear vCampus and/or Jason Boddy:
Did you ever write the ACE routine? I want to write an ACE routine to send a monthly report from MatLab. I think any information you came up with using ACE to schedule the trigger could help me. Thanks !
In general scheduling a calculation in PI ACE is either based on a PI tag or on regular time intervals. In the former, whenever a new event happens to the tag the calculation executes while in the latter case, the calculation repeats on specific time intervals (say every 1 minute).
There is also another option inside MATLAB which is the Timer object. It is an object within MATLAB that you can leverage to schedule your MATLAB calculations. I would suggest consulting with MATLAB documentation on that.
There is a nice example in the ACE user guide on how to call Matlab from ACE; I think that it is example 2.
As an alternative you could use the Windows scheduler for scheduling the reports; simply pass a Matlab command and startup file. To get the PI data we are using the PI OLE DB provider. This generally works well. There are however a few caveats:
- You need Matlab installed on the server
- There is a limit to the number of Matlabs that can run concurrently; I believe that it is 4 on the standard license.
As a side note point 1 can be remedied by compiling the Matlab. Unfortunately not all Matlab code can be compiled; but it is improving.
Thanks Michael! As far as I know most MATLAB Toolboxes can be compiled using MATLAB Compiler; is that what you were referring to? But of course it costs about $5000 extra!
Thank you both. Your replies have confirmed the work I have already started. I appreciate your replies. Now I know I am on the best possible path for the tools we have today. Sincerely, Alejandra
Glad you found the discussion useful
As far as I know most MATLAB Toolboxes can be compiled using MATLAB Compiler;
That is correct Ahmad, the Matlab compiler is pretty good. You can compile most Matlab code; the general rule of thumb is that code will compile as long as it doesn't contain eval or feval. There is a work around to this as well. I would suggest the Matlab NE Compiler rather than the straight C compiler.
The extra costs can be justified by removing the runtime license and limitation.
Please note that this will have next to no impact on speed.