Page Comparison

...

Scenario

Div

class	time

Estimated Time: 1011 min

In this scenario, we need to add the SQL File macro to a Confluence page by inserting Markup from the menu. The SQL File macro references a file located within the <confluence-home>/script (default) directory on the Confluence serverAs an example, let's create a 3-D bar chart to display the top publishers by revenue (in $M).

Background

In this Our scenario , use uses a data source profile, BookWarehouse, to query a database table, BOOKSPUBLISHERS. The table contains the following columns and rows:

Code Block

language	sql

SQL > select * from bookspublishers;
+----+------+----------------------+-------------------+--+-------+
| id | titlerank               | parent_company       | authorcompany_name              | pricerevenue |
+----+---------+-------------------+-----------------+----+-------+
| 10 1 | The1 Grapes of Wrath        | John SteinbeckPearson      | 12.99 | |  2 | NineteenPearson Eighty-Four       | George Orwell       |  85000.9900 |
| 11 3 | The2 Wind-Up Bird Chronicle | HarukiReed  Murakami     |  7.99 | |  4 | DonRELXGroup Quixote                | Miguel De Cervantes | 29.99| 4900.00 |
| 12 5 | Pilgrims Progress      3    | John Bunyan         | 17.99 |
|  6 | Robinson Crusoe            | Daniel Defoe        | 29.05WoodbridgeCompany | ThompsonReuters | 4700.00 |
|  713 | Gullivers4 Travels          | JonathanBertelsmannAG-NV Swift      | 15.98Bertelsmann | |  8 | Tom Jones                  | Henry Fielding      | 13.594500.00 |
| 14 9 | Clarissa  5                 | SamuelWoltersKluwer Richardson   | 13.59 |
| 10 | Tristram Shandy        | WoltersKluwer    | Laurence Sterne     |  7.594100.00 |
+----+---------+-------------------+-------------------+--+-------+

The statement is present in the books.sql file and is located within the <confluence-home>/script (default) directory on the Confluence server. The statement is as followsOur query against the PUBLISHERS table looks like:

Code Block

language	sql

SELECT titlecompany_name as "TitlePublisher", author as "Author", price CONCAT('$',format(revenue,0)) as "RetailM PriceRev" FROM books WHERE price < 30publishers ORDER BY rank titleasc;

Steps

The following steps demonstrate the steps involved in inserting a SQL File how to use Markup to insert Confluence's native Chart Macro and our SQL macro on a Confluence page:

Table plus

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

How to Insert markup from the menu

Image Removed

Alternative method:

Image Removed

Create a Bar Chart using SQL macro - 10.x

Image Removed Create a Bar Chart using SQL macro - 10.x

Create a Confluence page

Create a Confluence page that

contains a SQL File macro

will eventually contain the macros needed to produce the chart.

Create a page named Classic Books Top Publishers Worldwide in a space accessible to the desired audience.
Click Save to close the Page Restrictions screen.
Click Save to save to publish the new page.

Image Removed

Insert SQL File macro using the Insert markup menu

Edit the Classic Books page and:

Select Insert markup from the Insert content list at the top.

Type the following into the editor window. Note the dataSource name and file name referenced.

Code Block
{sql-file:dataSource=BookWarehouse\|file=books.sql}

Click Insert to include your Markup within the page.

Click Save to save your page and view your results.

Alternative method:

Edit the Classic Books page and:

On the page, begin typing { (open parenthesis) and then type sql-.
Select SQL File from the available list of macros.
The macro editor dialog appears.
Select BookWarehouse as the data source name.
Type books.sql into the File name field.
Click Save settings to insert your Markup within the page.
Click Save to save your page and view your results.

Configure the Chart macro

Edit the Top Publishers Worldwide page and do the following:

Insert the Chart macro.
Click the Chart macro container and click Edit so you can adjust its parameters as shown on the right. The following parameters indicate:
- Type - type of a chart.
- Width - width of the chart in pixels.
- Height - height of the chart in pixels.
- Display rendered data - a data table should be displayed after (below) the chart.
- Chart Title - title for the chart.
- Show in 3-D - add dimension to the bars within the chart.
- Opacity - recommended when using 3-D and for bar the recommendation is 75%.
- Horizontal-axis - column values rendered on the horizon line.
- Vertical-axis - column values rendered on the vertical line.
- Show legend - name associated with the colored bars rendered.
Click Save to save your changes to the macro's parameters.

Chart macro parameters:

Type	pie
Width (pixel value only)	600
Height (pixel value only)	400
Chart Title	Top Publishers by Revenue
Show in 3-D
Opacity	75
Horizontal-axis label	Publisher
Vertical-axis label	Revenue (in $M)
Show legend

Create a Bar Chart using SQL macro - 10.x

Configure the SQL Query macro

This step uses the SQL macro, to retrieve the data to be displayed in the bar chart. To configure the SQL macro, do the following:

Edit your Top Publishers Worldwide page.
Add a line below your Chart Macro.
Using Markup, insert a new SQL macro on the page (more on Markup here).
Adjust its parameters as shown on the right. The following parameters indicate:
- Data source profile - name of the data source profile that you set up.
- Use database column labels - usage of the column names defined within the SELECT statement (e.g., Publisher, M Rev.) rather than the columns names defined within the database table(s) themselves.
- Show error if there are no rows - displays an error if no rows are returned in the resultset of the SQL query.
- Text to display when there are no rows - text of the error message to be displayed when no rows are returned in the resultset of the SQL query.
Click Save to save your changes to the SQL macro's parameters.
Paste the SQL statement in SQL settings > SQL statement > SQL statement:
Code Block
language sql
theme DJango
linenumbers true
SELECT company_name as "Publisher" ,CONCAT('$',format(revenue,0)) as "M Rev" FROM publishers ORDER BY rank asc;
Using your mouse, drag the SQL macro container into your Chart macro container (see right).

SQL macro parameters:

*SQL settings > SQL statement > Data source profile*	BooksWarehouse
*Display settings > Data layout > Use database column labels*	On
Display settings > Data layout > Show error if there are no rows	On
*Display settings > Data layout >* Text to display when there are no rows	No rows selected

Moving the SQL macro inside the Chart Macro:

Image Added

Create a Bar Chart using SQL macro - 10.x

Save and test the page

Now, go back to the Top Publishers Worldwide page and click Save. You should now see a page that appears as shown on the right.

Top Publishers by Revenue (in $M):

Image Added

Versions Compared

Old Version 1

New Version 2

Key

Scenario

Background

Steps

Create a Confluence page

Insert SQL File macro using the Insert markup menu

Alternative method:

Configure the Chart macro

Configure the SQL Query macro

Save and test the page

Page Comparison

Versions Compared

Old Version 1

New Version 2

Key

<span class="diff-html-changed" data-a11y-before="Start of changed content" data-a11y-after="End of changed content" id="changed-diff-0">[data-colorid=</span>

Background

Steps

Create a Confluence page

Insert SQL File macro using the Insert markup menu

Alternative method:

Configure the Chart macro

Configure the SQL Query macro

Save and test the page