My Blog

BI Publisher 12c: Running Reports via the REST API

by Paul Cannon on 26th June 2017 No comments

BI Publisher 12c comes with a suite of REST APIs allowing you to manage and run reports via URL calls, primarily to allow you to make automated calls from custom applications. The APIs provide a wide range of functionality from creating reports & templates, updating definitions & xliff translation files to viewing the report definition & sample data, plus of course actually running the report.

In this blog I’ll go through the basics of viewing the report definition and running a report – providing parameters and downloading the report.

There are a number of ways to perform REST API calls, I’ll be using CURL, a command line utility that I’ve used before. If you need help downloading and installing CURL, see the first part of an earlier blog: http://www.redstk.com/bics-using-the-rest-api-with-curl/

The basic format of the CURL command is this:

curl -X GET -u userid:password -o outputfile.txt -H "Accept:application/json" -d "{}" http://bip_server:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/action

Where:

-X GET specifies the method of request, for BI Publisher calls there are two types of methods:GET and POST – essentially the POST type being used when data needs to be supplied to the request and GET when it doesn’t.

-u userid:password. This is the user-id to login to BI Publisher with. It can be sent in plain text (as in this example), 64 bit encoded (to at least obfuscate the details and make it less readable), or encrypted. Later on I’ll show how to encode it – encryption is beyond the scope of this blog!

-o outputfile.txt. The name of the file to save the response to. This is often a text file – you can extract the report definition, the data model, even the sample data. It can also be other types, such as PDF, for when producing the actual report. If the output of the call is TEXT then this parameter can be left off, in which case the output will simple display on the screen where you ran the command from.

-H “Accept:application/json”. The header details of the request – additional information the REST API requires to understand the request being made. Some requests require multiple headers.

-d “{}”.  This is any data required by BI Publisher to perform the request, such as sending parameters when running the report. Two curly brackets by themselves simply mean there is no data, however many (if not all – I’ve not checked every one!) of the BI Publisher REST API calls require the data parameter even if no data is needed.

http://bip_server:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/action. This is the URL for the BI Publisher report itself with an additional item on the end to indicate the required action (e.g. “run” to run the report). The URL is broken up into two sections:

The first part identifies the server and REST API service:

http://bip_server:9502/xmlpserver/services/rest/v1/reports/

The second part is the location of the report beneath the Shared Folder in the catalog:

/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation

Any spaces are replaced with %20 and any forward slashes with %2F, so my example is pointing at the following report in the catalog:

Shared Folders/BIP Reports/Order Confirmation/Order Confirmation

There are two additional optional parameters on the CURL command that are worth being aware of as they can help with testing if you are having problems:

-i   This includes the header detail of the request in the output, so you can confirm what you are requesting

-v  This is the verbosity setting, so CURL will output detail of what it’s doing as it goes along.

Before trying our first REST API call, a word about the user/password setting. As mentioned above the basic -u parameter will include this a plain text in the request – fine if you playing with a sandbox VM, not very clever otherwise. Encryption is a big subject by itself so I can’t go into that here. Encoding however is a sensible approach if using calls over a secure internal network. Encoding simply replaces the user-id and password with an obfuscated string of characters. It makes the userid & password unreadable to a human simply looking at the network traffic, although a determined hacker who recognises what he is seeing could un-encode it, so it’s not secure, but better than plain text.

There are many ways to encode a user-id/password. A simple quick way is to use the base64encode website: https://www.base64encode.org/

On this site select the ‘Encode’ tab and type in your userid/password in the form userid:password and click encode:

bip_rest_api_1

The encoded version when appears beneath:

bip_rest_api_2

In the CURL command we need to replace the -u parameter with an additional -H header parameter:

-H “Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx”

With the entire encrypted string following the word “Basic” with a space separator. I’ll be using this from now on in this blog.

The first REST API call I’m going to show is the ‘Get Definition’ call. This displays the basic information about the BI Publisher report – data model file, output formats, parameters and so on.

This is the most basic of API Calls, there is no action parameter on the URL (it’s the default action), there is one Header parameter, specifying that the output is in JSON format. It uses the GET method:

