I have a pivot table which displays information about sales, variance, profit etc. Those information are based on a department selection, so for example when user selects frozen department, it will display information only about that department. There are about 10 departments overall.
Is it possible to create a button, which will loop through those 10 departments, and print out sheet with pivot table for each department?
I'm not really sure how to create macro that outputs the report for each value. Could you point out some reference where I could find more information about it? I searched to forum but didn't find much information relevant to solve my problem
Set the Actions to Run Macro. Set the Macro Name property to PrintObjectToPDFForEachValue
Click Edit Module...
Paste this in:
Sub PrintObjectToPDFForEachValue Dim WSHShell set objSheet =ActiveDocument.Sheets("NAME OF SHEET YOU ARE PRINTING") Set val=ActiveDocument.Fields("NAME OF FIELD YOU ARE LOOPING THROUGH").GetPossibleValues(20000)
For i = 1 to Val.count - 1
ActiveDocument.Fields("NAME OF FIELD YOU ARE LOOPING THROUGH").Select val.item(i).Text
Just for those looking at this post after 2010, for QlikView 11.2 SR5 (12235) you can make use of a Print Report Action and then use banding within the report to loop through the values within the dimension (in this question, that would be department).
As per the help file:
This tab contains properties for banded output of the page. This means that the page is printed repeatedly for each possible value in a specified field.
When the check box Loop Page Over Possible Values in Field is marked, the page will be printed repeatedly while selecting each possible value in the field specified in the drop-down box below. If there are no possible values in the specified field, nothing will be printed. Note that if a selection loop has been specified also on the report level (see the The Selections Tab) you may face a situation where the report selection excludes all values in the page level loop variable. The page will then be skipped for that report loop value. After completed printing, the original selections will be re-applied. If page numbers are used, these will be continuous over all pages printed.