topic NPrinting - Summarize Excel Reports in Qlik NPrinting

NPrinting - Summarize Excel Reports

christiana — Tue, 01 Mar 2016 14:41:01 GMT

Hi,

I'm trying to setup an Excel Report in NPrinting. This Excel report is based on a Qlikview table. The Qlikview table has more details than what I want to show (e.g. Country, Customer and CoGS) but I only want to show Country and CoGS.

What do I need to do so that I get only one line per Country and not a line per country and customer ?

Currently:

Wanted:

Current: Setting in NPrinting:

Added via

Many thanks for your help and kind regards,

Christiana

Re: NPrinting - Summarize Excel Reports

Anonymous — Tue, 01 Mar 2016 16:50:57 GMT

I would just duplicate the table in Qlikview on a new sheet and hide the undesired columns and then use that chart to feed your report. Even though the data is available in another chart, I often create a new sheet and build charts and objects specifically to feed NPrinting Reports. You can always hide the sheet.

That way you can see the output you will get and control it in Qlikview.

For me, NPrinting is a tool to display the great visualizations that I have already created in Qlikview. It baffles me that people want to export data out of Qlikview and turn around and rebuild it again in another tool.

Re: NPrinting - Summarize Excel Reports

christiana — Tue, 01 Mar 2016 17:08:27 GMT

Hi Mike,

Dublicating the table is just what I wanted to avoid in order not to create hundreds of tables, I thought that I might put all in one table and then just choose the dimensions I need.

The original Qlikview application is used by a lot of users and already quite heavy, so I wanted to avoid to push the size greater than it is already. I'm using NPrinting as a reporting tool in order to get the data out of Qlikview to be able to send reportings to people who do not have access to Qlikview.

I appreciate any suggestion how to summarize the rows this would avoid that I need to create too many tables within QV.

Many thanks,

Christiana

Re: NPrinting - Summarize Excel Reports

Anonymous — Tue, 01 Mar 2016 18:10:44 GMT

Chistiana,

I apologize if my reply seemed less than helpful.

If you are not using On-Demand, my next suggestion would be to create a separate QVW file that loads the same data as your original model. Use the new qvw to build your charts that you want to use in your reports. This reduced load on your resource heavy app might improve your users performance.

For all of my Nprinting reports, I spin off a separate qvw file that is not the production qvw accessed by the users. If you use Publisher, you can just add the creation of that second qvw to your reload schedule. I use the same loader qvd to feed both.

You are either going to build tables in Nprinting or you are going to build tables and Charts in QV.

Not sure that was the simple answer you were looking for, but hope it is helpful moving forward.

Re: NPrinting - Summarize Excel Reports

Daniel_Jenkins — Thu, 03 Mar 2016 02:42:23 GMT

Hi Christiana,

You can create an Excel Pivot table in a QlikView NPrinting Excel report to summarize the values.

Here is the tutorial that shows you how: How to Create an Excel PivotTable Report

I have attached the project I used in case you want to take a look. Extract to folder C:\Temp\Community\045 or change the paths in the NSQ.

HTH - Daniel.

Re: NPrinting - Summarize Excel Reports

christiana — Thu, 03 Mar 2016 14:38:36 GMT

Dear Daniel, dear Mike,

many thanks for your answers, unfortunately they're not going in the direction I would like them to go.

As I understand from your messages, it is not possible to say to NPrinting to combine the rows.

Creating a new QVW-application for NPrinting was already in discussion with IT, but I don't really want to do this as a lot of calculations are done in the layout and not in the datamodel. Having two layouts would mean that I need to maintain the formulas in both which could easily lead to errors in case an update is once forgotten in one of the two layouts. Therefore I will not go for this solution.

A pivot is not an option neither as I don't want to have a databasis and a pivot included in official reports.

I was now building up a table with flexible dimensions which are called via variables. This is working fine (and is quite fast too ) for a straight table. When I want to switch the straight table to a pivot table, I'm not able to choose any dimension or expression:

As you can see on the screenshot the first table does not have a '+' where I can click on - this is a pivot table. The same table just as a straight chart is below - here I can choose the expressions and dimensions.

Any idea what I can do so that I can have the pivot with the dimensions and expressions? Background is, that I would like to have some subtotals which I can't do in the straight table, but which is possible in the pivot.

Or is there any other way how I can add subtotals to the straight table?

So far it is looking like this:

What can I do so that I do have a subtotal per each SalesOrg?

Many thanks to you both,

Christiana

Re: NPrinting - Summarize Excel Reports

Daniel_Jenkins — Thu, 03 Mar 2016 21:25:10 GMT

Hi Christiana,

I would use the straight table and create a Level on the SalesOrg field. This tutorial shows how to create nested levels and subtotals - you of course need only one level.

How to Create Excel Reports with Nested Levels & Subtotals

As you discovered, it is not possible to select individual dimensions or expressions with pivot tables.

HTH - Daniel.

Re: NPrinting - Summarize Excel Reports

christiana — Fri, 04 Mar 2016 15:56:43 GMT

Hi Daniel,

many thanks to you - that's a pity that individual dimensions / expressions can't be selected in a pivot.

Thanks for the hint with the Levels - I wasn't aware that I can combine levels and a straight tabel.

Have a nice weekend,

Christiana