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
sURL=mid(printResponse.URL,3)

        '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.

Test:
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

2 comments: