Image Modified

Create Confluence page

Create a Confluence page that will eventually contain the macros needed for the dashboard.

1. Create a page named BI Dashboard 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. 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.

Configure the AUI Tabs Container and AUI Tabs Page macros

In this step, you will be creating a 3-tabbed section on the page.

Edit the BI Dashboard page and do the following:

1. Insert the AUI Tabs Container macro, which is a premium (paid) macro of the Content Formatting Macros add-on by Adaptavist.
2. Click Insert to close the Insert Macro screen. No changes are needed to its parameters, as all default values are sufficient. 
3. Next you need to position the cursor within the AUI Tabs Container macro container and insert 3 occurrences of the AUI Tabs Page macro, which is another premium (paid) macro of the Content Formatting Macros add-on by Adaptavist. When doing each insert, set the macro parameters as shown to the right and then click Insert to save your changes and insert the macro.
4. Your page should look like the first screenshot shown to the right.
5. Click Save to save the new page in its present state. You should be able to see the 3 tabs shown in the second screenshot to the right.

AUI Tabs Page macro parameters (2nd occurrence):

AUI Tabs Page macro parameters (3rd occurrence):

BI Dashboard page, in Edit mode:

Configure the Sales by Product tab

1. If you haven't already completed the How to Create a Self-Service Report From SQL Data recipe, do so now so that you have a Monthly Sales by Product page that contains a Run with a user form and parameters macro, a Chart macro, and a SQL Query macro.
2. Edit the BI Dashboard page and do the following:
   1. Position the cursor inside the first AUI Tabs Page macro container that has the title "Sales by Product" and insert the Include Page macro.
   2. Set its parameters as shown to the right.
   3. Click Insert to save your changes and insert the macro.
3. Your page should look like the first screenshot shown to the right.
4. Click Save to save the new page in its present state.
5. You should be able to see the bar chart on the Sales by Product tab, as shown in the second screenshot to the right.

BI Dashboard page, in Edit mode:

Form generated by Run macro:

Configure the Sales by Country tab (part 1)

The Sales by Country tab will use the Run with a user form and parameters macro, the Chart Macro, and 3 SQL Query macros.

In this first of 3 steps to configure this tab, edit the BI Dashboard page and do the following:

1. Position the cursor inside the second AUI Tabs Page macro container that has the title "Sales by Country" and insert the Run with a user form and parameters macro.
2. Click the Run with a user form and parameters macro container and click Edit so you can adjust its parameters as shown on the right.
   1. The Replace field list parameter indicates to display the following 2 fields as selection criteria:

      1. The first field is a single select list that allows the user to select the Product. Each string within this field is delimited with a colon (:) except for the final one, which ends with a comma (,) to signify the end of the field.
         
         Field 1:

         Div
         style max-width: 600px;
         String Description country: This is the key, which is "country." In a later step, you will refer to this field as "$country" so its value can be substituted. United Statest: This is the value, which Indicates the initial value for the country field. Select a Country: This is the description, which indicates how the country field will be labelled when shown on-screen. select: This is the field type, indicating it should be a single select list. 1: This is the number of rows that should be visible in the select list before the user clicks the dropdown arrow. AU:Australia: through US: United Stated There is a key:value: pair for each country. The first string (e.g., "AU") defines the replacement value in the Country select list. The second string (e.g., "Australia") defines the text that is displayed to the user for that entry in the select list.

         
         

      2. The second field is a single select list that allows the user to select the year for which sales data should be selected. As with the first field, each string within this field is delimited with a colon (:) except for the final one, which needs nothing following it since it is the last field.
         
         Field 2:

         Div
         style max-width: 600px;
         String Description year: This is the key, which is "year." In a later step, you will refer to this field as ""$year" so its value can be substituted. 2014: This is the value, which Indicates the initial value for the year field. Select a Year This is the description, which indicates how the year field will be labelled when shown on-screen. integer-select This is the field type, indicating it should be a select list of integers. 2012: This indicates the lower bound of the Year select list. 2015 "2012" indicates the upper bound of the Year select list. Along with the lower bound, this means that the Year select list will list 2012, 2013, 2014 and 2015.

         
         

   2. The Automatically render body on display parameter indicates to automatically run the report when the Confluence page is rendered.
   3. The Field name to receive focus parameter indicates the field in which the cursor will be positioned when the Confluence page is initially displayed. This is set to the first field (Select a Product) to facilitate the user's selection for this field.
