Scenario
| Div | ||
|---|---|---|
| ||
|
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:
| 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 |
+----+------+-------------------+-----------------+---------+ |
Our example query against 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
The following steps will demonstrate how to use Markup to insert the SQL Macro on 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 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:
...
Type the follwing 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:
...
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.
| Include Page | ||||
|---|---|---|---|---|
|