Traversing JSON output from REST API datasource

[min_3]

I have used the Web Service Data Adapter to connect to a REST API as a datasource for my report.  The output is in JSON format.  My question is what syntax do I use to be able to traverse through the nodes in the JSON file?  Am I able to skip a node using a JSONPATH expression?

I've followed the tutorials on https://community.jaspersoft.com/project/web-service-data-source and have successfully managed to get the api to run, but not able to read the input fields as they are not returned in an array.

I am trying to read the tgaCode (highlighted below):

[narcism]

Are you tied to a specific Jaspersoft Studio version?

[min_3]

No but am currently trialling the latest community version that is available for download. Do the different versions provide different support for api access?

[narcism]

I am not sure about the Web Service Data Source's case since it is not officially supported by TIBCO, as stated in the link you provided. The reason I asked about you being tied to a specific version is because starting with Jaspersoft Studio v6.4.0 you should be able to achieve similar functionality with the built-in mechanisms that reside inside the JasperReports Library on which Studio relies. It is about the JSON Data Adapter(that supports URL parameters, custom headers, HTTP POST/GET/PUT) which used in conjuntion with JSONQL could easily solve your problem. I could only try and guide you towards that solution if that is fine with you.

[min_3]

Your guidance would be great.

[narcism]

Starting with TIBCO Jaspersoft^® Studio v6.4.0 you could safely use the JSON Data Adapter to achieve similar functionality that you get with Web Service Data Adapter.

The main steps are:

1. Create the JSON Data Adapter:

   • Either create one,

     • in the Repository Explorer view(Clik on the first icon in the top-right corner),

     • or, directly in one of your project's folder(Right Click on the folder -> New -> Data Adapter).

   • In the Data Adapter Wizard, you choose the JSON File Data Adapter.

   • Give it a name and paste your JSON API URL in the File/URL input field.

   • Once you have pasted/written the URL the File button will turn into an Options button where you could refine your API call(authentication, HTTP verbs and headers, URL parameters).

   • Check the "Use the report JSON expression when filling the report" to have more flexibility when writing the JSONQL query.

2. Create the Report without specifying the newly created adapter.

3. Open the Dataset and Query Dialog(Click the first icon in the top-right corner of your Design tab):

   •  Pick your JSON Data Adapter in the top-left corner of the Dialog.

   •  Choose jsonql as the language as the JSON one is too limited in functionality.

   •  Write a jsqonql query as explained in the next step.

4. Write a query in the dedicated input field. There are more options when doing this and some depend on the structure of your JSON response from the URL call:

   •  If the "tgaCode" is unique across the whole JSON, and you want only that as a field,  you could simply write this query: "..tgaCode" (without quotes). This means select the tgaCode key from everywhere. Then click the Read Fields button and notice only one field with the "[0]" expression. You could rename the field name at this point to be more appropriate.

   • If the "tgaCode" is not unique across the entire JSON you could write: "qualificationDtoMap..tgaCode" to give it a context to start from.

   •  If you want other keys besides tgaCode you could write: "..[id, tgaCode, tgaTitle]" or "qualificationDtoMap..[id, tgaCode, tgaTitle]".

These are just some of the options to write a query for this specific case. If you want to read more about JSONQL you could check out http://jasperreports.sourceforge.net/sample.reference/jsonqldatasource/index.html#jsonql

---

Note:

All the options in the Data Adapter can be dinamically set through the means of properties set to report parameters.

For instance, if you need to customize the URL endpoint to have it resemble https://jsonplaceholder.typicode.com/todos/{id} you could:

• leave it empty in the adapter

• add these report-level parameters:

<parameter name="todoId" class="java.lang.Integer">    <defaultValueExpression><![CDATA[7]]></defaultValueExpression></parameter><parameter name="theUrl" class="java.lang.String">    <property name="net.sf.jasperreports.http.data.url"/>    <defaultValueExpression><![CDATA["https://jsonplaceholder.typicode.com/todos/" + $P{todoId}]]></defaultValueExpression></parameter>[/code]

The other parameters that could be set this way could be found here:

http://jasperreports.sourceforge.net/api/constant-values.html#net.sf.jasperreports.data.http.HttpDataService.PROPERTY_BODY

[min_3]

Thanks for the guidance @narcism - I have tried and got that to work.  However, with the URL that I specifiy in the JSON data adapter, can that be made dynamic?

e.g. https://jsonplaceholder.typicode.com/todos/{id} 

 

[narcism]

Edited my answer with additional info.

[min_3]

