nPrinting February 2024 SR3 - Excel report - Pivot tables with "Page" feature

[cjgorrin]

Pivot tables don't seem to be working properly with the "Page" feature in nPrinting Excel reports.

To reproduce it:

1. Create a connection to any Qlik Sense app.
2. Create an Excel report using that connection.
3. Add any field as "Page". This will change the name of "Sheet1" to <NAME_OF_FIELD>.
4. Create a fixed table in the Excel (a table not coming from Qlik, but just some fixed data).
5. Create a pivot table in the sheet using the fixed table as source.
6. Preview the report.

Let's say you are iterating over field MYFIELD and values are MF1, MF2, MF3 and MF4. In this case, nPrinting will clone your sheet 4 times and they will be called MF1, MF2, MF3 and MF4.

The fixed table will be present in all sheets and the pivot table as well. However, in ALL sheets, the pivot table will point to the fixed table in sheet MF1 (the first one).

In this case, the expected behavior is for nPrinting to generate 4 sheets (MF1, MF2, MF3 and MF4), each sheet will have a copy of the fixed table and each sheet will have a pivot table using as data source the fixed table from the same sheet.

I understand in 2017 there was an issue (Jira issue OP-5657), which was fixed, related exactly to the issue of mixing "Page" with pivot tables (see release notes for nPrinting November 2017).

So, is this supposed to work as I expect it to? Is this an error? Is it a "feature"? (maybe @Andrew_Kruger or @Gianluca_Perin can help?).

Thanks a lot for your help!

[Frank_S]

If I understand your issue correctly, this should be how to make it work for you.

Ensure that you use a 'year' dimension value listed in the 'page' section of the designer (F\year) and not the year value for the straight table (O\WEKpup8).

As you can see here it works if you do it that way. 

I've also test the other two years and they line up perfectly as well.

Kind regards...

[cjgorrin]

Dear @Frank_S,

Thanks a lot for your swift answer.

I am not trying to insert data from a Qlik pivot table. I am trying to create an Excel pivot table in an Excel worksheet which should be generated several times by nPrinting as "Pages".

You need to create a "Page" in nPrinting, with whatever dimension you want, insert some data in the worksheet (does not need to come from Qlik) and create an Excel pivot table over it, in the same sheet.

When nPrinting generates the report, the sheet will be cloned once for each element of the Page dimension, but all pivot tables will point to the data in the first sheet.

Let me know if you managed to reproduce it. Thanks again!!

[Frank_S]

@cjgorrin 

Please start a support case and provide the detailed

• steps to reproduce
• expected results
• actual results
• images that described the steps,output etc
• mention if the issue is new or if it is degrade
• provide any log files requested by your assigned support engineer

This will ensure your case is better understood from end to end and should it require a defect fix, this will need to be managed via the Qlik support process.

Kind regards...

[cjgorrin]

Well, sure, @GeorgeSavu will probably do that. I don't even know if this is a defect or an expected limitation. I posted it here to see if anyone else had already experienced the same issue and found a way to make it work.

[Lech_Miszkiewicz]

HI @cjgorrin 

I am unable to test this now so just want to check about your 2 point:

1. Create a fixed table in the Excel (a table not coming from Qlik, but just some fixed data).
2. Create a pivot table in the sheet using the fixed table as source.

Are you creating the fixed table as a named table in excel? like Table1? Also when creating Pivot Table are you creating it using cell range (like A1:B20) or are you using reference to that named table as a source?

If the latter then I would assume result is as you described and it is working as designed.

Cheers

[cjgorrin]

Hi, @Lech_Miszkiewicz . Thanks a lot for taking the time to answer.

Maybe it's easier if I say what I want to achieve:

I need my report to:

1. Have one sheet per each element of a dimension (for that, I'm using a Page).
2. Each sheet should have a linear table and an Excel pivot table calculated over that linear table.
3. The pivot table of each sheet should be calculated over the linear table of that sheet.

The linear table could come from Qlik, but the problem happens even with fixed data. And yes, I am defining the pivot table over a range in the same sheet.

Thanks again!

[Lech_Miszkiewicz · Accepted solution]

Hi @cjgorrin 

I tested this and I come to conclusion that this works actually oposite to what I thought it would. You need to create named table first (insert-->table in Excel) and use that to build your pivot table. 

When you have such setup paging works:

cheers

[cjgorrin]

You are right, @Lech_Miszkiewicz. Creating a table and using that as data source for the Excel pivot table works as expected when the pivot table is in an Excel sheet which is an nPrinting "Page".

This could be a workaround for my issue. I do think Qlik should fix the pivot tables over ranges when combined with Pages and I also think that this should be better documented in the help. We are working on reporting it to support and see what they say.

Thank you very much for your time and your valuable help. 🤗

[Ruggero_Piccoli]

Hi,

This is the correct/documented way to create an Excel pivot table https://help.qlik.com/en-US/nprinting/February2024/Content/NPrinting/ExcelReports/Pivot-table-Excel....

It is supported starting from February 2019

Excel pivot charts used with Pages showed only values from the first page
Jira issue ID: OP-7375
Adding a native Excel Pivot Chart (chart linked to native Pivot Table) in an Excel worksheet that uses the Pages feature resulted in a generated report where the data source of charts from the second page were in the first worksheet. Starting from this version, each pivot chart in a worksheet with Pages is linked with the correct data source. 

And in April 2020 the issue

Excel pivot tables in paged worksheets displayed incorrect row order
Jira issue ID: OP-8994
Native Excel pivot tables in worksheets with the Page option would display rows in the wrong order. Only the first pivot table had the rows in the correct order.

was solved. So it works with the officially supported versions available now.

Best Regards,

Ruggero