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
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.
creating a greenbar matrix
the pattern used for a matrix is very similar to a table. since the matrix generates column cells dynamically, there is no way to specify a different expression for each column. if i wanted the row header to have an alternating background color, i could use the same technique as the table; toggling the odd/even flag explicitly on the row header textbox. but, if the pivot cell is to be the left-most item with an alternate background color on each row, this becomes more challenging. to work around this limitation, i define an extra row group on the same field expression as the previous group in the row hierarchy. figure 4 shows the group definitions. the two row groups both use the same expression. this will cause the second group header textbox to be repeated with each row. i'm going to hide this cell when i'm done.
figure 4
next, i set the backgroundcolor property using the same expression that i used in the table example. the second row header textbox sets the alternatecolor function to toggle the odd and even rows. since the pivot cell (the textbox at the intersection of the row groups and column group) is repeated with the same background color for every column in a row, the second function argument is set to false.
figure 5
in figure 6, i've reduced the width of this cell and i've also hidden it by setting the visibility/hidden property to true. you actually can't completely eliminate all evidence of a cell but you can make it very narrow. i've set the gridspacing property to .03125 (1/32 of an inch) so i could make this column as narrow as possible.
figure 6
figure 7 shows the end result. the utility cell causes a small gap between the row header and the remaining columns, alternate row colors are applied to the aggregate data cells.
figure 7
this article is the first in a series of 3 articles from paul turley excerpted from the 90 page chapter, "report solution patterns and recipes" from the book professional sql server 2005 reporting services written by paul turley, dave duvarney, james counihan and todd bryant (wrox, 2006, isbn: 0-7645-8497-9). the full chapter contains a deep analysis of successful report projects, including project profiles, success factors, user discussions and scope management. templates are provided to assist requirement gathering and management. the report recipes section of this chapter includes 19 specific examples of advanced report designs that implement custom coding and other techniques taught in previous chapters. this series of articles demonstrates 3 of these examples. the next 2 will be creating a business scorecard and creating sparklines. hitachi consulting has business and it consulting offices throughout the world.
copyright 2006 by wrox publishing, inc. all rights reserved. reproduced here by permission of the publisher.