topic collapse rows in excel pivot table - Nprinting in Qlik NPrinting

collapse rows in excel pivot table - Nprinting

Fri, 03 Jun 2016 13:25:20 GMT

I have created an excel pivot chart in nprinting reports. After refresh the chart is always expanded. Is a way to get it collapsed after refresh?

Daniel_Jenkins — Sat, 04 Jun 2016 23:59:55 GMT

Hi Christiana,

You will need to use XLSM format and write VBA code in the Workbook Open event.

If you have a Pivot Table in your template like this:

You can use this code to collapse the field <Country>:

Private Sub Workbook_Open()

Sheets("Excel Pivot Table").Select

ActiveSheet.PivotTables("PivotTable1").PivotSelect "Country[All]", xlLabelOnly _

+ xlFirstRow, True

ActiveSheet.PivotTables("PivotTable1").PivotFields("Country").ShowDetail = _

False

Range("B2").Select

End Sub

Remember to save and preview as XLSM.

HTH - Daniel.

Anonymous — Mon, 14 Nov 2016 15:44:01 GMT

Hi Daniel,

Is this aplicable for NPrinting 17.2 as well?

Thanks

Daniel_Jenkins — Mon, 14 Nov 2016 21:38:41 GMT

Hi Ladislav,

In NPrinting 17.2 you cannot set the output format to XLSM. Therefore not applicable.

- Daniel.

stig1984 — Tue, 27 Nov 2018 11:04:07 GMT

For reference the xlsm templates are back in place for sept 2018 onwards