Querying history blocks via SQL server reporting services extension

Historian includes an extension for SQL Server Reporting Services to enable SQL queries against the OData interface used for data stored in history blocks.

To configure the extension

1. Open the Configurator.

2. Under Historian, click the Reporting node.

3. Select the versions of Visual Studio installed with your SQL Server Reporting Services.

4. Click Configure.

To use the extension

5. Define a new Reporting Services data source.

6. Select "Historian OData Provider" as the source type.

7. Click Historian and then click Extension.

8. Enter a connection string in the form "Server=host:port"; for example:

   Server=localhost:32569

Three Ways To Query the Source

You can query the source using one of these options:

• Use a SQL-style query. For example:

  select * from Events where Source_Area='Site2' and EventTime >= '2014-08-10T05:56:21.302Z'

• Use an OData query. This OData query example is equivalent to the query above:

  http://Server1:32569/Historian/v1/Events?$filter=Source_Area+eq+%27Site2%27+and EventTime+ge+datetimeoffset%272014-08-10T05:56:21.302Z%27

  Note: For queries from a web browser, use "%20" to indicate a space. Use "%27" to indicate a single quote.
  If you are using the JSONView viewer in the Chrome browser, you can use a plus sign (+) to indicate a space to make the URI string more readable.

• Use an SQL syntax used within the URL. For example, this is equivalent to the other two queries above:

  http://Server1:32569/Historian/v1/Events?sql=select+*+from Events+where Source_Area=%27ite2%27+and+EventTime+>=+%272014-08-10T05:56:21.302Z%27