Documentation

Contents

---

Overview

QVExcel is an Add-in for Excel which allows live interactive reports to be created based on QlikView documents deployed to QlikView server.

All examples here, such as screenshots and formulas, are taken from the QVExcel demo file, which you should have recieved with your download (you can request the download by filling out this form). If you can't find the demo file, please contact us and we'll send it to you again.

These screenshots showing examples of reports created using the Add-in will give you an idea as to what can be achieved.

Listboxes in the QlikView application are automatically added to QVExcel's sidebar allowing you to make selections on your data before updating your Excel file and data can be extracted from the QlikView document by using QVExcel's formulas or the various object and field extraction options in the user interface.

NOTE: We are also developing an interface which allows communication with QVExcel via macros. If you would like more information about this please contact us.

---

Getting Started

QVExcel Side Panel

Once QVExcel is installed, a new side panel appears in Excel. From here you can enter the connection details for your QlikView server and document giving you a live link to your QlikView data in Excel.

Connection Panel

The connection panel is where you enter details about the QlikView Server where your QlikView applications reside.

The 'Basic' tab can be used where additional authentication isn't required. Simply enter the server's name or IP address and click 'Open'. This will then connect to QlikView Server and, once a connection is established, will list all the availble application in the dropdown.

If authentication is required, enter your credentials in to the advanced tab.

Your details will be stored in the QVExcel_Settings worksheet.

Selections Panel

The selections panel displays all of the listboxes that are available for you to make selections from, just like in QlikView.

A number of ListBox selection styles are supported such as checkbox style, classic and single select.

It's possible to only show a some of the listboxes that are in the application. To learn how to do this, read here.

You can also navigate forwards and backwards through your selections, as well as clear all the current selections using the buttons above the dropdown.

To the right of the dropdown, there are two buttons - one copies the selected listbox's values and the other clears it's selections.

The 'Update Workbook' button along the bottom forces the current workbook to be recalculated and any objects mapped on the 'Objects' tab to be refreshed.

The 'Update Sheet' button is similar to the previous button, but only recalculates the current sheet.

If you hover the cursor over the 'current selections' area, you will see more detail.

Objects Panel

The Objects panel is further split into two sub panels: Objects and Fields.

The objects panel is used to display all of the sheets in the application, along with all of the supported objects on those sheets. On right clicking on an object, you can extract or map values, which can be the most efficient way of getting data out, or extract tables by formulas from the selected object in your QlikView application.

On some objects, such as tables, you can also click on 'properties' to see the fields and expressions used.

The fields panel shows all of the fields, which can be extracted. Again these can be right clicked to extract their data into your spreadsheet.

---

Demo File

QVExcel comes with a demo Excel file (QVExcel Demo.xls) and associated QlikView application (QVExcel Demo.qvw).

QVExcel Demo.qvw should be placed in the root of your QlikView server folder.

The Excel file illustrates extracting data using many techniques and building advanced reports from the data.

---

Getting Data

There are currently two main methods of extracting data into your Excel spreadsheet from QlikView - QVExcel formulas and mapping/extraction. These are detailed below.

NOTE: We are also developing an interface which allows communication with QVExcel via macros. If you would like more information about this please contact us.

QVExcel Formulas

QVExcel makes the following formulas available:

• qv_cellvalue(OBJECTID, rowIndex, columnIndex)
  Description: Returns the value at a certain (zero based) row and column. This should work on listboxes, tables, charts and other objects with a tabular structure.
• qv_cellvalue2(OBJECTID, rowIndex, columnIndex, defaultValue (optional))
  Description: Returns the value at a certain (zero based) row and column. This should work on listboxes, tables, charts and other objects with a tabular structure. The final argument is an optional value to return if the cell at the specified row and column index cannot be found.
  Example: P&L Detailed Worksheet in the QVExcel demo file, Cell C210.
• qve(EXPRESSION)
  Description: Runs any QlikView expression and returns the results.
  NOTE: Please ensure that you only use single quotes ( ' ) within your expression. Only use double quotes ( " ), followed by an ampersand ( & ) when you wish to create a break within the expression to reference other cells as you normally would with Excel formulas.
  Example: P&L Summary Worksheet in the QVExcel demo file, Cell B11.
• qv_textobject(OBJECTID)
  Description: Returns the contents of a text object.
  Example: P&L Summary Worksheet in the QVExcel demo file, Cell A3.
• qv_fields()
  Description: Returns a comma separated list of fields in the document.
  Example: There is currently no example in the demo file.
• qv_fieldcount()
  Description: Returns the number of fields in the document.
  Example: There is currently no example in the demo file.
• qv_field(INDEX)
  Description: Returns the name of the field at the zero based index passed.
  Example: There is currently no example in the demo file.
• qv_rowcount(OBJECTID)
  Description: Returns the number of rows in a listbox, chart, table etc.
  Example: P&L Detailed Worksheet in the QVExcel demo file, Cell C216.
• qv_columncount(OBJECTID)
  Description: Returns the number of columns in a listbox, chart, table etc.
  Example: There is currently no example in the demo file.

Please let us know if there is a new type of formula that you need.

Extracting Tables with the qv_cellvalue() Formula

To make it easier to build tables with the qv_cellvalue() formula, you can right click on a table in the object panel and select 'Extract Table Using Cell Formulas'. This allows you to quickly create replicate tables, which you can then format, insert columns and rows etc. So, the table will automatically update with data, but you won't loose your formatting.

Extracting & Mapping Object Data

In addition to using formulas to retrieve data from QlikView, it is also possible to map or extract all the data from listboxes, tables and textobjects. To use these functions, from the objects panel, right click and select 'extract' or 'map' data.

Extracting takes a snap shot of the data from QlikView and places it in the active sheet. It's data doesn't change when you make selections in a listbox.

Mapping is slightly different in that it maintains a live link to the QlikView data by placing the extracted data to it's own worksheet. As you make selections in listboxes, the data will automatically update whenever you press the 'Update WorkBook' or 'Update Sheet' buttons.

On using these functions, the listbox will display a star next to it.

Extracting Field Data

Field data can be extracted by right clicking on a field and selecting to have it transferred vertically or horizontally from the active cell.

This can be used, for example in conjunction with the qve formula (see above), allowing you to create your own expressions based on these field values - effectively creating your own analysis which might not currently be expressed in the QlikView application.

---

Sending Out Reports

Having created a great report, you may need to send it out to other members of your organisation to review it, who may not have QVExcel installed.

It's possible to create a 'detached' copy of the spreadsheet where you can convert either just cells QVExcel formulas to values, or every formula converted to values.

To do this click one of the detach buttons on the Connection panel. You will then be able to enter a file name and location for the new copy of the file.