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?
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...
User contributions licensed under CC BY-SA 3.0