topic Re: How to speed up excel report generation if using custom conditional formatting rules in Qlik NPrinting

How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Tue, 31 May 2022 13:58:48 GMT

We are using and to generate Microsoft Excel spreadsheets. Our client wants to apply custom conditional formatting rules. But doing so, severely slows down report generation. And by severely, I mean the duration went up from a couple minutes to a couple hours.

I thought, wrongly, that Microsoft Excel's limitations are at the heart of the problem, and they need to fix their product. I have outlined my argumentation in this article:

https://medium.com/@aev_software/conditional-formatting-in-excel-reports-generated-by-qlik-nprinting-a86c67c34b17

But in the meantime I'm looking for a way to speed up report generation that allows me to continue to use custom conditional formatting rules.

We have 1 dedicated Qlik server and 1 separate dedicated NPrinting server. The Qlik document reloads data in like 3 seconds from a qvd that reloads in about 2.5 minutes. It only holds the data needed for NPrinting: no further filtering or cycling is needed or applied. There is no section access, no data reduction, and no triggers. The NPrinting server successfully caches the document's metadata from the Qlik server using the qvp protocol.

I'm in full control of every part of the documents, installation, and configuration.

Please advise?

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Wed, 25 May 2022 20:09:46 GMT

@aeveltstra

We appreciate the well-documented details!

Do you have a test NP report and test QVW that we can use to test this in May 2022 release of NPrinting?

Or conversely, you may wish to set up and May 2022 NPrinting Test Server deployment and try it there.

Perhaps the performance in NP May 2022 might improve the overall report generation.