curl -X GET -o definition.txt -H "Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx" -H "Content-Type: application/json" -d "{}" http://bip_server:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation

The output of this is a JSON formatted string which isn’t easy to read:

{"ESSJobName":"","ESSPackageName":"","autoRun":true,"cacheDocument":false,"controledByExtApp":false,"dataModelURL":"/BIP Reports/Order Confirmation/Order Confirmation DM.xdm","defaultOutputFormat":"pdf","defaultTemplateId":"Order Confirmation","diagnostics":false,"listOfTemplateFormatsLabelValues":[{"active":true,"applyStyleTemplate":true,"default":true,"listOfTemplateFormatLabelValue":[{"templateFormatLabel":"PDF","templateFormatValue":"pdf"},{"templateFormatLabel":"Excel (*.xlsx)","templateFormatValue":"xlsx"},{"templateFormatLabel":"PowerPoint (*.pptx)","templateFormatValue":"pptx"}],"templateAvailableLocales":["en"],"templateDefaultLocale":"en","templateID":"Order Confirmation","templateType":"rtf","templateURL":"Order Confirmation.rtf","viewOnline":true}],"onLine":true,"openLinkInNewWindow":true,"parameterColumns":3,"parameterNames":["p_orderid"],"reportDefnTitle":"","reportName":"Order Confirmation","reportParameterNameValues":[{"UIType":"Text","dataType":"xsd:integer","defaultValue":"2458","fieldSize":"15","label":"Order ID:","multiValuesAllowed":false,"name":"p_orderid","refreshParamOnChange":false,"selectAll":false,"templateParam":false,"useNullForAll":false}],"showControls":true,"showReportLinks":true,"templateIds":["Order Confirmation"]}

I usually open it in a text editor and do some quick reformatting:

{"ESSJobName":"",
"ESSPackageName":"",
"autoRun":true,"cacheDocument":false,
"controledByExtApp":false,
"dataModelURL":"/BIP Reports/Order Confirmation/Order Confirmation DM.xdm",
"defaultOutputFormat":"pdf",
"defaultTemplateId":"Order Confirmation",
"diagnostics":false,
"listOfTemplateFormatsLabelValues":[{
"active":true,
"applyStyleTemplate":true,
"default":true,
"listOfTemplateFormatLabelValue":[{
"templateFormatLabel":"PDF",
"templateFormatValue":"pdf"},
{"templateFormatLabel":"Excel (*.xlsx)","templateFormatValue":"xlsx"},
{"templateFormatLabel":"PowerPoint (*.pptx)","templateFormatValue":"pptx"}
],
"templateAvailableLocales":["en"],
"templateDefaultLocale":"en",
"templateID":"Order Confirmation",
"templateType":"rtf",
"templateURL":"Order Confirmation.rtf",
"viewOnline":true
}],
"onLine":true,
"openLinkInNewWindow":true,
"parameterColumns":3,
"parameterNames":["p_orderid"],
"reportDefnTitle":"",
"reportName":"Order Confirmation",
"reportParameterNameValues":[{
"UIType":"Text",
"dataType":"xsd:integer",
"defaultValue":"2458",
"fieldSize":"15",
"label":"Order ID:",
"multiValuesAllowed":false,
"name":"p_orderid",
"refreshParamOnChange":false,
"selectAll":false,
"templateParam":false,
"useNullForAll":false}
],
"showControls":true,
"showReportLinks":true,
"templateIds":["Order Confirmation"]
}

If you simply want to know the parameters of a report you can use the parameters request which brings back a shorter version of the definition. Note this has a request method of POST:

curl -X POST -o parameters.txt -H "Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx" -H "Content-Type: application/json" -d "{}" http://bip_server:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/parameters

which returns the following:

{"listOfParamNameValues":[{"UIType":"Text","dataType":"xsd:integer","defaultValue":"2458","fieldSize":"15","label":"Order ID:","multiValuesAllowed":false,"name":"p_orderid","refreshParamOnChange":false,"selectAll":false,"templateParam":false,"useNullForAll":false}]}

Which when nicely formatted looks like:

