Chapter 8 – Graphical Report Design

This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports.  Each of the posts is a condensed version of the material covered in a corresponding chapter from the book.  The goal for this posts is to provide useful and meaningful information you can use.  For more comprehensive details, I refer readers to the rather lengthy book itself.  Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.

• Understanding visual design principles and the fashion of visualization
• Understanding chart types and design approaches
• Getting to know the anatomy of a chart
• Creating a multi-series chart
• Using multiple chart areas
• Learning useful properties and settings

By some estimates, 60 to 70 percent of the population are visual thinkers. That means even when consuming numbers and information presented in text, most people perceive a visual representation of the same information in their minds. We have grown accustomed to data displayed in visual form. In fact, we expect important data to be visualized, especially when the presentation communicates comparisons and trends.

When used correctly, a chart more effectively answers questions and empowers consumers to take action on a set of data rather than a grid full of numbers. Conversely, a table filled with details may provide necessary context and balance with the completeness of a detail table.

A financial controller or accountant will not balance the books to a chart, but a chart may be a great way for the CEO to see the improved profitability trend based on the same details as the balance sheet.

This chapter begins with the guiding principles of visual report design and discusses how charts are used in analytical reporting. Then we examine the different chart types offered in Reporting Services, and criteria to choose the correct type of chart for different business purposes and data scenarios. The essential components of the most useful and popular charts are explained in simple terms, and then we’ll take a look at some of the many detailed properties and features used to create more advanced chart reports. The two exercises for this charter include a time-series chart using a line chart and column chart. You will create a multi-series chart with two separate vertical axis scales and then move to a multi-area chart with synchronized horizontal axis.

VISUAL DESIGN PRINCIPLES

Visual reports are often referred to as analytic reports or dashboard components. Indeed, the term “dashboard” is used very loosely in different products by different vendors to describe anything from a simple gauge graphic to a huge screen full of visual widgets. So, what, exactly, is the difference between a “visual report” and a “dashboard”? I think the reason that the industry can’t offer a precise definition of “dashboard” that can set a boundary between the two terms is because the term “dashboard” is a metaphor for something tangible that we all use, and metaphors are subject to interpretation.

Whatever the specific definition is, the meaning is clear. The purpose of a dashboard or visual report is to summarize and display information for quick and convenient consumption so that users can understand what’s going on in their business without taking their eyes off the road.

While working on a product engineering team at Hewlett-Packard, I gained a valuable perspective about successful design that has stayed with me throughout my career. Everything that we design has the following three elements, which exist in balance, and these elements should be considered in everything you design:

Form—How does it look and how does it make me feel? Is it attractive, eye-catching, and interesting? People like to use things that are familiar and make them comfortable.

Fit—How does it suit the need or solve the issue? Begin with a well-defined problem statement that supports the design, thus creating a useful tool that finds an answer, or completes an important task.

Function—Is it easy to use, intuitive, and practical? Does it work in all environments and scenarios where it is needed? In a report or user interface, will the report work on a mobile device with a touch interface away from the office, or is it limited to a desktop? Is it interactive or static; for print or display?

Keep Charts Simple

Basic chart design can be quite simple if the default chart styles suit your needs. After placing the chart in the report body, you can drag fields from the Dataset window directly onto the chart design surface. At the minimum, a chart should have one aggregated field for the value and one grouped field for the category. The category and series groups represent the x-axis and y-axis in bar, column, line, area, and point charts.

Before things get complicated, let’s make it simple. Don’t go crazy with chart styling at first. Keep charts simple and clean, and use visual properties to emphasize the data, rather than the graphics. Heavy borders, backgrounds, shadows, and three-dimensional (3-D) effects distract from the message and presentation of important information.

NOTE My rule of thumb for visuals is that the amount of “ink” used in a graphic should correspond to the importance of the information. Generally speaking, dark, heavy borders and large fonts should be used to accentuate important information. Light, thin borders, backgrounds, and fonts should be used for supporting composition. Unnecessary stylings should be eliminated.

Visual design is art as much as it is science, and thus it is difficult to articulate hard rules for visual design. Sometimes it just needs to look right. Avoid clutter and use white space to provide balance. Work from examples of established design patterns. Experiment and find a presentation style that works for you and your audience, and then work within that theme. You can always fine-tune when the important design work is finished, and then improve the aesthetic design as you go.

You will find several excellent examples in books from Nathan Yao and his website, FlowingData.com. Stephen Few provides outstanding guidance about the principles and concepts of effective, simple visual design in his books and website, PercetualEdge.com. There you will find many bad examples to avoid, as well as good examples to pursue.