Keep in mind to ensure that your QV server has sufficient resources since the heavy lifting of report processing is offloaded to the QV server when QVP connections are used. (The NP engine computer should also be monitored for hardware resource utilization. If it is getting maxed out consistently (or when this report is generated), you may wish to consider added system hardware resources accordingly.

Finally, if your QVW contains charts which in turn contain heavy usages of for example:

Calculated Dimensions
Conditional Columns

Would recommend moving as much of these (if any that is) to the load script where possible to further mitigate potential performance latency issues.

Please also note that calculated dimensions are not supported for use with levels and pages in the NP designer.

https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/ReportsDevelopment/Qlik-objects-supported-NP.htm#anchor-2

Kind regards...

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Wed, 25 May 2022 21:49:48 GMT

Hello!

Yes, I have available an Nprinting template and a QVW and the QVDs on which it is based.

I can share them tomorrow.

Yes, I can make time to set up an NPrinting test server.

I will review the other hints and see to implementing as many as possible.

Thank you,

André

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Wed, 25 May 2022 21:58:57 GMT

No worries!

#ICYMI

New download site here:

Downloads | Qlik Community

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Thu, 26 May 2022 14:29:27 GMT

Please find attached the QVW that is used as the data input and the charts source for the NPrinting excel template, also attached.

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Thu, 26 May 2022 14:30:44 GMT

Great!
I downloaded, then removed the files from the post in case there is any sensitive information associated with them.
Will test today if possible..thanks!

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Thu, 26 May 2022 18:51:30 GMT

@aeveltstra
Inspecting your file I found that you have a module password on what might appear to be protecting a macro.

If you do have a macro I suggest.

1. Removing the password first

2. Removing the macro entirely on your NPrinting reporting QVW.

This could be contributing to the issue.

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Thu, 26 May 2022 19:32:40 GMT

I found two more problems:

When attempting to import the report, I got the following:

When importing images to a test report, I got the following:

So it seems there are many possible problems here.

1. Use of a macro in the QVW. Remove the macro password and the macro itself.

2. Problem with the source data, it may have changed and should be updated in the NPrinting report by right-clicking on each table object on the left side of the designer and choosing 'update' from the context menu

3 It's not possible to import the report indicating other problems potential/unknown problems within the report. Possible that the report itself may have become corrupted.

Further, I did notice you are using merged fields as well.

Note that we have resolved some problems with use of merged fields in current versions of NPrinting

So in conclusion, here are my suggestions.

Update your table chart objects in your current report template.
Remove QVW macro and macro password.

If these do not work then suggest that you may consider:

Upgrading or Testing in an upgraded version of NPrinting. I suggest first restoring your NP database to another Sept. 2020 test NP server, then upgrading that test server to a May 2022 NPrinting server. If that doesn't solve the issue
Consider rebuilding the report entirely as it may have become corrupted if this issue persists after taking all the above actions.

Kind regards...

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Thu, 26 May 2022 20:17:19 GMT

OK. I removed the module password. There is no macro, no trigger.

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Thu, 26 May 2022 20:21:56 GMT

I'll review the report connection to the data objects. I didn't get the same errors you got, but I'll double-check nonetheless.

There was no macro but I have removed the module password, have explicitly unset variables, and removed some unused variables. I also removed conditional chart formatting in the QVWs that are used as an NPrinting source.

I have rebuilt these reports many a time, imported and exported them many a time, without fail.

I really don't want to rebuild them from scratch as they take a lot of work. But if that's the only way forward, then that is what will happen.

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Thu, 26 May 2022 20:33:36 GMT

Rebuilding the report is the last resort option.

I've shared other steps which precede that option that 'might' solve the issue.

One last point. it could be possible that you have an antivirus running that does not exclude key NPrinting folders. So ensure the exclude those going forward to mitigate potential file corruption in the case that they currently are not excluded.

https://community.qlik.com/t5/Knowledge/Qlik-NPrinting-Server-and-Designer-Anti-Virus-Folder-Exclusions/ta-p/1715440

Kind regards.

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 15:27:34 GMT

OK. I have excluded the folders and processes from the virus scanners. Thank you!

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 19:11:09 GMT

Removing the module password and removing superfluous sheet objects and variables seems to not have had any impact. NPrinting has been attempting to generate reports now, for 10 hours. Not failing, either.

Let me attach the latest template and its QVW file. You can't reload it as you don't have the QVD files that are its source. Feel free to remove the files as soon as you copy them.

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 15:38:13 GMT

Removing the module password did not help.

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 15:40:01 GMT

Yes, we have merged fields. I noticed the problem with merged cells a few years ago. So instead of having NPrinting write directly into them, I have it write into an adjacent cell, and then have Excel copy the contents of that into the merged cells. That has worked quite nicely.

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 16:32:44 GMT

Question:

We host the source QVD files on a shared server. Would that contribute to slow report generation? Not that I've seen that in the past. But just food for thought.

So here's what we do:

1. QMC background job reloads a loader.qvw on schedule. It pulls information in from our ERP, and stores that in a handful of QVDs on our file share. The file share is a dedicated server with a huge performance ability for I/O.

2. Small child QVWs have been created that load parts of the data from the QVD files filled by the loader process. Those child QVW have been stripped of anything not needed by its NPrinting report. They reload automatically upon successful reload of the loader process.

3. NPrinting accesses those small child QVWs through the Qlik server, using the QVP protocol. So most heavy lifting is performed on the Qlik server.

That's best practice, isn't it? Am I missing something?

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 18:52:41 GMT

Alright. I found something that works.

1. Instead of accessing the QVW from the QlikApp server, I copied it locally to the NPrinting server. Reading connections and metadata cache from there.

2. I removed excess levels from within the report. 5 Levels that now are addressed by producing the small dedicated QVW files, and 1 that is addressed by report cycling.

Together, that reduced the report generation from over 12 HOURS to just a few minutes. Including the conditional formatting rules for Excel.

Next: revert point 1 as to see whether point 2 works just as well on its own.

Re: How to speed up excel report generation if using custom conditional formatting rules

Frank_S — Fri, 27 May 2022 18:56:00 GMT

NPrinting connects only to the QMC reloaded QVW only. It does not connect to the QVDs behind the scenes.

I was able to import your report now with the latest QVW and report template.

I am running tests now.

Do you have any conditions or filters directly on the report or on publish task?

Further, I noticed in a chart that was collapsed that you are accessing files located on the network location.

Does the NPrinting Service account have access to that folder and does your report included those images? If yes, then the NP service account needs full shared and security (folder and NTFS file access) to that networked file location.
Processing images from a network location can be fraught with performance issues. (I won't be able to complete successful testing likely as my service account will most certainly not have access to those networked folders. But I will let the publish task with your report keep running in any case on my end so I can see any pertinent logging on this end. (the chart ID in the QVW provided that contains the network paths is StylesAndImages). This might have some impact on the issue.
Yes I also notice the heavy use of levels. That too will impact performance.

Kind regards...

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 18:58:58 GMT

The ridiculousness of #1 is that with one of our other dashboards, hosting the QVW file on the NPrinting server completely maxes out CPU and memory when regenerating the metadata cache. The difference is that that is a large dashboard with a huge amount of data, charts, and calculated dimensions, which all must be cached. I have a support case here in which the advice is, to break up the big dashboard and make small, dedicated QVWs, dedicated to the report at hand. So when I did that with the reports that lead to our current discussion, I was absolutely baffled to see that report generation was taking hours, with no progress. (Literally. It went from taking minutes to a few hours, then to over 12 hours with no progress.)

Re: How to speed up excel report generation if using custom conditional formatting rules

aeveltstra — Fri, 27 May 2022 19:02:57 GMT

Yes, I have removed several levels and that appears to have helped. Running a test to see whether other factors also play a role.

Yes, the NPrinting service account does have access to the images folder, but no, the images themselves are not put into the report by NPrinting. NPrinting just places the link to the images, and then a self-authored Excel VBA macro converts those into the actual images. Every client has access to the images.