{
"listOfParamNameValues":[{
"UIType":"Text",
"dataType":"xsd:integer",
"defaultValue":"2458",
"fieldSize":"15",
"label":"Order ID:",
"multiValuesAllowed":false,
"name":"p_orderid",
"refreshParamOnChange":false,
"selectAll":false,
"templateParam":false,
"useNullForAll":false
}]
}

Two more very similar API Calls are xdoSchema and sampleData, both are GET calls with request types of /xdoSchema and /sampleData  (note the uppercase S and D):

curl -X GET -o xdoschema.txt -H "Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx" -H "Content-Type: application/json" -d "{}" http://bip_server:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/xdoSchema

This lists all of the groups and fields from the data model:

<output>
<nodeList name="DATA_DS" label="DATA_DS" dataType="xdo:dataStructure">
<element name="P_ORDERID" label="p_orderid" dataType="xsd:double"/>
<group name="G_1" label="G_1">
<element name="ORDER_ID" label="ORDER_ID" dataType="xsd:long"/>
<element name="ORDER_DATE" label="ORDER_DATE" dataType="xsd:date"/>
<element name="ORDER_MODE" label="ORDER_MODE" dataType="xsd:string"/>
<element name="CUSTOMER_ID" label="CUSTOMER_ID" dataType="xsd:integer"/>
<element name="ORDER_STATUS" label="ORDER_STATUS" dataType="xsd:integer"/>
<element name="ORDER_TOTAL" label="ORDER_TOTAL" dataType="xsd:double"/>
<element name="SALES_REP_ID" label="SALES_REP_ID" dataType="xsd:integer"/>
<element name="PROMOTION_ID" label="PROMOTION_ID" dataType="xsd:integer"/>
<element name="TOTAL_COST" label="Total Cost" dataType="xsd:double" function="SUM"/>
<element name="ITEMCOUNT" label="Item Count" dataType="xsd:integer" function="COUNT"/>
<element name="CURRENCY" label="CURRENCY" dataType="xsd:string"/>
<group name="G_2" label="G_2">
<element name="ORDER_ID" label="ORDER_ID" dataType="xsd:long"/>
:
:

and the sampleData:

curl -X GET -o sampledata.txt -H "Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx" -H "Content-Type: application/json" -d "{}" http://bip_server:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/sampleData

In this case the data is output in XML format rather than JSON (despite JSON being mentioned in the header parameter):

<DATA_DS>
<P_ORDERID>2458</P_ORDERID>
<G_1>
<ORDER_ID>2458</ORDER_ID>
<ORDER_DATE>2007-08-16T22:34:12.234+01:00</ORDER_DATE>
<ORDER_MODE>direct</ORDER_MODE>
<CUSTOMER_ID>101</CUSTOMER_ID>
<ORDER_STATUS>0</ORDER_STATUS>
<ORDER_TOTAL>78279.6</ORDER_TOTAL>
<SALES_REP_ID>153</SALES_REP_ID>
<CURRENCY>EUR</CURRENCY>
<G_2>
<ORDER_ID>2458</ORDER_ID>
<LINE_ITEM_ID>5</LINE_ITEM_ID>
<PRODUCT_ID>3143</PRODUCT_ID>
<UNIT_PRICE>15</UNIT_PRICE>
<QUANTITY>129</QUANTITY>
:
:

The final REST API call I want to cover in this blog is the most important: Running your BI Publisher report!

This is much more complicated than the calls shown above as not only does it need to be able to download non-text files – my BI Publisher report outputs in PDF by default, but I also need to supply a parameter and other BI Publisher report settings. For this we need to use the -d data parameter on the CURL command.

The run request uses the POST method and my output filename is a PDF file.

I’m using three -H header parameters this time. The first is the standard authorisation.

The second specifies that the content type of the request is multipart and contains form data. If is multipart as it requires data to be sent to the BI Publisher report in addition to the URL call to the BI Publisher report itself. However because it is multipart, it also needs to know where each part starts and ends, or to be more precise where the first part starts and ends, the second part by definition starts after the first part ends.

