Create a XY Area Chart using the SQL Macro - 10.x

Scenario

Estimated Time: 9 min

In this scenario, we will create a XY Area chart to display "Publishing Revenue vs. Expense" by quarter in 2017.

Background

Our scenario uses a data source profile called "BookWarehouse" to query a rollup database table called "PUBLICATION_RVE". The table contains aggregate rollup revenue and expense data by book, by quarter. For this scenario, we are focusing on charting 2017 revenue and expense data for a single publisher:

SQL > explain publication_rve;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| publisher_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| type         | varchar(255)     | NO   |     | NULL    |                |
| Q1           | decimal(10,2)    | NO   |     | NULL    |                |
| Q2           | decimal(10,2)    | NO   |     | NULL    |                |
| Q3           | decimal(10,2)    | NO   |     | NULL    |                |
| Q4           | decimal(10,2)    | NO   |     | NULL    |                |
| year         | varchar(255)     | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

Our example query against the PUBLICATION_RVE table looks like:

SELECT type as "Statement Type"
,format(Q1,0) as "Q1"
,format(Q2,0) as "Q2"
,format(Q2,0) as "Q3"
,format(Q2,0) as "Q4" 
FROM publication_rve where year='2017' 
 AND type='Revenue'
UNION
SELECT type as "Statement Type"
,format(Q1,0) as "Q1"
,format(Q2,0) as "Q2"
,format(Q2,0) as "Q3"
,format(Q2,0) as "Q4" 
FROM publication_rve where year='2017' 
 AND type='Expense';

Steps

The following steps demonstrate how to use Markup to insert Confluence's native Chart macro and our SQL macro on a Confluence page.