topic Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter in Qlik NPrinting

How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

sten_lomme — Fri, 15 Nov 2024 21:25:47 GMT

Let's say we have a model with a user dimension with the following values:

John
Jane
Rich

Now all these three have sales connected to them, but when the NPrinting model filters to let's say August, where Jane has no sales. Jane should not be populated as a seperate sheet. Is this possible?

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Lech_Miszkiewicz — Thu, 17 Aug 2023 11:36:13 GMT

Of course it is possible. It is strange that it is not working for you since applying filter on period should by association already filter out data only to possible values which is why it is important to understand how data model is built and what filters are being applied.

Now if your period filter does not filter out users due to model design you can always apply advanced search filter on user field on task or report level which will be based on measure you use to check if there is data or not

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

sten_lomme — Thu, 17 Aug 2023 13:13:49 GMT

How would an advanced search filter look like in NPrinting to accomplish this?

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Lech_Miszkiewicz — Thu, 17 Aug 2023 13:28:34 GMT

Advanced filter in NPrinting will look the same as in Qlik Sense or QlikView.

you can read about it here: https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/

and here: https://help.qlik.com/en-US/nprinting/May2023/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm#anchor-9

....so assuming we filter your sample users by sales filter would be:

Field: User
Advanced Search: = sum(Sales)>0

cheers

btw - if your data by association removes those user that filter should not be required as it should just work without it as I mentioned before. So If you need filter - then you may first look at the cause why selection of period is not excluding users without data.

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according...

Frank_S — Mon, 21 Aug 2023 19:10:16 GMT

@sten_lomme

Another possible solution for your use case "where Jane has no sales. Jane should not be populated as a seperate sheet. Is this possible?"

This might required multiple tasks with conditions applied to each task

1. Task 1 (original report) - users get report if chart has data (condition rule: sales chart has data )

2. Task 2 - (new blank - no NP tags excel report) users get empty report sheet sent to them if chart has no data (condition rule: chart has no data/empty)

Here is a good video that describes NP conditions well.

https://www.youtube.com/watch?v=CvEp8_gHkvc

I may not have fully understood your requirement however this or some variation of therein might work.

Here also is something that 'might' help you to build out your requirement as well.

https://community.qlik.com/t5/Member-Articles/The-Multi-Report-NPrinting-template-that-conditionally-renders/ta-p/1515696

Kind regards.

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Ruggero_Piccoli — Tue, 22 Aug 2023 06:44:46 GMT

Hi,

Please add the label with the correct version of Qlik NPrinting you are using. This is not the case, but the answer could depend on it so take the good habits to fill every community post with the needed details. Thanks.

Bst Regards,

Ruggero

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

sten_lomme — Thu, 24 Aug 2023 10:34:16 GMT

Hi,

When we set the exact same filter in the QlikView app, all unwanted users are grayed out and I would think they are then removed by association.

We are using set expressions in load script with dynamic dollar sign expansion. Is it possible to use dollar-sign expansion in the NPrinting Advanced Search...something like below...but this does not seem to work. Am I using the wrong syntax?

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

sten_lomme — Thu, 24 Aug 2023 10:34:47 GMT

We are on Qlik NPrinting May 2022 SR4

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Lech_Miszkiewicz — Thu, 24 Aug 2023 12:45:51 GMT

I am not sure what you do there and what expression you have under the variable.

If you are asking if variable can be expanded with $ sign in Advanced search - I will say yes, it can and it works.

I just tested it and it worked as expected. I created variable vExp_1 with definition Sum(Expression1), I regenerated NPrinting connection metadata and I applied it as advanced filter. It worked as it should,

cheers

Lech

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

sten_lomme — Thu, 24 Aug 2023 13:03:47 GMT

Found the issue. The reason is the limiting filter was set on the report object, and not on the NPrinting task. So in essence for level to work correctly, the filter needed to be applied to the task instead.

Thanks for all help!!!

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Ruggero_Piccoli — Thu, 24 Aug 2023 13:12:42 GMT

Hi,

I want an Excel report with a worksheet for each salesman with more than 100000 $ in sales in USA:

1 Create a filter with Advanced search

2 create the Excel template with the field Salesman in Page

3 apply the filter country=USA and the advanced one to the report. Pay attention at filters order

4 run a preview and you will have a worksheet only for salesman with more than 100000$ in USA and no other black worksheet

I agree with @Lech_Miszkiewicz that you could have issue in how the variable is build.

Best Regards,

Ruggero

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Lech_Miszkiewicz — Thu, 24 Aug 2023 13:20:38 GMT

Funny - thats was a rookie mistake - you have to admit it!🤣😂

Anyway - I am glad it worked for you. Make sure you study documentation and get proper training in NPrinting or at least go through the part of help marked below as a total minimum!!! If you would have done that before you would know straight away that you need report or task filter.

Important: Make sure you understand that advanced search filter will only work within possible selection state. That means that values excluded from selection state by other filters will no be selected when advanced search filter gets applied. It works like that also directly in QlikView or Qlik Sense.

cheers

by the way - that below (the whole "Designing reports" section) is a absolute minimum you should go through before developing in Nprinting. Good luck!

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

sten_lomme — Fri, 25 Aug 2023 14:09:17 GMT

I can't use the filter on the task or report, as other sheets of the report needs access to more data. But we can live with the current limitations as of now. Thanks!!!

Re: How to limit Excel tabs when using Page-cycle to only include tabs with active data according to current filter

Lech_Miszkiewicz — Sat, 26 Aug 2023 02:07:51 GMT

You could have. 1 report but create 2 different connections to the same qlik app. For the purpose of sheet filtering and its content you could use one connection and for other sheets you could use second connection.

building Nprinting report should start from understanding what we need to build-knowing exactly what final outcome needs to look like, where data comes from, what filters are required, what levels, pages and object we need to apply. You should design everything from the beginning so you wouldnt waste time re-doing everything.

any challenges? I am sure we will find solution here-cheers