Collapse Columns/Rows in native Pivot Table

[varex_mike]

I've created a tabular report template that uses data tables to create native Excel Pivot tables. The Pivot table structure is like so:

I need to have the report generate with Fiscal Quarter and PC Hier Level 2 collapsed. Is that possible?

Thanks,

[Lech_Miszkiewicz · Accepted solution]

Hi @varex_mike 

This seems like standard excel behaviour. It expands values which are added to data set. 

One workaround would be to:

• pre-populate raw data table with values of quarter and week number (i know it may be long list as you may need to "future proof it" by creating data for past and future periods.
• refresh your template and collapse those values which at that point will appear in your pivot table
• use <deleterow> tags to which will remove those records when report is generated

That should keep your layout collapsed when you generate report.

I would suggest to setup template using desktop version of excel (not the web one) as desktop version has much more control over pivot table settings.

Lastly you may need to search excel forum for more answers as this behaviour is not based on Qlik but on Excel intricacies.

cheers