SSRS Show footer at the end of each group

0
votes

I have a SQL Server Reporting Services report which runs a batch of invoices. Each invoice has a header, body and footer. The reports are grouped by invoice number so that each invoice is printed on a seperate page.

My problem is that when the body of an invoice exteneds over one page the footer is repeated on each page. I only want it to show on the last page of the invoice (group).

I've tried unchecking the option "page footers: show on first page" but this only removes the footer from the first page of the whole batch, not the group.

I've also tried putting the footer information into the body but as these invoices are printed on preprinted paper it is important that the footer is at the very bottom of the page.

2
sql-serverreporting-servicesreportrdl

2 Answers

0
votes

I managed to achieve what I wanted but its nothing more than a work around. As far as I could tell there's no way to do this in SSRS 2005.

My solution was to count how many rows per group were being returned and limit the rows on each page. This allowed me to infer the page numbers in the stored procedure and return the group page number and max group page number per row.

SELECT CEILING((sum(1) over (Partition by sClientInvoiceNo))/ @MaxRowsPerPage) as GroupMaxPage,CEILING((row_number() over (Partition by sClientInvoiceNo order by iSortOrder,apportionmentID desc))/ @MaxRowsPerPage) as GroupPage

I then grouped my data by the group page number within the report and specifically check the footer data for the last page

=IIF(fields!GroupPage.Value <> fields!GroupMaxPage.Value, "", Fields!iBalanceDue.Value
0
votes

I managed to get a Page Footer visible only after the end of group.

  1. You need to add a field at the end of your Group Footer.
  2. Give a name and value to that field (ex: Name = txtShowFooter, Value = False)
    Value doesn't matter, it only needs to have a value.
  3. In the Page Footer item that you want to show or hide (usually the whole content in a rectangle), set the following expression in the hidden property: =iif(IsNothing(ReportItems!txtShowFooter.Value), true, false).

Explanations:
If you are in a page where the txtShowFooter exists, you show the Page Footer.
In details pages, the textbox isn't gonna exist, thus hiding the footer.

Hope this helps other folks!