Properties, Oh My!

Reporting Services provides about 60 different chart styles, if you count all the variations. For each, you have fine control of detailed properties used to control border styles, fills, colors, and sizes of

just about everything you can imagine. With all this flexibility comes the potential for a good deal of complexity. All told, the chart data region and its constituent objects support nearly 200 individual properties. Some of these properties apply to only certain chart types. But no matter how you look at it, that’s a lot of properties to dig through.

Ever since Microsoft acquired the code base for Dundas Software’s .NET charting components

and added newer versions along the way, they have done a remarkable job of simplifying the design interface. You can take chart design as far as you need to. The necessary effort to design charts may range from simple to tedious. Having been down this road many times, I recommend that after you familiarize yourself with charting basics, you approach the design with specific objectives in mind. Otherwise, you’re likely to get lost in the interface.

The Fashion of Visualization

Perfect data visualization is the holy grail of analytic reporting. I’ve read a lot of books and attended many lectures about “right” and “wrong” data visualization practices. Scores of dashboarding, scorecarding, and visualization products exist, all with the promise of filling a huge gap in the industry left by all the other vendors who, according to competitors, can’t seem to get data visualization right.

As I write this, it’s only been a few days since the news of pop legend David Bowie’s passing. Bowie was synonymous with fashion (and, of course, he performed a song about it). Think of fashion trends over the past few decades—long hair, short hair, big hair; bell-bottoms, straight legs; neon, pastels, plaid, and grunge. Funny thing is that when we look back on the fashions of a previous generation, they seem so incredibly ridiculous that it’s hard to believe that people took any of it seriously. But somehow, a few pop stars and celebrities can make extreme fashion look cool and trendy. Charts are like that, too.

When I began using Reporting Services in 2003, 3-D charts were all the rage. Visual reports had big, rounded borders with drop shadows, beveled edges, and gradient-filled backgrounds. Do

that now and the reports will immediately be branded as “fluff” and “eye candy.” Ten years later, “modern” looking charts were flat with light-shaded bars on a white background. What were we thinking back then? Give us another few years and maybe we’ll all be wearing our bell-bottoms and designing 3-D charts.

Visual Storytelling

The purpose of a chart is to highlight important information and let it tell a story. Different report types can effectively convey comparisons or trends, but it’s important to use the right chart for your data. Before moving on, I want to make an important point. If you work in a field where specialized visualizations are useful and appropriate, you may find some of the more abstract and special-purpose charts of great value. But for day-to-day business reporting, it’s common to use just a handful of traditional chart types to visualize business metrics. So, even though Polar, Stock, and Funnel charts look cool, they may not help you convey an effective message. In business, 99 percent of the industry uses about 5 percent of the available chart types—namely, columns, bars, lines, and a few variations of the same types.

Perspective and Skewing

Hopefully, I’ve already talked you out of using 3-D charts as a practice, but I suppose there are isolated cases where it might make some sense. Consider a case where you are preparing a PowerPoint presentation. The data is static with the numbers right in front of you. Now, you want

to choose the most effective and impactful visual for your presentation, and maybe a bold 3-D chart makes the right statement. You can tweak it, spin it around, and make adjustments so the data points are all visible and in exactly the right place. This is a much different experience than designing a report for changing data. As soon as the values change, the last row of columns in a

3-D perspective chart may no longer be visible.

Here’s a rhetorical question. Figure 8-1 shows two renditions of the same chart and the same data in two different styles. Which is easier to read? This is purposely an extreme example, but it makes the point that wasting screen space on visual styling (not to mention ink or toner if printed) is just unnecessary.

The purpose of marketing is to persuade a consumer to perform an action, often by using psychology and emotion, to amplify and de-emphasize certain information. The carefully crafted presentation of information can alter a user’s perception, and his or her belief about certain facts. In analysis (which is a tool of science), information is presented in a uniform and standard format so that the consumer can make an honest and unbiased assessment.

FIGURE 8-1: Two renditions of the same chart.

CHART TYPES

Some of the more common chart types (such as column, bar, line, and area) can be used for different views of the same data. Pie charts present a more simplified view and work well with fewer category values. Other charts are more specialized and may be appropriate for multi-value data points, range values, and variances. All the chart types support dynamic capabilities, such as actions and tooltips. Using these features in report design, a chart user can get more information and details by hovering over or clicking a range, point, or area of the chart. Let’s take a brief look all the major chart types.

Chart Type Summary

Twelve general chart types are available, as described in Table 8-1.

TABLE 8-1: Chart Type Categories

