Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Div
idProductTOC

On this page

Table of Contents
maxLevel1
excludeOn this page
typeflat
separator |

...

  • The Chart macro, which comes natively with Confluence, to produce the pie chart.
  • The SQL for Confluence add-on, which comes in both Express and Pro editions, to query a SQL database to retrieve the data to be presented in the chart.
  • The Macro Security for Confluence add-on to safeguard the page and the database it uses by ensuring that only Administrators can use the SQL macro.

(info) It will take you approximately 8-10 minutes to complete this recipe. Jump to Step 5 to see a preview of the final result!

See our other related BI recipes:

...

Recipe level

Table plus
border0
heading0
multiplefalse
enableHeadingAttributesfalse
columnAttributesstyle="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;"
enableSortingfalse
classingredients_table
enableHighlightingfalse

Ingredients

SQL for Confluence (Pro Edition)

Image Removed

Macro Security for Confluence

Div
classmore_info

Try it Free

Div
classmore_info

  Documentation

SQL Recipes
Table plus
border0
heading0
multiplefalse
enableHeadingAttributesfalse
columnAttributesstyle="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;"
enableSortingfalse
classingredients_table
enableHighlightingfalse

Preparation

...

SQL Query macro parameters:
Table plus
border0
heading0
multiplefalse
enableHeadingAttributesfalse
columnAttributesstyle="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;"
idsteps_table
enableSortingfalse
enableHighlightingfalse
Configure Macro Security -3

Once you have installed the Macro Security for Confluence add-on, it must be configured with a property file that identifies who can run each security-enabled macro. Each macro security enabled macro accepts specific parameters to controls its use. This safeguards your Confluence instance and data from misuse or performance issues caused by inexperienced users or inappropriate use.

Follow these steps to configure it:

  1. Download the sample macro-security.properties file that is configured for moderate restrictions. This restricts most security-enabled macros so that only Administrators can execute them, but allows some others to be used by any user.
  2. Review the file and ensure that the lines beginning with "sql-query.datasource.*", "run" and "run-now" are restricted to the confluence-administrators group as shown in the example configuration file to the right.

  3. Save the file.
  4. Create a Confluence page entitled Macro Security Configuration in a space accessible only to Administrators (or apply page-level restrictions so the page is accessible only to Administrators) and attach the file to it. Take note of the space key of the space. The space key appears in the URL of the page after you've saved, immediately after "display/". For instance, in the URL http://wiki.examplegear.com/display/admin/Macro Security Configuration, the space key is admin.
  5. Go to Manage Add-ons administration screen, locate the Macro Security add-on and click its Configure button. This will take you to the Macro Security configuration screen.
  6. In the Load Security Configuration section, supply the location of your macro-security.properties file, in the form of spacekey:pagetitle^filename such as admin:Macro Security Configuration^macro-security.properties and click Load.
  7. Select the Enable button and click Save.

 

