Nprinting Pivot table not shown in pdf report

[Anonymous]

Hi,

I am wondering anyone can shed some lights here.

I created a pivot table by using excel columns following the tutorial.  Afterwards, I insert a pivot chart on top of it.

In the end,  the pivot chart shows the information correctly in excel report,  but the chart is not shown in the PDF report.

The chart in the PDF only contains two columns, which seems to only reflect the first two columns and two rows information in the pivot chart, rather than the whole chart's table.

is there any solution for this situation?  Or is there anyway to create the pivot chart based on the columns in excel template?

Thanks, Wen

[Lech_Miszkiewicz]

Hi

this is very simple,

PDF output from XLS is actualy PRINT option from your EXCEL using PDF printer installed on NPrinting ENGINE.

simply test it by creating EXCEL version of this file and then try to PRINT it to PDF (NOT save as PDF!!!)

You have to set in excel printing area:

cheers

Lech

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[Lech_Miszkiewicz]

Apologies - i think i misinterpreted your question.

Actualy probloem may be slightly different

as you need to refresh pivot table and pivot chart before printing i am not sure whether this is happening when printing your pivot table to PDF.

Do you have Refresh Pivot Table and Pivot chart set in your Pivot objects properties in Excel?

[Anonymous]

hi Lech,

Yes, I did turn on the refresh pivot table option.

So the excel reports actually does refresh with proper data, but not for PDF.

Is there any other way to bring the straight table data and create a pivot chart in pdf?

Thanks,

Wen

[Lech_Miszkiewicz]

There is a way.... i think

What i did is: I have created an Excel template with macro (attached):

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 'You may need to change the pivot table reference

End Sub

This macro refreshes Pivot Table before saving file, so your table gets refreshed before it gets to be printed.

Then when creating report i have used a file with macro (see screnshot and attachement)

All other/further steps are the same as i have described in previous replies.

cheers

Lech

---------------------------------------------

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others.

[Lech_Miszkiewicz]

I have tested it and it worked

cheers

Lech

[Anonymous]

Hi Lech,

I tried your code, and still had no luck with it. The result stays same that the report using excel as output format gets refreshed,  but the reports using pdf  as output format doesn't get refreshed as expected. It plots few line of records in the left up corner,  more like it doesn't expand to print all records in the whole pivot table.

Now I am also wondering whether this is a version thing?  I noticed that I am using Nprinting 16 and it seems that you are on Nprinting 17.

Thanks,

Wen

[Lech_Miszkiewicz]

Hi,

It does not matter (NPrinting 16 & 17) as it only depends on MS Office really. To be honest it is not even NPrinting issue we discuss here but MS Excel issue.

You need to refresh ALL Pivot objects in your template and remember to USE XLSM file (not XLSX as it wouldnt work with macros)

So if you have more Pivot objects i think you have to list all of them in macro - something like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh 'You may need to change the pivot table reference

    ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

End Sub

In your template objects can have different names - find them out  and adjust accordingly.

regards

Lech