CHART TYPE	DESCRIPTION	BEST USE
Column	A classic vertical bar chart with columns representing values along the y-axis. Like-valued items along the x-axis are grouped, and bars representing the same x-axis values in each group have the same colors or patterns. Series values may also be grouped and subgrouped. Columns can have point labels, and the colored bars can be labeled using a legend. Columns can be arranged side by side (along the x-axis) or in front of one another (along the z-axis.) Columns may appear to be extruded from their base using a rectangular or circular (cylindrical) shape.	Discrete group values on the Category (x) axis. Also effective with linear time-series periods broken into discrete buckets (such as days, weeks, or months).

continues

TABLE 8-1 (continued)

CHART TYPE	DESCRIPTION	BEST USE
Bar	Functionally the same as a column chart turned 90 degrees. It has the advantage of more accurately depicting value comparisons for layouts in which you have more available horizontal space.	Used only with discrete group values, not for linear series groups.
Line	Like a column chart but with a trend line drawn from one point to the next in the series. This type of chart is appropriate for a series of values that tend to progress over a relatively even plane that describes a “level,” “up,” or “down” trend. It is inappropriate for series values that tend to jump around. This type of chart is useful for comparing multiple series (along the z-axis) without obscuring trend lines behind a series.	Time-series and linear interval category groups (time, dates, and progressive numeric values).
Area	Like a line chart but the area encompassed by the line is filled. The solid shading of the charted area depicts a volume of data values. Opaque fill colors may be appropriate when the lines don’t intersect. Otherwise, transparent fill colors may be useful to discern overlapping fill areas.	Time-series and linear interval category groups.
Pie	The classic pie chart can be an good tool for comparing a small number of relative values but it may not be as accurate as a tree map, bar, or column chart. Unlike these other charts, the aggregate value isn’t quantified. For simple comparisons, users understand pie charts because they put comparative values into a proportional context and can drive quick decision support at a glance. Pie chart views can be exploded to visually separate each slice.	Use only for discrete category groups and never for linear series values. However, may be used for discrete bucket values. Typically, use pie charts with no more than 6 to 10 slices.
Doughnut	A doughnut is a pie with a hole in the middle. It’s more effective as a bold marketing visual. A 3-D doughnut rendering may expose smaller slices more clearly than a pie chart because each slice has four sides rather than three.	Use with a subtle 3-D effect for conveying a bold statement and not for accurately measuring business metrics. Typically, limit slices to 6 or 10.

CHAR T TYPE	DESCRIPTION	BEST USE
Scatter	Plots several points in a range (both x and y) to show trends and variations in value. The result is more like a cloudy band of points, rather than a specific aggregated point or line.	Used with dozens to hundreds of data points when analyzing the general trend is of greater value than seeing a specific point.
Bubble	A technique for charting points on three dimensions. Values are plotted using different-sized points (or bubbles) on a two-dimensional (2-D) grid. The size of the bubble indicates the related value along the z-axis.	Appropriate when measuring two different series values along two different linear axes where size represents one value and position represents the other.
Range (Gantt)	Range and Gantt charts are often used to visualize project phases and the progress of stages in a process along a linear series.	Used when each data item (project, commodity, unit of work) has a beginning and end value on a linear axis.
Stock	This category of charts (sometimes called candlestick r whisker graphs) plots values vertically like a column chart having variable start and end points. For each item along the y-axis series, a vertical line indicates a start and end value for the range. A tick mark in the line can indicate a significant value in that range or an aggregation of the range. This type of chart is useful for showing trading stocks with opening, closing, and purchase values; wholesale, retail, and discount prices; and so on.	A specialized kind of visual for discrete data items that have multiple events along a linear axis, typically multiple start and end values.
Shapes	Shape charts like the funnel and pyramid are effectively a single, stacked column chart. These typically are used to model sales and production against goals, and sales opportunity pipelines. The Tree map and sunburst charts are 2016 product additions. The tree map chart is considered by many visualization experts to be a more versatile replacement for pie charts because it provides a more accurate comparison of values and can handle a larger number of data points.	Used in specific business scenarios where data items progress through ranked stages.

continues

TABLE 8-1 (continued)

CHAR T TYPE	DESCRIPTION	BEST USE
Polar (Radar)	Polar and radar charts plot points from a central hub at different angles and distances in a radial fashion. This kind of visual does have some useful applications, but traditional nonlinear charts (such as a column or bar) are often more suitable.	A specialized visual. Can be used with discrete, nonlinear but related categories. Best used to express that no category exists at the beginning or end of the range.

Column and Stacked Charts