Example configuration file:# See the documentation space - https://bobswift.atlassian.net/wiki/display/CMSP # More specifically: # - managed macros: https://bobswift.atlassian.net/wiki/display/CMSP/Macro+Security+Managed+Macros # # Macro security is enabled/disabled from the UPM configure link for Macro Security for Confluence # Property setting are loaded from the same configuration screen. # # Property settings here can restrict use of some powerful macro capabilities to trusted users # while still allowing non-trusted users to view content created by using these macros # - this is accomplished by requiring those pages using the restricted macros # are controlled by trusted users # - a page is controlled by having edit capabilities restricted to a group # that is identified as being trusted in the properties file # - a page is also considered to be controlled if it is in a permitted space # # Property file # - The property file can be loaded from any file location on the Confluence server or from an attachment # # No specific configuration # - Add-ons can implement their own rules if there is no specific configuration found # depending on what they are restricting # - Standard/default is # - Configuration entry must be specified if the macro is restricted # - *ANY can be used to allow all use (run is an example where *ANY is a likely use) # - Some macros have additional, finer grained (parameter level) restrictions # - these MUST also have something specified, otherwise macro use is not authorized # - Some macros only restrict a specific parameter # - these generally do not have to be specifically configured for use # # Property keys and values are case sensitive # # Property values are a comma separated list of groups or space names # - the page edit restriction must match one of the groups listed # - no other users or groups can be permitted to edit the page # - use *ANY to indicate no restriction # # SQL - https://bobswift.atlassian.net/wiki/display/SQL sql = confluence-administrators sql.datasource.* = confluence-administrators sql.datasource.testDS = *ANY sql.limit = confluence-administrators sql.disableAntiXss = confluence-administrators sql.querytimeout = confluence-administrators sql-query = confluence-administrators sql-query.datasource.* = confluence-administrators sql-query.datasource.testDS = *ANY sql-query.limit = confluence-administrators sql-query.disableAntiXss = confluence-administrators sql-query.querytimeout = confluence-administrators # Live template support - https://bobswift.LiveTemplateSupport sql-query&live-template.datasource.* = *global # Script - https://bobswift.atlassian.net/wiki/display/SCRP beanshell = confluence-administrators groovy = confluence-administrators gant = confluence-administrators jython = confluence-administrators # HTML - https://bobswift.atlassian.net/wiki/display/HTML html = confluence-administrators xslt = confluence-administrators # Run CLI Actions - https://bobswift.atlassian.net/wiki/display/CCLI cli = confluence-administrators cli.profile.* = confluence-administrators cli.product.* = confluence-administrators cli.directory.* = confluence-administrators cli.datasource.* = confluence-administrators include-remote = confluence-administrators include-remote.profile.* = confluence-administrators # Cache - https://bobswift.atlassian.net/wiki/display/CACHE cache = *ANY future = *ANY future.timeout = confluence-administrators # Run - https://bobswift.atlassian.net/wiki/display/RUN run = confluence-administrators run-now = confluence-administrators run.disableAntiXss = confluence-administrators # Advanced Tables - https://bobswift.atlassian.net/wiki/display/TBL csv.url.* = *ANY csv.disableAntiXss = confluence-administrators json-table.url.* = *ANY json-table.disableAntiXss = confluence-administrators # Excel - https://bobswift.atlassian.net/wiki/display/XL excel.url.* = *ANY excel.disableAntiXss = confluence-administrators # Flash - https://bobswift.atlassian.net/wiki/display/FLASH flash.url.* = confluence-administrators # Markdown - https://bobswift.atlassian.net/wiki/display/MARKDOWN markdown.allowHtml = confluence-administrators markdown-attachment.allowHtml = confluence-administrators markdown-url.allowHtml = confluence-administrators # Code Pro - https://bobswift.atlassian.net/wiki/display/CODE code-pro.url = *ANY code-pro.profile.* = confluence-administrators

 

Macro Security Configuration page, after loading the macro-security.properties file:

Image Removed

Adding page restrictions:

Image Removed

Chart macro parameters:
Image Modified

Create Confluence page

Div
classtime

Image Modified

Estimated Time: 2

min

Anchor
step1step1
Code Block
languagetext
themeDJango
titlemacro-security.properties file
linenumberstrue

Create a Confluence page that will eventually contain the macros needed to produce the chart.

  1. Create a page named Sales By Product in a space accessible to the desired audience.
  2. Click the Unrestricted button to open the Page Restrictions screen.
  3. Click the Restrict editing radio button.
  4. Start typing confluence-administrators in the text box and select that group name from the list of suggestions to apply an edit restriction to that group. (info) This restriction is being added so that Macro Security managed macros can be used. You will be configuring Macro Security in a later step.
  5. Click Save to close the Page Restrictions screen.
  6. Click Save to save the new page.

 

 

Anchor
step1
step1
Adding page restrictions:

Image Added


Image Added

Configure the Chart macro

Div
classtime

Image Added Estimated Time: 1 min 

