...
...
Scenario
| Div |
|---|
|
 Estimated Time: 11 min
|
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:
| Code Block |
|---|
|
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 |
+----+------+-------------------+-----------------+---------+ |
| Info |
|---|
For more information about data source profiles, click the appropriate version: |
Our query against the PUBLISHERS table looks like:
| Code Block |
|---|
|
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 macroMacro 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 that will eventually contain 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.
|
|
| | Edit the Top Publishers WorldwideWorldwide 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 | |
|
| | 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 resultset 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 statementstatement (10.x) or inside the SQL macro container (8.x) as: | Code Block |
|---|
| language | sql |
|---|
| theme | DJango |
|---|
| linenumbers | true |
|---|
| SELECT company_name as "Publisher"
,CONCAT('$',format(revenue,0)) as "M Rev"
FROM publishers
ORDER BY rank asc; |
Using 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
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 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): 
|
|
...
