13 Replies Latest reply on Jan 31, 2017 8:24 AM by Roger Palmen

    Down time tracking with Event Frames


      Hi guys! I am trying to implement down time tracking in my plant using event frames and Excel.


      The idea is to use excel to pull up a list of down time events, and allow operators to select a down time type (scheduled/unscheduled), down time code (a variety of reasons), and a remarks field so that the operators can key in additional comments if need to, all in Excel.


      I have already created the events frames template, have an EFGEN interface running and using the explore function, I can pull up the list of down time in Excel. But now, I need to allow the operator to key in the mentioned details and push save the input in the event frames.


      Does anyone have an idea on how to do this or can point me in the right direction? Thanks in advance!

        • Re: Down time tracking with Event Frames

          Hi Jason,


          Are you storing the comments to the downtime events as attributes to the event frames? I can think of two ways:

          • As a static attribute in the event frame
          • As a attribute referencing an attribute in the referenced element (PI Point data reference)


          I tested either scenario; as long as the attributes are not configuration items, you can use the DataLink functions PIPutVal/PIPutValX to write to the event frame attributes. These functions take the form of

               PIPutVal(AFAttribute, ValueToWrite, TimeStamp, RootPath, OutCell)


          What you can do is to add a button on the Excel spreadsheet and write a simple VBA macro that will write the comments using either of these functions. The format of the AFAttribute will be \\AFServer\AFDatabase\EventFrames[EFName]|AttributeName (You can obtain this by concatenating the "event path" with the name of the attribute). You can use the end time of the event frame as the TimeStamp of your comment.


          For more information about using PIPutVal and PIPutValX, consult the PI DataLink user guide. In addition, each PI DataLink installation comes with an example worksheet, piexam32.xls (located at %pihome%\Excel), that illustrates how to use these functions.


          I will be happy to provide an example if you need.

          3 of 3 people found this helpful
            • Re: Down time tracking with Event Frames

              Hi Daphne,


              This is exactly what I needed!


              I have a question though. I have an event frame template which is successful in identifying several downtimes with different start and end times. I also have a comment attribute which I wanted to write into, using vba. But PIPutVal only takes in a single time stamp. How do I identify the individual events to execute PIPutVal using just a single timestamp?

                • Re: Down time tracking with Event Frames

                  Hi Jason,


                  If you are using a static event frame attribute, PIPutVal will be writing to attributes that belong to a specific event frame. The path for the attribute will include the name of the event frame so they can be differentiated. Here is an example:

                  2015-06-25 08_54_31-DNG-PI2012 - Remote Desktop Connection Manager v2.2.png


                  The code behind "Submit Comment" is:

                  Sub Write_Comment()
                      Dim sAttr As String
                      Dim sTime As String
                      Dim valueCell As Range
                      Dim resultCell As Range
                      Dim macroResult As Variant
                      Dim i As Integer
                      i = 2
                      'Looping through all event frames
                      Do While Worksheets("EF").Cells(i, 1).Text <> ""
                          'If there are comments to write
                          If Worksheets("EF").Cells(i, 8).Text <> "" Then
                              sAttr = Worksheets("EF").Cells(i, 6).Text & "|" & Worksheets("EF").Cells(1, 5).Text
                              sTime = Worksheets("EF").Cells(i, 3).Text
                              Set valueCell = Worksheets("EF").Cells(i, 8)
                              Set resultCell = Worksheets("EF").Cells(i, 9)
                              macroResult = Application.Run("PIPutValx", sAttr, valueCell, sTime, , resultCell)
                          End If
                          i = i + 1
                  End Sub


                  Hope it clears things up. This is just one way to do this. Michael also gave some excellent suggestions below that you can take a look at to see which one works best for you.

                  1 of 1 people found this helpful
                    • Re: Down time tracking with Event Frames

                      Hi Daphne,


                      Your code works! But there is a problem now. If my event names are all the same, referencing that event with the sTime (end time) will not write the comments to the right event frame.


                      As below, using the piece of code, it will only write to the first event frame that it finds in every Do-While loop, and ending with the last remark (hello7) in the first event frame remarks. May I know how can I reference the right event frame?


                        • Re: Down time tracking with Event Frames

                          Hi Jason,


                          Yea.. using PIPutVal, the only reference made to the event frame static attribute is the path, which can be the same if the event frames have the same names. I can think of 2 workarounds:

                          • Use different names for the event frames (e.g. append the end time to the event name)
                          • Create a PI tag to store the comments, and use a PI Point data reference for the comment attribute. When you write the comments at the end time, it will correspond to the correct event frame. However, the downside of this is that if you don't have comments in a particular event frame, it will display the value from a "previous" comment.


                          If these workarounds are not ideal, we might have to go a different route (e.g. use PI AF SDK directly to update the value - either through a wrapper with VBA or by creating a VSTO addin).

                          1 of 1 people found this helpful
                  • Re: Down time tracking with Event Frames

                    Hi Jason,


                    Firstly I would recommend using AF Analytics for generating the event frames rather than EFGen. I've done both but have found AF Analytics to be easier.


                    For the reason codes I went the route of using AF elements. I have a reason tree of the available reasons and the users simply pick the relevant one. The reasons are then simply a referenced element. For data extraction purposes I have attribute on the event frame that uses string builder to pull the reason code into a field that Excel likes. The benefit is that you can add additional information to the reasons easily. Yes we do also support a free text comments field.


                    For the data entry we've gone the custom app route. If you are hell bent on using Excel you could look at going direct to AF via the SDK or via PI Web API. Personally I would avoid VBA and rather use a VSTO document addin (a document specific addin is generally easier to create).  While using a PI point will work for the comments you would effectively need one comment tag per item of equipment, you will need to align the time stamps and handle the capturing/recapturing of the data.

                    2 of 2 people found this helpful
                    • Re: Down time tracking with Event Frames
                      David Golverdingen

                      It seems a lot of people are looking for solutions like this.

                      Nice project building on Aurelia Michael Halhead I will give it a try as well. I am also diving into Angular 2.0 at the moment.


                      We also have build a OEE solution for a customer.

                      Speed loss and downtime event frames are generated with AF Analytics.

                      Classification and reporting are done with a custom ASP.net MVC dashboard using kendo UI controls


                      Our demo site with fake data:




                      You might find this interesting as well Jason Gogal, feel free to contact us on Contact - Magion - EN

                      2 of 2 people found this helpful