04 February, 2011

Creating Customer Invoices using Sql Server Reporting Services 2008 R2

Recently I was tasked with creating a customer facing invoice using Sql Server Reporting Services 2008 R2. This particular client had employed a technique using Page Headers and Footers that didn't upgrade properly once they moved to hosting their reports in SharePoint 2010 Integrated mode.

Because Page Headers and Page Footers are not allowed to have references to data fields, the main issue was with how to consistently place a totals section in a fixed position at the bottom of the page without using a Page Footer.

Making the task even more difficult was the fact that this report should generate all customer invoices at once. Page numbering had to be based on the individual invoice, not the total pages in the "report". Although SSRS 2008 R2 has introduced a new method to reset page numbers on a group, explained by Chris Hays here, I choose to generate my page numbers in SQL rather than in SSRS.

Each one of these techniques deserves a deep dive, but in general this report was accomplished by:
  • Using SQL Server window aggregate functions to both limit the number of invoice lines which should be presented on each page and to serve up the page numbers to be grouped upon and used in the display. Read this for more information: Aggregate Functions

  • Using one large cell of a table containing rectangles and a nested table for the order lines.

  • Fixing the report totals section at the proper location at the bottom of the report.
My idea was that I would limit the number of invoice line items to around 10 per page with my Sql generated page numbers. The report would be grouped by invoice number and page number. I would leave enough whitespace for those 10 lines to grow into therefore the information at the bottom of the report could be fixed just like a page footer.

I was having a difficult time initially getting this to work properly because the "footer" was getting pushed to the second page when the number of lines would grow. After some research, I discovered that the behavior for consuming whitespace has been changed in SSRS 2008 R2. See this article for details: Behavior Changes in Sql Server Reporting Services 2008 R2

Here is the important part:

Preserving White Space in a Report Body or Rectangle Container

Extra white space is no longer removed by default. When you render a report that had extra white space on the report body when viewed on the report design surface, the trailing white space after the last report item on the page is preserved. This may result in more pages for an existing report. To remove the white space, set the report property ConsumeContainerWhitespace to true.

Once I changed the ConsumerContainerWhitespace property to true, the report worked as I expected.


Ken L said...

Thank you so much. You just don't know how long I have been fighting the positioning issues. The ConsumeContainerWhiteSpace property was the holy grail for me.

Anonymous said...

Hi, I was hoping you had a sample rdl and SQL for creating Customer Invoices. I'm not 100% clear on how to put all this together. I have been struggling with SSRS's lack of Report Footer (vs. Page Footer like how Crystal Reports handles it)

FA said...

Hi, do you think SSRS is going to had fixed positioning capability to containers (let's say to rectangels) in order to make easier to format the layout of a report?
Creating an invoice, for instance, fixed position of some data regions (e.g. totals, vat information etc.) are mandatory, so it would be of great help the possibility to design a layout positioning some elements in absolute position in the final (print layout) page.
Thanks for any info regarding this topic.

raj said...

Hi, I was developed Invoice Report but there was a prblem.The white space not removed in the body and page footer in SSRS. As per your post I changed the ConsumerContainerWhitespace property to true but white space not remove. Plz help.

Dan said...

Did you find a solution to this. I too have a report footer that I print only on the last page, but it leaves whitespace on all previous pages. I have searched high and low for a solution but have not found one. Any help would be greatly appreciated.