JSON DataSource from SQL Database field

Posted by: tkuharski on 24 January 2019, 10:14 am EST

  • Posted 24 January 2019, 10:14 am EST

    Hello.

    I'm evaluating Active Reports Server 12 currently and trying to setup a shared dataset and/or source that will map or bind fields from JSON that we are storing in an SQL database for use with the server's web based report designer.

    The JSON objects include variable length arrays of sub objects that are needed also.
    Ex (simplified to save space):
    {
    "Name":"Bob",
    "Age" : 30,
    "Addresses":[
    {"City":"Milwaukee", "State":"WI", "Zip":"54123"},
    {"City":"Red Granite", "State":"WI", "Zip":"54456"}
    ]
    }


    I've successfully setup a basic MS SQL Provider that accesses our db and a JSON provider that reads static json but haven't yet gotten the json from the database completely accessible from the page report designer.

    I've tried a few ways already with little success.

    • I've looked into using the JSON provider to pull from an MS SQL provider but didn't find any materials on how to do it or any hints in the server admin UI

    • I've looked into using the JSON provider to pull the data from a web service but there doesn't appear to be any documentation or examples on how to use web resources with it besides saying that you can. (please let me know if this is incorrect, I'd have to be able to pass parameters to the web service in this case)

    • I've looked into using SQL JSON functions to query the json in the database using the MS SQL provider. I came close here unfortunately the OPENJSON() function needed to get the lists of sub objects properly, isn't available on our database because we're running a compatibility level of 100. (Maybe I missed something and there's another way to get those lists of sub objects as rows?)



    I've been scouring the documentation for a couple of days now and haven't found enough information to put together a complete solution.

    Would anyone here happen to know how to do this?

    Thanks in advance.
  • Replied 24 January 2019, 10:09 pm EST

    Hello Tom,

    We are working on this issue and will get back to you as soon as possible.

    Thanks.

  • Marked as Answer

    Replied 27 January 2019, 9:30 pm 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:
    https://help.grapecity.com/activereports/webhelp/AR12/webframe.html#JSONProvider.html
    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:
    https://help.grapecity.com/activereports/webhelp/AR12Server/webframe.html#ManagingDataSources.html

    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:
    https://help.grapecity.com/activereports/webhelp/AR12Server/webframe.html#ManagingDataSets.html

    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.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels