Confluence Reporting HOWTO - 3. Charts and Formatting

Introduction

In the last section we looked at getting your data onto a page. In this section you'll learn how to present your data visually using charts and formatting options. Confluence comes with several types of charts, each of which has many formatting options that let you present your data in cool and powerful ways.

Confluence's ability to display information visually using the {chart} macro is at the heart of its power as a reporting tool. Nothing compares to a chart for quickly conveying rich, multivariate comparative data.

Charts

Types of Charts

Confluence comes with several built-in charts:

Pie {chart} || Model || Cars || Trucks || Motorcycles || | Quantity | 2,500 | 9,000 | 4,200 | {chart}

Line | {chart:type=line} || Year || 2008 || 2009 || 2010 || | Cars | 2,500 | 2,000 | 3,200 | | Trucks | 3,250 | 6,100 | 9,000 | | Motorcycles | 4,200 | 4,250 | 5,000 |

Bar & Stacked Bar {chart:type=bar|stacked=true} || Year || 2008 || 2009 || 2010 || | Cars | 2,500 | 2,000 | 3,200 | | Trucks | 3,250 | 6,100 | 9,000 | | Motorcycles | 4,200 | 4,250 | 5,000 | {chart}

Stacked Area {chart:type=area|stacked=true} || Year || 2008 || 2009 || 2010 || | Cars | 2,500 | 2,000 | 3,200 | | Trucks | 3,250 | 6,100 | 9,000 | | Motorcycles | 4,200 | 4,250 | 5,000 | {chart}

Scatter {chart:type=scatter} | Delay | 172 | 175 | (etc...) | | Duration | 59 | 75 | (etc...) | {chart}

Nonstacked Area {chart:type=area} || Month || 04 || 05 || (etc...) || | Trucks | 3,250 | 6,100 | (etc...) | | Motorcycles | 4,200 | 4,250 | (etc...) | {chart}

There are more, including several types of XY plot and Gantt charts, listed at the {chart} documentation.

Chart basics

Dynamic Charts

The {chart} macro renders data from tables into chart graphics. Before you can chart anything, you need to get your data into tables. We typically use {sql} to extract data into tables, then wrap those tables in {chart} to produce charts. Remember, the output of the {sql} macro is just an ordinary table in the wiki. As far as Confluence is concerned, a table and the output of {sql} are the exact same thing.

This point is so important that I've done a little diagram to illustrate it:

Chart Terminology

Charts have a horizontal axis (the x-axis or "domain") and a vertical axis (the y-axis or "range"), along which your data (your "series") will be plotted.

Chart Options

Confluence lets you fine-tune your charts by specifying additional parameters to the {chart} macro.

We won't describe every single parameter to {chart}, just the ones that we use most often. For the detail, see the Chart macro documentation.

Orientation

It's natural to orient your tables vertically, especially if you're using SQL to retrieve your data. When you do this, you'll need to use the dataOrientation=vertical parameter to tell Confluence how to read the table.

{chart:type=line|dataOrientation=vertical} {sql:dataSource=shop} select Year, sum(cars) as "Cars", sum(trucks) as "Trucks", sum(motorcycles) as "Motorcycles" from sales group by Year {sql} {chart}

Produces:

Size

By default, Confluence makes charts 300 pixels high by 300 pixels wide, which isn't usually the size you want. You can override this using the height and width parameters.

{chart:type=line|height=250|width=500} {sql:dataSource=shop} select model, sum(FY08) as "2008", sum(FY09) as "2009", sum(FY10) as "2010" from sales group by model {sql} {chart}

Produces:

Colors and Shapes

{chart} comes with a default set of series colors that work well enough in most cases. If you need to, though, you can override the defaults and specify which HTML colors you want to use instead.

The line chart by default displays shapes at each data point. If you want to turn this off, you can use the showShapes=false parameter.

{chart:type=line|height=250|width=500| colors=#800000,#008000,#000080|showShapes=false} {sql:dataSource=shop} select model, sum(FY08) as "2008", sum(FY09) as "2009", sum(FY10) as "2010" from sales group by model {sql} {chart}

Produces:

Labels and Legends

You might have noticed that there are no y-axis labels in the charts above, so there's no way to tell if the values represent units, dollars or little colored stones. Fortunately, labeling axes is easy - just add yLabel or xLabel parameters to your {chart} macro.

If the legend is unneeded (often the case in a single-series chart), you can remove it by specifying legend=false.

{chart:type=line|height=250|width=500| yLabel=Quantity|xLabel=Financial Year} {sql:dataSource=shop} select model, sum(FY08) as "2008", sum(FY09) as "2009", sum(FY10) as "2010" from sales group by model {sql} {chart}

Produces:

If your labels are too crowded, adjust them as you see fit with the categoryLabelPosition parameter.

{chart:type=line|height=250|width=500| dataOrientation=vertical|yLabel=Quantity|xLabel=Financial Quarter|categoryLabelPosition=up45|dataOrientation=vertical} {sql:dataSource=shop} select Quarter, sum(cars) as "Cars", sum(trucks) as "Trucks", sum(motorcycles) as "Motorcycles" from sales group by Quarter {sql} {chart}

Produces:

Scale and Units

You can contol the vertical range of the chart using the rangeAxisLowerBound and rangeAxisUpperBound parameters.

Confluence picks a default "tick unit" based on the scale of the data and draws lines at each tick. You can override this and specify a manual tick unit with rangeAxisTickUnit.

{chart:type=line|height=250|width=500| rangeAxisLowerBound=2000|rangeAxisUpperBound=20000| rangeAxisTickUnit=1000} {sql:dataSource=shop} select model, sum(FY09) as "2009", sum(FY10) as "2010", sum(FY11) as "2011" from sales group by model {sql} {chart}

Produces:

You can exercise the same control over the horizontal axis with the domainAxisLowerBound, domainAxisUpperBound and domainAxisTickUnit parameters.

Multiple Sets of Data, One Chart

One of the hidden gems of {chart} is that it can wrap several sets of results into one graphic. Remember, all {chart} does is interpret tables and render them into charts. {chart} doesn't care if you give it one table or several: If the domain values are the same, it's smart enough to stitch the series together as of they were all in one table. This gives you an easy way to "bundle up" what might be disparate and complicated data series into a single visualization.

Here's an example: Say we're querying an accounts system to produce this sales table:

What if you wanted to chart this data alongside stock data from an inventory system?

As long as the domain axis values (in this case, the dates) are the same, {chart} will treat these series as if they were in the same table. For example:

{chart:type=line|dataOrientation=vertical| colors=#468cc4,#d7561f,#8acff8,#fb9a5f|height=250| width=600|categoryLabelPosition=up45} {sql:dataSource=sales} select Qtr, sum(cars) as "Car Sales", sum(trucks) as "Truck Sales" from sales group by Qtr {sql} {sql:dataSource=stock} select Qtr, sum(cars) as "Car Stock", sum(trucks) as "Truck Stock" from sales group by Qtr {sql} {chart}

Produces:

We used the color parameter to assign similar colors to related series, making visual comparisons easy - a neat trick.

Gotcha: If the domain axis values don't match exactly, {chart} won't be able to match your series together and you'll get a mess.

Section, Column and Center

When you're designing a report or dashboard you often need to have control over the layout of the page. With Confluence, it's easy to arrange charts and tables into columns with the {section} and {column} macros.

• Enclose the entire section that you will spread across columns in a {section} macro.
• Enclose the contents of each column in a {column} macro. Optionally set the width parameter.
• Enclose everything you want centered inside a {center} macro.

For example, a simple two-column layout:

{section}{column:width=33%}

Column 1 content goes here

{column}{column:width=66%}

{center}Column 2 content goes here{center}

{column}{section}

Further Reading

Effective charting is an art in itself. As a starting list for further reading, we recommend:

• Edward Tufte's The Visual Display of Quantitative Information
• Dashboard Zone
• Enterprise-dashboard.com
• Dashboard Spy
• Design Label's article on data visualization is loaded with ideas.