3 Replies Latest reply on Dec 12, 2017 9:57 PM by Dan Fishman

    Create a data link report

    alanR

      Hi,

      Im trying to create a data link report, with sensors that are in error. Each sensor is in a section of a building . So i have create you drop down so the user can filter by "Building" and "Section".  I would like the user to choose the options from the dropdowns and that would populate the excel with  the sensors in each section of the building that are in error.

       

      Thanks

        • Re: Create a data link report
          Dan Fishman

          Datalink is not the best at doing the types of searches you require.  PI OLEDB Enterprise can solve this, but that requires VBA, a PI Data Access license, and additional OSIsoft software.  Other options are to make a custom tool that displays this, use the PI Web API + VBA (not ideal either), or use a brute force method with Excel filtering for low sensor count.

           

          If the user must always select a building, and a section, you might be able to get away with just using the drop down feature and listing all the assets with an Excel filter that checks for the error state.

          3 of 3 people found this helpful
          • Re: Create a data link report
            bbregenzer

            Hi, Alan.

            Are you using AF to organize and contextualize the sensor data?  I'm thinking if you had a simple AF database with a hierarchy like the following, then there are a couple of ways to do it:

            Building
                - Section
                      - Sensors

            Here is one I built quickly in AF:

            Each sensor is based on a simple template with attributes:

            Here I leave Statuses as static attributes so I can easily change the values for testing, but you'd use the PI Point data reference to map them to tags.

            There are two ways I can envision this working.

            1. Use the Asset Filter in DataLink to create a filtered list of sensors with Status = Error:

            Box A (yellow) is just used to make a the Section drop down list conditional on the Building drop down list (see here: How to make Excel Data Validation Dependent Lists ).  Here is the step-by-step:

            1. Select the Asset Filter button in DataLink to bring up the menu on the right.
            2. Select the Building and Section from drop down lists.  This populates the Root Path cell (D2), which could be hidden in a production report
            3. Select D2 to populate the Root Path field for the Asset Filter Search.
            4. Select the template for the sensors.
            5. Set an attribute filter so we only return those with Status = Error.
            6. Select Status to see the value (optional).
            7. Select the following: Insert elements in: Column; Output as: Function Array; Output cell.

            If you want to see the ERROR value returned (column E here), then you can use the paths to the Status attributes returned by the Asset Filter (A5 on down) with the DL Current Value function.

            You could also use some Excel tricks to clean up output to just show the Sensor name rather than the full path or use the path to call an attribute that displays the element (sensor) name (see attributes above).

             

            2. Produce a table with all sensor values and use Excel Pivot Table to filter:

            The Filters can be used to select only those in ERROR as well as the Building and Section.  Additionally, you could get more of a summary report by moving the Building and Section from Filters to Columns:

            You could use DataLink search and Current Value functions to set up the data table referenced by the pivot table (A1:H9), but here I just used a PI Builder Element Search with the Show Values in Columns option turned on to return the table (this could be refreshed using the button in PI Builder).

            To get the attributes into columns, select them in the next PI Builder dialogue:

             

            Hopefully that gives you some ideas.  I've attached the Excel workbook that includes the two methods above and tabs with the templates and elements imported via PI Builder if you want to try it out.  Please let me know if you have any questions.

             

            Edit: reposted the Excel file due to file size; not sure why the first one got so bloated. Fixed typos.

            1 of 1 people found this helpful