3. Click Save to save your changes to the Run macro's parameters.
4. Your page should now look like the screenshot shown on the right.
5. If you click Preview and then select the Sales by Country tab, you should see a Run form as shown on the right.

 BI Dashboard page, in Edit mode:

Form generated by Run macro:

Image Modified

Configure the Sales by Country tab (part 2)

In this second of 3 steps to configure this tab, edit the BI Dashboard page and do the following:

1. Position your cursor inside the Run with a user form and parameters macro container and insert a Chart macro.
2. Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right.
   1. The Type parameter indicates to display a Time Series chart.
   2. The Width parameter indicates the width of the chart in pixels.
   3. The Height parameter indicates the height of the chart in pixels.
   4. The Chart Title parameter provides a title for the chart, with user-selected values substituted for $year and $country.
   5. The Columns parameter indicates to use the first two columns of the SQL data as the data for the Time Series chart. You will be defining the data in the next step when you configure the SQL Query macro.
   6. The Content Orientation parameter indicates to use the columns (as opposed to the rows) of the SQL data as the series.
   7. The Date format parameter indicates to display the dates in the SQL data as a 2-digit month followed by a 4-digit year, e.g, 01/2014.
   8. The Time period parameter indicates to show the time series as months rather than weeks, quarters, years, etc.
3. Click Save to save your changes the Chart macro's parameters.
4. Your page should now look like the screenshot shown on the right.

 Excerpt of BI Dashboard page, in Edit mode:

Configure the Sales by Country tab (part 3)

This is the last of 3 steps to configure this tab.

The data to appear in the Time Series chart is one line for each of Examplegear, Inc.'s 3 products. Therefore, you will need to have one SQL Query macro per product to grab the appropriate data.

Edit the BI Dashboard page and do the following:

1. Position your cursor inside the Chart macro container and insert three SQL Query macros. For each,
   
   1. Adjust its parameters as shown on the right.
      1. The Data source name parameter indicates to use the examplegeardb data source profile that you set up when installing the Examplegear database.
      2. 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.
      3. 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.
   2. Click Insert to save your changes and insert the SQL Query macro.

2. Position the cursor inside the first SQL Query macro and copy in these SQL statements:

   Code Block
   language sql theme DJango linenumbers true
   SELECT DATE_FORMAT(S.OrderDate, '%m/%Y') AS Month, ROUND(SUM(S.SubTotal)) As 'Demoware Revenue' FROM Sales S INNER JOIN LineItems LI ON S.SalesID = LI.SalesID INNER JOIN Products P ON LI.ProductID = P.ProductID INNER JOIN Addresses A ON S.CustomerID = A.CustomerID AND S.BillToAddressID = A.AddressID WHERE YEAR(S.OrderDate) = $year AND A.CountryCode = '$country' AND P.BaseProductName = 'Demoware' GROUP BY 1 ORDER BY 1;

   
   

3. Position the cursor inside the second SQL Query macro and copy in these SQL statements:

   Code Block
   language sql theme DJango
   SELECT DATE_FORMAT(S.OrderDate, '%m/%Y') AS Month, ROUND(SUM(S.SubTotal)) As 'Samplesoft Revenue' FROM Sales S INNER JOIN LineItems LI ON S.SalesID = LI.SalesID INNER JOIN Products P ON LI.ProductID = P.ProductID INNER JOIN Addresses A ON S.CustomerID = A.CustomerID AND S.BillToAddressID = A.AddressID WHERE YEAR(S.OrderDate) = $year AND A.CountryCode = '$country' AND P.BaseProductName = 'Samplesoft' GROUP BY 1 ORDER BY 1;

   
   

4. Position the cursor inside the third SQL Query macro and copy in these SQL statements:

   Code Block
   language sql theme DJango linenumbers true
   SELECT DATE_FORMAT(S.OrderDate, '%m/%Y') AS Month, ROUND(SUM(S.SubTotal)) As 'Usecaser Revenue' FROM Sales S INNER JOIN LineItems LI ON S.SalesID = LI.SalesID INNER JOIN Products P ON LI.ProductID = P.ProductID INNER JOIN Addresses A ON S.CustomerID = A.CustomerID AND S.BillToAddressID = A.AddressID WHERE YEAR(S.OrderDate) = $year AND A.CountryCode = '$country' AND P.BaseProductName = 'Usecaser' GROUP BY 1 ORDER BY 1;

   
   

