.png?version=1&modificationDate=1662627367996&cacheVersion=1&api=v2&width=226&height=72)
Scenario
Estimated Time: 11 min
In this scenario, we will create a xyLine chart to display "Books Printed vs. Sold" for the first 24 months in circulation.
Background
Our scenario will use a Data Source Profile called "BookWarehouse" to query a rollup database table called "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 example 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 will demonstrate how to use Markup to insert Confluence's native Chart Macro and our SQL Macro on a Confluence page.
