4 Replies Latest reply on Feb 7, 2012 3:52 PM by gkrueger

    Number of values in TraceValuesCount for traces on trend.  Also Export trend values to Excel

    gkrueger

      Just curious if anyone can tell me, is there a way to set how many samples PB uses to plot a trace on a trend?  I'm working to develop some code to export the values in a trace to Excel when a user presses a button, so I'd like each trace to have the same number of points, preferably a number that could be adjusted by the user based on the precision they are looking for.

       

      If anyone has done something similar to this in the past and has sample code they would be willing to share, it would be much appreciated

       

      Somewhat related to this, how does ProcessBook determine the default resolution to poll the PI server at when it's displaying traces in a trend?  Is it based on settings for sample rate and compression on the PI server, or is it calculated in Processbook based on the length of time being displayed?  I did some brief tests in a dev environment, and it seems like the points are not the same for each trace, so there must be some reliance on what the data looks like/how much it's changing.

       

      -Greg

       

       

        • Re: Number of values in TraceValuesCount for traces on trend.  Also Export trend values to Excel
          andreas

          Greg, ProcessBook uses plotvalues. Plotvalues take in account the resolution you can display and retrieves a corresponding amount of data (but not more than compressed data). But you can not influence what ProcessBook uses as the resolution. Also, the returned amount of data depends on the compressed data.

           

          How about passing only the tag names to Excel and using Datalinks sampled data for retrieving the values from PI?

            • Re: Number of values in TraceValuesCount for traces on trend.  Also Export trend values to Excel
              gkrueger

              Is that something that could be done with a single button press by a user from within ProcessBook?  I imagine I'd have to figure some way to trigger the macro within Excel so that it can execute the commands to run the datalink queries and resize the arrays depending on the sample interval and number of tags selected in ProcessBook.

               

              I'm a decent VBA programmer, but not a professional (I do DeltaV, AB PLC's and other process control work, so computer science is not my full time job) so this might be getting a bit beyond my capabilities...but any tips you have would be helpful...

               

              Thanks,

               

              Greg

                • Re: Number of values in TraceValuesCount for traces on trend.  Also Export trend values to Excel
                  andreas

                  Gregory,

                   

                  first of all we need to add MS Excel to PB VBA. In the VBA editor, Tools->References and add "Microsoft Excel 12.0 Object Library".

                   

                  Next step is from our VBA code to start MS Excel:

                   
                      Dim xlTmp As Excel.Application
                      Set xlTmp = New Excel.Application
                      xlTmp.Visible = True
                  

                   Afterwards we create a new empty workbook and put in the A1 cell the tagname (in my case SINUSOID - you will have to catch that from your trend):

                   
                      Dim xlWrk As Excel.Workbook
                      Set xlWrk = xlTmp.Workbooks.Add()
                      Dim xlSht As Excel.Sheets
                      Set xlSht = xlWrk.Sheets
                      xlSht(1).Cells(1, 1).Value = "SINUSOID"
                  

                   

                   

                  It turns out that Excel does not like add ins when started from VBA (see here ) - so we have to do some magic:

                   
                      Dim addXL As Excel.AddIn
                      Set addXL = xlTmp.Addins.Add(Environ("PIHOME") + "\Excel\PIPC32.XLL")
                      addXL.Installed = False
                      addXL.Installed = True
                      xlTmp.ActiveWorkbook.RefreshAll
                  

                   

                   

                  The following constructs the formula for PI DataLink. In my case sampled data for the last day in 1h steps. Note that you have to adjust the PI Servername (SCHREMMERAVMPI) for your environment:

                   
                      Dim tmpEquation As String
                      tmpEquation = "=PISampDat(A1," _
                                    & Chr(34) & "Y" & Chr(34) & ", " _
                                    & Chr(34) & "T" & Chr(34) & ", " _
                                    & Chr(34) & "1h" & Chr(34) & ",1," _
                                    & Chr(34) & "SCHREMMERAVMPI" & Chr(34) & ")"
                  

                   

                   

                  What is left is adding this as a FormulaArray and adjusting the format of the timestamp. We start in A2 and expect to get 24 pairs of timestamp & value, so the range ends at B26:

                   
                      xlSht(1).Range("A2:B26").FormulaArray = tmpEquation
                      xlSht(1).Range("A2:A26").NumberFormat = "dd-mmm-yy HH:mm:ss"
                  

                   

                   

                  hope this helps!