We recently launched QVExcel, a new integrated Excel Add-in for building reports in Excel from your QlikView applications and data.
We're really excited about the release and have been getting some great feedback from QlikTech consultants, partners as well as QlikView end users and in this post I would like to give an overview of the reasons behind the product and how it works. More information and a free download can also be found over at www.QVExcel.com.
The Use Case
So why is a product like QVExcel needed?
We have been seeing for some time a requirement for producing certain types of reports from QlikView applications and data - particularly financial reports and corporate reports such as balance sheets, profit and loss statements, forecasting and budgeting reports. An example is given below and more example reports and screen shots can be found over on the website.
Reports such as these often have very specific 'pixel perfect' formatting and layout requirements, sometimes these formats are even legally mandated.
An organisation adopting QlikView will often already have these reports developed in Excel and need a way to feed them with data from their new QlikView systems or may need to develop new Excel reports and it is here where QVExcel can really help out.
Any values or text extracted from QlikView via QVExcel can be treated like any other Excel content and so, for example, can be formatted, used as part of other Excel calculations or linked to other Excel reports.
So QVExcel is not intended as a replacement for QlikView reports but rather a tool which can be used in those particular cases where the existing QlikView reporting functionality does not quite provide the flexibility required. A typical usage scenario might be the finance department of an organisation using QVExcel to produce highly customised reports which are then distributed (see below) to non-QVExcel users.
System Requirements
The first thing to mention is that QVExcel requires a QlikView Server (version 8.5, 9 or 10) to connect to. QVExcel will not work with a local desktop installation of QlikView and indeed does not require any other QlikView components to be installed on the user's machine in order to function.
QVExcel works with Excel 2000 or later and also requires the .NET framework 3.5 to be installed. You will of course also need an appropriate QlikView user licence to be able to connect to QlikView Server and use the product.
Getting Started
You can request a free 2 week evaluation of QVExcel on the website. Once installed QVExcel shows up as an integrated side bar in Excel as shown here.
One of the strengths of the system is that there are no third party products to launch in combination with Excel and no need to even install any other QlikView components on the system.
Once you enter the name or IP address of your QlikView Server you are presented with a list of available documents which you can then connect to.
QlikView Selections and Associativity
Once you are connected to your document QVExcel will retrieve all the listboxes from your QlikView application, allowing you to move between them and make selections from within Excel just as you would in QlikView, with your reports updating to reflect your selections.
There is also some standard navigation (back and forward) and clear functionality as well as a current selections display.
By default QVExcel will show all the listboxes in your application, however if you append '_QVEXCEL' to the object id of one or more listboxes in the application then only these listboxes will be displayed. This provides an easy way of simplifying the options presented to the Excel user.
Extracting Data
We're now ready to start extracting data from our QlikView application. QVExcel is a bit like a swiss army knife and provides a number of ways of doing this which can either be used on their own or mixed and matched to produce the reports you need.
Method 1: The QVExcel User Interface
The 'Objects' tab of QVExcel presents a treeview showing all of the sheets and objects available in the QlikView application and right clicking on any of these provides a number of quick methods for extracting the object's data into the worksheet.
These options are explained further below.
Extract to Active Sheet: This option will extract the object's data to the active cell of the current sheet. It will be a snap shot of the data with the selections currently made in the listboxes on the 'Selections' tab and will not update if the selections are changed.
Extract to New Sheet: As above but in this case a new sheet is created with the same name as the object id of the object being extracted.
Map to Sheet Named '????': This will also create a new sheet named after the object id of the object being extracted. However in this case a 'mapping' will be automatically established between the QlikView object and the sheet (indicated by a star in the treeview icon) and now, when the listbox selections are updated and either of the 'Update WorkBook' or 'Update Sheet' buttons at the bottom of the Add-in's sidebar are pressed, the table will be updated. In this way you have a table of data which you can reference in your QlikView reports which is always in sync with your QlikView data.
Extract Using Cell Formulas: I'll come back to this in the next section.
The 'Fields' tab next to the 'Objects' tab has a similar list showing the available fields in the QlikView application and provides a quick mechanism for extracting them to the Excel file.
Method 2: Excel Functions
QVExcel also makes available a number of custom functions which you can use directly in your Excel cells to extract data from QlikView and also run and return the result of arbitrary QlikView expressions.
So, for example, the screen shot below illustrates using the 'qve' function (which stands for 'QlikView Expression') to run a simple sum against a QlikView field.
The qve function is particularly useful when used in conjunction with QlikView set analysis to build advanced tables and reports. Some examples of this can be seen in the demo Excel file discussed at the end of this post.
The full set of functions currently supported is listed below and we welcome suggestions for additional functions would be useful.
Note that the 'Extract Using Cell Formulas' option mentioned above automatically creates a table using the 'qv_cellvalue2' formula, automatically filling in the objectId, rowIndex and columnIndex parameters based on the table being extracted. This is useful because a table can be built with a 'live link' to the underlying QlikView object but because the data is extracted using Excel functions, cells can be formatted, rows inserted etc. in order to produce a table such as that illustrated in the demo file (see end of post), also shown in the screenshot below:
Method 3 - VBA Macros
The last technique which can be used to extract your QlikView data is the VBA macro interface. This feature is currently in development and so far the following functionality of extracting a tables data is available.
qvExcel = Application.COMAddIns("IndustrialCodeBox.QVExcel").Object
RowCount = qvExcel.GetRowCount(objectId)
ColCount = qvExcel.GetColumnCount(objectId)
startRowIndex = 2
startColIndex = 1
Worksheets("TB Data").Cells(startRowIndex, startColIndex).CurrentRegion.Clear()
calcMode = Application.Calculation
Application.Calculation = xlCalculationManual
For r = 1 To RowCount
For c = 1 To ColCount
Worksheets("TB Data").Cells(r + startRowIndex, c + startColIndex - 1).Value = _
qvExcel.GetCellValue(objectId, r - 1, c - 1)
Next c
Next r
This feature is currently in development and we welcome any feedback or suggestions for what should be available in the API.
Distribution
Once you have built your Excel report you can export a version of it with either all formulas converted to values or just the QVExcel formulas converted to values. In this way you can easily distribute snap shot versions of your reports to other users who do not have QVExcel installed and do not need the QlikView selection and associative functionality.
Next Steps
QVExcel is now commercially available from QVExcel.com where more information is available as well as a free download. The download comes with a comprehensive demo Excel file (and associated QlikView application) which shows how advanced reports can be built using the features outlined above, and each report in the demo Excel file also has an associated explanatory sheet discussing the techniques used.
Please feel free to contact me through this blog if you have any questions or would like an online demo of the product.