5. The page should now look like the first screenshot shown to the right.
6. Click Save to save the new page in its present state.
7. You should be able to see the Time Series chart on the Sales by Country tab, as shown in the second screenshot to the right.

 BI Dashboard page, in Edit mode:

Configure the Yearly Sales tab

The Yearly Sales tab will use the Chart Macro and the SQL Query macro.

Edit the BI Dashboard page and do the following:

1. Position the cursor inside the third AUI Tabs Page macro container that has the title "Yearly Sales" and insert the Chart macro.
2. Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right.
   1. The Type parameter indicates to display a Bar chart.
   2. The Width parameter indicates the width of the chart in pixels.
   3. The Height parameter indicates the height of the chart in pixels.
   4. The Chart Title parameter provides a title for the chart.
   5. The Columns parameter indicates to use the first four columns of the SQL data as the data for the bar chart. You will be defining the data in the next step when you configure the SQL Query macro.
   6. The Content Orientation parameter indicates to use the columns (as opposed to the rows) of the SQL data as the series.
   7. Click Save to save your changes to the Chart macro's parameters.
3. Position the cursor inside the Chart macro container and insert the SQL Query macro.
   1. Adjust its parameters as shown on the right.
      1. The Data source name parameter indicates to use the examplegeardb data source profile that you set up when installing the Examplegear database.
      2. 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.
      3. 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.
   2. Click Insert to save your changes and insert the SQL Query macro.

   3. Position the cursor inside the SQL Query macro and copy in these SQL statements:

      Code Block
      language sql theme DJango linenumbers true
      SELECT YEAR(S.OrderDate) AS Year, SUM(CASE WHEN P.BaseProductName = 'Demoware' THEN S.SubTotal END) AS 'Demoware Revenue', SUM(CASE WHEN P.BaseProductName = 'Samplesoft' THEN S.SubTotal END) AS 'Samplesoft Revenue', SUM(CASE WHEN P.BaseProductName = 'Usecaser' THEN S.SubTotal END) AS 'Usecaser Revenue' FROM Sales S INNER JOIN LineItems LI ON S.SalesID = LI.SalesID INNER JOIN Products P ON LI.ProductID = P.ProductID GROUP BY 1 ORDER BY 1;

      
      

4. Your page should now look like the first screenshot shown on the right.
5. If you click Preview, you should be able to see the bar chart on the Sales by Product tab, as shown in the second screenshot to the right.

SQL Query macro parameters (applies to all 3 occurrences):

Excerpt of BI Dashboard page, in Edit mode:

Expand
title View the BI Dashboard page in View mode...

Tune the page for appearance

To add some white space between the bottom of each tab and the Run form, continue editing the BI Dashboard page and do the following:

1. Position the cursor at the bottom of the page (outside any macro container) and insert the CSS Stylesheet macro.
2. Position the cursor inside the CSS Stylesheet macro container and insert the CSS shown to the right.
3. Click Save to save the new page in its present state.
4. You should be able to see some white space above the Run forms, as shown in the second screenshot to the right.

.aui-tabs.horizontal-tabs > .tabs-menu ~ .tabs-pane {
    padding-top: 15px;
}

Run form, after inserting the empty paragraph:

Tune the page for performance (part 1)

To ensure your business intelligence dashboard renders as quickly as possible, you will now wrap the contents of each tab inside a Future and Cache macro.

In this first of 3 steps to accomplish this, edit the BI Dashboard page and do the following:

1. With the cursor positioned within the first AUI Tabs Page macro container that has the title "Sales by Product":
   1. Insert the Future macro.
   2. You do not need to adjust its parameters as all default settings are appropriate for this first tab.
