Scenario
Div | ||
---|---|---|
| ||
Estimated Time: 8 min |
In this scenario, we will add the SQL macro on a Confluence page by inserting Markup from the insert more content menu. SQL macro will reference the statements entered in the macro body.
Background
In this scenario will use a data source profile called "BookWarehouse" to query a database table called "PUBLISHERS". The table contains the following columns and rows:
Code Block | ||
---|---|---|
| ||
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:
Code Block | ||
---|---|---|
| ||
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:
...
border | 0 |
---|---|
heading | 0 |
multiple | false |
enableHeadingAttributes | false |
columnAttributes | style="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;" |
id | steps_table |
enableSorting | false |
enableHighlighting | false |
...
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.
...
Insert the SQL macro using the Insert markup menu
Edit the Top Publishers Worldwide page and:
...
Type the following into the editor window. Note the dataSource name referenced.
Code Block | ||
---|---|---|
| ||
{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} |
...
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.
...
How to SQL macro using Insert markup menu
Alternative method:
Include Page | ||||
---|---|---|---|---|
|