Create an xyArea chart using the SQL macro

Scenario

Estimated Time: 9 min

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: