1

I have been tasked with cleaning up some outstanding issues with my company's invoices. It is a very complicated report (four separate invoice formats are combined and determined conditionally because the application that calls them only allows 1 invoice format). I am going to try to keep this limited to just the current issue, but will provide as much info as necessary to solve the issue.

There are tons of existing questions about repeating group headers, like this one and they have helped me solve most of my problems, but I have not seen the problem I am still having addressed. I am sorry if I missed it!

The issue: The last tablix on my report is finally repeating the headers on all pages, but I now have the issue where when there is enough room, the header will display in the middle of the page below the second to last tablix.

Unfortunately, adding a page break breaks the page numbers even with reset page number set to false. (I have explicitly added the xml tag and it still resets)

What is the best way to only show the group headers at the top of the page when there is extreme variability in the size of many elements in the report (note fields that vary between one short line and epic novel length and images that vary in physical size and quantity).

Thank you for taking the time to read and help with my issue, I will continue searching through the similar questions that writing this prompted.

Community
  • 1
  • 1
WhatEva
  • 36
  • 6
  • I tried the page break again, to confirm. While the page numbers are correct when pulling up an individual invoice, pulling up multiple invoices resets all pages to display page 1 of 1. Most of our invoices are printed in batches. – WhatEva Dec 15 '16 at 15:55
  • Have you tried removing the headers row from the Tablix and just having textboxes with the header names? – Osie J O'Connor Dec 16 '16 at 09:39
  • Unless I'm missing something, textboxes outside a tablix won't repeat on every page. – WhatEva Dec 16 '16 at 13:05
  • I need them on every page, but only at the top of the page. Thank you, for your question! (Didn't realize a return would post the comment and I can't edit) – WhatEva Dec 16 '16 at 13:06
  • Here is the layout, which I have tried keep as much as possible. Main report: Header/Footer; logo & page number... SubReport: Tablix1: Header: Customer & Invoice Info... Details: Invoice LineItems This could be 1-20 pages, header is appearing at top of all pages Tablix 2: Header: Similar to Tablix1 header some info changes and dataset is different Details: Tax lines This tablix may not appear at all, or it may be many pages long – WhatEva Dec 16 '16 at 13:14
  • Have you tried ticking the "repeat header rows on each page" options in the rablix properties? – Osie J O'Connor Dec 16 '16 at 13:16
  • Tablix3 is where the issue is. It also has the similar header, but the details are two groups of images which could be 0-10. When I started on this, the headers weren't appearing on every page, that is fixed, but I occasionally have the issue where Tablix2 ends with enough room on the page, SSRS will insert the Tablix3 header, showing two headers on the page. – WhatEva Dec 16 '16 at 13:22
  • I can't figure out how to not display the header when it is based on physical space on the page – WhatEva Dec 16 '16 at 13:23

2 Answers2

0

I will wait to accept this as THE answer, in the hopes that someone out there has a brilliant solution that has escaped me.

Based on comments here https://www.mssqltips.com/sqlservertip/3482/sql-server-reporting-services-ssrs-controlling-report-page-breaks/#comments

It appears my options are: 1. Accept that some pages will have two headers 2. Accept multi-page reports displaying page 1 of 1 on every page 3. Rework the report to not use a subreport

Thank you to everyone who has taken the time to consider my problem!!

WhatEva
  • 36
  • 6
  • With regard to option 3 (reworking the report) an approach I've been forced to in the past is to push some of the report layout structure into the query. For example, if your various subreports can be rewritten to function as components in a stored procedure that uses them to write invoice data to a temporary table, and then outputs that table for your report, you can deal with a good deal of the complexity inside the SP, and output data that you can arrange to format correctly. Not a convenient answer, but it might be your least-worst option. – Mike Christie Dec 16 '16 at 21:54
  • Thank you! I will definitely look into/play around with that – WhatEva Dec 19 '16 at 13:19
  • @MikeC I am having a lot of trouble imaging how to transfer the complexity to a stored procedure. I am sure it is a block in my understanding, but if you could help with an example or two I should be able to run with it. Thanks! – WhatEva Dec 19 '16 at 16:26
0

@WhatEva sorry about the slow reply; am in and out of work this week and next. Capsule response: imagine the final, correctly formatted report, and imagine that you have all the text (non-header, non-footer) data in a single table. Add group columns as necessary to allow you to trigger the relevant headers and footers. You're likely to need a sequence id column to govern the order of printing of the rows. The report you're trying to write would have generated the text body fields; rewrite the report data sets as SP procedures to insert those text rows into the table, and ensure the group columns are filled out suitably to generate the right headers etc. Then write an SSRS report to pull that data and use the usual mechanisms to manage the headers and footers. It's hard to be more specific without knowing details, and there are certainly limitations on this approach, but it can solve some problems.

Mike Christie
  • 381
  • 1
  • 10
  • Thank you so much for your well explained response!! The part I have trouble visualizing is how all the one to many relationships would fit in a single table. There is already a work-around in place for the page number issue, so I am currently working on getting everything else working using page breaks. Thank you again! – WhatEva Dec 22 '16 at 13:11