Create a Bar chart using SQL macro - 11.x
Scenario
As an example, let's create a 3-D bar chart to display the top publishers by revenue (in $M).
Background
Our scenario uses a data source profile, BookWarehouse, to query a database table, PUBLISHERS. The table contains the following columns and rows:
SQL > select * from publishers;
+----+------+-------------------+-----------------+---------+
| id | rank | parent_company | company_name | revenue |
+----+------+-------------------+-----------------+---------+
| 10 | 1 | Pearson | Pearson | 5000.00 |
| 11 | 2 | Reed | RELXGroup | 4900.00 |
| 12 | 3 | WoodbridgeCompany | ThompsonReuters | 4700.00 |
| 13 | 4 | BertelsmannAG-NV | Bertelsmann | 4500.00 |
| 14 | 5 | WoltersKluwer | WoltersKluwer | 4100.00 |
+----+------+-------------------+-----------------+---------+For more information about data source profiles, refer to Configure Data Source Profiles.
Our query against the PUBLISHERS table looks like:
SELECT company_name as "Publisher", CONCAT('$',format(revenue,0)) as "M Rev" FROM publishers ORDER BY rank asc;Steps
The following steps demonstrate how to use Markup to insert Confluence's native Chart Macro and our SQL Macro on a Confluence page:
Create a Confluence page
Create a Confluence page that contains the macros needed to produce the chart.
Create a page named Top Publishers Worldwide 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.
2. Configure the Chart macro
Edit the Top Publishers Worldwide 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. The following parameters indicate:
Type - type of a chart.
Width - width of the chart in pixels.
Height - height of the chart in pixels.
Display rendered data - a data table should be displayed after (below) the chart.
Chart Title - title for the chart.
Show in 3-D - add dimension to the bars within the chart.
Opacity - recommended when using 3-D and for bar the recommendation is 75%.
Horizontal-axis - column values rendered on the horizon line.
Vertical-axis - column values rendered on the vertical line.
Show legend - name associated with the colored bars rendered.
Click Save to save your changes to the macro's parameters.
Chart macro parameters:
Type | bar |
Width (pixel value only) | 600 |
Height (pixel value only) | 400 |
Chart Title | Top Publishers by Revenue |
Show in 3-D | |
Opacity | 75 |
Horizontal-axis label | Publisher |
Vertical-axis label | Revenue (in $M) |
Show legend |
Configure the SQL Query macro
This step uses the SQL macro, to retrieve the data to be displayed in the bar chart. To configure the SQL macro, do the following:
Edit your Top Publishers Worldwide 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 resultset of the SQL query.
Click Save to save your changes to the SQL macro's parameters.
Paste the SQL statement in SQL settings > SQL statement > SQL statement (10.x) or inside the SQL macro container (8.x) as:
SELECT company_name as "Publisher" ,CONCAT('$',format(revenue,0)) as "M Rev" FROM publishers ORDER BY rank asc;Use your mouse to drag the SQL macro container into your Chart macro container (see right).
SQL macro parameters:
10.x: SQL settings > SQL statement > Data source profile | BooksWarehouse |
10.x: Display settings > Data layout > Use database column labels | On |
10.x: Display settings > Data layout > Show error if there are no rows | On |
10.x: 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 Top Publishers Worldwide page and click Save. You should now see a page that appears as shown on the right.
Top Publishers by Revenue (in $M):
Need support? Create a request with our support team.