Edit the Sales By Product page and do the following:

  1. Insert the Chart macro.
  2. Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right.
    • The Type parameter indicates to display a pie chart.
    • The Width parameter indicates the width of the chart in pixels.
    • The Height parameter indicates the height of the chart in pixels.
    • The Display rendered data parameter indicates that a data table should be displayed after (below) the chart.
    • The Chart Title parameter provides a title for the chart.
    • The Content Orientation parameter indicates that the data will be derived from the data rows rather than the data columns. You will be defining the data in the next step when you configure the SQL Query macro.
  3. Click Save to save your changes to the Chart macro's parameters.

Anchor
step2
step2
Chart macro parameters:

Typepie
Width (pixel value only)700
Height (pixel value only)500
Display rendered dataafter
Chart TitleSales by Product
Content Orientationvertical



Image Added

Configure the SQL Query macro

Div
classtime

Image Added Estimated Time: 2 min 

This step uses the SQL Query macro, which is part of the SQL for Confluence (Pro Edition) add-on, to retrieve the data that will be displayed in the pie chart. To configure the SQL Query macro, do the following:

  1. With the cursor positioned inside the Chart macro container, insert the SQL Query macro.
  2. Adjust its parameters as shown on the right.
    • The Data source name parameter indicates to use the examplegeardb data source profile that you set up when installing the Examplegear database.
    • The Use database column labels parameter indicates to use the column names defined within the SELECT statement (e.g., Product, Revenue) rather than the columns names defined within the database table(s) themselves.
    • The Show error if there are no rows parameter indicates to show 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.
  3. Click Save to save your changes to the SQL Query macro's parameters.
  4. With the cursor positioned inside the SQL Query macro container, paste in these SQL statements:

    Code Block
    languagesql
    themeDJango
    linenumberstrue
    SELECT P.BaseProductName AS Product, ROUND(SUM(S.SubTotal)) As Revenue
    FROM Sales S
    INNER JOIN LineItems LI ON S.SalesID = LI.SalesID
    INNER JOIN Products P ON LI.ProductID = P.ProductID
    GROUP BY P.BaseProductName
    ORDER BY 2 DESC;

Anchor
step3
step3
SQL Query macro parameters:

Data source nameexamplegeardb
Use database column labelsselected
Show error if there are no rowsselected
Text to display when there are no rowsNo rows selected


Sales By Product page, in Edit mode:

Image Added


Image Added

Configure Macro Security

Div
classtime

Image Added Estimated Time: 2-3 min

