Thing Property used in SQL Server and SSRS

[Gizem_Turan]

Hello,

we want to use SSRS Reporting to link the Reports in mashups in ThingWorx.

Therefore we want to Access property values of Things in SQL Server, to be able to create queries. Is that possible? Is there a SQL CLR Option for ThingWorx for example?

The data of the Thing properties will come from our PI Connector.

Thank you in Advance!

Regards,

Gizem

[rjanardan]

It seems SSRS can consume REST API. You can probably create a datasource utilizing the ThingWorx REST API to get the property values from TWX to SSRS. 

[Gizem_Turan]

Hello,

thank you for the quick answer. I have found out that via a http REST call I can get the property values of a specific Thing. But how can I integrate this REST call into SSRS? I could not find a way to configure a datasource with a REST call.

And another question; does ThingWorx support something like SQL CLR? To create e.g. functions in SQL Server by using property values of ThingWorx Things?

Thank you!

[rjanardan]

Probably on ThingWorx side you could write a service that compiles all the required property values and outputs it in the required format let's say xml or JSON. Then you could make rest calls to this service instead of fetching individual properties values.

Probably, this video(not  from PTC) could be helpful in configuring datasource with a REST call. 

[Gizem_Turan]

Thank you for your answer and the link. The video is helping to understand how to integrate a REST call in XML format.

But how can I get the REST Response in ThingWorx in XML format?  I am calling for example the properties of a thing like this:

https://localhost/ThingWorx/Things/<MyThingName>/Properties

And how can I get all values of a property for a time range with start and end time Parameters?

Thank you very much in advance for your help!

[rjanardan]

Unfortunately, I do not know how to set headers when making REST calls from SSRS(i.e. if this is possible). The below screenshot shows how you can call a service as QueryPropertyHistory on a Thing from Postman client and get an XML result. 

[Gizem_Turan]

I could find out that when adding the following text in bold, the Output of the REST call is in XML Format:

https://localhost/ThingWorx/Things/<ThingName>/Properties/<PropertyName>/?Method=Get&Accept=text/xml

In General my aim is to be able to read data out of properties (which is coming from an external historian) and use this data in SQL Server for SSRS Reports. I could find out that we could make an Service which is doing an INSERT INTO the SQL Database with the values coming for example from a thing-property.

[rjanardan]

This guide would be helpful for your purpose. 

[slangley]

Hi @Gizem_Turan.

If one of the previous responses answered your question, please mark the appropriate one as the Accepted Solution for the benefit of others with the same question.

Thank you for your participation in our community!

Regards.

--Sharon