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

[aeveltstra]

We are using QlikView and Qlik NPrinting 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...

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?

[Frank_S]

The recommendations that you were provided with are correct. 

They can be found here in our help pages as well.

• https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/DeployingQVNprinting/Performance.htm

Good to know about the vba macro then. That should take the image processing out of the equation. 

Seems to be the heavy usage of levels that are impeding the performance.

My task is still running but down from 104 request to 87 after 40 minutes of processing.

[Frank_S]

Today's files removed...thanks for sharing them!

[aeveltstra]

#1  Definitely plays a role. With the QVW stored locally, the report generation task finished in 3 minutes. With it hosted on the Qlik server, it's already taking more than 15 minutes. I'm going to let it run and see whether it will complete, and if so, how long it took.

[Frank_S]

Perhaps your QlikView server is engaged by day to day processes which cause system resources to be consumed there and slow down NPrinting report performance. Adding resources to QV server might improve performance where QVP connections are used.

Further, it's not clear which of QlikView you are running. Current QlikView versions supported for use with NPrinting May 2021 and higher releases have been reported to show improved overall performance.

• https://help.qlik.com/en-US/nprinting/May2022/Content/NPrinting/DeployingQVNprinting/System-requirem... (QlikView Compatibility section)

[Frank_S]

Just one more observation for today

The requests have jumped up again  over 100 (123 exactly when looking at the admin plan) . This is inline with report generation going through each page and or level. 

With each page or level, the requests will start over until all pages and levels are processed.

I'll update the time of the report generation on Monday. Looks like it won't finish before the end of today with the original report template.

Have a good weekend!

ps: now 188 requests

Now 285

 

Now 396 and moving lower again

[aeveltstra]

Most certainly problematic. It's been running for over an hour, with no visible progress. Whereas accessing the same file from the local NPrinting server took minutes.

[Frank_S]

@aeveltstra
I have confirmed the issue with May 2021 SR 4 and QlikView 12.5 SR 4

Steps to reproduce:

1. Upgraded your Report to May 2021 SR 4 using the NP server installation file executible
2. Created two connections to your QVW (Local and QVP
3. Created two side by side publish tasks and assigned the report to each task
4. Executed the tasks simulatenously

Results:

• Report associated with the local connection executed in just over one minute
• Report associated with QVP connection is still executing. (In the pre-upgrade tests, the report took 7 hours to execute).

Please start a support case asap so we start a defect report with R&D.

• Create Case in Case Portal

Wish the news was better. Thanks for sharing and working with me on this.

Kind regards...

[Frank_S · Accepted solution]

Well I may have made that last comment too quickly.

There is no need to submit a support request afterall.

The solution is quite simple. 

You simple need to

1. open a local copy of the QVW
2. go to the 'server' tab
3. deselect 'enable push from server'
4. save the QVW and reload your NPrinting connection using QVP
5. rerun the publish task

You will find that it is working in about 3 minutes approximately in your current version of NPrinting.

I will write an article about this. 

In the meantime, note that your version of NPrinting should have the fix but it doesn't seem to be working as expected. Current versions of NPrinting do have much better success where ;enable push from server; is used (testing in May 2021 SR 4 using your report and it took 17 minutes with enable push from server and only 1 minutes with 'enable push from server deselected).

So no need for a support request after all. 

Upgrading to current versions of NPrinting will improve performance with out changing this setting but you can resolve this today by following the above steps and deselecting 'enable push from server'

Kind regards...

[aeveltstra]

Really? That's odd. Looking forward to an article explaining why that setting is detrimental to NPrinting report generation!

[aeveltstra]

I guess that explains why generation performs quickly when copying the source to the local NPrinting server: there is no Qlik server available there, to push any updates.