Use SQL Macro with Markup - 11.x

Scenario

Estimated Time: 8 min

In this scenario, add the SQL macro on a Confluence page by inserting Markup from the Insert more content menu. The SQL macro references the statements entered in the macro body.

Background

In this scenario, use a data source profile, BookWarehouse, to query a database table, 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 |
+----+------+-------------------+-----------------+---------+

The illustrative script for querying 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

Follow the steps below to insert a SQL macro into a Confluence page:

Create a Confluence page

Create a Confluence page that contains a SQL macro.

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

2. 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 following 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 include your Markup within the page.
Click Save to save your page and view your results.

How to SQL macro using the Insert markup menu

Alternative method

Edit the Top Publishers Worldwide 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 profile.
Type your SQL statement into the SQL statement field.
Click Save settings to insert your Markup within the page.
Click Save to save your page and view your results.