Posted 27 January 2019, 9:30 pm EST
- Updated 30 September 2022, 12:52 am EST
Hello Tom,
Sorry for the delay! We are very thankful to you for showing interest in ActiveReports Server.
how to do it or any hints in the server admin UI
Actually, ARS supports both SQL provider and Json provider to bind the report with SQL and Json data respectively. However, Json provider only supports the json data from the files, URL or directly embedded in report. To know about the JSON provider, please refer to the following link:
ActiveReports 12
There is no direct way to use the json data return a SQL query in Json provider. However, there is an indirect to achieve the above thing. Please follow the below steps:
1:Create a SQL DataSource on Admin site. To know more about, how to create Datasource on Admin site, please refer to the following link:
ActiveReports 12 Server User Guide
2:Create a SQL DataSet on Admin Site from the above the DataSource which returns the JSON data. To know more about, how to create DataSet on Admin site, please refer to the following link:
ActiveReports 12 Server User Guide
3:Create a JSON DataSource on admin site. Json provider connection string need Json data and json schema. For generating JSON schema, use the JSON schema generator available at http://jsonschema.net/#/ . As Json data return from the above created datasource, so we can’t provide the json data directly to json provider. Hence, we have to create the parametric connection string like below:
="jsondata=" + Parameters!ReportParameter1.Value + ";schemadata={ 'definitions': {}, '$schema': 'http://json-schema.org/draft-07/schema#', '$id': 'http://example.com/root.json', 'type': 'array', 'title': 'The Root Schema', 'items': { '$id': '#/items', 'type': 'object', 'title': 'The Items Schema', 'required': [ 'Month', 'Jurisidictional', 'Referral', 'Cre mation Only' ], 'properties': { 'Month': { '$id': '#/items/properties/Month', 'type': 'string', 'title': 'The Month Schema', 'default': '', 'examples': [ 'Jan' ], 'pattern': '^(.*)$' }, 'Jurisidictional': { '$id': '#/items/properties/Jurisidictional', 'type': 'integer', 'title': 'The Jurisidictional Schema', 'default': 0, 'examples': [ 50 ] }, 'Referral': { '$id': '#/items/properties/Referral', 'type': 'integer', 'title': 'The Referral Schema', 'default': 0, 'examples': [ 10 ] }, 'Cre mation Only': { '$id': '#/items/properties/Cre mation Only', 'type': 'integer', 'title': 'The Cre mation only Schema', 'default': 0, 'examples': [ 60 ] } } }}"
Value of ReportParameter1 can be given in the report in which above datasources is used
4:Create a json dataset from the above json datasource.
5:Now, create a report Web Designer. To know more about, how to create report using Web designer, please refer to the following link:
https://help.grapecity.com/activereports/webhelp/AR12WebDesigner/webframe.html#GetStarted.html
6:Add both datasets(Created above) in the report.
7:Create the “ReportParameter1” in the report like below:
8:Drag and drop the table. After that, bind the table with json dataset.
9:Preview the Report
I’ve looked into using the JSON provider to pull the data from a web service
“I’d have to be able to pass parameters to the web service” from this, I have understand that either (A)you want to pass the parameter in URL of Web Service or (B)you want to pass the parameter(like method, headers, body) in RestAPI.
To achieve A, please follow the below steps:
1.Create the parametric Json datasource like above:
="jsondoc=https://jsonplaceholder.typicode.com/posts?userId="+Parameters!ReportParameter1.Value+";schemadata={ 'definitions': {}, '$schema': 'http://json-schema.org/draft-07/schema#', '$id': 'http://example.com/root.json', 'type': 'array', 'title': 'The Root Schema', 'items': { '$id': '#/items', 'type': 'object', 'title': 'The Items Schema', 'required': [ 'userId', 'id', 'title', 'body' ], 'properties': { 'userId': { '$id': '#/items/properties/userId', 'type': 'integer', 'title': 'The Userid Schema', 'default': 0, 'examples': [ 2 ] }, 'id': { '$id': '#/items/properties/id', 'type': 'integer', 'title': 'The Id Schema', 'default': 0, 'examples': [ 11 ] }, 'title': { '$id': '#/items/properties/title', 'type': 'string', 'title': 'The Title Schema', 'default': '', 'examples': [ 'et ea vero quia laudantium autem' ], 'pattern': '^(.*)$' }, 'body': { '$id': '#/items/properties/body', 'type': 'string', 'title': 'The Body Schema', 'default': '', 'examples': [ 'delectus reiciendis molestiae occaecati non minima eveniet qui voluptatibus\naccusamus in eum beatae sit\nvel qui neque voluptates ut commodi qui incidunt\nut animi commodi' ], 'pattern': '^(.*)$' } } }}"
2.Create the dataset like above
3.Create the report and add the above json dataset
4.Create a “ReportParameter1”
5.Preview the report
6.Enter the parameter value
To achieve B, please follow the below steps:
1.Create the Json datasource like below:
method=POST;body={ "User": "admin", "Password": "1"};jsondoc=http://localhost:8080/api/accounts/login;schemadata={ "$schema": "http://json-schema.org/draft-04/schema#", "definitions": {}, "id": "http://example.com/example.json", "properties": { "Token": { "id": "/properties/Token", "type": "string" } }, "type": "object"}
2.Create the dataset like above
3.Create the report and add the above json dataset
4.Preview the report
I’ve looked into using SQL JSON functions to query the json in the database using the MS SQL provider
In my understanding, you don’t need to use the “OPENJSON” function if you use the above solution. If I am missing something, could you please explain in detail.
Hope it helps.
Thanks.
