12 Replies Latest reply on Jun 17, 2015 3:01 PM by Halenger

    SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs

    Halenger

      Hello,

      Using SQL Server Reporting Services (SSRS) to generate reports for a system only using PI Manual Logger, I'm looking for help on a query that can retrieve the last two tour runs of a specific tour run.  In essence, its a tour ran once per day, so I need to retrieve the current and previous tours so I can subtract totalizers, for example, to see that days usage.  Any ideas?@

        • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
          dng

          Hi John,


          PI Manual Logger writes the tour run information into PI Data Archive. The best way would be to query the PI Data Archive for values directly. Check out PI OLEDB or PI OLEDB Enterprise, if you are not using them currently. With these products installed, you can set up linked servers in the SQL server, you can then run queries to get values from the PI Data Archive directly (via PI OLEDB, if you are not using AF), or from attributes in PI AF (via PI OLEDB Enterprise).

           

          Since you indicated that there is a tour run once a day, you can query results from yesterday at midnight to today to get the archived value from a linked server set up with PI OLEDB. E.g.

          SELECT * from OPENQUERY
          ([OLEDB PROVIDER 2],
          'SELECT * FROM [piarchive].[picomp2]
          WHERE tag = ''sinusoid''
            AND time between ''y'' and ''*''')
          
            • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
              Halenger

              Hi Daphne,

              Sorry it's taken me awhile to get back to this, I recently had my second child and thus been away.

              Thank you for the suggestion of using PI OLEDB (Enterprise), but the customer does not have a PI Data Access license and most likely won't. My understanding is a PI Data Access license is needed in order to use PI OLEDB and other suites (PI Web Services for example).

              We've been using SSRS querying directly the SQL database for reports - I know the PI Server (Data Archive) is not really being utilized using this, but the customer is only using the PI Server for PI Manual Logger (& PI ML Mobile), so the data goes thru a SQL database before being sent to the PI Data Archive.  We ended up doing this because PI DataLink isn't report friendly with PI Manual Logger (for example retrieving data from specific tours by specific operators).

              Any idea how to query against the SQL database to find last two runs of a specific tour?

               

              Thanks,

              John

                • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
                  scheung

                  When I read your original post it sounds like you're looking for the difference between two values of a daily totalizer. Is PI ML data being fed into a totalizer tag? Or are you using PI ML to record the daily total? If the former, then it would be easiest to pull the totalizer data from the PI Data Archive so you don't need to redo the totalizations. This can be accomplished using PI OLEDB for SQL-based applications (Daphne's suggestion). If the latter, then you might be able to get away querying PimlWindows directly. You'll likely want to bring together info tblTour, tblTourRun, tblTourRunTag, and tblPoint. Here's a sample query that you can work off of:

                   

                  WITH TourRunData_CTE([PointID], [Value], [TimeStamp])
                  AS
                  (
                  SELECT trt.[PointID], trt.[Value], trt.[TimeStamp]
                  FROM tblTourRunTag trt
                  WHERE trt.[TourRunID] IN(
                    SELECT TOP 2 tr.[TourRunID]
                    FROM tblTourRun tr
                    WHERE tr.[TourID] = (
                    SELECT t.[TourID]
                    FROM tblTour t
                    WHERE t.TourName = 'MyTour')
                    ORDER BY tr.[TourRunTimeStamp] DESC)
                  )
                  SELECT p.[TagName], CAST(t2.[Value] AS int) - CAST(t1.[Value] AS int) AS [Delta]
                  FROM TourRunData_CTE t1
                  INNER JOIN TourRunData_CTE t2 ON t1.[PointID] = t2.[PointID] AND t1.[TimeStamp] < t2.[TimeStamp]
                  INNER JOIN tblPoint p ON p.[PointID] = t1.[PointID]
                  
                  

                   

                  We start with a CTE that includes the tag data for the most recent 2 tour runs for a tour named MyTour. The CTE is joined to itself using a condition to match up the tag data -- newer stuff on the right. The final result gives us the difference between the newer and older tag values (t2.[Value] - t1.[Value]) as well as the name of the tag we calculated a difference for. Here's a screenshot of sample query results:

                   

                  results.png

                   

                  Please note:

                   

                  1. Query assumes tag data is integer. You will need to introduce logic for other tag types.

                  2. Your tour name should replace MyTour. You can pass this in as a query parameter.

                  3. There are no guarantees that this query will stand the test of time. Schema changes down the road may break these queries.

                    • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
                      Halenger

                      Wow Sam, this is good stuff!  I'll need to dissect your code a little more, as you introduced many new SQL procedures to me (right off the bat the WITH statement for example).

                      I am indeed desiring to query PimlWindows db directly.  However, not every tag in the tour I look to find delta, only a select few... So I guess what I'm looking for help with is retrieving values of the last two tours performed - perhaps two separate queries?  I can calculate delta(s) in SSRS for the desired tag(s).  I'm looking to make this a subscription to print out daily to a local printer.

                       

                      Side note - how would I query for values that are integer and other types?  Your code has me quite intrigued!

                        • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
                          scheung

                          If you're looking for 2 separate result sets -- one with the most recent and another with the 2nd most recent tour run -- then maybe use something like this:

                           

                          WITH RankedTourRun_CTE([RowNum], [TourRunID], [TourRunTimeStamp])
                          AS
                          (
                          SELECT ROW_NUMBER() OVER (ORDER BY tr.[TourRunTimeStamp] DESC) AS RowNum
                            , tr.[TourRunID]
                            , tr.[TourRunTimeStamp]
                          FROM tblTourRun tr
                          WHERE tr.[TourID] = (
                            SELECT t.[TourID]
                            FROM tblTour t
                            WHERE t.[TourName] = 'MyTour')
                          )
                          SELECT p.[TagName], trt.[TimeStamp], trt.[Value]
                          FROM RankedTourRun_CTE c
                          INNER JOIN tblTourRunTag trt ON trt.[TourRunID] = c.[TourRunID]
                          INNER JOIN tblPoint p ON p.[PointID] = trt.[PointID]
                          WHERE c.[RowNum] = 1 -- or 2, if you want the 2nd most recent
                          
                          

                           

                          CTE goes and assigns a rank to each tour run for MyTour (1 for the most recent, n for the least). With this information in hand, you can go looking for the tblTourRunTag records for the tour runs ranked 1 or 2 (depending on whether you want the most or 2nd most recent tour run data). Here are some screenshots on the same sample data used in my earlier post.

                           

                          Rank 1:

                          rank1.png

                           

                          Rank 2:

                          rank2.png

                          As you can see, subtracting the Rank 2 stuff from the Rank 1 yields the Delta from before.

                           

                          Note:

                           

                          1. When I mentioned you'll need additional logic for non-integers, this is because of how the Delta is calculated. The Values are casted to integers. Not all Values will be integers, so additional handling on how the Delta is calculated is needed depending on point type.

                  • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
                    Halenger

                    I'd like to ask for help again with this topic.  The original post was about creating a report that would print out daily the last two tours that happen to be taken around midnight each night; I've got this to work using Sam's suggestion/queries.  I've basically created a dataset for each tag I need on the report, where each dataset is querying that specific PI tag value recorded from the last two tours.  Inside the report, I can use an expression to find the difference from the two values where this gives me consumption's for the previous day (from the printout date).

                    The customer has now requested to be able to select a specific date to query against to find that days consumption.... So I can no longer query based off the number of tours from the most recent tour.  I'm asking for help on 1.) make a drop down of tours taken of a specific tour, 2.) when the operator selects desired tour, retrieve the value of the PI tag that tour AND the value from the tour taken previous of the selected tour.  I again assume I'll have to make a dataset for each tag in the tour to be able to show values in a table and use an expression to find the difference.

                    I've attached a screenshot of what the report was/is to look like.... it was not easy aligning everything!  Your continued support is very appreciated...

                    Midnight Report.png

                      • Re: SSRS - PI Manual Logger - Retrieve Last 2 Tour Runs
                        dng

                        Hi John,

                         

                        Regarding your first request, what did you intend to display (e.g. timestamp of tour run, tour run "number" on that day)? This following SQL request allows you to retrieve the timestamps for all tour runs in a specific day:

                        SELECT t.TourName, tr.TourRunTimeStamp
                        FROM [PimlWindows].[dbo].[tblTourRun] tr
                        INNER JOIN [PimlWindows].[dbo].[tblTour] t ON tr.TourID = t.TourID
                        WHERE t.TourName = 'ML test'
                        AND tr.TourRunTimeStamp > '2015-04-09 00:00:00'
                        AND tr.TourRunTimeStamp < '2015-04-10 00:00:00'
                        ORDER BY tr.TourRunTimeStamp ASC
                        

                         

                        The results:

                        2015-06-17 09_45_31-SQLQuery3.sql - SDSQL_SQLEXPRESS.PimlWindows (OSI_dng (59))_ - Microsoft SQL Ser.png

                         

                        Regarding your second request, you can use a similar query to what Sam has provided before to achieve what you want:

                        WITH TourRunData_CTE([RowNum], [TourName], [TourRunID], [TourRunTimeStamp])
                        AS
                        (
                          SELECT ROW_NUMBER() OVER (ORDER BY tr.TourRunTimeStamp ASC) AS RowNum
                          , t.TourName
                          , tr.TourRunID
                          , tr.TourRunTimeStamp
                          FROM [PimlWindows].[dbo].[tblTourRun] tr
                          INNER JOIN [PimlWindows].[dbo].[tblTour] t ON tr.TourID = t.TourID
                          WHERE t.TourName = 'ML test'
                          AND tr.TourRunTimeStamp > '2015-04-09 00:00:00'
                          AND tr.TourRunTimeStamp < '2015-04-10 00:00:00'
                        )
                        SELECT c.TourName, c.TourRunTimeStamp, p.TagName, trt.Value
                        FROM TourRunData_CTE c
                        INNER JOIN tblTourRunTag trt on trt.TourRunID = c.TourRunID
                        INNER JOIN tblPoint p on p.PointID = trt.PointID
                        WHERE RowNum = 3 
                        OR RowNum = 3-1
                        

                         

                        In the above query, we are retrieving the tag values for the 3rd tour run of the day, as well as the 2nd tour run (previous tour). Here is the results:

                        2015-06-17 09_49_43-SQLQuery6.sql - SDSQL_SQLEXPRESS.PimlWindows (OSI_dng (54))_ - Microsoft SQL Ser.png

                         

                        Hope it helps. Please let us know if you have any questions.