Great, I have so far been able to successfully get that to work. But when I deploy this report to the report server, how do I set up the datasource?

[narcism]

1. Make sure you have a Data Adapter XML file in your workspace. You can export the one from Repository Explorer if you chose that path. Let's assume you named it JSON_DA.xml and placed it in the same folder with your report.

 

2. Link it to the report either:

- by manually setting this property:

- or by setting the Default Data Adapter in the Report Properties tab to point to your XML file

 

3. JasperSoft Studio should pick it up automatically this way when publishing to server

 

[min_3]

I am getting a NullPointerException when trying to preview the report after setting the "net.sf.jasperreport.data.adapter" value to my xml file. Could it be because I have blanked out the url in the data adapter and am using the "net.sf.jasperreports.http.data.url" property to dynamically set the url?

 

 

net.sf.jasperreports.engine.JRException: java.lang.NullPointerException

at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:536)

at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$17(ReportController.java:511)

at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:429)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)

Caused by: java.lang.NullPointerException

at net.sf.jasperreports.eclipse.util.HttpUtils$1.determineRoute(HttpUtils.java:57)

at org.apache.http.impl.client.InternalHttpClient.determineRoute(InternalHttpClient.java:124)

at org.apache.http.impl.client.InternalHttpClient.doExecute(InternalHttpClient.java:183)

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:82)

at org.apache.http.impl.client.CloseableHttpClient.execute(CloseableHttpClient.java:107)

at net.sf.jasperreports.data.http.HttpDataConnection.getInputStream(HttpDataConnection.java:67)

at net.sf.jasperreports.data.DataFileStream.(DataFileStream.java:47)

at net.sf.jasperreports.data.DataFileUtils.getDataStream(DataFileUtils.java:79)

at net.sf.jasperreports.data.json.JsonDataAdapterService.contributeParameters(JsonDataAdapterService.java:75)

at net.sf.jasperreports.engine.fill.JRFillDataset.contributeParameters(JRFillDataset.java:1153)

at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:665)

at net.sf.jasperreports.components.list.FillDatasetRun.start(FillDatasetRun.java:164)

at net.sf.jasperreports.components.list.VerticalFillList.prepare(VerticalFillList.java:102)

at net.sf.jasperreports.engine.fill.JRFillComponentElement.prepare(JRFillComponentElement.java:152)

at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:542)

at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:453)

at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:428)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillTitle(JRVerticalFiller.java:325)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:256)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:110)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:615)

at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:135)

at java.lang.Thread.run(Thread.java:748)

 

Also the published .xml file was renamed to .xml__6378735146977274512. Is this what it should do? Should the report also be pointing to this xml when I look at the Data Source for this report on the server?

 

[narcism]

Yes, that NPE is caused by that. Setting the Evaluation Time to "Early" for each parameter involved in URL creation should fix this.

Regarding the XML file name, I'm not sure why there's no control over the naming, but the report will be linked to it if. You could check that by opening it from the Repository Explorer view instead of the workspace.

[min_3]

Thanks for your help so far @narcism. I've set the net.sf.jasperreport.data.adapter to my xml file and all parameters involved in the URL creation to "Early" but am still getting the NPE when previewing the report in jasperstudio. I'm running version 6.6.0 community edition. The report previews successfully when I have not set "net.sf.jasperreport.data.adapter" to my xml file, but use "com.jaspersoft.studio.data.defaultdataadapter" to access the same xml file in my workspace.

[narcism]

Setting the *net.sf.jasperreport.data.adapter* property should suffice. It will be automatically picked up by Studio. I am also using version 6.6.0 CE final but not receiving any NPEs. Not sure I can help on that anymore unless I reproduce it locally.

[sushobhan.b]

hi narcism , where to add these parameters.

[andremrezende]

I added new parameters within report e.g

<parameter name="ENDPOINT_URL" class="java.lang.String" isForPrompting="false" evaluationTime="Early">
        <property name="net.sf.jasperreports.http.data.url"/>
        <defaultValueExpression><![CDATA[$P{REST_API_URL}.toString()+"/users"]]></defaultValueExpression>
    </parameter>

On XML Adapter, I removed url element.

And everything works perfect.

[rn.chotu.177]

the same issue my system I can not get data you can help me. please anybody help for first will provide the same of money for successfully report. this is my id rameshwar9760881058@gmail.com

[chakradhar.a]

How to override the header value dynamically.

<header><name>Authorization</name><value>Token</value></header>

As I can only see "net.sf.jasperreports.http.data.header" in the constants. I am assuming this property will set the name of the header. If I want to set the value of the header, what do I need to do?