Scenario
| Div |
|---|
|
 Estimated Time: 9 min
|
As an example, let's create an xyArea chart to display "Publishing Revenue vs. Expense" per quarter in 2017.
...
| Code Block |
|---|
|
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 | |
+--------------+------------------+------+-----+---------+----------------+ |
| Info |
|---|
For more information about data source profiles, click the appropriate version: |
The query against the PUBLICATION_RVE table looks like:
...
The following steps demonstrate how to use Markup to insert Confluence's native Chart macro and our SQL macro 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 a Confluence pageCreate a Confluence page to create the macros needed to produce the chart. - Create a page named Publication Revenue vs. Expense in a space accessible to the desired audience.
- Click Save to close the Page Restrictions screen.
- Click Save to save to publish the new page.
|
|
| | Edit the Publication Revenue vs. Expense page and do 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.
- 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.
- Click Save to save your changes to the macro's parameters.
| Chart macro parameters: | Type | xyArea | | Width (pixel value only) | 600 | | Height (pixel value only) | 400 | | Chart Title | Pearson | | Chart Subtitle | Revenue vs. Expense - 2017 | | Show legend |  |
|
| | This step uses the SQL macro to retrieve the data to be displayed in the chart. To configure the SQL macro, do the following: - Edit your Publication Revenue vs. Expense 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:
- 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 result set 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 result set 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 "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: 10.x: SQL settings > SQL statement > Data source profile
8.x: Data source profile | BooksWarehouse | 10.x: Display settings > Data layout > Use database column labels
8.x: Use database column labels | On | 10.x: Display settings > Data layout > Show error if there are no rows
8.x: Show error if there are no rows | On | 10.x: Display settings > Data layout > Text to display when there are no rows
8.x: 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 Publication Revenue vs. Expense page and click Save. You should now see a page that appears as shown on the right. | Final xyArea Chart: 
|
|
...
