Confluence Reporting HOWTO - 2. Retrieving Data Into Tables

Confluence can display data from a variety of sources:

• Static tables within the page
• Static CSV data within the page
• An attached Excel spreadsheet
• An SQL database

In each case, a results table is produced. This table can also be turned into a chart, as explained later.

Static tables within the page

Static tables may lack the dynamic nature of retrieving 'realtime' information from a database, but is an excellent means of sharing data with Confluence users without having to send documents via email.

A table can be inserted into a Confluence page using table markup:

|| ordernumber || orderdate || status || customername ||
| 10151 | 2007-09-21 | Shipped | Oulu Toy Supplies, Inc. |
| 10172 | 2007-11-05 | Shipped | Gift Depot Inc. |
| 10260 | 2008-06-16 | Cancelled | GiftsForHim.com |
| 10217 | 2008-02-04 | Shipped | Handji Gifts& Co |
| 10252 | 2008-05-26 | Shipped | Auto Canal+ Petit |

If you're interested in producing tables with fancy formatting, see the {table} macro.

Static CSV data within the page

If you've got a lot of data, it could be easier to paste it directly into the Confluence page, using the {csv} macro to format it into a table.

{csv}
ordernumber, orderdate, status, customername
10151,2007-09-21,Shipped,"Oulu Toy Supplies, Inc."
10172,2007-11-05,Shipped,Gift Depot Inc.
10260,2008-06-16,Cancelled,GiftsForHim.com
10217,2008-02-04,Shipped,Handji Gifts& Co
10252,2008-05-26,Shipped,Auto Canal+ Petit
{csv}

An attached Excel spreadsheet

You can also attach an Excel spreadsheet to the Confluence page and use the {excel} macro to pull it onto a page. The WebDAV plugin is handy for editing spreadsheets you've attached.

{excel:file=^Orders.xls}

An SQL database

For maximum reporting capability, use the {sql} macro to retrieve data from a JDBC-compatible database such as MySQL, PostgreSQL, Oracle and Microsoft SQL Server (ask your Database Administrator if your database can be accessed via JDBC*).

A query can be sent to the database, which results in data being returned to Confluence:

Inline queries

The default output is a table, but single-line results can be included within a paragraph:

You can even have your SQL return wikimarkup, to be formatted by the Confluence wiki renderer:

Table Capabilities

All of the table macros - {csv}, {excel}, and {sql} - produce tables with some slightly hidden extra capabilities. For example, you can click on any column header to sort the table by that column.

You can also use the autoTotal=true parameter to add a row to the bottom of the table showing totals for each numeric column. Notice that the total row stays at the bottom when you sort the columns by clicking on the headers.

{sql:datasource=shop|autoTotal=true}
select
  ordernumber,
  orderdate,
  status,
  customername,
  dollarvalue
from customers c, orders o
where
  c.customernumber = o.customernumber
limit 5
{sql}

You can see that in addition to the dollarvalue column, there's a total for the ordernumber column, which we probably don't want. Confluence did this because it recognized the contents of the ordernumber column as numbers. You can prevent this by to explicity specifying the data types for each column using the {columnTypes} parameter:

{sql:datasource=shop|autoTotal=true|columnTypes=S,S,S,S,C}
select
  ordernumber,
  orderdate,
  status,
  customername,
  dollarvalue
from customers c, orders o
where
  c.customernumber = o.customernumber
limit 5
{sql}

You can also use columnTypes to work around some restrictions in SQL. In many databases, if you format a number as currency you lose the ability to sort values as numbers - the database sorts them as character strings instead. Tables in Confluence are smart enough to sort and total currency-formatted numbers correctly, though.

{sql:datasource=shop|autoTotal=true|columnTypes=S,S,S,S,C|
sortColumn=5|sortDescending=true|sortIcon=true}
select
  ordernumber,
  orderdate,
  status,
  customername,
  to_char(dollarvalue, 'LFM99999990D00') as dollarvalue
from customers c, orders o
where
  c.customernumber = o.customernumber
limit 5
{sql}

In that example, we also used sortColumn and sortDescending to specify that we wanted the highest dollar value at the top of the table. sortIcon=true adds a nice graphical indicator of the current sort key.

* = The specifics of exactly how to set up a datasource vary depending on how your instance of Confluence is set up. Bob Swift's SQL Plugin documentation includes guidelines that cover many common setups.