---
title: "How to Automatically Create a Formatted Table From SQL Data"
canonical: "https://support.appfire.com/space/SUPPORT/89138977/How%20to%20Automatically%20Create%20a%20Formatted%20Table%20From%20SQL%20Data"
format: markdown
---
> Macro (div)
>
> ### On this page
>
> > Macro (toc)
> Macro (include)
# Recipe overview
This recipe provides step-by-step instructions for building a formatted table based on a SQL data source. It allows you to transform raw data into attractive and compelling information. This recipe utilizes the following items:
- The SQL for Confluence add-on, which comes in both [Express](https://marketplace.atlassian.com/plugins/org.swift.confluence.sqlexpress) and [Pro](https://marketplace.atlassian.com/plugins/org.swift.confluence.sql) editions, to query a SQL database to retrieve the data to be presented in the chart.
- The [Cache for Confluence add-on](https://marketplace.atlassian.com/plugins/org.swift.confluence.cache) to ensure the rendering of the page occurs as quickly as possible.
- The [Macro Security for Confluence add-on](https://marketplace.atlassian.com/plugins/org.swift.confluence.macrosecurity) 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 10-12 minutes to complete this recipe. [Jump to Step 5](#HowtoAutomaticallyCreateaFormattedTableFromSQLData-step5) to see a preview of the final result!
# Recipe level
> Macro (table-plus)
>
> | |
> | --- |
> |  |
# Ingredients
> Macro (table-plus)
>
> | | |
> | --- | --- |
> |  | [Confluence](https://bobswift.atlassian.net/wiki/spaces/recipes/pages/103613154/Confluence+Recipes) |
> |  | [Cache for Confluence](https://bobswift.atlassian.net/wiki/spaces/recipes/pages/106660166/Cache+for+Confluence+Recipes)
> Macro (legacy-content)
> Macro (legacy-content) |
> |  | [Macro Security for Confluence](https://bobswift.atlassian.net/wiki/spaces/recipes/pages/104400285/Macro+Security+for+Confluence+Recipes)
> Macro (legacy-content)
> Macro (legacy-content) |
> |  | [SQL for Confluence (Pro Edition)](https://bobswift.atlassian.net/wiki/spaces/recipes/pages/106660111/SQL+for+Confluence+Recipes)
> Macro (legacy-content)
> Macro (legacy-content) |
# Preparation
- Install [MySQL and the sample Examplegear Database](https://appfire.atlassian.net/wiki/spaces/recipes/pages/72810740), including defining the "examplegeardb" data source profile.
# Steps
> Macro (table-plus)
>
> | | | |
> | --- | --- | --- |
> |  | ## Create Confluence page
> Macro (legacy-content)
In this step, you will create a Confluence page that will eventually contain the macros needed to produce the table.
1. Create a page named **Products** 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.
> 5. Click **Save** to close the Page Restrictions screen.
> 6. Click **Save** to save the new page. | > Macro (anchor)
>
> **Adding page restrictions:**
 |
> | --- |
> |  | ## Configure the SQL Query macro
> Macro (legacy-content)
In this step, you will insert and configure the SQL Query macro, which is part of the [SQL for Confluence (Pro Edition)](https://marketplace.atlassian.com/plugins/org.swift.confluence.sql) add-on, to retrieve and format your data.
Edit the **Products** page and do the following:
1. 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, Sell Start Date, Status) 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.
> - The **Row styles** parameter provides the [CSS](https://en.wikipedia.org/wiki/Cascading_Style_Sheets) to format the table rows. This parameter is a comma separated list of styles, each of which can contain one or more properties. The first (and only) style is colored in blue to the right. Since only one style has been specified, it will be applied to the heading row. It formats it with a gray shaded background and a thick border around it.
> - The **Column styles** parameter provides the [CSS](https://en.wikipedia.org/wiki/Cascading_Style_Sheets) to format the table columns. This parameter is also a comma separated list of styles, each of which can contain one or more properties. The first style is colored in blue to the right, and it formats the first column with a yellow shaded background and a thick border. The second style is colored in green, and it formats the second column with a thick border but no shaded background. The third style is colored in red, and it indicates to apply the same style as was specified for the second column.
> 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 the SQL statements.
>
> 1. All three columns in the resultset are specified with an alternate column name, e.g., Product, Sell Start Date and Status.
> 2. The third column uses a CASE statement to turn the DiscontinuedDate into a [Status macro](https://confluence.atlassian.com/display/DOC/Status+Macro) indicating the product is active (if the DiscontinuedDate is null) or discontinued (if the DiscontinuedDate is not null). Note that the parameter names for this macro are case-sensitive. | > Macro (anchor)
>
> **SQL Query macro parameters:**
> Macro (nested-table)
**Products page, in Edit mode:**
 |
> | --- |
> |  | ## Configure the Cache macro
> Macro (legacy-content)
This step uses the Cache macro, which is part of the [Cache for Confluence](https://marketplace.atlassian.com/plugins/org.swift.confluence.cache) add-on, to ensure the page will render as quickly as possible. Although there is a small amount of data being retrieved in this recipe, it is a best practice to use the Cache macro to guard against slow load times and provide a better user experience when users view this page.
To configure the Cache macro, do the following:
1. With the cursor positioned above the SQL Query macro, insert the **Cache** macro.
> 2. Click the Cache macro container and choose Edit to adjust its parameters as shown on the right.
> - The **Cache refresh period** parameter indicates that viewers will view a cached version of this page for 2 days. The first user viewing the page after that will trigger the refreshing of the cache.
> - The **Check attachments** parameter indicates says that there is no need to refresh the cache if any file attachments to the page are changed.
> - The **Show refresh icon** parameter indicates to show an icon that a user can click to force an immediate refresh of the cache, without waiting for its expiration and automatic refresh after 2 days.
> - The **Show date** parameter indicates to display the date the cache was last refresh.
> - The **Date format** parameter indicates the format in which to show the date the cache was last refreshed.
> 3. Click **Save** to save your changes to the Cache macro's parameters.
> 4. Next, click the SQL Query macro container and drag it into the Cache macro container.
> 5. Your Products page, in Edit mode, should now look like the screenshot shown to the right. | > Macro (anchor)
>
> **Cache macro parameters:**
> Macro (nested-table)
**Products page, in Edit mode:**
 |
> | --- |
> |  | ## Configure Macro Security
> Macro (legacy-content)
Before you save the **Products** page after adding the SQL Query and Cache macros, you should [install](https://appfire.atlassian.net/wiki/spaces/CMSP/pages/67405324) 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](https://appfire.atlassian.net/wiki/spaces/CMSP/pages/67404857) 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](https://appfire.atlassian.net/wiki/spaces/CMSP/pages/67404400) 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 most of the lines beginning with "sql" and "sql-query*" 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](https://confluence.atlassian.com/display/DOC/Page+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](http://wiki.examplegear.com/display/admin/Macro%20Security%20Configuration), 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**. | > Macro (anchor)
>
> **Example configuration file:**
##### **macro-security.properties file**
```plaintext
> # 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+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.timtout = confluence-administrators
>
> # Run - https://appfire.atlassian.net/wiki/display/RUN
> run = *ANY
> run-now = *ANY
> 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:**
 |
> | --- |
> |  | ## Save and test the page
> Macro (legacy-content)
Now, go back to the **Products** page and click **Save **to save it. You should now see a page that appears as shown on the right. The Refresh icon and date are shown, with the table appearing below 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](https://appfire.atlassian.net/wiki/pages/createpage.action?spaceKey=support&title=Building%20the%20Examplegear%20Database&linkCreation=true&fromPageId=89138977). | > Macro (anchor)
>
> **Products page, in View mode:**
 |
>
> > ℹ️ > Macro (legacy-content)
> > ℹ️
> > ℹ️ > Macro (style)
> Macro (include)