Doug Lane

SQL Server Entertainer

This Stuff Should Be Fun: Introduction

by

Playing with reports are a great way to learn Reporting Services. I use the word “playing” deliberately; working with reports and playing with them are two entirely different things. I spend all day working with reports. When I’m done or I need a break, I don’t want to work. I want to play.

I also really want to expand my knowledge of Reporting Services, especially when it comes to design. What better way to stay engaged than to pick reports I already like but don’t know how to make? I’m recreating some reports I’ve really enjoyed dating all the way back to my childhood.  (If you attended my SQL Saturday #66 session “Make It Fast, Make It Pretty”, you probably know what’s coming.)  I’ve replicated them as best I can using only images and the native capabilities of Reporting Services — i.e., no add-ins.  In the coming weeks, I’ll be presenting the recipes for these reports along with some insight I picked up while making them.  I hope you have as much fun learning from them as I have.

As a preview of coming attractions, I’ll introduce my first project: G.I. Joe file cards, circa 1986.

Original G.I. Joe File Card
Original G.I. Joe File Card
Recreated through BIDS
Recreated through BIDS

Stay tuned!

I’m bringing sexy back [to my reports]

by

Socks + Sandals
Wrong in so many ways

I’m generally ashamed of my wardrobe from 1997-2004.  Socks with sandals, too-short golf shorts.  Oh, it was heinous.  I found the cure to bad style was simply to pay more attention to what looks good on others, and start wearing more of that.  Much like my fashion sense, my sense of style for reports has evolved over the years into something a little more sophisticated.  I take almost as much pride in a report or web page that looks clean as I do one that runs clean.  Through lots trial and error — mostly error, it seems — I’ve found a few schemes that work well.  I want to share the schemes I use, along with the code to implement them.

I like these because with only one hue (or is it tint? See, I told you I’m not a graphic designer) you can use other colors to draw attention to important information, a la Stephen Few.  I prefer greens and blues (I dress the same way — shocking) because they’re unprovocative and people seem to prefer them over warm tones in reports.

Blue

#DEF1FA
Highlight row/column		 #BCD7E4
Header/Footer		 #9AC2D6 #6FA4BF #437C99

Green

#EAF8D2
Highlight row/column		 #D7E4BC
Header/Footer		 #C2D69A #A4BF6F #688038

I rarely use the last darkest two colors in the scheme because black text starts to blend into the background, especially when it’s printed using color laser.  It’s better to use white text on the darkest one.  The lightest three colors are best for header/footer rows and sub-total/total columns in a table.

Note I’m using HTML color codes, not .Net named colors.  I’ll elaborate on that in a separate post some other time, but for now, I’ll just mention it involves Find & Replace in Visual Studio.

The sets of five are also good for illustrating depth or degrees of severity, like a heatmap. Here’s an example where Thursday has a lot of something and Wednesday has a little: 

Heat Map Color Scheme

If you want to plug them into a chart, just add an expression like this one to the Fill attribute in the series properties of the chart:

=IIf(Fields!val.Value < .2, “#DEF1FA”,
IIf(Fields!val.Value < .4, “#BCD7E4”,
IIf(Fields!val.Value < .6, “#9AC2D6”,
IIf(Fields!val.Value < .8, “#6FA4BF”,
IIf(Fields!val.Value >= .8, “#437C99”, “Black”)))))

And there you have it.  Style being a matter of individual taste, these themes don’t appeal to everyone.  That said, of all the themes I’ve come up with so far, these have been the most popular with my customers.  I hope you find them useful too.

How to tell if a report is empty without opening it

by

Report Half Full
I prefer to think of the report as half full.

Once in a while, I’ll run a scheduled report and for whatever reason, the report won’t return any data.  It ran just fine — no errors, no failures — but there’s just no data in the body.  The reports are saved into various folders on the network.  In thumbing through these folders, I can spot the reports that ran with no data quickly.  It’s the ones that have a certain file size.

In my case, the report in question renders at 67 KB if it’s empty.  Dashboards with graphs, sparklines, and other fixed-size objects will show a little variance in size if a section is empty.  Table rows, on the other hand, will have a much greater impact on the file size when they are populated.  The exaggerated file size difference then makes spotting the blank ones much easier.

So, next time you open a report and you find the content to be empty, take note of the file size.  That number will raise a red flag for you whenever you see it and hopefully help you to catch (and correct) it before your users do.

How to keep Report Server awake

by

Server Rack
It's not asleep, it's pining for the fjords.
There are few things more frustrating than running a demo of a new report for your users, telling them how fast it is, then having Report Server deliver your three-second report in thirty-three seconds because the server had to wake up first.  Until Microsoft makes the sleep setting for RS configurable to something other than twenty minutes, the best solution I have heard comes from Steve Wake of the Denver SQL Users Group.

Steve said his company runs a report every fifteen minutes to keep the server at attention.  I didn’t ask what was in the report, but I’m assuming it’s one of two things:

  • The report is blank, so from a code standpoint it can’t fail, and it won’t consume any resources beyond the bare minimum.
  • The report is for monitoring or auditing, and needs to be run every fifteen minutes anyway.

In either case, it’s a simple and practical solution and I couldn’t help but grin when I set it up on my server.