Create an xyArea chart using the SQL macro

Scenario

As an example, let's create an xyArea chart to display "Publishing Revenue vs. Expense" per quarter in 2017.

Background

Our scenario uses a data source profile, BookWarehouse, to query a rollup database table, 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    |                |
+--------------+------------------+------+-----+---------+----------------+

For more information about data source profiles, click the appropriate version:

The 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: