As I discussed last week in part 1 of this article, color can be used to highlight common elements across groups. As promised, here's the necessary code to achieve this effect in Reporting Services 2005 (or you can download the finished report here): 1. Create a new report, and add a query. I've used AdventureWorks and a simple query that returns employees and years of service: --get employees by Department, with years of service from AdventureWorks SELECT TOP 50 D.[Name] AS [DepartmentName], EMP.[EmployeeId], C.[FirstName] + ' ' + C.[LastName] AS [EmployeeName], ...

Often a report must be able to be grouped by one or more categories, depending on the user's requirements. Sometimes, across groups, it's helpful to highlight common elements. Imagine that the illustration below lists employees by department (the main grouping), with the last column being a common element to all employees: It's difficult to identify common elements across the main group for the last column in figure 1, even though values like "AAAAA" and "ZZZZZ" are repeated. In fact, it's not possible to draw any conclusions about the last column (like how many employees share the common element "ZZZZZ") without physically scanning...

Microsoft's David Lean has a great series of posts on "heatmapping" and other color-based conditional formatting in Reporting Services tables and charts (link is to part 4 of the series) (via Robert Bruckner).   Particularly useful are Dave's algorithms that work on colors and shades and conversion to hex values; part 2 of the series has how to change hue, while part 3 details how to change saturation. And, Dave packs as much useful content into his tips and asides as his main content! Tags: reporting services, sql server, color

Although it's easy enough to paste code samples into a PRE tag in a blog post, I reckon it's worth using one of the many tools available for formatting or "prettifying" code so the code is more readable - as long as it's not too much effort. My number one criteria for any tool that "prettifies" code to HTML for posting to a blog is that the tool is free. Secondly, that it's easy to use - I can do without hundreds of options. As for the output HTML, my current preference is for inline styles as it makes posting code...

In Reporting Services, the syntax for changing the text color of a table cell, say to red if the value is negative, is to set the "Color" property to this expression: =IIf(Me.Value < 0, "Red", "Black") Another useful formatting tool is changing the background color of a cell depending on the cell's value, perhaps to achieve a "heatmap" effect. I find it's far better to do this using code than the macro-type language I used above. Setting the background color in code can be achived by putting an expression like the following into the "BackgroundColor" property, replacing the section in square brackets with...