none
page number & records number RRS feed

  • Question

  • 1. how to show page number & total page number in report body?

    2. how to show total records number?

    Tuesday, June 20, 2006 9:46 AM

Answers

  • For #1: Create a text box. For the value, enter the following:

    ="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()

    For #2: If you just want to show the number of rows in a table for example, then create a text box, and enter the following:

    =CountRows(Fields!FieldName.Value)

     

    Tuesday, June 20, 2006 4:05 PM
  • Okay, so I have a solution that works for me. I do not know if it will be a fix all for everyone interested, but I would like to share anyway. I was talking to our SQL developer about my issues with using page numbering in my data. In my case, I am building a table of contents. He intrigued me when he asked me if I thought about using my SELECT statements to predict my page numbers off of my row numbers. I can use this, because I found in my main report I had to define x number of rows per page to keep multiple detail lines of my table together. What I did in SQL is this:

    SELECT

    ,...

    , ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

    , ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

    ...

    I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

    Friday, June 30, 2006 1:36 PM

All replies

  • For #1: Create a text box. For the value, enter the following:

    ="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()

    For #2: If you just want to show the number of rows in a table for example, then create a text box, and enter the following:

    =CountRows(Fields!FieldName.Value)

     

    Tuesday, June 20, 2006 4:05 PM
  • thanks for reply, but errors found.

    for #1:

    [rsPageNumberInBody] The Value expression for the textbox ‘textbox1’ refers to the global variable PageNumber or TotalPages.  These global variables can be used only in the page header and page footer.

    for #2:

    [rsInvalidAggregateScope] The Value expression for the textbox ‘textbox2’ has a scope parameter that is not valid for an aggregate function.  The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

    pls advise, thanks

    Wednesday, June 21, 2006 2:19 AM
  • for #1: I can't think of another way to output the page numbers, so you might have to just use the header and footer to handle it.

    for #2: Create a text box and enter the value:  =CountRows("DataSetName")

    This will output the number of rows returned by the dataset.

    Wednesday, June 21, 2006 1:25 PM
  • for #2 is work. thanks.
    Thursday, June 22, 2006 3:08 AM
  • Okay, I found a solution for #1:

    Go to "Report" -> "Report Properties" -> "Code"

    In the Custom Code section, enter the following:

    Public Funtion 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


    Now you will be able to access these functions anywhere in the report (header, body, or footer). So, to output the page number and total pages in a textbox located in the body simply enter this for the value:

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

    • Proposed as answer by Jacob_Zac Sunday, March 10, 2013 6:52 AM
    Monday, June 26, 2006 6:20 PM
  • I cannot get this to work. I get an end of statement expected error for the first line. I copied the code into report, report properties, code tab and change the function names to suit my naming convention. Also, when I try to refer to the functions in a textbox Code.FunctionName does not recognize the function in the custom code section.
    Thursday, June 29, 2006 2:37 PM
  • Okay, I found the error. When I typed in the code for the post, I spelled "Function" incorrectly on the very first line... Just add the missing "c" and you will be good to go. Sorry about that.

    Joel

    Thursday, June 29, 2006 5:47 PM
  • ah, it's okay... I don't know why I didn't notice that. I got the code in without any errors this time. When I test it, it doesn't seem to function as desired.

    = Code.FunctionName()

    I only get the number 1 in all the textboxes that carry the above information.

    Thursday, June 29, 2006 6:19 PM
  • Can you copy and paste your custom code as well as your expression for the textbox?

    Thanks,
    Joel

    Thursday, June 29, 2006 6:28 PM
  • Custom Code

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

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

    in Text Boxes in Tables

    =Code.PageNumber() & " " & Code.TotalPages()

    I'm running VS2k5 & SSRS2k5. My report has two columns, and runs about 200 pages. I have two tables, and I'm trying this in both tables in group headers and detail lines. All I get for all the text boxes is "1 1". I hope this information helps. I know I specified earlier that I changed the function names for our naming conventions, but I decided to go with exactly what you have until I can get it to work, then I'll make any necessary changes.

    Thanks!

    Curtis

    Thursday, June 29, 2006 6:43 PM
  • Hmm, I'm also getting the same results. I think it's safe to say I didn't test this very well. When I first wrote the function, I tested it on a report with only one page and when it outputed "Page 1 of 1", I figured it was good to go. However, after playing around with a report that is 3 pages, it's clear this function is not going to work.

    I'm not really sure why we are only getting the value 1 for both globals. It is clearly accessing these globals because if you change their names it will create an error saying they are not members of "Globals". Anyway since that didn't work, I really can't think of another idea on how to output page # and total pages in the body. If you come up with a solution though, I'd be interested to see it.

    Thanks,
    Joel

    Thursday, June 29, 2006 7:40 PM
  • I'll see if there is anything I can come up with! If anyone else has any ideas or thoughts, feel free to share! I just do not understand why SSRS should not allow us to use pagenumbering in the data. It doesn't seem to make much sense to me.

    Thanks,

    Curtis

    Thursday, June 29, 2006 7:44 PM
  • Okay, so I have a solution that works for me. I do not know if it will be a fix all for everyone interested, but I would like to share anyway. I was talking to our SQL developer about my issues with using page numbering in my data. In my case, I am building a table of contents. He intrigued me when he asked me if I thought about using my SELECT statements to predict my page numbers off of my row numbers. I can use this, because I found in my main report I had to define x number of rows per page to keep multiple detail lines of my table together. What I did in SQL is this:

    SELECT

    ,...

    , ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

    , ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

    ...

    I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.

    Friday, June 30, 2006 1:36 PM
  • Thanks for the post. I was thinking about trying something like that myself, good idea.
    Thursday, July 6, 2006 7:46 PM
  •  Curtis Zeiszler wrote:

    Okay, so I have a solution that works for me. I do not know if it will be a fix all for everyone interested, but I would like to share anyway. I was talking to our SQL developer about my issues with using page numbering in my data. In my case, I am building a table of contents. He intrigued me when he asked me if I thought about using my SELECT statements to predict my page numbers off of my row numbers. I can use this, because I found in my main report I had to define x number of rows per page to keep multiple detail lines of my table together. What I did in SQL is this:

    SELECT

    ,...

    , ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

    , ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

    ...

    I determined my table of contents will always be three pages, and I know that I have fifty rows per page. I have run my 200+ page report and compared random sections in my TOC to my report, and I found it was accurate. If there are any questions, please feel free to ask. I would be more than happy to clarify if it is necessary.



    I couldn't understand your code.I have a problem similar yours.Now I will tell my problem.Could you help me?
    I have a table containing two group and a detail details group.Their names are First group-ProductGroup , Second group-ProductSubgroup and the Details group-ProductName.I only want total page number to use in the body of the report.Could you help me?

    Monday, July 17, 2006 3:25 PM
  • I'll try and explain my code a little better. I'm not sure I understand exactly what you are looking for, but I will make an effort.

    What I am proposing from my answer is that we determine row numbers and page count in a T-SQL SELECT statement we use to pull our information from the database(s).

    How my code works:

    ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC) AS ROWNUMBER

    I am creating a column that contains the row number of the data row. The OVER (...) part specifies the order that I want my records counted. This should be the same as the ORDER BY that would be at or near the end of your SELECT statement.

    ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / 50) + 4 AS PAGENUMBER

    This line of code determines what page number your data is located based on how many data rows per page and which page you are starting on.

    More to the point:

    ((ROW_NUMBER() OVER (ORDER BY P.PRODLINE, P.PRODCLASS, P.PRODDESC)) / [number of lines per page]) + [start page] AS PAGENUMBER

    I needed a start page, because I generated a table of contents before my data. The start page section should be used if you know there are going to be pages before your data starts (cover pages, table of contents, etc.).

    The big picture:

    SELECT

       COLUMN1

       , COLUMN2

       , COLUMN3

       , COLUMN4

       , ROW_NUMBER() OVER (ORDER BY COLUMN3) AS ROWNUMBER

       , ((ROW_NUMBER() OVER (ORDER BY COLUMN3)) / 50) + 1 AS PAGENUMBER

    FROM

       ADATABASE

    ORDER BY

       COLUMN3

    What precedes is me selecting 4 columns from a database. I am sorting the data by column 3 ascending. This causes me to use column 3 in my ROW_NUMBER lines, because if I were to use a different column, my numbers wouldn't match. I have determined that in my situation, I am going to have 50 lines per page, and I am starting my data rows on page 1. This works best if you can make an assumption on your lines per page and your data start page.

    If you are looking for total gross pages in the report, the grouping does not matter. You only need to predict how many data rows you will include on a page and what page your data is going to start on. I believe there is a T-SQL function that will return to you a column with all the rows you have (I stumbled upon it while trying come up with my current solution, but I cannot remember what function I used), and if you divide the number of rows by how many data rows you have per page, you will get a total page count per data row. If you need to know how many pages per group, I'm sure it can be derived from making some changes to what I have laid out, but I haven't had the need to delve into that.

    I also want to be clear that this solution is used where you are selecting the data from the database and two columns (ROWNUMBER and PAGENUMBER) are created. I am using mine in a stored procedure on a server. I call the stored procedure from my report. Whether you use a view, stored procedure, or type out your SELECT(s), you only need to include the two ROW_NUMBER lines and keep all the ORDER BYs the same.

    I hope this makes my code more clear. If you have any more questions, feel free to post, and I'll help. I would like to see what you are doing to get your information, because that is where the key for this to work lies. I hope this is helpful.

    ***EDIT***

    Once you have my code inserted, I believe you can use LAST(column name) in your table in the data row where you are outputting your data. This would be done in SQL Reporting Services in you design. I haven't tested it, but I feel it would be logical to use.

    ***EDIT***

    Thanks,

    Curtis

    Monday, July 17, 2006 4:17 PM
  • How would you accomplish incrementing the page number on a field grouping...like...

    An invoice table that has lines of an invoice that if the invoice has 50 lines per page per invoice number it incremets the pages, but resets to page one when the field Invoice number changes to the next invoice.  Or if the invoice has only 25 lines it set back to page one when the next invoice number comes up.

    Tuesday, November 21, 2006 12:00 AM
  • I am working on exactly this problem and found a solution.

    Group on =ceiling (Rownumber("groupInvoice") / 50)

    Set the group to do a newpage on change and repeat the header  and use the same calculation to show the page number.  To determine the last page do =ceiling(Count("groupInvoice") / 50) {no positive on the Count, but there a calculation should be able to give you the number of lines in the group}.

    Tuesday, November 21, 2006 1:43 PM
  • ="Page " + Globals!PageNumber.ToString() + " of " + Globals!TotalPages.ToString()

    This syntex works for me in SQL server,but when I export this to Excel  in all pages it's displays only   "Page 1 of 1"

    How to fix this

    Thanks

    Masi

    Monday, November 27, 2006 11:34 PM
  • Thanks for Joel's effort.  And I guess I know why always "Page 1 of 1", becase of the rendering order!
    You can get more detail from my blog post

    Jeffrey

    Wednesday, December 6, 2006 11:12 AM
  • for #1:

     

    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()

    Tuesday, October 16, 2007 10:17 AM
  • Curtis you're right in terms of how you can figure out the page number and supply that with your query to your dataset. For details on how to use this for groupings and the different scenarios that apply check out Determine page number in SSRS 2005/2008
    • Proposed as answer by Ahmad Alkilani Thursday, February 18, 2010 6:44 PM
    Thursday, February 18, 2010 6:44 PM
  • Well i m facing the same limitation while working on SSRS 2005. The solution proposed by Mr. Curtis Zeiszler apeal me, but what if i have a variable length column in my select statement? In report designing, i have checked the property "Can increase to accomodate contents". that means if my character of specific field becomes greater than the width set, it will break this line into next one. Which can effect the report formatting as well let assume
    i have 100 lines in my record set and i have set 10 rows per page.
    Row 1 to 10 will calculate its page_number as 1 and its variable field length exceed due to variable field only 5 rows set to page 1.

    what would be your proposed solution in this case?

    Wednesday, February 24, 2010 1:30 PM
  • Hi, guys, please, vote for the feature

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32910019-table-of-contents-in-ssrs

    Thanks!

    Tuesday, February 13, 2018 4:47 PM
  • Hi, guys, please, vote for the feature

    https://feedback.azure.com/forums/908035-sql-server/suggestions/32910019-table-of-contents-in-ssrs

    Thanks!
    Tuesday, February 13, 2018 4:53 PM
  • Hey, this looks promising for what I need to do. My table of contents is in a tablix, at the beginning of the report.  Then there are subsequent tablixes.  Would there be a way to add to the code:

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

    So that I can grab the page number of where each tablix starts?  Then I could put that in a column of the TOC tablix.  Is that possible?

    Tuesday, March 20, 2018 12:55 PM