.png?version=1&modificationDate=1662627367996&cacheVersion=1&api=v2&width=226&height=72)
Scenario
Estimated Time: 9 min
In this scenario, we will create a XY Area chart to display "Publishing Revenue vs. Expense" by quarter in 2017.
Background
Our scenario will use a Data Source Profile called "BookWarehouse" to query a rollup database table called "PUBLICATION_RVE". The table contains aggregate rollup revenue and expense data by book, by quarter. For this scenario, we will focus 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 | | +--------------+------------------+------+-----+---------+----------------+
Our example 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 will demonstrate how to use Markup to insert Confluence's native Chart Macro and our SQL Macro on a Confluence page.
