topic Re: URLTemplateAction could generate JSON in Windchill

URLTemplateAction could generate JSON

rleir — Mon, 27 Sep 2021 16:15:21 GMT

Hi all

When you run a report from a URL using URLTemplateAction you have the option of getting the results in CSV, XML, or HTML formats:

https://support.ptc.com/help/wnc/r12.0.2.0/en/#page/Windchill_Help_Center%2FWCCG_BusLogicCust_ReportGeneration_CustDetails.html%23

But it is 2021, how about the JSON format? Yes I know I could write some XSLT and do this myself.. It looks as if I will need to parse the XML.

Thanks

Rick

Re: URLTemplateAction could generate JSON

aaronjlarson — Mon, 27 Sep 2021 16:21:39 GMT

I've thought this too - it would be a nice addition to the output types. Depends on what your end goal is and where the users are going to interact with the data. But if you want JSON you're going to have to use the REST APIs as far as I know. If I use Query Builder I now am using CSV output and I read this direct into PowerBI. I used to use XML but the CSV is way cleaner, faster, and less error prone. XML always has some issues with data types changing on enumerations and also with invalid hex characters.

Re: URLTemplateAction could generate JSON

rleir — Mon, 27 Sep 2021 16:59:51 GMT

Aaron

Agreed, CSV or TSV is quick and simple. But we have field values containing line breaks, and that problem gets messy.

Yes, the REST API's... I have done some work with them but could not access all the tables that QB could see. It seemed that development would be quicker if I run a report. Next I will see if WC provides a usable Excel format via excel2000WebQuery.xsl or excel97WebQuery.xsl.

Thanks -- Rick

Re: URLTemplateAction could generate JSON

rhart — Mon, 27 Sep 2021 18:17:32 GMT

Hi @rleir ,

Another option is to run the query builder report using REST, there’s two ways. The report can be Invoked from an IE task or a Generate-Report’ webject action can be invoked directly from REST, both return a JSON or XML object containing the results and you can pass parameter to the query using ‘INPUT’ params from REST. You also have some extras like running two or more reports and getting both results in the same output.

http://support.ptc.com/help/windchill/wc111_hc/whc_en/index.html#page/Windchill_Help_Center%2FWC_WebjectLib_GenerateReport_PARAMETERS27.html%23

Re: URLTemplateAction could generate JSON

rleir — Tue, 28 Sep 2021 12:22:44 GMT

Hi @rhart ,

Would you be able to show me an example using a Generate-Report’ webject action, invoked directly from REST please? I have explored the link you showed, and I have looked into $domain/Windchill/netmarkets/html/wrs/doc.html, and neither seems to show how to do it.

thanks

Rick

Re: URLTemplateAction could generate JSON

rhart — Tue, 28 Sep 2021 15:51:02 GMT

Hi @rleir

Looking at the wrs documentation area, here's how to produce a working example.

I attached a video but you might have to wait for a moderator to clear it before you can watch.

(view in My Videos)

Re: URLTemplateAction could generate JSON

rleir — Wed, 29 Sep 2021 10:01:35 GMT

Hi @rhart

Lots of the PTC doco assumes that I am using JSP and never shows a full URL. But no, think Python or Ruby or curl or Excel Macro VBA. An example URL that starts a Generate-Report webject action would be appreciated!

A video is nice thanks but just write a single line of text please.

cheers -- Rick

Re: URLTemplateAction could generate JSON

rhart — Wed, 29 Sep 2021 10:09:22 GMT

curl -X POST --header 'Content-Type: application/json' --header 'Accept: application/json' --header 'CSRF_NONCE: mynonce' -d '{"Task":"com/ptc/windchill/enterprise/report/ExecuteReportTemplate.xml","Params":[{"Value":"wt.query.template.ReportTemplate:603747175","Name":"object_ref","ValueSet":[]}]}' 'http://mywindchillserver/Windchill/servlet/odata/v1/IE/InvokeIETask?%24count=false'