Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Highlighted
jordanc
New Contributor II

Expanding Pivot Table/Chart in NPrinting

Hi there,

I am trying to use NPrinting to generate an Excel report from a QlikView application and I am trying to export the same chart/pivot table at different levels, but I can't seem to figure out how to do this.

I have created an example below to help you understand what I am trying to do:

Let's say the chart that I am exporting is called Sales and has Chart ID CH501. Now, this chart has the following dimensions, in this order:
 1. Region
 2. Country
 3. Company
 4. Product
That is, I have a chart in QlikView that will show the calculated sales on Region if the chart is expanded to the Region level, but will break down sales on Region>Country>Company>Product Level if expanded to the "Product" level.  

Now, the problem I am having is as follows: I am generating a report that would like to have a "Region" tab that breaks down the sales on a Region level, another tab that shows the sales breakdown on Country level, and so on.  The problem comes when I want to use the same chart with the same Chart ID for each of the tabs. I cannot seem to get NPrinting to expand or collapse a table to a specific dimension and the table will simply export at the level that the application is saved at. 

Due to the size of the table and complexity of the formulas, it would be too difficult to create copies to create different Chart IDs for fear that they will be forgotten if updates are made to the original table.

Please respond with any questions if I have not made something clear. Thanks in advance for the advice.

4 Replies
Support
Support

Re: Expanding Pivot Table/Chart in NPrinting

Hi @jordanc 

Please clarify if you are trying to use an existing pivot table chart or a straight table.

 "I am trying to export the same chart/pivot table at different levels"

To create/define a NPrinting Pivot Table created from QV data, you need to:

1. Copy and convert your QlikView (Qlik Sense) pivot table to a straight table save the app then reload your NPrinting Metadata connection to view the new chart in the NP template editor.

NPrinting requires the individual fields of the QV or QS chart in order to build a new NPrinting Pivot table made from QV or QS data sources. An existing pivot table cannot be used to create a new NPrinting pivot table.

2. Follow the instructions found in the following Qlik NPrinting tutorial: 

https://help.qlik.com/en-US/nprinting/April2019/Content/NPrinting/ExcelReports/Excel-pivot-table-rep...

or

https://help.qlik.com/en-US/nprinting/April2019/Content/NPrinting/ExcelReports/Pivot-table-Excel.htm

If I didn't understand your requirement correctly, please provide some images of your expected results and your actual results.

Thank you.

We are just 'like' you & like to be 'liked' when we provide a helpful answer and when you press the 'Solution Accepted' button if an answer provided resolves your question or issue 🙂 Cheers!
jordanc
New Contributor II

Re: Expanding Pivot Table/Chart in NPrinting

Hi @Frank_S

I apologize for the confusion.  In the QlikView application is the existing pivot table chart, not a straight table, so I believe what you are saying from your reply here is that I cannot do what I am trying to do.  

Just to ensure that I am conveying the request correctly, I have attached a picture of a sample chart (QlikView Pivot.PNG) that is similar to the chart I am working on (please note that I used the sample data from QlikView and so it is slightly different from the example I provided above). In addition, I have attached two pictures of the information I would like NPrinting to export (QlikView Export 1.PNG & QlikView Export 2.PNG, see below). In an ideal world, I would be able to use this one pivot table, and export both export 1 and export 2 using the pivot table.

                                        QlikView Export 1.PNG                QlikView Export 2.PNG

Thanks,

 

Jordan

Support
Support

Re: Expanding Pivot Table/Chart in NPrinting

Thanks for the clarification!

So you will likely need to use the 'image' object tool in the template editor to export your chart into your NPrinting report (note that this will result in a static image and not a table that can be manipulated.

In this case if you want both a collapsed version of the chart and an expanded version of the chart (again both would need to be exported as images) you will likely need to clone your chart, leaving the original chart collapsed in the QV application and the second 'cloned' chart expanded in the QV application as you've shown.

Save the charts in this state with the QVW. Then reload the NP metadata so they can be used in NPrinting.

Next add the charts as images in to your report as needed.

Then after the NP report is created, you can choose to 'hide' the cloned/expanded chart in the source QVW only 'after' it has been added to your NPrinting report. It needs to be visible to NPrinting during the creation of the report.

You can also do the original solution as well but the output cannot be forced to be expanded when you create an NP pivot table with QV or QS data. This is a limitation of excel which NPrinting leverages in the creation of excel reports. But at least they data can be manipulated with this method.

Try this and let us know if it works. If not, someone else in this forum might be able to provide an even better, more robust solution if one is available.

Hope this helps...
We are just 'like' you & like to be 'liked' when we provide a helpful answer and when you press the 'Solution Accepted' button if an answer provided resolves your question or issue 🙂 Cheers!

Re: Expanding Pivot Table/Chart in NPrinting

This is because you need to build separate charts for different levels of granularity and use them separately in NPrinting.

As you mentioned it is hard to maintain the table due to its size (maybe use variables as your measures) then you will be just maintaining your variables definitions and they will flow to all copies of the chart.

Currently this is the only way it can work so you either take that risk or have no report. Sorry!

cheers

Lech

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.