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 will use 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):
...
The following steps will demonstrate how to use Markup to insert Confluence's native Chart Macro and our SQL Macro on on a Confluence page.
Table plus |
---|
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 pageCreate 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 desired intended audience.
- Click Save to close the Page Restrictions screen.
- Click Save to save to publish the new page.
|
|
| | Edit the Books in Circulation page and do 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:
The Type parameter indicates to display a xyLine The parameter indicates the - - width of the chart in pixels.
The parameter indicates the - - height of the chart in pixels.
- The Chart Title parameter provides a - title for the chart.The
- Chart Subtitle parameter provides a - subtitle for the chart.
- The Show legend parameter will display - 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 | |
|
| | 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: - Edit your Books in Circulation page.
- Add a line below your Chart Macro.
- Using Markup, insert a new SQL Macro on the page (more on Markup here).
- Adjust its parameters as shown on the right. The following parameters indicate:
- The Data source name parameter indicates to use the BooksWarehouse profile - name of the data source profile that you set up.
- The Use database column labels parameter indicates to use - 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.
- The Show error if there are no rows parameter indicates to show - displays an error if no rows are returned in the resultset of the SQL query.
- The Text to display when there are no rows parameter indicates the - text of the error message to be displayed when no rows are returned in the resultset of the SQL query.
- Click Save to save your changes to the SQL Macro's parameters.
With the cursor positioned inside the SQL Macro container, paste in this SQL statement: Code Block |
---|
language | sql |
---|
theme | DJango |
---|
linenumbers | true |
---|
| 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 nameprofile | BooksWarehouse | Display settings > Data layout > Use database column labels | selected | On | Display settings > Data layout > Show error if there are no rows | selected | 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 pageNow, 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:
|
|
...