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.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.
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) _
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
Return pagenumber - offset
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.
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
I would suggest 2 solutions to this:
- 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.
- 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?
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
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
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