Expanding Pivot Table/Chart in NPrinting

[jordanc]

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.

[Frank_S]

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.

[jordanc]

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.

                                                        

Thanks,

Jordan

[Frank_S]

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...

[Lech_Miszkiewicz]

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