sql query to show average of each tag present in proficy historian

Question

I need to develop ssrs report in which user will provide a month as varchar(2) and year as varchar(4). When a user clicks on a view report button, an ssrs report should display result as follows. (suppose if user pass month as 12 and year as 2013)

Date                 TagName               Average
12/01/13             Tag01                   45.23
12/01/13             Tag02                   89.23
12/02/13             Tag01                   2.363
12/02/13             Tag02                   45.23
.
.
.
12/31/13             Tag01                   55.24
12/31/13             Tag02                   95.24

I designed the following query

CREATE TABLE #tempval 
  ( 
     [timestamp] VARCHAR(30), 
     tagname     VARCHAR(300), 
     tagval      DECIMAL(18, 5) 
  ) 

DECLARE @month VARCHAR(2) 
DECLARE @year VARCHAR(4) 

SET @month='08' 
SET @year='2014' 

DECLARE @startdate VARCHAR(30) 
DECLARE @enddate VARCHAR(30) 

SET @startdate=@month + '/01/' + @year 

IF ( CONVERT(INT, @month) >= Month(Getdate()) 
     AND CONVERT(INT, @year) >= Year(Getdate()) ) 
  BEGIN 
      SET @enddate=CONVERT(VARCHAR, (SELECT Getdate())) 
  END 
ELSE 
  BEGIN 
      SET @enddate=CONVERT(VARCHAR, Dateadd(day, -1, Dateadd(month, 1, CONVERT( 
                                                     DATETIME, @startdate 
                                                          )) 
                                    )) 
  END 

DECLARE @query VARCHAR(1000) 
DECLARE @starttime VARCHAR(30) 
DECLARE @endtime VARCHAR(30) 

SET @starttime= CONVERT(VARCHAR, Datepart(month, Dateadd(hour, 22, Dateadd(day, 
                -1, CONVERT( 
                DATETIME, @startdate))))) 
                + '/' 
                + CONVERT(VARCHAR, Datepart(day, Dateadd(hour, 22, Dateadd(day, 
                -1, CONVERT(DATETIME, @startdate))))) 
                + '/' 
                + CONVERT(VARCHAR, Datepart(year, Dateadd(hour, 22, Dateadd(day, 
                -1, CONVERT(DATETIME, @startdate))))) 
                + ' ' 
                + CONVERT(VARCHAR, Datepart(hour, Dateadd(hour, 22, Dateadd(day, 
                -1, CONVERT(DATETIME, @startdate))))) 
                + ':' 
                + CONVERT(VARCHAR, Datepart(minute, Dateadd(hour, 22, Dateadd( 
                day, -1, CONVERT(DATETIME, @startdate))))) 
                + ':' 
                + CONVERT(VARCHAR, Datepart(second, Dateadd(hour, 22, Dateadd( 
                day, -1, CONVERT(DATETIME, @startdate))))) 
SET @endtime=CONVERT(VARCHAR, Datepart(month, Dateadd(hour, 22, CONVERT(DATETIME 
             , @startdate) 
             ))) 
             + '/' 
             + CONVERT(VARCHAR, Datepart(day, Dateadd(hour, 22, CONVERT(DATETIME 
             , @startdate)))) 
             + '/' 
             + CONVERT(VARCHAR, Datepart(year, Dateadd(hour, 22, CONVERT( 
             DATETIME, @startdate)))) 
             + ' ' 
             + CONVERT(VARCHAR, Datepart(hour, Dateadd(hour, 22, CONVERT( 
             DATETIME, @startdate)))) 
             + ':' 
             + CONVERT(VARCHAR, Datepart(minute, Dateadd(hour, 22, CONVERT( 
             DATETIME, @startdate)))) 
             + ':' 
             + CONVERT(VARCHAR, Datepart(second, Dateadd(hour, 22, CONVERT( 
             DATETIME, @startdate)))) 
SET @query='select * from openquery(muri,''set StartTime=''''' 
           + @starttime + ''''',EndTime=''''' + @endtime 
           + 
''''' select * from ihrawdata where tagname=MURISERVER.MURI.DCS.ASSETS.87A.87A_FI_2101.DACA.PV  and samplingmode=rawbytime'')'

EXEC (@query) 

But it displays blank columns but I'm not sure why.

Answer 1

The easiest way I've found for an SSRS report to query GE Proficy Historian is to use dynamic SQL within a stored proc.

The prerequisite is that you have a SQL Server linked server configured using the IhOLEDB.iHistorian provider. That would look something like this once installed:

You then create a stored proc as normal, I usually have a fairly generic proc which takes most of the arguments you'd want to query a GE Historian with. Example stored proc definition:

CREATE PROCEDURE [utility].[Historian_GetData] @DateFrom DATETIME,
                                           @DateTo DATETIME,
                                           @TagList  VARCHAR(1000),
                                           @Historian NVARCHAR(100),
                                           @SampleInterval NVARCHAR(50),
                                           @SampleMethod NVARCHAR(50),
                                           @DefaultRowCount INT = 5000

However to keep the example simple, here is an anonymous block with some dates which will be used to query. Essentially, for each of the parameters in the stored proc you'd add them to the dynamic SQL.

DECLARE @DateFrom NVARCHAR(100) = '2018-05-02 06:00'
DECLARE @DateTo   NVARCHAR(100) = '2018-05-03 06:00'

DECLARE @SQL NVARCHAR(4000)
DECLARE @Result TABLE (TagName NVARCHAR(100) NOT NULL, [Timestamp] DATETIME NOT NULL, Value NVARCHAR(100) NOT NULL, Quality NVARCHAR(100) NOT NULL)

BEGIN
   SET @SQL = 'SELECT tagname, timestamp, value, quality
           FROM OPENQUERY ([Your-Historian],
           ''SET StartTime = "' + @DateFrom + '", EndTime = "' + @DateTo + '",
             SamplingMode = RawByTime
             SELECT tagname, timestamp, value, quality
             FROM ihRawData
             WHERE TagName = XYZ'')';

  INSERT INTO @Result EXEC sp_executesql @SQL

  SELECT TagName,
         [Timestamp],
         [Value],
         Quality
  FROM @Result ORDER BY TagName, [Timestamp]
END

The result for that will look something like this:

So in summary:

1. Configure your linked server to point to your Historian
2. Create your stored proc accepting all the Historian query arguments as parameters.
3. Execute your dynamic SQL
4. Calculate your result as required, in your case some averages.