Create an xyLine chart using the SQL macro

Scenario

As an example, let's create an xyLine chart to display "Books Printed vs. Sold" for the first 24 months in circulation.

Background

Our scenario uses a data source profile, BookWarehouse, to query a rollup database table, CIRCULATION. The table contains aggregate metadata associated with the circulation of books which are rolled-up and reported on by year.

For this scenario, we focus on charting print vs. sold totals for the book "Nineteen Eighty-Four" in it's first year of circulation (1949):

SQL > SELECT * FROM circulation WHERE book_id =24 and year=1949;
+---------+---------+----------+-----------+-----------+-----------+
|type    | 6_months | 12_months | 18_months | 24_months | year |
+---------+---------+----------+-----------+-----------+-----------+
|Printed |  8500.00 |  12000.00 |  20000.00 |  25000.00 | 1949 |
|Sold    |  9340.00 |  19000.00 |  14000.00 |  27000.00 | 1949 |
+---------+---------+----------+-----------+-----------+-----------+

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

Our query against the CIRCULATION table looks like:

SELECT type as "Statistic Type"
,format(6_months,0) as "6"
,format(12_months,0) as "12"
,format(18_months,0) as "18"
,format(24_months,0) as "24" 
FROM circulation where year='1949' 
 AND type='Printed'
UNION
SELECT type as "Statistic Type"
,format(6_months,0) as "6"
,format(12_months,0) as "12"
,format(18_months,0) as "18"
,format(24_months,0) as "24" 
FROM circulation where year='1949' 
 AND type='Sold';

Steps

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