With so many variations of this chart type, the right choice will depend on the objective. Is it sufficient to demonstrate that one data point is less than or greater than another, or do these points need to be strictly measurable? An artistic chart might help to make an impact, but a flatter view is usually more appropriate to maintain accuracy.

Stacked charts effectively show the proportion of series values within each category group. The stacked column chart shown in Figure 8-2 demonstrates that each series value increases the column height in proportion, but the trade-off with this visual is that it’s more difficult to compare the individual series values across each category.

FIGURE 8-2: Stacked column chart.

To emphasize the proportion of like values rather than the comparative accumulation, the 100 percent stacked view makes all the bars in the chart the same length, rather than depicting the sum of all the values in the bar.

Area and Line Charts

As shown in Figure 8-3, an area chart plots the values of each point, and then draws a line from point to point to show the progression of values along the series. This is an effective method for analyzing trends, and works well when values tend to climb, decline, or remain level in the series. This type of chart is accurate when data exists for all category values on the x-axis. It typically doesn’t work well to express a series of values that are not in a relatively uniform plane.

FIGURE 8-3: Area chart.

A line chart is a variation of an area chart using a line or ribbon rather than a solid area. The line chart works better than the area chart for comparing multiple categories for a series of values, because one layer may obscure another in the area view. In the preceding example, the area chart works because of how the values are sorted. Larger values are in the background, and other points in the foreground are smaller, and the trend increases back to front.

Pie and Doughnut Charts

A pie chart can be a good tool for comparing proportional values when you have a relatively small number of categories, and each group is in an easily comparable range. If your goal is to use screen space effectively, geometry proves that this not the best visual. Simply put, a circle-shaped chart inside a rectangle-shaped page wastes a lot of screen or page space. Pie charts have received very bad

press for some good reasons amplified by the strong opinions of a few noted data visualization industry experts. Right or wrong, some people simply don’t take this visual very seriously anymore. That said, it’s still one of the most effective ways to convey a simple proportion of values in an uncluttered view or public presentation. The bottom line is that you should make sure this is the right visual for your purpose, and consider alternatives if in doubt.

Pie charts have fallen out of vogue in recent years due to their limited ability to visualize relatively similar values. Often, a pie chart is chosen when it works with a sample set of data that has a few categories with drastically different values, but ends up being a poor choice when the actual report data is less suitable for this type of chart. Consider using a tree map, column, or bar chart when more accurate comparisons are needed.

Here’s a quick experiment. Open the report named C08 – Pie and Column Chart in the Wrox SSRS 2016 Samples project. Preview the report and try to quickly determine the sales ranking of the three Bike product categories in 2013. Figure 8-4 shows the data visualized in a pie chart. That’s a tough one, huh?

FIGURE 8-4: Pie chart for Bike product categories.

You can see that I’ve added a report action in a textbox that shows and hides a column chart in the same report. I’m not going to give away the answer so you’ll have to use the sample report to find the answer for yourself, but you may be surprised.

Pie charts are also a very poor choice in a number of scenarios where column and bar charts would provide a more accurate presentation.

The purpose of a chart is to tell a complete story about the data, and it’s important to label it clearly so that you know what the groups represent. Group values can be titled using point labels, call-out labels, or in a legend.

A doughnut chart is a pie chart with a hole. Yes, it’s really that simple. Variations of the pie and doughnut charts allow you to separate the slices and, of course, twist and bend them using 3-D effects. Again, under the right conditions, this might be prudent with small, management result sets, but always consider your options before going down this route.

A significant limitation of this chart style is evident when the number of grouped values exceeds single digits. Consider the following example. Open the sample report Ch08 – Pie and Bar Chart with Many Values. You will notice I have added a visible parameter that allows you to specify the number of products to display in this sales summary. Figure 8-5 shows that it is difficult to discern between 25 different values. Below the pie chart is a bar chart visualizing the same results. In this case, the bar chart is a much better choice. The text arrangement of the vertical axis is ideal when comparing many values.

FIGURE 8-5: Doughnut chart versus bar chart.

The advantage of a bar chart over a doughnut chart, in this scenario, is that vertical page space is virtually unlimited in multiple page reports.

But Wait. There’s More!

A couple of product versions ago, a nifty trick was introduced that allows column and bar charts to grow dynamically. This feature is not as simple as just switching it on, though. Like most of

the cool capabilities in Reporting Services, you have to write a little code. If you take a look at the

DynamicHeight property of the bar chart in Design view, you’ll see how this works.

=(COUNT(Fields!ProductName.Value, “ProductSalesSummary”) / 5) & ” in”

