8 Replies Latest reply on Mar 30, 2016 8:16 AM by FaisalAbdulla

    Performing Calculations on a Dataset

    FaisalAbdulla

      Hello

       

      I am currently working with VBA in PI Processbook and got stuck on a problem. I have a sheet with a large number of PI tags, each having a button of their own. When that button is clicked, that tag is plotted as a trace on a trend. In addition to this, it also updates a pre-defined calculated dataset (please correct my terminology if I am wrong) based on some other tags in relation with this one. This dataset is also on the trend.

       

      I would now like to perform calculations on this dataset. Particularly, I would like to calculate the Min, Max, St.Dev, and Mean of this dataset. However, the catch is, I would like to calculate these statistics for only the time range of the trend. This time range is dynamic and can be set by the user.

       

      How do I go about doing this? So far, the code I have is as follows:

      Private Sub recalculate()
          Dim ptPoint As PISDK.PIPoint
          Dim ds As Datasets
          Dim calc As PIExpressionDataset
          
          Set ptPoint = PISDK.Servers("ServerNameHere").PIPoints("PreTagHere" & Text1.Contents)
          Set ds = ThisDisplay.Datasets
          Set calc = ds.GetDataset("Spec_Calc")
      
      
          With calc
              .Expression = "'PreTagHere" & Text1.Contents & "Suffix1' - 'PreTagHere" & Text1.Contents & "Suffix2'"
              .Interval = "1m"
              .ColumnName = "Value"
              .RefreshInterval = 60
              .Description = "Calculated Factor"
              .Stepped = False
          End With
      
      
          Call ds.SetDataset(calc)
          averagetag.Contents = ptPoint.Data.Summary(TextBox1.Text, TextBox2.Text, astAverage, cbTimeWeighted)
      End Sub
      

       

      As you can see in the above code, I was successfully able to calculate the average of the data in the trend easily, because it was a PI tag. However, I'm not sure how to do the same with a DataSet (in this case, "calc").

       

      Thanks for your help

        • Re: Performing Calculations on a Dataset
          Kenji Hashimoto

          >I would like to calculate these statistics for only the time range of the trend. This time range is dynamic and can be set by the user.

          You can get Trend's start time and end time by following. (If your display contains Trend1 object)

          ThisDisplay.Trend1.StartTime

          ThisDisplay.Trend1.EndTime

            • Re: Performing Calculations on a Dataset
              FaisalAbdulla

              Hi there!

               

              Thank you very much for your response. Yes, I have been able to change the Trend's start and end time (that's how I was able to get the user to specify the time range), however, how do I calculate the average, min, max, st. dev for the data set within this particular time range?

                • Re: Performing Calculations on a Dataset
                  Kenji Hashimoto

                  One possible way is using PISDK 1.3 Type Library.

                  Open Microsoft Visual Basic editor > Tools > references > Check PISDK 1.3 Type Library.

                  You can call pisdk functions by this reference.

                  There is IPICalculation Interface and you can get the results of PE syntax by this method.

                  For example getting Maximum

                  Sub test()
                  Dim srv As Server
                  Dim ipiCalc As IPICalculation
                  Dim vals1 As PIValues
                  Dim expr1 As String, startTime As String, endTime As String
                  Dim testTimes As Variant
                  Dim time As String
                  Dim val As Double
                  
                      startTime = "t+30m"
                      endTime = "t+1h"
                      expr1 = "'cdt158' - 'sinusoid'"
                      Set srv = Servers("localhost")
                      Set ipiCalc = srv                        ' Get pointer to IPICalculation Interface
                  
                  ' note that the SampleInterval argument is ignored if stRecordedValues is the SampleType
                      Set vals1 = ipiCalc.Calculate(startTime, endTime, expr1, stRecordedValues, "")
                      val = vals1.Summary(startTime, endTime, ArchiveSummaryTypeConstants.astMaximum)
                      MsgBox ("Maximum : " & val)
                  End Sub
                  
                  
                  2 of 2 people found this helpful
                    • Re: Performing Calculations on a Dataset
                      gregor

                      Hello Faisal,

                       

                      I am uncertain if PI Calculation Datasets do not already include the kind of calculations you are after and therefore you may want to create / modify PI Calculation Datasets instead of adding custom PI SDK code.

                       

                      1 of 1 people found this helpful
                        • Re: Performing Calculations on a Dataset
                          FaisalAbdulla

                          Hi Gregor!

                           

                          I would have really liked to do that (it would have saved me a lot of hassle). Unfortunately, the calculation interval and the overall time interval for this is dynamic, based on how the user resizes the chart, and hence, I don't think it is possible using the method you described. I think I will be using Kenji's method, above, after I fix a small hitch I'm having with the code.

                           

                          Thank you very much for your time!

                        • Re: Performing Calculations on a Dataset
                          FaisalAbdulla

                          Hi Kenji

                           

                          This was exactly what I was looking for. Thank you! I was unaware that an iPiCalculation interface existed. My code now looks like this (well, part of it at least!):

                          Dim ipiCalc As IPICalculation
                          Dim vals1 As PIValues
                          Dim val As Double
                          Set ipiCalc = PISDK.Servers("servernamehere")
                          Set vals1 = ipiCalc.Calculate(TextBox1.Text, TextBox2.Text, "'PreTagHere" & Text1.Contents & "Suffix1' - 'PreTagHere" & Text1.Contents & "Suffix2'", stInterval, "1m")
                          val = vals1.Summary(TextBox1.Text, TextBox2.Text, astAverage, cbTimeWeighted)
                          

                           

                          This seems to work fine when the chart is set at its default time range. However, when the time range is changed via the user (through a regular resize by selecting a region), I get the following error:

                          Requested summary range outside PIvalues time range by: x micro seconds

                          where x is just a number that keeps changing based on the chart range selected.

                          Any ideas what could be causing this? Just some extra information: The textbox1 and textbox2 contain text strings, either in the form of "*-1 day", for example, or a date and time, eg. "29-Mar-16 9:31:27.846 AM".

                           

                          Edit: It looks like this error is only thrown if the end date is not set to "*". Any ideas why this would happen?

                           

                          Thank you for all your help!