25 December 2013

Sending Qlikview Reports in Excel

Would not it be nice if we could send some qlikview charts by email to a recipient automatically?

By using qlikview OCX and a piece of C# code, we can do this!!!

Let's examine the attached solution:

1) We need an excel template under template folder in which the position and the name of the qlikview chart to be transferred into excel is defined.
For example:
In the screenshot below, the definition CH01;image;0.5 in cell A1 means, export the chart CH01 from qlikview as an image and resize it by factor 0.5 and position its top-left corner with cell A1.
The definition CH02;text in cell H1 means, export the chart CH02 from qlikview as text and position its top-left cell with cell H1.

2) The output folder is used to copy the template into with a unique name and transfer the data from qlikview to this unique excel file before it is sent by email.

3) The qlikview file(s) should be placed in the same directory as template and output folder.

4) The excel template should have the same name as qlikview file. For example: if the qlikview file is named as Contoso-Simple.qvw then the excel file should be named as Contoso-Simple.xlsx.

5) The email account credentials which will be used to send the email are stored in app.config file.

How to use the application:

Before using the application, provide the email account credentials in app.config file which is under reportertest\Reporter\Reporter folder.

1) Manual use: double click on the reporter.exe under reportertest\Reporter\Reporter\bin\Debug folder. In the opening screen:
  • select the folder where qlikview files and template and output folders are located
  • select the qlikview file
  • make any selection in the qlikview file shown in the big frame
  • enter email address to which the excel report will be sent
  • click on Send XLS

2) Automatic use:  The reporter.exe can also be called from console or scheduled to run automatically in windows scheduled tasks. While calling from console or scheduling it requires five arguments:

arg 1: full path of the file
arg 2: user name (if section access is used in qlikview file)
arg 3: user password (if section access is used in qlikview file)
arg 4: selections to be made in the qlikview file 
arg 5: the email address to which the report will be sent

For example: The following console command will open the reporter application, access the contoso-simple.qvw file, then make the selections (20071 and 20072 as CalendarQuarter Online as ChannelName) and send out the excel report to xyz@gmail.com.

Reporter.exe "C:\\Users\\Bilge\\Desktop\\HappyStartinUSA\\OCX\\Contoso-Simple.qvw" "" "" "<CalendarQuarter:number:20071,20072><ChannelName:text:Online>" "xyz@gmail.com"

Source Code

Here is the source code and sample files (template, qvw file): reportertest.zip

No comments:

Post a Comment