The expression counts the number of rows returned from the dataset used in a calculation and concatenates a string value that resolves to the number of inches for the height of the chart. The result will be one plus the number of rows divided by five. Adding one avoids a division-by-zero error. The chart will allow room for about five rows per inch. I find it necessary to adjust this math in different reports to avoid adding extra white space.

Like the previous sample, I added a textbox with a drill-through action, but this one controls the chart series labels on the pie chart. Having data labels enabled does help you see the value that each slice represents, but, again, this is only useful with a limited number of axis values. Now that you see how this works, preview the report once more. Now, select 200 for the Top Products parameter list and press Enter. Click the “Show data labels” link below the pie chart and, as shown in Figure 8-6, it should be obvious that the bar chart is a better choice.

FIGURE 8-6: Top 200 Product Sales report.

Bubble and Stock Charts

Bubble charts are essentially a point plotted in a grid representing three dimensions. The value of the z-axis is expressed by the size of the bubble. Imagine that the bubble exists in a 3-D plane and appears larger if it is closer to you. Actually, the bubble can be a circle, square, triangle, diamond, or cross shape. This also means that a combination of shapes can be used to represent different data elements in the same chart space. The example shown in Figure 8-7 is included in the sample project and is named Ch08 – Bubble Chart Product Subcategory Profit Analysis

FIGURE 8-7: Bubble chart.

For sales orders grouped by subcategory, for a selected year and country, the product cost is plot- ted on the vertical axis and order quantity on the horizontal axis. Bubble size represents the simple profit calculated in the query. The example shown in Figure 8-7 helps you discover a valuable insight by revealing that Mountain Bike sales were particularly profitable in Canada.

The bubble chart properties are most easily set in the Series Properties dialog shown in Figure 8-8. With the sample report open in Design view, click the chart to show the Chart Data window and then use the call-out menu for the series in the Values field list. Here, the StandardCost, SimpleProfit, and OrderQuantity fields are assigned to the Bubble, “Bubble size,” and Category fields, respectively.

New Chart Types

Two new chart types introduced in SQL Server 2016 Reporting Services are the Tree Map and Sunburst charts. Sample reports are provided for both, which you can see in Figures 8-9 and 8-10.

It is notable the chart designer is generically designed to manage settings for every chart type. Consequently, the Category and Series group assignments, used to visualize fields differently in each chart type, can be quite arbitrary and some experimentation may be necessary to make sense of it.

FIGURE 8-8: Series Properties page for bubble chart.

FIGURE 8-9: Tree Map chart.

FIGURE 8-10: Sunburst chart.

The Anatomy of a Chart

The best way to learn how to use the charts in Reporting Services is to work through a few examples and exercises. An exhaustive review of all the properties and settings (some of which are shown in Figure 8-11) usually leads to getting lost and confused. Like learning to drive, start with the basic process and rules, and then just do it. You’ll figure out the details along the way.

Chart objects are organized into the hierarchy shown in Figure 8-12. Understanding this structure and the relationships between these objects will save you a lot of time and effort.

The Chart object is really is just a container. The Chart Area object does most of the work, and contains most of the useful properties. Take some time to explore the chart objects in the design interface. Because there are so many different objects, selecting the right object can be tricky at first, but with a little experience, you will become comfortable with the interface.

With a chart open in the designer, show the Properties window and then click different areas of the chart to select and view the names of various objects. Use the chart objects hierarchy as a reference as you click different areas to select objects. Additionally, you can right-click an object to reveal menu options that will take you to subordinate objects and collections. For example, if you were to right-click the Chart object, the Properties window and the right-click menu will let you find the Chart Area objects. Taking time to do this now will save you time and effort when you follow the exercise later in this chapter.

FIGURE 8-11: Chart properties and settings.

FIGURE 8-12: Hierarchy of chart objects.

Note that many of these objects are organized into collections to make them fit neatly into the standard Properties windows and design interface. As a rule, you can select an object and set its properties in one of two ways.

If you right-click an object in the chart designer, you see a menu item to edit the object’s properties. That menu may also include related objects that you can edit. Choosing this menu option opens a custom property page for the object. Choosing an option in a property page to edit another object opens another property page. These are stacked in the order in which they were opened. Some properties are actually object collections. Clicking an ellipsis (…) button for that collection opens a dialog with the object collection and associated properties.

In addition to the custom properties pages for each chart-related object, you can edit properties in the standard Properties window.

The remainder of the chapter covers charts with Multiple Series, Axes, and Areas; using the following examples:

EXERCISES

The exercises in the book include:

Exercise 1: Creating and Styling a Simple Chart

Exercise 2: Creating a Multi-series Chart