Click here to Skip to main content
13,352,175 members (76,708 online)
Click here to Skip to main content
Add your own
alternative version


23 bookmarked
Posted 5 Aug 2007

SQL Server 2005 Reporting Services – RDL Programming Issues

, 20 Aug 2007
Rate this:
Please Sign up or sign in to vote.
RDL Programming - some issues and their solutions


In the last three months, I had a chance to work with "SQL Server 2005 Reporting Services – RDL Programming." SQL Server Reporting Services currently has many limitations. Due to this, my team members and I faced a lot of issues in our journey to develop a variety of reports for one of our clients. We were to develop more than 20 reports and we had no prior experience in that.

In this article, we'll discuss some of the issues / problems that we faced and their solutions that we came up with.

Straight to Business

#1. Reset Page Number on Group Break

SQL Server Reporting Services provides 2 Global variables: Globals.PageNumber and Globals.TotalPages. You may use these to display page numbers in your "Page Footer." However, suppose you need to "reset" the page number whenever a "group" in your RDL breaks. For example, say you are displaying customer invoices and you want the page number to reset on each new customer. This is such an obvious requirement, but unfortunately there is no native support for this in the reporting services.

Possible Solution

Initially, we were very happy to find a similar solution at Chris Hay's Weblog. In fact, there are some other interesting articles too at his Weblog that are worth reading. His solution really lies in using the "shared" variables. Precisely, add a custom function to set the shared variables and retrieve the group page number.

Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) _
    as Object
    If Not (group = currentgroup)
        offset = pagenumber - 1
        currentgroup = group
    End If
    Return pagenumber - offset
End Function

Use the function in the page header or footer:


But Our Problem was Bigger than This.

We also had to display the total number of pages in the group, along with the current page number, i.e. we were supposed to display "Page 1 of x" where both page number and total pages should be group-specific. Using the above approach, there is no way you can calculate the total number of pages for a "group" in advance, to display on each page.

Our Solution

Unfortunately, there is no such native support currently available for this in Reporting Services. Thus, what we had to do was fix the number of rows of data that could be displayed on a page (say that turns out to be R = 40 rows). Then from the SP that was fetching our data for this RDL, we inserted a column in our dataset that indicated the "total pages required for the group." We then used that column for displaying the total number of pages. If you can take this approach, you may also include a column for the current page number in your dataset in a similar way.

#2. How to Extend a Table 'til the End of the Page?

Let's say you are displaying a telephone invoice for a customer in your report and the requirement is that even if the invoice data ends much before the page ends, the border of the "invoice table" should still stretch 'til the end of the page. Also, after that, say you need to display the customer "invoice total," also in a separate table.


I would suggest 2 solutions to this:

  1. If you can take a similar approach as in problem #1 in your report -- i.e. fixing the number of rows that can display on your page -- then you may insert some empty rows in your dataset that would fill the page. Thus, if your page gets filled with 40 rows (keeping the total table separate) and for a customer you get 22 rows, you may fill 40 – 22 = 18 empty rows in your dataset (from your SP itself) to fill the page. Then you may just apply the table border to your dataset in your RDL.
  2. Another approach could be to use a border for the Body of your report instead of using the table border. Then you may have the total table as a part of the "Page Footer" instead.

#3. How Can I Have a Dynamic Height Page Header and Page Footer?

Suppose your Page Footer displays 6 fields of customer address (cust_name, cust_addr1, cust_addr2, cust_addr3, city, state, zip) in 6 separate lines. The requirement is that if the customer's address is just 4 lines in length -- i.e. say cust_addr2 and cust_addr3 are empty -- you need to suppress the 2 empty lines. In that case, your page footer also shortens in height, giving more space to the report body.


Please note that there is no such native support in RDLs. You must keep the height of your page footer fixed. The only option would be to exclude the Page Header and Footer from your report and use a Group Header and Group Footer instead, having "Repeat Header/ Footer on each Page" set to true.


I will continue adding other problems and their solutions to this article soon. So watch out!!


  • 20 August, 2007 -- First update
  • 5 August 2007 -- Original version posted


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Jatin Jatin
United States United States
12+ years of IT experience with proven expertise in the areas of Technical Architecture and Design for Microsoft .Net based applications using C#, WCF, BizTalk, Silverlight, REST, MVVM, ASP .Net MVC, JSON, AJAX, SQL Server and SSRS.

You may also be interested in...


Comments and Discussions

GeneralMatrix Reports in Reporting Service Pin
Ballita18-Feb-09 7:42
memberBallita18-Feb-09 7:42 
GeneralSetting the zoom in Reporting Services Pin
Ni Na2-Dec-08 0:13
memberNi Na2-Dec-08 0:13 
GeneralReg. Chart in SSRS Pin
T. Ravindran12-Aug-08 6:13
memberT. Ravindran12-Aug-08 6:13 
Questionwhat about the total table in the footer? Pin
rdxreg12-Feb-08 7:12
memberrdxreg12-Feb-08 7:12 
AnswerRe: what about the total table in the footer? Pin
patthiswathi29-Dec-08 6:10
memberpatthiswathi29-Dec-08 6:10 
QuestionCustom Code in Group Footer? Pin
MattPenner6-Sep-07 13:10
memberMattPenner6-Sep-07 13:10 
QuestionVB Pin
sssbank8-Aug-07 19:59
membersssbank8-Aug-07 19:59 
GeneralHey Pin
rilov5-Aug-07 16:34
memberrilov5-Aug-07 16:34 
GeneralRe: Hey Pin
Jatin.India6-Aug-07 20:06
memberJatin.India6-Aug-07 20:06 
GeneralRe: Hey Pin
Jatin.India20-Aug-07 16:40
memberJatin.India20-Aug-07 16:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180111.1 | Last Updated 20 Aug 2007
Article Copyright 2007 by Jatin Jatin
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid