Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

lornafnb
Valued Contributor

Excel behaving oddly in report

hello guys

I create an Excel report using Nprinting and email it as an attachment to some of our users.

if I open this spreadsheet directly from the email, the figures are totally off.

if I save it somewhere on my pc, manually open up Excel, and then File->Open AND enable editing, the figures come through right.

Any idea what this could be?

Thanks

1 Solution

Accepted Solutions
Lech_Miszkiewicz
Honored Contributor III

Re: Excel behaving oddly in report

Hi Lorna,

Maybe you can implement solution with Excel spreadsheet with macro in it, which would force refresh pivot table prior saving and sending file?

See instructions how to do this here:

Nprinting Pivot table not shown in pdf report

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.

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
11 Replies
Lech_Miszkiewicz
Honored Contributor III

Re: Excel behaving oddly in report

Do you have "vanila office installed" or with some plugins? Often companies are installing bunch of add-ons for version control of their XLS files etc.. then this may be a case.

How about if you preview this document on your phone, or tablet?

cheers

Lech

btw - which version of NPrinitng are you using? 16 or 17,

Can you creenshot your template? are there any macros / formulas in your template? It is possible that file has to be processed before showing right numbers if it was designed in this way.

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
lornafnb
Valued Contributor

Re: Excel behaving oddly in report

Hello Lech,

When I preview the doc on my phone, the figures are also skewed.

We’re on Nprinting 16, I’m including a copy of the template.

Regards

Lorna

Lech_Miszkiewicz
Honored Contributor III

Re: Excel behaving oddly in report

Hi Lorna,

Ok, thank you for this template.

Now the questions:

  • are numbers in ALL sheets skewed?
    • SSCC - GROUPED DETAIL
    • CH 57 Data
    • SSCC - GROUPED DETAIL
    • Overview Data
  • In above mentioned sheets i would expect to have correct numbers
  • I would only expect issues with following sheets as they are Pivot Tables and need to be refreshed prior to viewing.
    • SSCC CONSOLIDATED
    • Overview

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.

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
lornafnb
Valued Contributor

Re: Excel behaving oddly in report

You are 100% spot on – the skewed figures come from SSCC Consolidated and the Overview tab.

What puzzles me – I have ticket the option for both of these pivots to refresh data..

Lech_Miszkiewicz
Honored Contributor III

Re: Excel behaving oddly in report

Hi Lorna,

Maybe you can implement solution with Excel spreadsheet with macro in it, which would force refresh pivot table prior saving and sending file?

See instructions how to do this here:

Nprinting Pivot table not shown in pdf report

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.

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
lornafnb
Valued Contributor

Re: Excel behaving oddly in report

Thank you I will have a look at it

lornafnb
Valued Contributor

Re: Excel behaving oddly in report

Hello,

I’ve checked out your suggested post. Understand it totally.

Just one question – I’ve got multiple objects on the one report that I’m creating. So would I still add this bit of code to my existing template ?

thanks

Lech_Miszkiewicz
Honored Contributor III

Re: Excel behaving oddly in report

Hi,

Just one question – I’ve got multiple objects on the one report that I’m creating. So would I still add this bit of code to my existing template ?

What do you mean? Multiple PivotTables? - because macro only needs to refresh them

So:

  1. first of all you need to change your file from XLSX to XLSM (in order to get macros in Excel)
  2. once you create your template in XLSM file - you can use file i have attached (this will look exactly the same like the one you have sent me) you have to edit macro i have attached in order to refresh all PivotTables in your file
  3. I do not think you can edit macro using NPrinting designer so just use normal MSExcel to open your XLSM template, hit ALT+F11 (this should open VB script editor). There you can modify a code which may look 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 'You may need to change the pivot table reference

End Sub


you need to check  and list all Pivot tables which need to be refreshed (as an example I have put "PivotTable1" and "PivotTable2"

does it make sense?

cheers

Lech

cheers Lech
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution.
Please LIKE threads if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem.
lornafnb
Valued Contributor

Re: Excel behaving oddly in report

Hi Lech,

Yes, thank you very much for making this clear to me.

Have a good day

Community Browser