Scenario

Estimated Time: 8 min

In this scenario, we will insert the SQL Macro on a Confluence page by inserting Markup from the menu. Our SQL Macro will reference the SQL statement loaded into the body of the Macro.

Background

Our scenario will use a Data Source Profile called "BookWarehouse" to query a database table called "PUBLISHERS". The table contains the following columns and rows:

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 |
+----+------+-------------------+-----------------+---------+

Our example query against the PUBLISHERS table looks like:

 SELECT rank as "Worldwide Ranking", company_name as "Company", format(revenue,0) as "M Revenue" FROM publishers ORDER BY rank asc;

Steps

The following steps will demonstrate how to use Markup to insert the SQL Macro on a Confluence page.

Create Confluence page

Create a Confluence page that will eventually contain the SQL Macro needed to list out top publishers.

Create a page named Top Publishers Worldwide in a space accessible to the desired audience.
Click Save to save the new page.

Insert the SQL Macro using the Insert Markup Menu

Edit the Top Publishers Worldwide page and:

Select Insert Markup from the top menu.

Type the follwing into the editor window. Note the dataSource name referenced.

 {sql:dataSource=BookWarehouse}
 SELECT rank as "Worldwide Ranking", company_name as "Company", 
 format(revenue,0) as "M  Revenue" FROM publishers ORDER BY rank asc;
 {sql}

Click Insert to insert your Markup within the page.
Click Save to save your page and view your results.

Alternative:

Insert the SQL Macro using Markup on page

Edit the Classic Books page and:

On the page, begin typing "{" (open parenthesis) and then type "sql"
Select "sql" from the available list of Macros.
The Macro editor dialog will appear.
Select "BookWarehouse" as the Data source name
Type your SQL statement into the body of the Macro block.
Click Insert to insert your Markup within the page.
Click Save to save your page and view your results.

How to insert Markup from the menu

Explore Tutorials

We've prepared a number of tutorials which demonstrate how to use our SQL Macros with the native Chart Macro.

Using the SQL Macro with Markup