EASkills Enterprise Architecture Skills

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
4. You have a dynamic range of columns and/or rows and you need to repeat rows when the page cannot accommodate for all  your columns. When your columns move to the next printable page, you want your rows to show too. I’ll tackle this in another article.

The solution:

Note: You can download the code for the article from the attachments at the end of the article

I’ll give a quick overview of the ranking functions for clarity, for a more detailed dive check out my article on Ranking Functions.

If you don’t need the review, skip to the good part:

• Row_Numer() OVER (ORDER BY x) : Returns the row number of the result set based on the order provided in the order by clause.

• Rank() OVER (ORDER BY x) : Similar to Row_Number() only Rank determines the position, or lack for a better word, ranking of each row based on the Order By clause. Rank is usually used with the Partition clause to cluster your result sets. Rank also skips numbers, if 2 or more records tie in value, they will receive the same rank. The following rank would be 1+ the total number of records in the same partition so for example (1, 2, 2, 2, 5, 6)

• Dense_Rank() OVER (ORDER BY x) : Same as Rank() only guarantees consecutive integers (No skipping) (1, 2, 2, 2, 2, 3). If a tie occurs, it will sort arbitrarily (based on the execution plan and indexes used) and continue.

• NTile()  OVER (ORDER BY x) : Used to distribute the rows in an ordered partition into x number of groups. Each row receives the group number it belongs to.

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:

employee:

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.

More Examples:

Reporting Services Paging By Group

Let’s say you wanted to number pages and reset page numbering for each group of data you have. You also want to display the total number of pages as in Page x of y per group. This example was inspired by this post
http://darkthread.blogspot.com/2006/12/kb-reporting-service-paging-by-group.html

Assuming you fall under scenario #1 where you know the number of records per page (2 in our example)

• We first need to figure out the total number of pages per group (count of records per group/records per page)

SELECT
 group_name
 ,total_number_of_pages_per_group = CEILING(COUNT (*) / @rows_per_page)
 FROM
 employee
 GROUP BY
 group_name

• We then use this information to join it with our original query. I’m using a CTE (Common table expression) for simplicity but you can just as easily have made this an inner query

DECLARE @rows_per_page INT
SET @rows_per_page = 2

;WITH total_pages -- CTE
AS
(
 SELECT
 group_name
 ,total_number_of_pages_per_group = CEILING(COUNT (*) / @rows_per_page)
 FROM
 employee
 GROUP BY
 group_name
)
SELECT
 employee.name
 ,employee.group_name
 ,page = (Row_Number() OVER (PARTITION BY employee.group_name ORDER BY employee.sales_amount ) / @rows_per_page) + 1 -- page x
 ,total_pages.total_number_of_pages_per_group -- of y
FROM
 employee
INNER JOIN -- Join to get page x of y
 total_pages ON total_pages.group_name = employee.group_name

The results

Note: You can download the code for the article from the attachments