2. With the cursor positioned within the second AUI Tabs Page macro container that has the title "Sales by Country":
   
   1. Insert the Future macro.
   2. Click the Future macro container and click Edit so you can adjust its parameters as shown on the right.
      1. The Delay in seconds parameter indicates to delay the rendering of its content by the number of seconds requested. This allows the first tab (with no delay) to render right away and have the other 2 tabs render after that.
   3. Click Save to save your changes to the Future macro's parameters.
3. With the cursor position within the third AUI Tabs Page macro container that has the title "Yearly Sales":
   1. Insert the Future macro.
   2. Click the Future macro container and click Edit so you can adjust its parameters as shown on the right.
   3. Click Save to save your changes to the Future macro's parameters.
4. Your page should now look like the screenshot shown to the right.
5. Complete part 2 and part 3 of this step before saving the page.

Excerpt of BI Dashboard page, in Edit mode:

Tune the page for performance (part 2)

In this second of 3 steps to accomplish this, continue editing the BI Dashboard page and do the following:

1. With the cursor positioned within the Future macro you added inside the first AUI Tabs Page macro container that has the title "Sales by Product":
   1. Insert the Cache macro.
   2. Click the Cache macro container and click Edit so you can adjust its parameters as shown on the right.
      1. The Cache refresh period parameter indicates that the cache will be refreshed the next time the page is viewed after the refresh period (1 day.
      2. The Check attachments parameter indicates that the cache does not need to be refreshed if there is any change in the attachments on the page.
      3. The Show refresh icon parameter indicates to display a refresh icon, in case the user wants to force an immediate refresh.
      4. The Show date parameter indicates to display the date the cache was last updated.
      5. The Date format parameter provides the format used to display the date the cache was last updated.
   3. Click Save to your changes to the Cache macro's parameters.
2. With the cursor positioned within the Future macro you added inside the second AUI Tabs Page macro container that has the title "Sales by Country":
   
   1. Insert the Cache macro.
   2. Click the Cache macro container and click Edit so you can adjust its parameters as shown on the right.
   3. Click Save to your changes to the Cache macro's parameters.
3. With the cursor positioned within the Future macro you added inside the third AUI Tabs Page macro container that has the title "Yearly Sales":
   
   1. Insert the Cache macro.
   2. Click the Cache macro container and click Edit so you can adjust its parameters as shown on the right.
   3. Click Save to your changes to the Cache macro's parameters.
4. Your page should now look like the screenshot shown to the right.
5. Complete part 3 of this step before saving the page.

Excerpt of BI Dashboard page, in Edit mode…

Tune the page for performance (part 3)

In this last of 3 steps to tune the page for performance, continue editing the BI Dashboard page and do the following:

1. In the first AUI Tabs Page macro container that has the title "Sales by Product":
   1. Click on the Include Page macro container and drag it into the Cache macro container. This causes the rendering of its content to now be controlled by the Future and Cache macros.
2. In the second AUI Tabs Page macro container that has the title "Sales by Country":
   1. Click on the Run with a user form and parameters macro container and drag it into the Cache macro container. This causes the rendering of its content to now be controlled by the Future and Cache macros.
3. In the third AUI Tabs Page macro container that has the title "Yearly Sales":
   1. Click on the Chart macro container and drag it into the Cache macro container. This causes the rendering of its content to now be controlled by the Future and Cache macros.
4. The page should now look as it is shown on the first screenshot to the right.
5. Click Save to save the page.
6. When the page is in the process of being rendered, you will see the "Loading" message shown in the second screenshot to the right.
7. When the rendering is complete, you will see the Refresh icon above the Run form as shown in the third screenshot to the right, indicating the date on which the cache was last refreshed. A user can click this icon to force the cache to be immediately refreshed.

Cache macro parameters:

Excerpt of BI Dashboard page, in Edit mode…

Excerpt of BI Dashboard page, in View mode, while page is being rendered:

Excerpt of BI Dashboard page, in View mode, after page is fully rendered:

Configure Macro Security

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 restricted properties. This restricts most security-enabled macros so that only Administrators can execute them.

2. Review the file and ensure that the line beginning with "sql-query.datasource.*" is 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.
8. The BI Dashboard page should work exactly as before except that now only members of the confluence-administrators group can edit the page and the macros on the page will not function if the page restriction allowing editing by the confluence-administrators group is removed.

# 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.atlassian.net/wiki/display/CMSP/Live+Template+Support
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: