Qlik Reporting API with selections in SenseExcelTemplate

[HenkTenk]

Hi all,

I have been busy with requesting excel reports from Qlik Saas using the API's using this endpoint: https://yourtenant.yourlocation.qlikcloud.com/api/v1/reports

with this body:

{

  "type":"sense-excel-template-1.0",

  "output": {

    "type": "xlsx",

    "outputId": "xlsx1",

    "excelOutput": {

          "outFormat": "xlsx"

          }

    },

    "SenseExcelTemplate":

    {

        "appId": "b555f8b3-c8b4-678e-77aa-30281315a06f",

         "id": "rZbjLrt",

        "templateLocation":{

            "path": "https://yourtenant.yourlocation.qlikcloud.com/api/v1/report-templates/appid",

            "format": "url"

        }

        }

}

This works and it gives me the right report, now I want to add selections during this API call but i am struggling with the right syntax for SenseDataTemplate.

When i make a similar request with sense-image, the selections are applied as described in the documentation. Can it be that the combination of SenseDataTemplate and selections does not work? 

Does anyone know how to add fieldselections in this API call?

[diegozecchini]

Hi!
What if you utilize bookmarks within your Qlik Sense app? Bookmarks capture a specific selection state, and you can reference them in your API request to apply those selections to your report.

When you create a bookmark, note the bookmarkId and modify your API request body, to include the bookmarkId
Here's an example of how your API request body might look:

{
"type": "sense-excel-template-1.0",
"output": {
"type": "xlsx",
"outputId": "xlsx1",
"excelOutput": {
"outFormat": "xlsx"
}
},
"SenseExcelTemplate": {
"appId": "b555f8b3-c8b4-678e-77aa-30281315a06f",
"id": "rZbjLrt",
"templateLocation": {
"path": "https://yourtenant.yourlocation.qlikcloud.com/api/v1/report-templates/appid",
"format": "url"
},
"bookmarkId": "your-bookmark-id"
}
}
By referencing the bookmarkId, the report will generate based on the selections saved in that bookmark.

[HenkTenk]

Hi Diego,

Thank you for your reply. However, I want to make dynamic selections using the API, based on CustomerID for example. Can I also send a variable value with this API call ?

[diegozecchini]

Hi!
It is not clear for me in docs so this is a try, add a "selections" object inside your request body, similar to how selections work for sense-image reports.

{
"type": "sense-excel-template-1.0",
"output": {
"type": "xlsx",
"outputId": "xlsx1",
"excelOutput": {
"outFormat": "xlsx"
}
},
"SenseExcelTemplate": {
"appId": "b555f8b3-c8b4-678e-77aa-30281315a06f",
"id": "rZbjLrt",
"templateLocation": {
"path": "https://yourtenant.yourlocation.qlikcloud.com/api/v1/report-templates/appid",
"format": "url"
},
"selections": [
{
"field": "CustomerID",
"values": ["12345"]
}
]
}
}

You can pass multiple field selections like this:

"selections": [
{
"field": "CustomerID",
"values": ["12345"]
},
{
"field": "Region",
"values": ["Europe"]
}
]
This should apply the selections dynamically before generating the report. If this doesn’t work, check the Qlik API documentation to confirm whether sense-excel-template-1.0 supports direct field selections

[HenkTenk]

Hi Diego,

Unfortunately this syntax does not work, it seems to ignore the values in the selections part.

The documentation states that selectionChain is a possibility for senseExcelTemplate but i have not been able yet to figure out the right syntax. When I apply this selectionChain property for the image generation part, it does apply the selections as requested.

I hope anyone is able to assist to find the right syntax