Before you save the Sales By Product page, you should install the Macro Security for Confluence add-on (if you haven't do so already) and configure it with a property file that identifies who can run each security-enabled macro. Each macro security enabled macro accepts specific parameters to controls its use. This safeguards your Confluence instance and data from misuse or performance issues caused by inexperienced users or inappropriate use.

Follow these steps to configure it:

  1. Download the sample macro-security.properties file that is configured for moderate restrictions. This restricts most security-enabled macros so that only Administrators can execute them, but allows some others to be used by any user.
  2. Review the file and ensure that the lines beginning with "sql-query.datasource.*", "run" and "run-now" are restricted to the confluence-administrators group as shown in the example configuration file to the right.

  3. Save the file.
  4. In a new tab in your browser, create a Confluence page entitled Macro Security Configuration in a space accessible only to Administrators (or apply page-level restrictions so the page is accessible only to Administrators) and attach the file to it. Take note of the space key of the space. The space key appears in the URL of the page after you've saved, immediately after "display/". For instance, in the URL: http://wiki.examplegear.com/display/admin/Macro Security Configuration, the space key is admin.

  5. Go to Manage Add-ons administration screen, locate the Macro Security add-on and click its Configure button. This will take you to the Macro Security configuration screen.
  6. In the Load Security Configuration section, supply the location of your macro-security.properties file, in the form of spacekey:pagetitle^filename such as admin:Macro Security Configuration^macro-security.properties and click Load.
  7. Select the Enable button and click Save.

Anchor
step4
step4
Example configuration file:

Code Block
languagetext
themeDJango
titlemacro-security.properties file
linenumberstrue
# See the documentation space - https://appfire.atlassian.net/wiki/display/CMSP
# More specifically:
# - managed macros: https://appfire.atlassian.net/wiki/display/CMSP/
Macro+Security+
Managed+
Image Removed

Create Confluence page

Div
classtime

Image Removed Estimated Time: 2 min

Create a Confluence page that will eventually contain the macros needed to produce the chart.

  1. Create a page named Sales By Product in a space accessible to the desired audience.
  2. Click the Unrestricted button to open the Page Restrictions screen.
  3. Click the Restrict editing radio button.
  4. Start typing confluence-administrators in the text box and select that group name from the list of suggestions to apply an edit restriction to that group. (info) This restriction is being added so that Macro Security managed macros can be used.
  5. Click Save to close the Page Restrictions screen.
  6. Click Save to save the new page.

 

 

Anchor
step2step2Image Removed

Configure the Chart macro

Div
classtime

Image Removed Estimated Time: 1 min 

Edit the Sales By Product page and do the following:

  1. Insert the Chart macro.
  2. Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right.
    • The Type parameter indicates to display a pie chart.
    • The Width parameter indicates the width of the chart in pixels.
    • The Height parameter indicates the height of the chart in pixels.
    • The Display rendered data parameter indicates that a data table should be displayed after (below) the chart.
    • The Chart Title parameter provides a title for the chart.
    • The Content Orientation parameter indicates that the data will be derived from the data rows rather than the data columns. You will be defining the data in the next step when you configure the SQL Query macro.
  3. Click Save to save your changes to the Chart macro's parameters.
Anchor
step3step3
Typepie
Width (pixel value only)700
Height (pixel value only)500
Display rendered dataafter
Chart TitleSales by Product
Content Orientationvertical
Image Removed

Configure the SQL Query macro

Div
classtime

Image Removed Estimated Time: 2 min 

This step uses the SQL Query macro, which is part of the SQL for Confluence (Pro Edition) add-on, to retrieve the data that will be displayed in the pie chart. To configure the SQL Query macro, do the following:

  1. With the cursor positioned inside the Chart macro container, insert the SQL Query macro.
  2. Adjust its parameters as shown on the right.
    • The Data source name parameter indicates to use the examplegeardb data source profile that you set up when installing the Examplegear database.
    • The Use database column labels parameter indicates to use the column names defined within the SELECT statement (e.g., Product, Revenue) rather than the columns names defined within the database table(s) themselves.
    • The Show error if there are no rows parameter indicates to show 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.
  3. Click Save to save your changes to the SQL Query macro's parameters.
  4. With the cursor positioned inside the SQL Query macro container, paste in these SQL statements:

    Code Block
    languagesql
    themeDJango
    linenumberstrue
    SELECT P.BaseProductName AS Product, ROUND(SUM(S.SubTotal)) As Revenue
    FROM Sales S
    INNER JOIN LineItems LI ON S.SalesID = LI.SalesID
    INNER JOIN Products P ON LI.ProductID = P.ProductID
    GROUP BY P.BaseProductName
    ORDER BY 2 DESC;
Anchor
step4step4
Data source nameexamplegeardb
Use database column labelstrue
Show error if there are no rowstrue
Text to display when there are no rowsNo rows selected

Sales By Product page, in Edit mode:

Image Removed

   
Macros
#
# Macro security is enabled/disabled from the UPM configure link for Macro Security for Confluence
# Property setting are loaded from the same configuration screen.
#
# Property settings here can restrict use of some powerful macro capabilities to trusted users
#   while still allowing non-trusted users to view content created by using these macros
# - this is accomplished by requiring those pages using the restricted macros
#   are controlled by trusted users
#   - a page is controlled by having edit capabilities restricted to a group
#     that is identified as being trusted in the properties file
#   - a page is also considered to be controlled if it is in a permitted space
#
# Property file
# - The property file can be loaded from any file location on the Confluence server or from an attachment
#
# No specific configuration
# - Add-ons can implement their own rules if there is no specific configuration found
#   depending on what they are restricting
# - Standard/default is
#   - Configuration entry must be specified if the macro is restricted
#     - *ANY can be used to allow all use (run is an example where *ANY is a likely use)
#   - Some macros have additional, finer grained (parameter level) restrictions
#     - these MUST also have something specified, otherwise macro use is not authorized
#   - Some macros only restrict a specific parameter
#     - these generally do not have to be specifically configured for use
#
# Property keys and values are case sensitive
#
# Property values are a comma separated list of groups or space names
# - the page edit restriction must match one of the groups listed
# - no other users or groups can be permitted to edit the page
# - use *ANY to indicate no restriction
#

# SQL - https://appfire.atlassian.net/wiki/display/SQL
sql = confluence-administrators
sql.datasource.* = confluence-administrators
sql.datasource.testDS = *ANY
sql.limit = confluence-administrators
sql.disableAntiXss = confluence-administrators
sql.querytimeout = confluence-administrators

sql-query = confluence-administrators
sql-query.datasource.* = confluence-administrators
sql-query.datasource.testDS = *ANY
sql-query.limit = confluence-administrators
sql-query.disableAntiXss = confluence-administrators
sql-query.querytimeout = confluence-administrators

# Live template support - https://appfire.atlassian.net/wiki/display/CMSP/Live+Template+Support
sql-query&live-template.datasource.* = *global

# Script - https://appfire.atlassian.net/wiki/display/SCRP
beanshell = confluence-administrators
groovy = confluence-administrators
gant = confluence-administrators
jython = confluence-administrators

# HTML - https://appfire.atlassian.net/wiki/display/HTML
html = confluence-administrators
xslt = confluence-administrators

# Run CLI Actions - https://appfire.atlassian.net/wiki/display/CCLI
cli = confluence-administrators
cli.profile.* = confluence-administrators
cli.product.* = confluence-administrators
cli.directory.* = confluence-administrators
cli.datasource.* = confluence-administrators

include-remote = confluence-administrators
include-remote.profile.* = confluence-administrators

# Cache - https://appfire.atlassian.net/wiki/display/CACHE
cache = *ANY
future = *ANY
future.timeout = confluence-administrators

# Run - https://appfire.atlassian.net/wiki/display/RUN
run = confluence-administrators
run-now = confluence-administrators
run.disableAntiXss = confluence-administrators

# Advanced Tables - https://appfire.atlassian.net/wiki/display/TBL
csv.url.* = *ANY
csv.disableAntiXss = confluence-administrators
json-table.url.* = *ANY
json-table.disableAntiXss = confluence-administrators

# Excel - https://appfire.atlassian.net/wiki/display/XL
excel.url.* = *ANY
excel.disableAntiXss = confluence-administrators

# Flash - https://appfire.atlassian.net/wiki/display/FLASH
flash.url.* = confluence-administrators

# Markdown - https://appfire.atlassian.net/wiki/display/MARKDOWN
markdown.allowHtml = confluence-administrators
markdown-attachment.allowHtml = confluence-administrators
markdown-url.allowHtml = confluence-administrators

# Code Pro - https://appfire.atlassian.net/wiki/display/CODE
code-pro.url = *ANY
code-pro.profile.* = confluence-administrators

 

Macro Security Configuration page, after loading the macro-security.properties file:

Image Added


Save and test the page

Div
classtime

  Estimated Time: 1-2 min 

Click Save to save Now, go back to the Sales by Product page and click Save to save it. You should now see a page that appears as shown on the right. The pie chart is shown, with the data table appearing after it.

If you see an error such as "Error rendering macro 'sql-query' : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException", then check the syntax of the SELECT statement within the SQL Query macro container.

If you see an error such as "Error rendering macro 'sql-query' : Unable to connect to database", then verify that the SQL Query macro's Data source name parameter is set to the correct value and that the examplegeardb data source profile was set up per the installation instructions.

 

 

Anchor
step5
step5
Sales by Product page:

...