10 Replies Latest reply on Mar 3, 2016 4:00 PM by gregor

    Accesing AF Element Hierarchy Structure from Excel

    DaniDava

      Currently we have an Excel  report selected via web site. The engineer will select an area of the plant to (AF element structure)  and an Excel spreadsheet is created pulling the equipment values (i.e. pump) for all equipment pumps with the scope selected below:

       

       

      Element:           Pumps

      Status               Off

      Time Frame:     Daily

      Values:             Status     Discharge Pressure     Discharge Temperature     Suction Pressure      Suction Temperature      Flow Rate

      Date Range:     From: 02/01/2016     To 02/15/2016

      Run Report (button to generate the excel report)

       

      The report generates in Excel a report like this;

      Equipment          Timestamp     Status     Discharge Pressure     Discharge Temperature     Suction Pressure      Suction Temperature     Flow Rate

      ESP-Pump1        02/01/2016     Off               1472                                   174                              537                              192                              68

      ESP-Pump2        02/07/2016     Off               1279                                   153                              435                              179                              59

       

      We want to get rid of the web site and try to generate the Excel report directly from MS Excel. For this new approach, we want to explore the possibility to perform the selection of the Pumps in Excel (Driven by the AF Element hierarchy structure). This would allow users to select a plant area and pull in data for all relevant pumps belonging to the selected area in a report with the same format to the Excel report generated via web site.

       

      Based on the above, I would be grateful if you can provide me some recommendations to approach the new desire solution to generate the Excel report directly from Excel. The idea is to connect directly to AF element hierarchy structure from Excel.

       

      Looking forward to hearing from you soon,

       

      All the best,

       

      Daniel Davalillo

        • Re: Accesing AF Element Hierarchy Structure from Excel
          Shane318

          Hi Daniel,

           

          Sounds like you would find PI Datalink to be a useful tool for extracting data from your AF Element hierarchy and inserting it into pre-developed Excel spreadsheets.

           

          OSIsoft have numerous tutorials to get started through this link.

           

          Regards,

          Shane

          1 of 1 people found this helpful
            • Re: Accesing AF Element Hierarchy Structure from Excel
              DaniDava

              Esteemed Patrice,

               

              Thanks a for your valuable help. Much appreciated!

               

              I am try to figure doing so in PI Datalink in Excel but I don’t think it can be done from PI Datalink. Please see the requirements.

               

               

              1.      We have a AF Hierarchy that have the following AF Structure:

               

              Division>District>Substation-->Feeders

               

              Division>District>Substation-->Transformers

               

               

               

              2.      Currently we have a web site when we perform the selection of the elements and attributes with the data range and generate an Excel report  Please see below.

               

               

               

               

               

              3.      The website generates an excel report like this one.

               

               

               

               

               

              4.      The idea is to replace the web site to Excel in order to perform the selection in Excel and Generate  the report. This way we will have everything in Excel.

               

               

              Based on the above I don’t see the way to approach it in excel at least I use AF SDK or another PI tool. How about using PI OLEDB Enteripse. Is it possible to do so?

               

              Looking forward to hearing from you soon,

               

              All the best,

               

              Daniel Davalillo

                • Re: Accesing AF Element Hierarchy Structure from Excel
                  Roger Palmen

                  Yes, is possible in Excel using DataLink. Have done this (and more complex reports) before. It takes a lot of work though. Building a production-ready version of such an Excel-sheet typically takes me a few days.

                    • Re: Accesing AF Element Hierarchy Structure from Excel
                      DaniDava

                      Thanks a lot Rogers for your valuable response. Much appreciated!

                       

                      Is it possible to schedule a remote session tomorrow to show you the current approach I am using?

                       

                      Looking forward to hearing from you soon,

                       

                      All the best,

                       

                      Daniel Davalillo

                        • Re: Accesing AF Element Hierarchy Structure from Excel
                          Roger Palmen

                          Ah, sorry. Can't do that. The forum receives posts from both OSIsoft employees and regular members of the community, both end users and fellow integrators like me (CGI).

                           

                          I think this is more an Excel-related problem than it concerns DataLink. In essence you can get all the required information from PI into Excel through DataLink. The key problem is the sequential processing of the data in Excel, based on selections, and the formatting. Excel is not a reporting tool, so all that functionality needs to be built manually which takes time and Excel skills.

                          Maybe a call for Techsupport might help in getting you started in the right direction?

                          1 of 1 people found this helpful
                          • Re: Accesing AF Element Hierarchy Structure from Excel
                            gregor

                            Hello Daniel,

                             

                            I understand you are currently initiating Asset based reports through a web interface. After the user has selected and submitted the report parameters, the report becomes created in Microsoft Excel. You are seeking for a solution to generate Reports directly in Excel. That's exactly what PI DataLink is designed for. Please allow me to refer you to some related videos at OSIsoft's youtube channel:

                            As suggested by Roger, you may be interested discussing your requirements with OSIsoft Technical Support. Please let us know if you like to be contacted by a Technical Support representative.

                              • Re: Accesing AF Element Hierarchy Structure from Excel
                                DaniDava

                                Thanks a lot Gregor for you valuable feedback. Much appreciated!

                                 

                                I have created the report manually using some Datalink functions but my only concern is how to implement the selection to get rid of the web site. For example, I have a hierarchy with the following structure:

                                 

                                Division

                                Districts

                                Substation

                                Bus Bars

                                Feeder 1

                                Feeder 2

                                Feeder N

                                                                                Transformers

                                                                                                Transformer 1

                                                                                                Transformer 2

                                                                                                Transformer N

                                 

                                Now, in order to get rid of the web site program and have everything in Excel  I need to create a basic selection based on the structure of the AF hierarchy structure, as Follows:

                                 

                                Select Division

                                Select the Districts underneath the Division

                                Select the Substation underneath the selected District

                                Select Feeders or Transformers

                                Select the required AF Attributes

                                Select the Data Range

                                 

                                With the selection above mentioned I need to generate the Excel Report. As part  of the report I need to calculate daily averages and standard deviations for many of the AF attributes and look for the timestamp when the Max and Min average happens during a day. Many of the calculation are created using AF analysis service but other we need to generate in Datalink to avoid the PI tags creation.

                                 

                                For this I a evaluating all possible options so I would be grateful if I can be contacted for a OSIsoft representative to get the best approach for this implementation.

                                 

                                Have a great day,

                                 

                                All the best,

                                 

                                Daniel Davalillo

                      • Re: Accesing AF Element Hierarchy Structure from Excel
                        Roger Palmen

                        This can be achived using PI DataLink. In general you first need to get the Elements (ESP-Pump1, ...) into a list into Excel, and next get all the attribute values for each of the pumps. This is fairly straightforward. Is there any specific thing you are struggling with to achieve? For DataLink there's a good set of videos here: OSIsoft: Obtain a current value for a tag. v4.0 - YouTube

                        One of the key things to get right is the size of the arrays for the DataLink formula's in Excel. These have to size for the maximum number of events you expect to get. The way i prefer to do this is to have separate sheets for data-loading, and then copy the data to another sheet for formatting. That makes maintenance a bit easier.

                        1 of 1 people found this helpful