8 Replies Latest reply on Apr 8, 2015 8:44 AM by Sharmeeze

    Develop Excel Report to Count How Many Time The Tags Exceeded The Limit


      Hi Guys,


      Anyone have an example of VB Excel code to count how many time the tag exceeded the limit in specified time. I'm thinking of using PI MDB to store the information such as tag name, the properties and the limit. As a result I want the result displayed in table with the count and the details.


      Really appreciate for your help.



        • Re: Develop Excel Report to Count How Many Time The Tags Exceeded The Limit

          Hello Muhamad,


          Is it absolutely critical that you do it programmatically? It could be done quite easily in DataLink using compressed data with a filter expression. Simply specify that the tag value must be greater than the limit in the filter expression field, and then you can use native excel functions to count the result. If you don't want to see the table of all values you can always hide those columns.


          If you do need to use VBA, consider something like this:


          Sub TagCount()
              Dim mySrv As Server
              Dim myTag As PIPoint
              Dim myVals As PIValues
              Dim starttime As New PITimeFormat
              Dim endtime As New PITimeFormat
              Set mySrv = Servers("PIServer")
              Set myTag = mySrv.PIPoints("PITag")
              starttime.InputString = "*-3h"
              endtime.InputString = "*"
              Set myVals = myTag.Data.RecordedValues(starttime, endtime, btAuto, "'PITag'>Limit")
              Range("A1") = myVals.Count
          End Sub


          If you need to grab properties from the MDB, you can use something like this:


              Dim myMod As PIModule
              Dim myProp as String
              Set myMod = mySrv.PIModuleDB.PIModules("ModuleName")
              myProp = myMod.PIProperties("PropertyName").Value


          Hope this helps.

          • Re: Develop Excel Report to Count How Many Time The Tags Exceeded The Limit

            Hi Muhamad,

            As Samuel has indicated, the easiest way is to use built-in PI DataLink functions to perform the count.

            2015-04-07 09_22_40-Book1 - Excel.png

            In the example above, the filtered expression is calculated in Excel formulas (e.g. =CONCATENATE("'",B5,"' > ", B6)). The Count is achieved by using the Calculated Data function in PI DataLink. Specify the filter expression with "count" as the calculation mode and "event-weighted" as calculation basis.



            If you only want the count but not the actual values, consider using functions that will perform the filtering and summation on the server side so you are not unnecessarily retrieving more values than you need from the server over the network. An equivalent PI SDK function would be the IPIData2.FilteredSummaries method. If you are interested, please take a look at the PI SDK help guide (located in %pihome%\HELP\pisdk.chm).