More on Reporting Services: Fun with Rectangles

I have been working on this fairly complex report for a client using SQL Server 2000 Reporting Services. I wanted to capture some of the lessons that I have picked up along the way. You never know in my line of work when these lessons can come back to haunt you.

Rectangles Are Your Friend

Reporting Services ("RS") has a relatively small number of "report item" types: textbox, line, table, matrix, rectangle, list, image, sub-report, and chart. The purpose of the rectangle is to hold or contain other report items from the list, including other enclosed or "child" rectangles. A rectangle does take up landscape on the report canvas; it has a top, left, width, and height. It can have a back color, and border. One of the nice things about a rectangle is that the positioning of contained report items is relative to the parent rectangle. This means that the contained report items just "ride along" when the parent rectangle is repositioned.

A rectangle also has a visibility characteristic that allows everything in the rectangle to be displayed or hidden. The visibility can be controlled through an expression. What this means is that I can have a whole set of report items blink in and out of existence (with respect to the displayed universe) on the basis of a single expression.

Collapsing Space Means No Space

You may have seen a mime assuming a posture that makes it appear that the mime is leaning against empty air. RS is not mime-friendly. A mistake that I have made is to layout the report using several rectangles or other report items. I then add an expression to the visibility property that can make one of the earlier rectangles not visible. The problem is that RS not only makes the contents of the report item not visible, it collapses the space taken up by the report item. If the report item takes up an entire line on the report, the line disappears. This type of collapse is almost always a very good thing. The difficulties come in when the report item that disappears is only part of a line. When RS collapses the space, it shifts the report items to the right of the disappearing report item into the collapsed space, even if those report items have top and height property that would place them where you want them to be. This is not goodness.

I am not sure if I think that this is a bug or a feature. And, given the circumstances, it is really not important what I think. RS exhibits this behavior and like the good Microsoft loyalists that we are, we deal with the reality of the situation.

What this means is that if you take something away that is providing positioning support, you must also put something back to replace the positioning support. What I ended up doing was to define two rectangles with identical shapes and positions and complementary expressions for the visibility. When rectangle A was visible, rectangle B was not visible; when rectangle B was visible, rectangle A was not visible. The report items that follow the shared space always had something to lean on so that they did not drift into collapsed space.

Reaching for the Stars

The collapsing space issue is not just for rectangles. It seems to affect any report item. In my case the issue manifested itself with stars. The complex report that I am working on contains data that rates the performance of corporate entities. The best rating is three stars, a good rating is two stars, an OK rating is one star, and an incomplete rating is "Not Rated" and so on. (Even sturdy corporate types need "love".) The "stars" are images and other ratings are text. I used two rectangles to alternately display the star images and the text as appropriate. Rather than create multiple images for "one star", "two stars" and "three stars", I chose to include three separate star image report items, each of which refers to the same star image. I then added appropriate expressions to turn them on and off as needed. The "third star" was only visible when the rating was "three stars". The "second star' was only visible when the rating was "two stars" or "three stars". And so on.

If you have been paying attention, you will see the problem. When the rating is two stars, the third star is no longer visible and the second and first stars shift over to fill the now invisible space. This is cruel: not only does the rated organization not get three stars; they also get a sloppy looking report. The solution is to create a blank image, modify the third star image report item to show a star when the rating applies and a blank image otherwise. The value property of the image report item identifies the name of the image to display. This value can be an expression that returns a string holding the name of the image; in this case, the returned value is either "star" or "blank". Now, at least, the sting of a lesser rating is tempered with the joy of a well-formatted report.