Reporting Services Footer issue and workarounds

I had to create an invoice report, where there is a stub on the buttom of the last page.

I need a report footer and RS does not have it.

I talked to Microsoft about it, here is the full emails between me and Microsoft.

hope this helps

Read from buttom to top

--

Hi Ronnie

Sorry I was late in getting back to you as I was out of the office in the last couple of days

I made some checks and it seems the problem that you have is a genuine one.

The workarounds that you specified are the only solutions available now.

I checked with the development team and even though they are aware of the problem, they told me that there won’t be changes to fix it in SQL 2008

So you have to decide on one of the workarounds that you specified

Best Regards,

Hi Tamer,

I wonder if you had any updates on our issue with RS2005.

Thanks,

---

Thanks for your help on this issue, I am going to more research on your solution (moving the footer to the body).

Meanwhile, I be looking forward for your sample as well.

Thanks again for all your efforts.

Ronnie Peretz

---

Hi neil

I think there is a way to implement your need in Reporting Services

Reporting Services has a free form designer which means that whatever you put on the report will appear in the same order in the final view. This is why we don’t have a report footer.

Instead of putting the stub in the footer, just put it after the last item.

You can use a list dataregion to make sure that the stub will always appear at the bottom of the page in the expected location

Ronnie, I hope that solves your problem

Contact me directly if you need more info on how to implement this

Best Regards,

Tamer Farag

---

Here are all the facts I learned about Reporting Services "Report Footer" limitations and workaround I have tried.

Objective: On the last page of an invoice at the bottom of the last page have a stub of the invoice:

Issue: Reporting Services does not support Report Footer only Page Footer. (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1439673&SiteID=1)

Work around that I have tried:

Work around 1:

I have created a page footer with a rectangle and set the Visibility expression to =Globals.PageNumber < Globals.TotalPages

Results: The page footer did show up only in the last page, but created a block of space in the first pages.

I was trying to see if there is a way to suppress the spaces from the first pages, and found out it's not supported in Reporting Services

This behavior where the report body would consume the space of the empty page header & footer is currently not supported.

-- Robert (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=340650&SiteID=1)

Work around 2:

I tried to fit all the footer objects in the rectangle in a very small height range, hoping it will auto grow at run time. That did not work as well.

The textboxes did auto grow, but the Rectangle object or the Page Footer itself does not auto grow, cause to cut the footer information.

Work around 3:

Set the property on the page footer PrintOnFirstPage to False, and PrintOnLastPage = True, Reporting services does print the footer in pages in between.

works great for reports that have only 2 pages, but not not more then 2 pages

Best workaround:

Instead of having a space block at the bottom of each page, have a marketing text on every page except the last page

I had overlap a textbox on top of the rectangle (in the page footer) and having this expression on the Visibility =Globals.PageNumber = Globals.TotalPages.

For most of the invoices which are one page, that text will not show up and the Invoice stub will show up

For Invoices which are two pages or more, the marketing text will show up in the first pages only and in the last page the Invoice stub will show.

note: this workaround cause a warning saying "Overlapping report items are not supported in all renderers."

but it is supported for PDF.

In Reporting services 2000 there is actually a messy way of representing data in your headers and footers.

I used the follwoing method to present header and footer values without using global variables for an invoice showing a remittance advice/footer with dataset values.

Basically it seems reporting services renders the body section content first, one would presume that this is because it needs to know what content lies there to produce the values for page totals and numbering etc.

What you need to do is use a text box (or multiple) in the body field, with the repeat with "table1" (which contains your invoice row detail and totals) option set, and set the hidden attribute to "true". Then overlap your table with your invoice rows on top of these fields. This will ensure that your header and footer values are repeated on each page.

Now to use the hidden text boxes you have just created in the header and footer, you just need to create another text box containing this expression

=ReportItems("TextBox1").Value

Where "TextBox1" obviously reffers to one of the fields you've created and hidden in the body content.

I hope this helps, why you can reference a textbox value in the body and not a field value, I have no idea... something overlooked I guess?

Thanks

Gary