Passing parameters into REST API connection

[kateperry]

I am trying to load data from Adaptive Insights using REST connection.

When I setup the rest connection, I specify the:

URL https://api.adaptiveinsights.com/api/v19

Method: POST

Request Body: 

<?xml version='1.0' encoding='UTF-8'?>
<call method="exportData" callerName="Qlik">
<credentials login="xxxxx" password="xxxxx"/>
<version name ="xxxxx"/>
<format useInternalCodes="true"/>
<filters>
<accounts>
<account code = "Revenue" isAssumption="false" includeDescendants="true"/>
</accounts>
</filters>
<rules includeRollups="true">
<currency useCorporate="true"/>
</rules>
</call>

I then have the following data returned, with success = true and the whole dataset returned in a csv format.

I need to make the "version name" parameter dynamic in the script, so I have been trying to use the "With Connection" syntax.

Let vRequestBody ='';
let vRequestBody = vRequestBody &'<?xml version="1.0" encoding="utf-8"?>';
let vRequestBody = vRequestBody &'<call method="exportData" callerName="Qlik">';
let vRequestBody = vRequestBody &'<credentials login="xxxxx" password="xxxxx"/>';
let vRequestBody = vRequestBody &'<version name ="xxxxx"/>';
let vRequestBody = vRequestBody &'<format useInternalCodes="true"/>';
let vRequestBody = vRequestBody &'<filters>';
let vRequestBody = vRequestBody &'<accounts>';
let vRequestBody = vRequestBody &'<account code = "Revenue" isAssumption="false" includeDescendants="true"/>';
let vRequestBody = vRequestBody &'</accounts>';
let vRequestBody = vRequestBody &'</filters>';
let vRequestBody = vRequestBody &'<rules includeRollups="true">';
let vRequestBody = vRequestBody &'<currency useCorporate="true"/>';
let vRequestBody = vRequestBody &'</rules>';
let vRequestBody = vRequestBody &'</call>")';

let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));

RestConnectorMasterTable:
SQL SELECT
"attr:success" AS "success",
"output"
FROM XML "response" QDL
WITH CONNECTION (URL "https://api.adaptiveinsights.com/api/v23",
BODY "$(vRequestBody)"
);

This doesn't error, but I don't get any results. 

I don't suppose anyone has an idea why this might be??

I am using the same connection for both.

Many thanks in anticipation of any help!

Kate

[petter · Accepted solution]

Have a look at this line:

let vRequestBody = vRequestBody &'</call>")';

Is the last two characters in the string something that should really be there. To me it looks like something that shouldn't be there...

[kateperry]

Thank you so so so so much for responding with that. You are 100% correct. Removing those characters returned the data perfectly. 

You would not believe how many hours I spent looking at this and I still missed it. What a silly copy/paste error to have caused me such a headache!

You're a star!!!!! Much appreciated.