Page number in landscape reports

Question

Please consider this custom report:

I want to generate that page number automatically on all pages. I have some landscape pages. I want to place the page number in the footer, but I have problem with this in landscape reports because footer in landscape reports place in bottom of the page not in right of the page so I should place page number in body section.

How I can place a page number in the body section?

Answer 1

I don't think this will be possible unless you get creative.

Without having the program on hand at the moment I hope this could start some thinking.

Try add an exrta column to your data counting the nuber of rows and force a page break every x rows. If that works you may be able to play with some formatting to get it visualy right.

Wow good luck!

Answer 2

This article may be of some use: Determine page number in SSRS

Determine page number in SSRS

This method takes advantage of SQL Server’s new ranking functions and can be used in your SQL Reports to determine the page number in the body of a report and also possibly act upon that information to hide/display columns.

Use this method if:

1. You have a preset limit of how many records display on a page
2. You have grouping and each group shows on its own page
3. You can accurately calculate the number of records on each page

Solution in article uses the Ranking Functions in SQL, well worth a look to see if it is applicable.

So I mentioned there were 3 scenarios where this could work for you. I’ll give an example on how to do each and let your creativity do the rest. This will server as our sample sales table:

name     sales_amount    group_name  Scenario #1     Scenario #2     Scenario#3
A        10              A1          1               1               1
B        20              A1          1               1               1
C        30              A1          2               1               1
D        30              A2          2               2               1
E        40              A3          3               3               2
F        40              A3          3               3               2
G        50              A4          4               4               2
H        60              A5          4               5               2

---

Scenario #1:

You have a preset # records to be places on each page: Here’s what your SQL Query would look like:

DECLARE @rows_per_page INT
SET @rows_per_page = 2
SELECT
 employee.name
 ,page = (Row_Number() OVER ( ORDER BY employee.sales_amount ) / @rows_per_page)
FROM
 employee

In SSRS you can then create a parent group in your tablix (SSRS 2008) and set the option to break “Between each instance of a group”. (Page Breaks tab in SSRS 2008)

---

Scenario #2:

You have grouping and each group shows on its own page

SELECT
 employee.name
 ,page = Dense_Rank() OVER ( ORDER BY employee.group_name )
FROM
 employee

On your report in SSRS, on the group properties for the table/matrix/tablix set the option to break “Between each instance of a group”. (Page Breaks tab in SSRS 2008)

---

Scenario #3:

You can accurately calculate the number of records on each page If you can accurately calculate the number of records on each page, it becomes an easy task to use a similar methods as in #1. In the example, the number was 4 records per page.

Answer 3

Use this: Access Page number in report body In SSRS

Or this: http://social.msdn.microsoft.com/forums/en-US/tfsreporting/thread/b901c9b4-320e-4fbc-afa9-495d5566c168/

You just need to create custom code for the report and then reference that code where you want (referenced from the second link):

With the Data or Layout tab view active select "Report" -> "Report Properties" On the "Report Properties" dialog select the "Code" tab In "Custom code:" add the new Functions to get the page numbers:

Public Function PageNumber() as String
     Dim str as String
     str = Me.Report.Globals!PageNumber.ToString()
     Return str
End Function

Public Function TotalPages() as String
     Dim str as String
     str = Me.Report.Globals!TotalPages.ToString()
     Return str
End Function

Use it like:

="Page " + Code.PageNumber() + " of " + Code.TotalPages()