The Analysis Services team has been working overtime lately to provide added community support for the traditional UDM-based OLAP engine (not that any of our friends in Building 35 have actually ever worked as little as a 40 hour week during their tenure at Microsoft!) In light of the team’s focus on project Gemini (now known as PowerPivot and the forthcoming BI Semantic Model in SQL Server Denali) over the past couple of years, it’s very encouraging to see their continued commitment to this staple analytic data platform. Last year they announced the Maestros enterprise-scale training and certification program with the second round of sessions scheduled this month in Redmond and Madrid.
The latest deliverable is the SQL Server 2008 R2 Analysis Services Operations Guide, a very comprehensive, 108-page document that focuses on operational support for Analysis Services database servers and solution environments. This guide was written by Thomas Kejser, John Sirmon, and Denny Lee from the SQL Server Customer Advisory Team (SQLCAT); with contributions from 26 product team members and leading independent industry experts; which include Alejandro Leguizamo and Alberto Ferrari from SolidQ. I attended an excellent session at TechEd 2011in Atlanta last month by Adam Jorgensen on large-scale SSAS operational guidance where he made reference to the forthcoming operational guide.
This article is based on my session at TechEd on May 18th, 2011
Report styles vary depending on the needs of the consumer and purpose of the report. With so many capabilities in current reporting toolsets, report designers are often prone to add graphics, color and “chrome” wherever they can. This article presents some ideas, concepts and guidance for the visual aspects of report design.
Although the statistical claims vary greatly from one study or observation to another, a consensus remains that the majority of the population are visual thinkers. Even if the information isn’t specifically graphical in nature, visual thinkers often associate concepts and facts with some sort of imagery. It stands to reason that if a large portion of the population processes information as imagery, as report authors it may be more effective to communicate in the same manner. Is this the best approach in all reporting scenarios?
While working on an analytical reporting project for a division of Boeing, I learned an important lesson. The client represented a business unit that produces aircraft and weaponry for the United States Air Force. The users were technically adept and either accountants or engineers who managed very complex projects dealing with highly-technical components. We were working with Microsoft Consulting Services to propose a replacement for an outdated “green screen” reporting system. As I prototyped replacements for their static, columnar reports, I made it a point to use graphics, color and thematic styles. The client immediately disapproved and pushed back on the report designs. They told us that “serious reports aren’t pretty.” I was reminded that it was important to understand the perspective of the audience and their needs before moving to design. This project was an exception to the rule where the only acceptable medium for a purely analytical reporting solution was a collection of non-graphical reports.
Principles of Report Design
Most all design projects include a balance of three basic principles.
There should be no argument that any business report must have business value and should deliver accurate results that answer key business questions. A seasoned report designer understands the need to ask thorough questions and document specific functional business requirements, which ultimately become report solution deliverables. In formal projects, this is usually the job of the Business Analyst who possesses functional business skills. The report solution developer possesses technical development skills that are used to translate the functional design and business requirements into reports that offer features that enable users to navigate, view, print; drill-up, down, through and otherwise deliver information in some creative fashion. If reports fit the business requirements and function in such a way that users can get access to the data they need, what does artistic form have to do with reporting or solution design – and is it really important?
Styling & Color
Styling and image are more important than many technical experts realize. The role of Report Designer is often assumed by seasoned programmers who have little aptitude or concern for such things. On the other hand, some designers; regardless of their aptitude or skill set are easily prone to obsessive concern to get the design “just right”. Many of us have invested many long hours tweaking properties and details at the last minute before delivering a report, dashboard or application form. Needless to say that some of us have minds that are just wired to think visually while others are not.
Aesthetic form and styling are important elements of design and the degree of importance depends greatly on the audience and business goals of the solution. This is often a delicate balancing act so many report designers simply play it safe by duplicating common designs and using standard palettes and template styles.
Establish standards before report design
Nearly every organization has a standard color palette used to define their brand. Many companies enlist marketing firms and professional graphic designers to help them create a marketing image with standard colors, fonts and graphics. In a competitive marketplace, many organizations have re-branded to modernize their image and help customers easily recognize their offering. This effort often follows decades of reorganizations, acquisitions and market redirection. For many, this seemingly simple effort to present an easy-to-recognize company image can be critical to their survival in the marketplace. Designing application user interfaces and reports aligned to these standards may also be critically important but it’s not enough to just change the background and border colors to match corporate standard colors and slap the logo into report title heading. There’s a lot of art and science behind color selection and figuring out “what goes with what”. Nearly all recognizable companies enlist the services of well-paid experts to craft their brand image.
I recently spent an evening with my wife and girls at a local home improvement store helping them decide on new paint color schemes for rooms in our home. I honestly didn’t have much of an opinion to offer but I did learn that “Aqua Breeze” goes with “Hallowed Hush” when using “Ash White” for the trim. I also learned that “Blackberry Harvest” is compatible with “Raspberry Mousse” but cannot be combined with “Glacier Lake” or “Dragon Fire”. I read that “warm” colors make the room look larger – or smaller… I can’t remember. I’m happy to leave color selection to experts like Martha Stewart. If you are color inept like I am, you may also find a site called ColourLovers.com helpful. Among other things, this is a forum for people who like to mix colors and showcase their coordinated palette choices, as you can see in the following samples.
Aside from thematic styling; color can be an effective way to differentiate values in the context of a report visual; like a chart, graph, gauge or grouped table. It can also be a means to call out exceptions and alerts. The balance of these elements is the essence of an effective report design.
Different styles of reports serve different purposes. For example, operational reports are often used to deliver transactional details in textual, columnar format. By contrast, an analytical dashboard is intended to deliver “the big picture” of the business by surfacing aggregate values and exceptions so business leaders can become immediately aware of trends and concerns so they can take appropriate action. Where necessary, users should be able to drill-down, beyond surface metrics to explore details and understand the cause for alert conditions.
Please Don’t Scream
We live and work in a world filled with increasing noise and clutter. Take a quick look at the following picture and ask yourself what you see. I see a lot of people trying to get my attention by yelling just a little bit louder than their neighbor. Unfortunately this is the same approach used by many report designers to draw attention to important details.
Rather than yelling louder, try reducing the noise so the important message can be delivered without giving your users a headache. Bring important data to the surface and drop less-important elements to the background. In particular, default borders, backgrounds, shadows and 3-D style effects are common place and should be reduced or eliminated if they compete for screen space and the user’s attention.
I’ve gone back and forth in my attempt to use background colors on dashboards and there isn’t one design that works for all. Most professional web sites use white backgrounds simply because all text and images work well against it. However, a school of thought for dashboards that will be used for long periods of time is to use black or a dark background color so the user isn’t forced to process all the bright white background as they look at it.
The Five Second Test
As a rule, use color, shapes and contrasting shades to surface the most important information. For analytic dashboards, use the five-second test to check the effectiveness of the design. Prepare to display the dashboard report, ask the use to look at it for five seconds and then remove it from display. Ask the user to describe the information they saw using only the five-second snapshot they took in their mind. If they can describe the most important information the report was intended to convey, then the design is a job well done. If they remember background colors, borders, lines and other styling elements before they got to the data; you may have some redesign work to do.
The matrix report shown below may contain a lot of useful information in an operational reporting scenario but it fails the five second test.
The sparkline report in the following figure, which might be only a small region of a larger executive dashboard, provides useful trend information at a glance. A click-through feature in this report could enable the user to view more detail and expand on the context of the trend to gain more insight.
Use subtle colors for styling
Stephen Few is well-known for his writing and expertise on this subject. In his book “Information Dashboard Design”, he states that by using subtle variations of color shades between visual data points, the consumer can easily discern different values and thresholds without unnecessary clutter. People can generally recognize 16 different shades of the same color. This leaves a lot of room for contrast between, let’s say, five to eight different shades of the same color. To obtain different “shades” or levels of saturation for the same color, equal proportions of red, green and blue are mixed in any quantity to produce a darker or lighter shade of the same color. The convenient thing about this technique is that it translates well to gray scale. The images below show how shades of the color #A0C5DB convert to gray scale to accommodate black and white printing or color blind users.
Use bold colors for alerts & exceptions
The use of heavy borders, solid black and bold colors – if exceptional – will draw the user’s eye and rise to the surface as important information. This is an appropriate theme for Key Performance Indicator (KPI) alerts, traditionally displayed in red, yellow and green. In most cultures, users relate these colors to traffic signals where green means “everything’s OK”, yellow means “things may be heading in the wrong direction” and red means “stop and take notice – something is wrong here”.
Combine Colors and Shapes
The traffic signal metaphor works well for KPI states as long as the reader is able to discern colors. This might be a challenge if a report were printed in black and white or if the user were color blind. Compare the green, yellow and red indicators in the following example with the gray scale versions in the graphic below.
By combining color with shapes, the problem is resolved as long as we inform the user that icons of a certain shape consistently represent a specific KPI state. For example, the round green, triangular yellow and diamond red shapes in the following example can be discerned by users who understand that circles are good news and diamonds are bad.
Putting It to Use
Designing a report in SQL Server Reporting Services can be easy of you use default styling elements. However, Reporting Services has little support for thematic styles and style sheets to coordinate colors among different design elements. Some day we may have the ability to use style sheets in Reporting Services but until then, you have to do some manual work to design a well-crafted report with consistent styling. I’ve seen many different techniques used to apply style sheet-like themes via information stored in database tables and property expressions. In my opinion, this is a lot of work that may save time and energy on large-scale projects but may be overkill in most cases. My approach is a simple custom function that allows you return different shades of a single color to different report elements and properties in a report. The following code can be stored in a report definition, to be used as a template for new reports; or in a custom .NET assembly referenced by all the reports in a business solution. As an external assembly, the code may be written in C# but as an embedded function, it must be VB.NET, as shown in the example (line continuations are used to format code for the printed page).
‘ Creates colors in different shades within the same color palette, ‘ mixing each RGB value in the same proportion in percentage to either ‘ black or white color scale. ‘ Paul Turley 5/19/2011 ‘ ‘ Enter 3 arguments: ‘ Comma-separated string for the RGB value of the color as "RRR,GGG,BBB" ‘ or hex color as #RRGGBB ‘ Percentage as a whole integer (0 to 100) ‘ "Black" or "White" to indicate the color scale range. ‘ 0 percent will return either black or white.
Function RGBToHexPalette(ByVal PaletteRGB As String, ByVal PalettePct As _ Integer, ByVal PaletteScale As String) As String
Dim sNumString As String() Dim InR As Integer, InG As Integer, InB As Integer Dim OutR As Byte, OutG As Byte, OutB As Byte
If PaletteRGB.SubString(0, 1) = "#" Then InR = CInt("&H" & PaletteRGB.SubString(1, 2)) InG = CInt("&H" & PaletteRGB.SubString(3, 2)) InB = CInt("&H" & PaletteRGB.SubString(5, 2)) Else sNumString = Split(PaletteRGB, ",") InR = CInt(sNumString(0)) InG = CInt(sNumString(1)) InB = CInt(sNumString(2)) End If
A base color may be passed as either a comma-separated string of RGB values or as a standard hexadecimal color value. For example, pure Magenta is expressed as “255,0,255” or “#FF00FF”. The second argument is a percentage of color saturation between that color and either black or white, indicated in the third argument as values “Black” or “White”. If the function were called with the value “255,128,255” or “#FF80FF” (a light Pink Magenta) with “50” for the second argument and “Black” for the third, a dark version of the color value would be returned. The saturation level of that color is half-way between the base color and pure black.
Here’s a simple example of the function applied to several report element background and foreground color properties. I’ve used the SolidQ corporate standard colors #A0C5DB and #CBDFEC to create various shades. The example below shows the Color property of a line chart series color set using the RGBToHexPalette function. In this case the chart line will be a shade half way between the base color and white.
The final report applies various shades of these two colors to several elements; including the report header, table header, group header and page footer background and text. Custom colors are also applied to all of the field text, chart lines and bullet graph ranges and pointers. All of the colors in the following report example are set from expressions using the RGBToHexPalette function based on one of the two base colors mentioned before.
Too Much or Too Little?
Graphical report design is a balancing act between the simplicity of no-frills design and the extremities of over-done graphics and colors. This balance changes for reports of different styles and purposes. Advertisements and billboards are bold and artistic in an effort to grab the viewer’s attention to make an impression for a brief moment. Reports and user interfaces that a business user will utilize day in and day out should put fit and function well above form to serve the need without distraction and unnecessary clutter.
What’s in a Metaphor?
Graphical report elements can mimic tangible measuring instruments and devices in the real world. At times, users relate to these metaphors because they seem familiar to their experience with real devices. However, these components are often chosen purely based on their “cool” factor and can be a very ineffective use of screen real estate. Reconsider using glitzy report items that show more fluff than substance. Many of the industry experts, like Tufte and Few, argue that there is no place for such glamorous visual symbolism in reports and dashboards. I think there’s a time and place for everything and there are exceptional situations where these may be an appropriate choice. Consider whether users will find them of value after they have been using the same report after several initial uses.
Three dimensional charts are usually only effective when designed with the data points that will be displayed in the final delivery. As an advertising or presentation medium, this may work but when the data changes, they can quickly become useless. As a case in point, consider the chart on the right, below. What were the sales totals for France, UK, Australia and Germany for 2001 and 2002?
In a clustered bar chart like this one, if the bars in any row are larger than those in front, they are hidden from view and the data become unreadable.
There is much to be considered when designing visual reports. Above all, be mindful of the business value offered to the user. If visual elements, colors, styling features and graphics support these objectives then the design may be sound. Otherwise, too much visual clutter can distract the user from the core message and the most important information on the report or dashboard. In summary, consider the following guidelines as a checklist for visual report design:
Use screen space effectively, leave white space to make the content readable
Reduce the noise – bring the most important information to the surface
Keep it simple but don’t insult the user’s intelligence by over-simplifying