topic Re: loop and print selection in QlikView

loop and print selection

Tue, 10 Aug 2010 19:37:17 GMT

Hello,

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?

Thanks

loop and print selection

vgutkovsky — Tue, 10 Aug 2010 21:23:11 GMT

Well, you can create a report for that dimension and then create a macro that outputs the report for each values. The macro could then be triggered by a button.

loop and print selection

Thu, 12 Aug 2010 15:37:31 GMT

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

Thank you

loop and print selection

vgutkovsky — Thu, 12 Aug 2010 23:32:25 GMT

See these posts, they're not exactly the same but may help point you in the right direction.

http://community.qlik.com/media/p/90766.aspx

http://community.qlik.com/media/p/89326.aspx

http://community.qlik.com/media/p/81032.aspx

http://community.qlik.com/media/p/64479.aspx

Also, take a look at the API Guide; that should have a lot more information on macro syntax for automating reports.

Regards,

loop and print selection

Fri, 13 Aug 2010 15:26:59 GMT

Thank you,

I will definetly look through those, and post my solution next week. Thanks

loop and print selection

Tue, 02 Nov 2010 12:26:18 GMT

Install PDFCreator

Add a button on your report.

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

Set WSHShell = CreateObject("WScript.Shell")
filename = val.item(i).Text & "_Report.pdf"
RegKey = "HKEY_CURRENT_USER\Software\PDFCreator\Program\"
WSHShell.RegWrite RegKey & "UseAutosave", 1, "REG_SZ"
WSHShell.RegWrite RegKey & "AutosaveFilename", filename, "REG_SZ"
WSHShell.RegWrite RegKey & "SaveFilename", filename, "REG_SZ"

ActiveDocument.GetApplication.Sleep 5000
ActiveDocument.GetSheetObject( "NAME OF OBJECT YOU ARE PRINTING" ).PrintEx "PDFCreator"

ActiveDocument.GetApplication.Sleep 5000

Next
WSHShell.RegWrite RegKey & "AutosaveFilename", "", "REG_SZ"
WSHShell.RegWrite RegKey & "SaveFilename", "", "REG_SZ"
WSHShell.RegWrite RegKey & "UseAutosave", 0, "REG_SZ"

Set WSHShell = nothing
msgbox "done"
end sub

Re: loop and print selection

Tue, 17 Jun 2014 16:04:24 GMT

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:

Banding

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.

HIH

Charles

Re: loop and print selection

joggiek — Fri, 18 Jul 2014 07:29:43 GMT

Hi,

I need to suppress blank pages. Blanks happen when one of the values I band over does not return any values and the graph is blank in QV.

I tried adding a calculation condition to the table but then get the "Calculation Condition not met" printed on the "empty" page.

The I tried the same as the above but with no Caption, same result as above, except now without table headings.

Then I added a Conditional Show expression on the Layout tab for every item on the report, that resulted in the same number of pages, but now the ones I wanted to be suppressed were printing blank.

This could be a solution if you are not using page numbers and remove the blank pages from the printed report (atleast you can reuse those pages again ).

Will keep on trying and let you know if I get it figured out...

Let me know if you get to suppress the "empty" pages

Cheers

Re: loop and print selection

sebastiandperei — Wed, 30 Jul 2014 12:57:59 GMT

You know some condition that disables the field by which you select the different reports?

You could make an extra field in script, with only the values you want to loop.

original_field report_field

A A

B B

Re: loop and print selection

joggiek — Wed, 30 Jul 2014 13:08:59 GMT

Do you mean I must do a check for Nulls in the field the report loops over, and then create a report lookup temp table...?

Re: loop and print selection

sebastiandperei — Wed, 30 Jul 2014 14:33:59 GMT

No, in first place, try to identify why these report is blank. You said: "... Blanks happen when one of the values I band over does not return any values and the graph is blank in QV..."

This is always like that? e.g.: The field that i loop is "Product", and if you know that the product "Beer" doesn't have any data, you should create another dimention like "Product_report", linked with "Product", but without the row "Beer"...

Could you send a reduced sample?

Re: loop and print selection

sebastiandperei — Tue, 12 Aug 2014 15:16:15 GMT

So?