Click here to Skip to main content
Click here to Skip to main content
Go to top

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

Introduction

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:

=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)

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.

Solution

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.

Solution

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.

Conclusion

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

History

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

License

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

Share

About the Author

Jatin Jatin
Architect
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.

Comments and Discussions

 
GeneralMatrix Reports in Reporting Service PinmemberBallita18-Feb-09 6:42 
GeneralSetting the zoom in Reporting Services PinmemberNi Na1-Dec-08 23:13 
Questionwhat about the total table in the footer? Pinmemberrdxreg12-Feb-08 6:12 
AnswerRe: what about the total table in the footer? Pinmemberpatthiswathi29-Dec-08 5:10 
QuestionCustom Code in Group Footer? PinmemberMattPenner6-Sep-07 12:10 
QuestionVB Pinmembersssbank8-Aug-07 18:59 
GeneralHey Pinmemberrilov5-Aug-07 15:34 
GeneralRe: Hey PinmemberJatin.India6-Aug-07 19:06 
GeneralRe: Hey PinmemberJatin.India20-Aug-07 15:40 

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

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

| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 20 Aug 2007
Article Copyright 2007 by Jatin Jatin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid