Page Comparison

Scenario

In this scenario, we will create a XY Area chart to display "Publishing Revenue vs. Expense" by quarter in 2017. 

Background

Our scenario uses 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 are focusing 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 demonstrate how to use Markup to insert Confluence's native  Chart macro and our SQL macro on a Confluence page.

...

...

Image Removed

Create a Confluence page

Create a Confluence page that will eventually contain the macros needed to produce the chart.

1. Create a page named Publication Revenue vs. Expense in a space accessible to the desired audience.
2. Click Save to close the Page Restrictions screen.
3. Click Save to save to publish the new page.

...

Image Removed

...

Configure the Chart macro

Edit the Publication Revenue vs. Expense page and do the following:

1. Insert the Chart macro.
2. Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right.
   • Type - type of a chart.
   • Width - width of the chart in pixels.
   • Height - height of the chart in pixels.
   • Column - column name or order of mapping from the query result to the chart.
   • Chart Title - title for the chart.
   • Chart Subtitle - subtitle for the chart.
   • Show legend - displays the name associated with the colored bars rendered.
3. Click Save to save your changes to the Chart macro's parameters.

Chart macro parameters:

Type	pie
Width (pixel value only)	600
Height (pixel value only)	400
Chart Title	Pearson
Chart Subtitle	Revenue vs. Expense - 2017
Show legend

...

Image Removed

Configure the SQL Query macro

This step uses the SQL macro, to retrieve the data that will be displayed in the bar chart. To configure the SQL  macro, do the following:

1. Edit your Publication Revenue vs. Expense page.
2. Add a line below your Chart macro.
3. Using Markup, insert a new SQL macro on the page (more on Markup here).
4. Adjust its parameters as shown on the right. The following parameters indicate:
   • Data source profile - name of the data source profile that you set up.
   • Use database column labels - usage of the column names defined within the SELECT statement (e.g., Publisher, M Rev.) rather than the columns names defined within the database table(s) themselves.
   • Show error if there are no rows - displays an error if no rows are returned in the resultset of the SQL query.
   • Text to display when there are no rows - text of the error message to be displayed when no rows are returned in the resultset of the SQL query.

...

With the cursor positioned inside the SQL macro container, paste in this SQL statement:

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';

Using your mouse, "drag" the SQL Macro container into your Chart Macro container (see right).

SQL macro parameters:

Moving the SQL macro inside the Chart macro

Image Removed

...

Image Removed

...

Save and test the page

Now, go back to the Publication Revenue vs. Expense page and click Save to save it. You should now see a page that appears as shown on the right.

Final XY Area Chart:

Image Removed

Image Removed