CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Report Recipes
As we have endeavored to solve various business problems, we’ve learned to do some interesting things with Reporting Services. On consulting engagements, I often find myself in front of a client who is asking questions like “can you do this or that?” Almost inevitably, the answer is “yes,” but the question becomes what the best method would be to meet the requirement. With a little outside-the-box thinking, a lot of interesting things are possible. This may involve some custom programming, embedding report items or using customer application components in concert with Reporting Services.
In the Report Recipes section of the book Professional SQL Server 2005 Reporting Services, I’ve compiled a description of reporting challenges and solutions we’ve encountered, developing reports for our clients. For each “solution recipe,” I provide a brief list of skills, techniques, and resources needed to apply the report feature. This should give you a good idea about how prepared you may be to use the techniques based on your skill set and the level of complexity. Some of these are easy to duplicate and others require more advanced skills, which may include Transact-SQL and Visual Basic programming. These are not intended to be exercises or step-by-step instructions. I have made a point to provide enough information to demonstrate the concepts and techniques. However, to implement these solutions you will need to apply the skills you learned in the previous chapters of Professional SQL Server 2005 Reporting Services.
Greenbar Reports
Once-upon-a-time, most reports were printed on special continuous-feed paper. This paper is fan folded, with a perforation between each page, making it stackable in the input and output printer bins. The long scroll of pages has pin-feed holes on each side to feed it through and align each row with the mechanical print head. One of the common characteristics of this paper is that it has pre-printed green bars for every-other row data. In more modern reports, this format remains popular to help readers visually separate each row of printed information. This typically involves using a light pastel background color for alternating table rows.
What you’ll need:
- A Visual Basic function
- Expressions used to call the function on the BackgroundColor property of row items
I’ve seen a few different techniques used to implement this feature and they all require complex expressions or some use of Visual Basic programming. Fortunately, this isn’t hard to do, even if you’re new to VB programming. This technique involves using a VB function to return a different color for odd and even rows. Report items are rendered from top to bottom and then from left to right — like the carriage of a typewriter (for the younger generation, a typewriter is sort of like a computer with moving parts.) This means that custom code procedures and expressions associated with report item properties will always be executed in this order. In our solution, the start of a new row is indicated by passing a toggle flag when the function is called in the left-most column textbox. The following Visual Basic code begins with a class module-level variable used to hold the odd-or-even row indicator between calls. As you can see, the bOddRow variable value is toggled between True and False on each call.
Private bOddRow As Boolean
'*******************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*******************************************************************
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function
The program code is entered on the Code tab of the Report Properties dialog. To access this window, choose Report Properties from the Report menu while using the report designer’s Layout tab. This is shown in Figure 1. After entering or making modifications to code, click the OK button to update the report definition.
Figure 1
For the BackgroundColor property of the first textbox (in the left-most column of the row,) enter the following expression to call the custom code function:
=Code.AlternateColor("AliceBlue", "White", True)
The first parameter is the name of the background color for odd-numbered rows. The second is the background color for even-numbered rows. These two values may be the name of any standard web color (available for the color drop-down list in the designer.) These may also be any one of about 16 million Pantone colors expressed in web-style hexadecimal format (i.e. #FF8000 for orange and #9932CD for dark orchid.)
Creating a Greenbar Table
The first example I’ll demonstrate uses a table with a single detail row. Every textbox in the row contains and expression on the BackgroundColor property. This expression calls the AlternateColor custom function that returns the name of the color for this property. As you see in Figure 2 , the expression for left-most column passes the value True to toggle the odd/even row. For all other columns, the third argument value is False.
Figure 2
Figure 3 shows the report in preview. The AliceBlue color I chose for odd rows is subtle. Any combination of standard color names or hexadecimal values can be used. For example, the hex value #FF0000 is equivalent to the color Red.
