topic Re: Show/hide sheets in excel generated from n printing in Qlik NPrinting

Show/hide sheets in excel generated from n printing

pradosh_thakur — Tue, 15 May 2018 14:38:05 GMT

Hi All,

Using N printing on demand Nprinting 17.6.x

Coming straight to the question

Lets say i have 3 straight table in my app Straight-1 straight-2 straight-3

instead of creating 3 different reports(lets say excel) i am using one report with th three table being in 3 different sheets.

What i want is to have 3 buttons 1,2,3 . When user click on button 1 and run the on demand task only sheet 1 and table 1 should be exported to excel. When 2 and run the same task sheet 2 and table 2 should be exported to excel and other needed to be hidden.

I tried using macro but seems n printing 17 doesn't allow macros. I tried using pagination as well but that didn't work out well in this case. Can anyone has any approach. I have like 15-20 tables and i don't want to create 15-20 reports. Any suggestion would be valuable. If you know who can help pelase tag them as well.

regards

Pradosh

Re: Show/hide sheets in excel generated from n printing

isingh30 — Tue, 15 May 2018 15:09:24 GMT

Can you share your data or sample application?

Thanks.

Re: Show/hide sheets in excel generated from n printing

Lech_Miszkiewicz — Tue, 15 May 2018 22:12:57 GMT

Hi Pradosh

understanding on demand functionality is the key to implemnt this solution. Since On-demand can only pass user selections as a filter and then trigger report generation what you probably will have to do is:

For your 3 buttons:
- instead of buttons (probably with variables) you have to create a data island field say "__ReportId" witth 3 values (like Report 1, Report 2, Report 3 so it looks nice on UI in QlikView. Based on selection from this field i would create a conditional calculation for your tables 1,2,3) - calculation condition on QlikView chart properties under general tab
- In NPrinitng Designer bring all 3 tables into left nav nodes
- In NPrinting Designer create in lev nav Formula with expression: =Only(__ReportId)
- Create on single sheet all 3 reports by dragging and dropping column tags to spreadsheet (one under another)
- Put in cell (for example in Cell a1 a formula which would return value what report needs to be created)
- Based on this value created conditional formulas to display your report headers... see image...
- User needs to select which report he/she wants to receive and the selection will pass filter on cell A1
This solution is not ideal as report 1 will start say from rows 2-3, report 2 from rows 4-5 and report 3 from rows 6-7... but it would as long as report formatting is not too complex or does not differ too much between those reports
you can take the same approach and for each sheet build separate report - the only thing is that you will not be able to hide sheets - but repectively each sheet will contain only report user has selected and other sheets would be empty

Re: Show/hide sheets in excel generated from n printing

pradosh_thakur — Wed, 16 May 2018 05:16:58 GMT

Hi Lech,

That's what essentially i have done with one exception i have used getfieldselection(field) instead of only(field).

I am not so clear here.

Put in cell (for example in Cell a1 a formula which would return value what report needs to be created)

Based on this value created conditional formulas to display your report headers... see image...

User needs to select which report he/she wants to receive and the selection will pass filter on cell A1

How this formula will control the appearance or disappearance of header. Till now i can hide the data but the header are still showing up.

I was trying to hide the sheets using this vba code. https://www.extendoffice.com/documents/excel/3561-excel-hide-sheet-based-on-cell-value.html

Re: Show/hide sheets in excel generated from n printing

Lech_Miszkiewicz — Wed, 16 May 2018 05:26:53 GMT

Using getfieldselection allows multiple selection values to be returned, so i don't think it is a good idea to use it. I would use rather binary condition!

Have a look at the screenshot:

There is an arow pointing from formula to cell A1. Formula is: =Only(Dim1) ;in my case it can return values A or B or C
now when you look at the column headers for example Cell "B2" on my screenshot - they have excel formula in there: If(A1="C","Label for header"
,"") which basicly means that headers will only apear when the cell A1 will have "C". Since cell A1 is dependent on my formula from QlikView it will then conditionally change content of cell headers to empty strings making them invisible. In my case "Label for header = Dim1

regards

Lech

Re: Show/hide sheets in excel generated from n printing

pradosh_thakur — Wed, 16 May 2018 05:55:43 GMT

Thanks Lech .. I think this will do. Did you have a look at the vba code i attached for hiding tabs do you think that will work ?

I know this question is out of the scope of this discussion but can you please let me know the flexibility of on demand feature?

1: If the user size is more than is it a good option to go to ?

2: Let say 5 person ran different on demand report at a time is the execution parallel or one after another like in a queue ?

3: Which one is faster scheduling report or on demand?

4: is there is any drawback of using on demand.

We just started using on demand and these are the question we are being asked by customers. I tried to search these but cant get a full proof answer. Can you please answer these as well.

Regards

Pradosh

Re: Show/hide sheets in excel generated from n printing

Lech_Miszkiewicz — Wed, 16 May 2018 06:44:07 GMT

Hi VBA code will not work - unless you are using NPrinitng 16.X. btw - i can't see attachment neither...

Regarding other question please create new topic and we will discuss it there

thanks

Lech