7 Replies Latest reply: Jun 2, 2017 8:52 PM by Lech Miszkiewicz RSS

    Nprinting Pivot table not shown in pdf report

    wen huang

      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

        • Re: Nprinting Pivot table not shown in pdf report
          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:

          1.png

           

          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.

            • Re: Nprinting Pivot table not shown in pdf report
              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?

                • Re: Nprinting Pivot table not shown in pdf report
                  wen huang

                  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

                    • Re: Nprinting Pivot table not shown in pdf report
                      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.

                       

                      1.png

                       

                      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.

                        • Re: Nprinting Pivot table not shown in pdf report
                          Lech Miszkiewicz

                          I have tested it and it worked

                           

                          cheers

                          Lech

                            • Re: Nprinting Pivot table not shown in pdf report
                              wen huang

                              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

                                • Re: Nprinting Pivot table not shown in pdf report
                                  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