Displaying page number in body of RDLC

Question

How do I get Page Number in Body Section of RDLC report. Globals!PageNumber can be used only inside either Report Header or Footer. What if I put Row number to my dataset and get the record number. Limiting the number of records per page and do the visibility calculation based on the records number is the best solution so far that I've heard of. Can anyone educate me on this logic? Or is there any other workaround for this?

P.S: Other so-called solution like using Custom code is not giving you the correct page number. It will always show 1.

Answer 1

There isn't really an easier way to get the page number in the body. I think working with the dataset row count is the only reliable way.

What I have here is a short SQL statement to get Project Status information:

SELECT * FROM PROJ_STATUS

So I'll add the row number as a field, and also divide it by the number of records I want per page and add 1 (giving me the page number of each row)

SELECT * , ((DENSE_RANK() OVER(ORDER BY PRS_ID) -1)  / 3 ) +1  AS [CountRow]  FROM PROJ_STATUS

Now in my report I've got a table showing the status names and if they are active or not... I'll also add the page number as a column.

Next put a list in the report and put the table inside it.

Then click the top left square corner on the list and in the properties window set the dataset to the one you are using.

Then right click on the row group in the list and set the grouping to the page number column.

And put page breaks in between instances.

And there you go!

Reason why the -1 for @4Star. See that without the -1 the 3rd row is on the second page.

Answer 2

If your dataset is a row per whatever you want to get page numbers for, then

=RowNumber("DataSet1")

will work.

This is the same as using

row_number() over (order by (select null))

as it gives you an arbitrary ordering for row numbers.