Unable to query with SSRS parameter to iHistorian

Question

I'm trying to get iHistorian to accept a query from SSRS using a parameter for Date, with OLEDB.

Query is like this in the Report Builder Dataset

SELECT timestamp, tagname, value
FROM     ihrawdata
WHERE timestamp LIKE '@date' 

This gives error message from Report Builder when running report preview:

iHOLEDB.iHistorian.1' failed with no error message available, result code: E_INVALIDARG(0x80070057)

Anyone know how I can get a parameter from SSRS query to work with iHistorian?

Answer 1

The best way I've found to query Proficy Historian via the OLEDB with parameters is to create a stored procedure and have the report use the stored procedure as its source, passing the parameters you need.

Here's an example using your example of a timestamp param to filter Historian data. You could also extend the stored proc pretty easily to pass in parameters for SamplingMode, DateFrom, DateTo, etc

Step 1 Use the the help documentation to install the OLEDB driver onto your SQL Server instance and create the linked server pointing to your Historian.

Step 2 Verify the OLEDB driver and Linked Server exist. They should look something like this:

Step 3 Create a stored procedure in the DB that uses the new linked server:

CREATE PROCEDURE [Historian_GetDataByTimestamp]
    @HistorianLinkedServer NVARCHAR(100),
    @Timestamp DATETIME

AS

BEGIN
    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);

    SET @SQL = 'SELECT tagname, timestamp, value, quality
                FROM OPENQUERY ("' + @HistorianLinkedServer + '",
               ''SET SamplingMode = RawByTime
                 SELECT tagname, timestamp, value, quality
                 FROM ihRawData
                 WHERE Timestamp = "' + CONVERT(nvarchar, @Timestamp, 20) + '"'')';

      INSERT INTO @Result EXEC sp_executesql @SQL

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

Step 4 Test your stored proc from SSMS or similar:

Step 5 Configure your SSRS dataset to query via the stored proc, passing parameters as necessary. Here's a step by step guide to configure that if you've not done that before, it's super simple...