Create a xyLine chart using the SQL macro

Scenario

Estimated Time: 11 min

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: