Create a Pie chart using the SQL macro - 11.x

Scenario

Estimated Time: 13 min

As an example, let's create a Pie chart to display "Book Print Operational Costs".

Background

Our scenario uses a data source profile, BookWarehouse, to query a database table, PRINTOPERATION. The table contains current minimum, maximum, and, average operational costs associated with the printing and go-to-market costs for books.

For this scenario, we focus on charting the current average market costs for each type of print operation.

SQL > mysql> select po_book_id, printoperation, avgcost from printoperation;
+------------+-----------------------+----------+
| po_book_id | printoperation        | avgcost  |
+------------+-----------------------+----------+
|          2 | Developmental Editing | 18000.00 |
|          3 | Copyediting           |  7000.00 |
|          4 | Cover Design          |  3500.00 |
|          5 | Formatting            |  2500.00 |
|          6 | Illustrations         |  4000.00 |
|          7 | ISBN Registration     |   250.00 |
|          8 | Marketing & PR        |  5000.00 |
+------------+-----------------------+----------+

For more information about data source profiles, refer to Configure Data Source Profiles.

Our query against the PRINTOPERATION table looks like:

SELECT printoperation as "Service"
,format(avgcost,0) AS "Cost" 
FROM printoperation;

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 to add the macros needed to produce the chart.

Create a page named Print Operations 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.

Configure the Chart macro

Edit the Print Operations page and do the following:

Insert the Chart macro.
Click the Chart macro container and click Edit so you can modify 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.
- Column - column name or order of mapping from the query result to the chart.
- Chart Title - title 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	pie
Width (pixel value only)	600
Height (pixel value only)	400
Column	Service, Cost
Chart Title	Average Print Operational Costs
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 Print Operations 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 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.
With the cursor positioned inside the SQL macro container, paste in this SQL statement:
SELECT printoperation as "Service" ,format(avgcost,0) AS "Cost" FROM printoperation;
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 page

Now, go back to the Print Operations page and click Save. You should now see a page that appears as shown on the right.

Book Print Operational Costs: