13 December 2012

Qlikview: How to send a report by email from access point

Step 1)
Create a report on a qlikview application which has some dummy data. (e.g. sales report in the attached qlikview file.)

Step 2)
Create a field in the qlikview applications to keep recipient email address. (e.g. recipient field in the attached qlikview file.)  and display it in a listbox.

Step 3)
Create a variable in the qlikview application to keep a string which will contain recipient's email address and the name of the output PDF file separated by semi colon.  (e.g vPdfName2 variable in the attached qlikview file.)

Step 4)
Create a listbox in the qlikview application which will display the content of the variable.

Step 5) 
Create a button in the qlikview application that will call the following macro:

Sub PrintRpt()
        'print the report and get the reponse back in a variable
set printResponse=ActiveDocument.PrintReportEx("RP01")

'get the name of the PDF file created by the print action above

        'get the email address of the recipient selected in the recipients listbox
set recipient=ActiveDocument.fields("Recipient").GetSelectedValues
set email=recipient.Item(0)

        'set the concatenated string (email address and name of the PDF file) as content of the listbox created in step 4
ActiveDocument.Variables("vPdfName2").SetContent chr(39) & email.text & ";" & sURL & chr(39), true

'set the path of the folder in which the text file will be crated
sFile="C:\_QVW\PDFfolder\WatchThis\email_" & replace(sURL,".pdf","") & ".txt"

        'Export the content of the listbox into a text file that contains the email address and name of the PDF file
set lb = ActiveDocument.GetSheetObject("LB05")
lb.ServerSideExportEx sFile , ";" , 1 '0=HTML, 1=Text, 2=Bitmap, 3=XML, 4=QVD, 5=BIFF
End Sub

Step 6)
Write a powershell script that can read a text file to get email address and path of the pdf file and send it by email.

Following posts have related examples:
how to loop through files in a folder by windows powershell
How to send email by windows powershell

Step 7) 
Schedule a windows task which will monitor the folder for incoming files and trigger the powershell script to read the file's content and send email with the PDF attached.

Following post have a related example:
How to monitor a folder and trigger an action for incoming files, in Windows 7
The only difference from the above example will be the source defined in the XML definition of the event filter. Instead of  "C:\windows\explorer.exe", we should now have "C:\Program Files\QlikView\Server\QVS.exe", because the text file will be created by qlikview server.

Deploy the qlikview application on the server and open it from access point and click on the button to see the result.

Example File: email_report.rar

1 comment:

  1. This comment has been removed by a blog administrator.