/
Using the SQL macro with Markup - 9.x and earlier

Using the SQL macro with Markup - 9.x and earlier

Feb 24, 2020

Scenario

Estimated Time: 8 min

In this scenario, let's insert the SQL macro on a Confluence page by inserting Markup from the menu. The SQL macro uses the SQL statement loaded into the body of the macro.

Background

This scenario uses 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 | +----+------+-------------------+-----------------+---------+

For information about data source profiles, refer to:

The sample 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 demonstrates how to use Markup to insert the SQL macro on a Confluence page:

Create Confluence page

Create a Confluence page to add the SQL macro needed to list out top publishers.

  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:

  1. Select Markup from the top menu.

  2. Type the following into the editor window: (Note the data source 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}

  3. Click Insert to insert your Markup within the page.

  4. 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:

  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 appears.

  4. Select BookWarehouse as the Data source name.

  5. Type your SQL statement into the body of the macro block.

  6. Click Insert to insert your Markup within the page.

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

How to insert Markup from the menu




Alternate method:

Explore Tutorials

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