topic Re: Make a report automation with multiple sheets in Qlik Automate

Make a report automation with multiple sheets

qlikhalmar — Thu, 15 Jun 2023 12:08:26 GMT

Hi All,

I want to make a simple 'Excel automation'. I have two straight tables in one report and i want each straight table on a different sheet in one Excelfile.

I did the trick for one straight table in one sheet with the nice article of @MattGrayndler . But I need more sheets.

I hope someone can help me with a Json template.

Thanks in advance!

Halmar

Re: Make a report automation with multiple sheets

Fornevere — Thu, 20 Jul 2023 04:53:56 GMT

Certainly! I can provide you with a JSON template to automate the creation of multiple sheets in an Excel file using the table element. Here's an example:

{
"version": "2.0",
"template": {
"dataSources": {
"table1": {
"type": "table",
"data": {
"columns": [
// Define your columns here
],
"rows": [
// Provide the data for table1
]
}
},
"table2": {
"type": "table",
"data": {
"columns": [
// Define your columns here
],
"rows": [
// Provide the data for table2
]
}
}
// Add more data sources for additional tables if needed
},
"sheets": [
{
"name": "Sheet1",
"tables": [
{
"dataSource": "table1",
"position": {
"row": 1,
"column": 1
}
}
]
},
{
"name": "Sheet2",
"tables": [
{
"dataSource": "table2",
"position": {
"row": 1,
"column": 1
}
}
]
}
// Add more sheets with tables as required
]
}
}

In this template, there are two data sources defined (table1 and table2) representing the data for each table. You can provide the column definitions and row data for each table in their respective data sources.

The sheets array contains objects representing each sheet in the Excel file. Each sheet has a name property defining the sheet name. Inside each sheet object, you can specify one or more tables using the tables array. For each table, specify the dataSource (matching the data source name) and the position where the table should be placed using row and column values.

You can expand this template to include more sheets and tables by adding more objects to the sheets array and defining additional data sources as needed.

Please note that this is a basic template structure, and you will need to populate the column definitions and row data for your tables according to your specific requirements.

I hope this helps you automate the creation of multiple sheets with tables in your Excel file!

mymilestonecard

Re: Make a report automation with multiple sheets

qlikhalmar — Thu, 27 Jul 2023 07:26:00 GMT

Hi Fornevere,

Thanks for your reply!

But I want to get data from two straight tables in a Qlik Sense App and past them in two seperate Excel sheets.

I have allready made a exceltemplate and one straight table works, but two....

Thanks!

Re: Make a report automation with multiple sheets

Maarsen_ — Fri, 04 Aug 2023 08:33:04 GMT

It's definitly possible. You should reset key variables and you can use the same blocks to populate a new named range table;

- Variable columncount (back to 1)

- Variable ColumnProcess (back to 0)

- Variable columns make empty

- Variable ExcelPayload -- make empty.

From there you can use the same blocks as before, starting at Get Straight Table Data and pinpoint all the blocks to the proper loop.

See this file for an example; but this is also possible with more worksheets.

Re: Make a report automation with multiple sheets

p_verkooijen — Fri, 03 Nov 2023 13:08:17 GMT

Hi @Maarsen_ I tried your example but I keep getting stuck at Create Excel Table With Headers with the error

Error calling endpoint "Microsoft Excel - Update Rows In Worksheet"

Open navigation properties are not supported on OpenTypes. Property name: '$metadata#users'.

Ok, I have found the issue.

In Item Id: select Id from the first block instead of the Parent Reference > Id

Select this

You get Id

Wrong Id

You get Parent Reference

Re: Make a report automation with multiple sheets

vbarberat — Mon, 10 Jun 2024 08:29:50 GMT

It works for you @p_verkooijen ??

Re: Make a report automation with multiple sheets

p_verkooijen — Mon, 10 Jun 2024 08:33:49 GMT

Hi @vbarberat, it has been a while but yes this worked for me.

You got the same error ?

Re: Make a report automation with multiple sheets

vbarberat — Mon, 10 Jun 2024 08:49:50 GMT

Hello @p_verkooijen! Thanks for answering so quickly.

Yes, we receive the excel report with the first table but the second one we only receive the header.

In fact, it is possible to receive the reports but each table in two diferent excel files?

Re: Make a report automation with multiple sheets

p_verkooijen — Mon, 10 Jun 2024 08:55:27 GMT

Hi @vbarberat ,

Maybe you could take a look at tabular reporting, this makes it easier to create Excel reports.

When I used automations to create Excel reports tabular reporting did not exists yet.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Reporting/cloud-tabular-reports-intro.htm

Re: Make a report automation with multiple sheets

vbarberat — Mon, 10 Jun 2024 10:47:19 GMT

Hi @p_verkooijen ,

The objective is basically that our clients have to be able to download a report or have the capability of sending it directly by email. It seems a good option using tabular reporting.

I get to work.

Thank you so much.

Re: Make a report automation with multiple sheets

MorneBester — Thu, 10 Oct 2024 16:13:05 GMT

Tried changing the Item ID and still not getting past the error.

Quite frustrating that Qlik makes something that should be simple, so complicated