topic Re: Level with multiple sheets in Excel in Qlik NPrinting

Level with multiple sheets in Excel

danielact — Tue, 03 Jan 2017 15:08:06 GMT

I'm creating an NPrinting report in Excel, and I need to cycle through field values, creating multiple sheets each time.

For example, Company A would need to have sheets 1-3, Company B would be 4-6, Company C would be 7-9, etc.

Any ideas on how to do this?

Re: Level with multiple sheets in Excel

Tue, 03 Jan 2017 15:12:07 GMT

Using recipient/user filters + levels should give you the result you want.

If you create a level for a dimension with (for instance) 5 values and you send that report to a recipient who has a filter with 3 values of that dimension, that recipient will receive a report which only has 3 of the possible 5 sections of that level corresponding to the appropriate values of the dimension

Re: Level with multiple sheets in Excel

danielact — Tue, 03 Jan 2017 15:16:13 GMT

The purpose isn't to limit what gets sent to a recipient, it's to split out the data into different worksheets. I have 3 worksheets for each company. I can easily set up a Page-level using Company, but the order would then be Company A - Sheet1, Company B-Sheet1, Company C-Sheet1, Company A-Sheet2, etc.

I want it to be A1, A2, A3, B1, B2, B3.

Re: Level with multiple sheets in Excel

Tue, 03 Jan 2017 15:23:42 GMT

So it's a question of sort order of the worksheets?

Re: Level with multiple sheets in Excel

danielact — Tue, 03 Jan 2017 15:25:05 GMT

Pretty much, yes

Re: Level with multiple sheets in Excel

jacob_hoffmann — Tue, 03 Jan 2017 16:34:22 GMT

Hi Daniel,

You need to use the "Add Page" function in the designer. Pretty straight forward:

Qlik NPrinting features for creating Excel reports ‒ Qlik NPrinting

Go to the part named: Multi-page publishing: select a page

Jacob

Re: Level with multiple sheets in Excel

danielact — Tue, 03 Jan 2017 16:38:05 GMT

As I wrote to Aran earlier, I could do that, but the sheets wouldn't be in the order I need,

Re: Level with multiple sheets in Excel

Tue, 03 Jan 2017 16:39:58 GMT

Sheet sort order is based on sort order for values of the dimension in the Qlik app. Change the sort order there and the order of sheets will also change.

Re: Level with multiple sheets in Excel

jacob_hoffmann — Tue, 03 Jan 2017 16:44:37 GMT

OK - and just like Aran just wrote: Work with the sort order of the dimension. One easy way to control that would be to load the dimension value as a dual in QlikView/Sense with a built in sort order. I think you're set

Jacob

Re: Level with multiple sheets in Excel

danielact — Tue, 03 Jan 2017 16:48:02 GMT

But I'm not having issues with the sort order of the Dimension. I just have 3 different worksheets for the one dimension. If my dimension is Company, I want the pages to end up as A1, A2, A3, B1, B2, B3, C1, C2, C3.

If I use Page to create the pages by company, I'll end up with A1, B1, C1, A2, B2, C2, A3, B3, C3.

Re: Level with multiple sheets in Excel

jacob_hoffmann — Tue, 03 Jan 2017 16:56:50 GMT

My bad,

But still I would go for the page-functionality, that's the only way in NPrinting.

And then I would go for a little work-a-round in QlikView, because the feature you need isn't precooked, though it's a very good idea! I can clearly see it as a requirement I could encounter in the future.

I got intrigued and got some way...

My thought was to create a new table in the data model, which was linked to the company id. For each company id in the new table, I created 3 entries of similar company names. They have the same company name, but because I loaded them as duals they appear as different.

With that in place, I thought that I would be able to loop over this new dimension...

BUT now I really realized the pain: I guess you would show different objects on the 3 sheets per company... and this fact my work-a-round didn't help. You’ll end up with 3 similar outputs.

Next twist was to build-in some logic into the objects, so they displayed something different for each sheet. That's possible but arguably quite a long shot.

For inspiration or how-not-to I have attached my sample QVW. Perhaps some sleep will bring an idea to mind, or perhaps you will

Jacob

Re: Level with multiple sheets in Excel

Anonymous — Wed, 04 Jan 2017 13:51:18 GMT

Hi,

If I understand correct, you want the show the same tables with different filters (for company A, B, C, ect) in different sheets.

You can do this if your excel template use the "Table" object, you can add the dash table multiple times, and use filter to get the Company you want.