Versions Compared

Old Version 3

changes.mady.by.user Randall Ward

Saved on

compared with

New Version 4

changes.mady.by.user Saumya Sunder (Inactive) (Deactivated)

Saved on

Key

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

Scenario

Div
classtime

Estimated Time: 11 min

In this scenario, we will create a 3-D bar chart to display "Top Publishers by Revenue (in $M). 

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:

...

The following steps will demonstrate how to use Markup to insert Confluence's native Chart Macro and our SQL Macro on a Confluence page.

Table plus
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


Image Modified

Create a Confluence page

Create a Confluence page that will eventually contain the macros needed to produce the chart.

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





Image Modified

Configure the Chart macro

Edit the Top Publishers Worldwide page and do the following:

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

Chart macro parameters:

Typepie
Width (pixel value only)600
Height (pixel value only)400
Chart TitleTop Publishers by Revenue
Show in 3-D(tick)
Opacity75
Horizontal-axis labelPublisher
Vertical-axis labelRevenue (in $M)
Show legend(tick)


Image Modified

Configure the SQL Query macro

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

  1. Edit your Top Publishers Worldwide page.
  2. Add a line below your Chart Macro.
  3. Using Markup, insert a new SQL Macro on the page (more on Markup here).
  4. Adjust its parameters as shown on the right.
    • The Data source name parameter indicates to use the BooksWarehouse data source profile that you set up.
    • The Use database column labels parameter indicates to use the column names defined within the SELECT statement (e.g., Publisher, M Rev.) rather than the names of the columns names defined within the database table(s) themselves.
    • The Show error if there are no rows parameter indicates to show an error if no rows are returned in the resultset of the SQL query.
    • The Text to display when there are no rows parameter indicates the text of the error message to be displayed when no rows are returned in the resultset of the SQL query.
  5. Click Save to save your changes to the SQL Macro's parameters.

  6. With the cursor positioned inside the SQL Macro container, paste in this SQL statement:

    Code Block
    languagesql
    themeDJango
    linenumberstrue
    SELECT company_name as "Publisher"
,CONCAT('$',format(revenue,0)) as "M Rev" 
FROM publishers 
ORDER BY rank asc;


  7. Using your mouse, "drag" the SQL Macro container into your Chart Macro container (see right).


SQL Macro parameters:

Data source nameBooksWarehouse
Use database column labelsselected
Show error if there are no rowsselected
Text to display when there are no rowsNo rows selected


Moving the SQL Macro inside the Chart Macro


Image Modified

Save and test the page

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



Top Publishers by Revenue (in $M):


...