SQL Server 2005 Reporting Services – RDL Programming Issues

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