Arranging tables across excel sheets and for multiple countries

[Madhur]

Hi All,

I have to create a PDF report that I have created using Excel template. Report consist of 3 tables that need to be published for 5 countries. I want that while publishing, each table should start from different page of the published PDF as the tables are huge and can spread in 1-2 pages.

Now the issue is, If I place my 3 tables in same sheet and create pages based on Country, then each table is not starting from separate sheet, and if I put each table in different sheet of the excel then, the report will not be generated for 5 countries as levels do not work across sheets.

Please suggest , How this can be handled    - I want that each table should start from separate page and report should be generated for all the countries

[Lech_Miszkiewicz]

Hi,
Your requirement seems like simple excel based "page break" which you need to setup in excel template - see screenshots below.

On first screenshot you see that after each table i selected row and choose --> Page Layout--> Break-->Insert Page Break. The result of PDF is that each table starts from new page (to show it i zoomed out PDF preview).

[Madhur]

Hi @Lech_Miszkiewicz,

Thank you for the reply. I have already tried this solution, It will work only for the first time. If we have to generate report for multiple countries then this solution will  generate the desired output for 1st country only. 

[Lech_Miszkiewicz]

I am not sure if I understand.

I tried this solution and it worked for me. I put 3 tables in there as in my previous screenshots then I also created a Page tag (which in your case is a Country page). That generates multiple sheets as per values sitting in dimension used as a page tag. When outputting this as PDF from NPrinting I got 1 page per each table per each combination of values in page tag. 

I am not sure what else I can suggest, but it seems that you are doing something wrong there...

can you explain what do you mean by: ".....It will work only for the first time . If we have to generate report for multiple countries then this solution will  generate the desired output for 1st country only....."

I am attaching video below of what i did and how it worked for me.

[Ruggero_Piccoli · Accepted solution]

Hi,

@Lech_Miszkiewicz the template in the first screenshot uses Levels, you doesn't. If you insert an Excel page break at the end of a Level in an Excel template you suppose that the page break will be copied one time for each level so you suppose the generated report in PDF will insert a page break after each value of the level. Instead this doesn't work; the page break is inserted only after the first value in the level after all other values in the level there will not be a page break. 

@Madhur you have to try and find a solution by using native Excel page formatting feature. Creating PDF from Excel templates create those issues. We already shared ideas about PDFs from Excel formatting here in the community. The main reason is that an Excel template is done to create an Excel output and Excel itself can generate PDFs but it is not optimized for that use, for example to fine tuning a PDF layout. The best practice is: develop a template of the same kind of the output you want. For PDFs create a PixelPerfect template. 

Best Regards,

Ruggero

[Lech_Miszkiewicz]

Hi @Ruggero_Piccoli 

Fair point... I just looked one more time at this and I think this level could be moved to the page node, right?  As far as I understand requirement each table needs to start from new page. Table in the level would also produce new table, hence should be on new page... 

The alternative would be to use separate level s(based on the same field/table) for each table and put page break in between them. I know it would be an overhead on performance, but it should work, right?

cc: @Madhur 

[Ruggero_Piccoli]

Hi,

If it is possible to place the field in the PAges node this will create a new page for every new value.

Pages, Levels, cycles are all filters so from a computational point of view placing a field as a level or as a page it should be the same. More levels (also nested) you add more filters will be applied and this will require times.

Do you mean to crete the first level, then the second and manually place the page break between them? This is ok but you will have a page break only in the middle between the two levels.

If the number of distinct values in the level is low a workaround could be to create a filter for each one, add multiple times the same table in the tables node and apply for each a different object filter. Then drag and drop them in the template and manually add a page break after each one. 

In PixelPerfect instead you can add a page break at the end of the level and it will be repeated automatically at every occurance.

Best Regards,

Ruggero

[Madhur]

Hi @Lech_Miszkiewicz @Ruggero_Piccoli, 

Thank you for the resolution, levels are not working as expected with page break, so now I have removed the levels and achieving the desired result by using pages only. I am also looking at the option of using Pixel perfect template instead of Excel for better formatting.