Scenario
Div | ||
---|---|---|
| ||
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 uses 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):
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
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.
...
border | 0 |
---|---|
heading | 0 |
multiple | false |
enableHeadingAttributes | false |
columnAttributes | style="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;" |
id | steps_table |
enableSorting | false |
enableHighlighting | false |
...
Create Confluence page
Create a Confluence page that will eventually contain the macros needed to produce the chart.
- Create a page named Books in Circulation in a space accessible to the intended audience.
- Click Save to close the Page Restrictions screen.
- Click Save to save to publish the new page.
...
...
Configure the Chart macro
Edit the Books in Circulation page and perform the following:
- Insert the Chart macro.
- Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right. The following parameters indicate:
- Type - type of a chart.
- Width - width of the chart in pixels.
- Height - height of the chart in pixels.
- Chart Title - title for the chart.
- Chart Subtitle - subtitle for the chart.
- Show legend - displays the name associated with the colored bars rendered.
- 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 | Nineteen Eighty-Four |
Chart Subtitle | Books Printed vs. Sold in 1949 |
Show legend |
...
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:
...
- 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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
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'; |
Using your mouse, "drag" the SQL Macro container into your Chart Macro container (see right).
SQL Macro parameters:
SQL settings > SQL statement > Data source profile | BooksWarehouse |
Display settings > Data layout > Use database column labels | On |
Display settings > Data layout > Show error if there are no rows | On |
Display settings > Data layout > Text to display when there are no rows | No rows selected |
Moving the SQL Macro inside the Chart Macro
...
Save and test the page
Now, go back to the Books in Circulation page and click Save to save it. You should now see a page that appears as shown on the right.
Books in Circulation:
...
Include Page | ||||
---|---|---|---|---|
|