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 will 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 | +---------+---------+----------+-----------+-----------+-----------+
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: