Creating templates for Excel reports based on QlikView data

Uploaded by NPrinting on 30.11.2011

You can use NPrinting to create
structured Excel reports
using data from pivot table charts
and straight tables charts in QlikView.
In this tutorial you will learn how to use NPrinting templates
to create output in Excel format
from QlikView data.
So we'll create a new Excel report, and open the template editor to create a new
which we will populate with QlikView data. The NPrinting template editor has
three areas:
the data area is where we'll put information about our QlikView charts and
fields which we can use in our report. The range area is used to define the
dynamic ranges into which we will put our data from our QlikView documents and in
the Excel area we will select which information to put into our reports as
well as being able to add Excel formulas formatting and objects like charts and pivot
tables. If we click on the toolbar button here we can show the Excel toolbar up
The Excel toolbar you see will depend on the version of Excel you have installed
on your computer.
The first thing we need to do when creating a new template is to select the
QlikView charts from which we want to get our data. To do this we click on the
report node we right click on it and click on add chart.
We then click on import charts from QlikView.
This opens a window with a list of all the straight table and pivot tables
charts in our QlikView document.
We can select one
or multiple charts and click on ok.
This will then populate the Select Charts window
from which we can select charts to use in our report. We'll select one and click
on ok.
And when we open the report node,
notice that the chart is listed down here.
If we open the chart node
you can see that along with the chart ID and the name of the chart we also
have a list of fields which are found inside that chart. Notice that those field names
are exactly the same as those in the QlikView chart.
We can now add fields from the QlikView chart
into the Excel area
either by dragging and dropping single fields like this,
or by grabbing the top of the report node and dragging the entire chart
into the Excel area like this.
When we grab the entire chart notice that not only do we drag in the individual
fields we also drag in the field headers. Now we have to define a dynamic range
which will tell NPrinting where to populate the QlikView data into the Excel
report. So we select all of the cells which contain fields from our QlikView chart,
click the range button, click on this drop down menu
and then select the chart ID from a list of available values.
Click on ok and now we have defined the data range for that chart. Data ranges are
always contiguous cells and no two QlikView charts can have a data range on the
same row in an Excel worksheet. It's possible to put fields from the same QlikView
chart on different rows, and in this case that data range would have to
contain a contiguous
set of cells like this one.
Typically you will want all of your fields from a single QlikView chart on the same
row. Now that you have defined your range you also have to tell NPrinting where
to look for fields from your QlikView charts, so select a range which includes
all of your fields.
Click on the range button here, click here
and we'll call that range the main range.
Every worksheet in every NPrinting template must contain a main range.
Ranges are hierarchical. Notice that the main range contains
the entire data range.
We can now add formatting to our report
just like we would
to any normal Excel worksheet, and for our numeric fields we can add totals at the
bottom of the columns by inserting the sum function
two rows below the cell
where the field is. The range for the sum function has to include the cell
where the field is and the cell immediately below.
In addition to formulas at the bottom of the column,
we can also add formulas which are populated down the side of the column like this.
This formula
which calculates on cells across the row, will now be populated down this
along with the field values from our QlikView chart.
Now we'll save our template by clicking here
and clicking on save. That creates an .XLS file which is saved to our hard
drive. We can now close the template by clicking here and preview the output by
clicking here. Notice the values are populated
into these columns here,
our Excel formula here is pushed down the column and our Excel sum
functions grab the appropriate range up the column to calculate totals for our
numeric columns.
We could add all kinds of Excel objects
like PivotTables and charts which run off this data,
and we can now distribute this report
in Excel or PDF formats. For more information about distributing reports
or adding Excel objects to reports watch the tutorials specific to those topics.