On a recent project, I had to figure-out a way to dynamically display icons to represent that status of projects that were being reported on using Microsoft SQL Server 2000 Reporting Services.  Based off of information in the report’s data, I needed the report to show a different icon for projects that were either behind schedule (value = -1), on schedule (value = 0), or ahead of schedule (value = 1).  I initially thought this would be an easy task, but after spending nearly a whole day researching and testing different methods for doing this, I found it was not so easy!

First of all, one must understand that an image that looks good on a monitor will not print very clearly.  Most monitors have a resolution in the range of 72 to 96 dpi (so I’ve been told), where as an image should be at least 150 dpi (dots per inch) in order to print with any clarity. Vector images that have ‘hard edges’ like the circle-shaped icons I had chosen definitely need to be saved at a resolution higher than screen resolution.   Higher resolution images look very blurry and pixilated when displayed on-screen but should print very nicely.  The end-user must be made aware that just because the report looks terrible on-screen doesn’t mean it will print that way!

Knowing this, I recommend that if your goal is to have crisp graphics when the report is printed, start by creating higher resolution graphics, most likely a JPEG in the 300 dpi range.  If your reports will only be viewed on-screen, create lower resolution images; I found that GIFs are fine for that.  Stay away from using PNGs because the Reporting Services PDF generating doesn’t seem to like them.

I opted to save my status icons as 300 dpi JPEGs after creatig them in Adobe Illustrator.   I also found that the icons should be saved with dimensions (width and height) significantly larger than the desired print dimensions.  When I tried to create icons that were as small as I thought they needed to be, the end result was that they didn’t look good on screen or in print.  Go figure.  The image dimensions will be scaled down in the image control's properties in the report.

When accessing images in SQL Reporting Services, you have several options. By dragging an image control from the Visual Studio Toolbox, you’ll be presented with this dialog:


Figure 1: The Image Wizard dialog

Although I would’ve preferred that my dynamic icons be embedded into the report, I couldn’t figure-out a way to dynamically display embedded images so I opted for using images hosted on a web server.  If you know of a way to dynamically display embedded images, I’m all ears!

Because the Report Manager of SQL Server Report Services is a web site, I opted to create an images folder in IIS to host any images I might use.  I figure that if the person viewing the report can see the reports on the report web server, he or she will also have the ability to see the images in the report.  Make sure you test this before deploying your reports to a live environment.

To do the actual ‘dirty work’ of determining which image to display, I had to resort to using a combination of a custom code function and a custom expression in the image control's Value property.  First off, here is the code I used:

Function ShowStatusImage(value as Object) As String
      Dim strURL as String
      strURL = "http://<Server Name>/images/"

      Dim strImg as String
      Select Case value
            Case Nothing
                  strImg = "iconschedule.jpg"
            Case -1
                  strImg = "iconschedulebehind.jpg"
            Case 0
                  strImg = "iconscheduleon.jpg"
            Case 1
                  strImg = "iconscheduleahead.jpg"
      End Select
      Return strURL & strImg
End Function

This method must be placed in the report's code section which can be accessed by selecting Report -> Report Properties -> Code (tab) in the VS.NET designer:


Figure 2: The Report Properties window showing the dynamic image function

Note that it is not a good idea to use ‘localhost’ in the image URL as shown in the image above.  Instead, use the PC name or IP address so you can be assured that other people will be able to view the images from any where on your network.

So, how do you use this ShowStatusImage function?  That part is easy.  In the table cell where you want the image to be displayed, drag an image control from the Toolbox into a cell in the report.  In the Data : Source field, select External.  In the Data : Value field, enter the following:

=Code.ShowStatusImage(Fields!<YourDynamicImageField>.Value)

Obviously, you will have to insert whatever data field value you want to use as the input parameter of the ShowStatusImage function which will determine which image is shown. Modify the select statement in the function accordingly. The field I used obviously returned either no value, -1, 0, or 1.  The figure below shows the properties I set for each dynamic image:


Figure 2: Dynamic image field properties

Note that the Layout : Size property relates to the size of the report field that the image control is in, not the size of the image control itself.  In order to keep my icons from stretching to the size of the field they were in, I had to set the Layout : Sizing property to FitProportional. I also found that it was necessary to put some padding all the way around the field which reduced the size of the image slightly and seemed to keep the edges of the image from being cropped.  My icons are circle shaped, and without the padding, the circles kept ending-up with flat sides.

I opted to select no Data : MIMEtype as the images were not being served by the report itself.

Below is a screen shot of how my dynamic image fields look in the Layout tab of the VS.NET designer.  Note the icon legend at the top of the report:


Figure 3: Layout view of the report

And here a small snippet of the dynamic images in the report in Preview mode:


Figure 4: Preview of the report

I know you’re thinking, “Those status icons look like crap! They’re all pixilated and bumpy!” Yes, they do look terrible on-screen and look even worse when you try to export the report as a PDF.  However, they look great on paper, which was my goal.

An Alternate Approach (That Didn’t Work)

For those of you who know your fonts, you might notice that the icons I’ve chosen look an awful lot like some of the characters in the Wingding font family… and that’s because they are *inspired* by the Wingding fonts :-)  Instead of using actual images as status icons, I tried using various icon looking fonts instead.  The idea seemed great to me because I knew that fonts would look great on screen and print well.  There was one problem with this plan, I knew that people would be generating PDFs from my reports and Reporting Services does allow for embedding fonts into PDFs :'(  As a result, I could print and view the reports directly from Report Manager and the icons looked great, but when I exported to PDF, I ended-up with some other crazy font symbols.

If you know of a better way to display dynamic images than the way I demonstrated here, please let me know!

Although I have to tinker with SQL, I’m no DBA.  Upon doing some research for an up-coming project, I discovered the Templates folder in Microsoft SQL Server 2000’s Query Analyzer.  However, there was a problem… even though I installed the templates, they didn’t show-up in the Query Analyzer Object Browser L

See all the pretty + boxes next to each folder in the image below (indicating that each folder contains templates)?  I didn’t have any of those… only the folders were appearing.

After racking-my-brain to figure-out what search words to use to find help on this problem, I found the problem: Query Analyzer won’t display template query files that are compressed with NTFS Compression.

To fix this, I had to turn-off NTFS Compression on each .tsql file.  To turn-off NTFS Compression:

• To open My Documents, double-click the My Documents icon on your desktop.
• You can also right-click a file or folder and then click Properties.
• To change properties for archiving, indexing, compression, or encryption on NTFS drives, right-click the file or folder, click Properties, click Advanced, and then select the options you want to change.