For this we need to use a boundary. This is a marker which identifies the start and end of the first part. In the header parameter, you specify the boundary with a text string, in this case:

-H "Content-Type: multipart/form-data; boundary=\"Boundary_1_1153447573_1465550731355\""

note that the \ before the quotes are simply because these are within the outer header parameter quoted string. The boundary string can be any unique string, using strings in this long format seems to be the accepted standard – it prevents with boundary string from being mis-identified by a developer as anything else. This boundary will be used in the -d data second below.

The third -H header parameter tells the REST API of cache-control, we don’t want the REST API caching data for this report.

 -H "Cache-Control: no-cache"

The -d data section contains information to pass to BIPublisher on how to run the report – it can include additional settings and, most usefully, parameters to pass to the report. My Order Confirmation report requires a single parameter – Order ID.  Note that the data parameter is a multi line field – do not try to fit it altogether on a single line, it will not work!

So, my data field starts with the boundary ID, as specified about, but prefixed with — This tells API it’s a start of a boundary section.

This is followed by a couple of lines determining the content of the boundary section:

Content-Type: application/json
Content-Disposition: form-data; name="ReportRequest"

This is followed, in curly brackets, separated by commas, a list of report settings. I’m using two in this example. The first, byPassCache, set to true ensures no cached data is included in my report (the BI Publisher cache, not the REST API cache). The second parameterNameValues, let me list, again in more curly brackets the parameters I wish to pass to the report.

I keep mentioning curly brackets as the format of this section (in JSON) is very specific, you need to be careful to get it 100% right or the report will not work and the error message you will get is unlikely to be meaningful!

Finally the data section is finished off with a Boundary ID end marker. This is boundary ID from above but both prefixed and suffixed with —

an end quote closed the data section.

Finally we have the URL to the report with the /run action on the end.

So my completed call to run the report is as follows:

curl -X POST -o output.pdf -H "Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx" -H "Content-Type: multipart/form-data; boundary=\"Boundary_1_1153447573_1465550731355\"" -H "Cache-Control: no-cache" -d '
--Boundary_1_1153447573_1465550731355
Content-Type: application/json
Content-Disposition: form-data; name="ReportRequest"

{
"byPassCache":true,
"parameterNameValues": { "listOfParamNameValues": [
 {
 "name": "p_orderid",
 "values": "2397"
 }
 ]
}

}
--Boundary_1_1153447573_1465550731355--
' http://linuxvm:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/run

This runs and downloads a file called output.pdf:

 

bip_rest_api_3

I can take this a stage further. By placing my command with a shell script (I’m running this on Linux, but should work just as well in Windows), I can use shell script parameters and pass them into the command using $1 – I’ve used this as the parameter in the data section and appended it to the output file name:

run_order_confirmation.sh

curl -v -X POST -o  Order_Confirmation_$1.pdf  -H "Authorization: Basic d2VibG9naWM6UGFzc3dvcmQx"  -H "Content-Type: multipart/form-data; boundary=\"Boundary_1_1153447573_1465550731355\"" -H "Cache-Control: no-cache" -d '
--Boundary_1_1153447573_1465550731355
Content-Type: application/json
Content-Disposition: form-data; name="ReportRequest"
{
"byPassCache":true,
"parameterNameValues": {  "listOfParamNameValues": [
 {
 "name": "p_orderid",
 "values": "'$1'"
 }
 ]
}

}
--Boundary_1_1153447573_1465550731355--
' http://linuxvm:9502/xmlpserver/services/rest/v1/reports/BIP%20Reports%2FOrder%20Confirmation%2FOrder%20Confirmation/run

So, I can run off an order confirmation just by calling the script with the order number:

 

./ run_order_confirmation.sh 2440

So that covers the basics of being able to run off a BI Publisher report via the REST API. There is much more to this – many more parameters you can send to BI Publisher plus many API Calls for managing reports, including creating reports & templaces, replacing XLIFF files and so on. See the BI Publisher  REST API guide for more.

Enjoy!

 

 

Paul CannonBI Publisher 12c: Running Reports via the REST API

Related Posts

Take a look at these posts

Join the conversation