Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Scenario

Div
classtime

Image Removed 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
languagesql
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
languagesql
 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:

...

border0
heading0
multiplefalse
enableHeadingAttributesfalse
columnAttributesstyle="border:0;width=5%;",style="border:0;width=65%;",style="border:0;width=30%;max-width=30% !important;"
idsteps_table
enableSortingfalse
enableHighlightingfalse

...

Create a Confluence page

Create a Confluence page that contains a SQL macro.

  1. Create a page named Top Publishers Worldwide in a space accessible to the desired audience.
  2. 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
languagesql
 {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:

  1. On the page, begin typing "{" (open parenthesis) and then type "sql."
  2. Select "sql" from the available list of macros.
  3. The macro editor dialog will appear.
  4. Select "BookWarehouse" as the data source profile.
  5. Type your SQL statement into the SQL statement field.
  6. Click Save settings to insert your Markup within the page.

  7. Click Save to save your page and view your results.

...

How to SQL macro using Insert markup menu

Image Removed

Alternative method:

Image Removed

Image Removed

Include Page
SUPPORT:Use SQL Macro with Markup - 10.x
SUPPORT:Use SQL Macro with Markup - 10.x