RS Page breaks by row number and group: Using DENSE_RANK and ROW_NUMBER to create group ids

[highlighter]

I’m a bit green when it comes to the rank functions in T-SQL so this was a nice exercise.

We needed a report to display totals per page in grouped data so this was a bit of a head scratcher.. Reporting Services doesn’t make it easy to do that! I tried a number of methods then came up with this solution. Basically, due to the type of data we’re displaying

we knew we only ever need to show 6 rows on one page unless we hit one of the other things we’re grouping on where we reset the group.. I want to split the result set into a series of named groups

which will allow me to do my group by totals in the report.

Make sense?

No?

Then read on..

Which gives me:

groupid rnum group1reference group2value sort_date
1 1 2015 Friday 7th December 2012 07/12/2012 09:00:00
1 2 2015 Friday 7th December 2012 07/12/2012 11:00:00
1 3 2015 Friday 7th December 2012 07/12/2012 13:00:00
1 4 2015 Friday 7th December 2012 07/12/2012 15:00:00
1 5 2015 Friday 7th December 2012 07/12/2012 17:00:00
1 6 2015 Friday 7th December 2012 07/12/2012 19:00:00
2 7 2015 Friday 7th December 2012 07/12/2012 21:00:00
3 1 2015 Saturday 8th December 2012 08/12/2012 09:00:00
3 2 2015 Saturday 8th December 2012 08/12/2012 11:00:00
3 3 2015 Saturday 8th December 2012 08/12/2012 13:00:00
4 1 2016 Saturday 8th December 2012 08/12/2012 15:00:00
4 2 2016 Saturday 8th December 2012 08/12/2012 17:00:00
4 3 2016 Saturday 8th December 2012 08/12/2012 19:00:00
4 4 2016 Saturday 8th December 2012 08/12/2012 21:00:00
5 1 2017 Monday 6th May 2013 06/05/2013 09:00:00
5 2 2017 Monday 6th May 2013 06/05/2013 11:00:00
5 3 2017 Monday 6th May 2013 06/05/2013 13:00:00
6 1 2017 Tuesday 7th May 2013 07/05/2013 08:00:00
6 2 2017 Tuesday 7th May 2013 07/05/2013 08:00:00
6 3 2017 Tuesday 7th May 2013 07/05/2013 10:00:00
6 4 2017 Tuesday 7th May 2013 07/05/2013 10:00:00
6 5 2017 Tuesday 7th May 2013 07/05/2013 12:00:00
6 6 2017 Tuesday 7th May 2013 07/05/2013 12:00:00
7 7 2017 Tuesday 7th May 2013 07/05/2013 14:00:00
7 8 2017 Tuesday 7th May 2013 07/05/2013 16:00:00
7 9 2017 Tuesday 7th May 2013 07/05/2013 16:00:00
7 10 2017 Tuesday 7th May 2013 07/05/2013 18:00:00
8 1 2018 Tuesday 7th May 2013 07/05/2013 18:00:00
8 2 2018 Tuesday 7th May 2013 07/05/2013 20:00:00
8 3 2018 Tuesday 7th May 2013 07/05/2013 20:00:00

Thanks to ChrisM at SQLServerCentral for help with the dense_rank!

Leave a Reply

Your email address will not be published.