This post is fifth in a series on using Confluence as a Business Intelligence tool.
We’ve covered a lot of ground up to this point, from accessing your data to charting it to making it run like a greased weasel. But Confluence isn’t just for making pretty webpages out of your data. It can also generate reports based on user input on a web page or the URL-line. Strap yourself in, ’cause we’re about to get dynamic.
The {run} macro is the foundation of Confluence’s interactive reporting power. The {run} macro gives Confluence the ability to use variables anywhere within a page, including within another macro such as {sql}. The variables can be set from a simple text input field or on the URL-line. This allows you to use fields or specially-constructed hyperlinks to pass in reporting parameters. In this post you’ll learn how to create these parameter-passing fields and hyperlinks, then how to make the parameters themselves dynamic.
In order to follow along with the examples in this post you’ll need to have the run plugin installed in your Confluence instance.
You use {run} to define variables in your page that get replaced before the page is rendered. Here’s a “Hello World” in {run}:
run_1.jpg
Everything within the body of the {run} macro, including $greeting, is hidden until we enter a value in the input field and select Run:
run_2.jpg
Confluence has taken what we entered in the input field, substituted it for $greeting, and then rendered the body of the page. This is essentially all that {run} does. In practice you probably want {run} to do something that’s actually useful, like allowing your users to enter query parameters that get used in SQL. For example:
run_3.jpg
Confluence substitutes what you enter in the “category” field for all occurrences of $category in the page, changing the SQL query into:
run_4.jpg
before executing it. We’ve just created our first dynamic report – How easy was that?
run_5.jpg

Please be sure to read and understand the section on security before you enable run or scripting.

Since {chart} just makes a chart out of a table, all that you need to do to generate charts dynamically based on user input is to wrap the table in {chart}:
run_6.jpg
Hey presto, dynamic data visualization in your wiki.
{run} has some noteworthy options that we’ll use later on. You can:

  • Specify a default value for a variable: {run:replace=category:Motorcycles}
  • Make it execute automatically when the page loads: {run:replace=category:Motorcycles,color:blue|autoRun=true}
  • Hide the input field, or hide the Run link itself: {run:replace=category:Motorcycles,color:blue|autoRun=true|hideparameters=true|hiderun=true}

These options are important when we start passing parameters on the URL-line in the next section. Other {run} options that we won’t cover here are detailed in the documentation.
Passing Variables in Hyperlinks
Like us, you probably don’t want your users manually keying report criteria into form fields, what with typos and that new hire Bobby Tables over in Finance. The good news is that {run} can accept input from the URL the same way it does from a field. You just construct a special hyperlink that tells {run} the values to use for its variables. For example, this URL:
https://intranet.transmat.com/display/SALES/Sales+Report?run_1=run&run_1_category=Motorcycles
tells the {run} macro at https://intranet.transmat.com/display/SALES/Sales+Report to use the value Motorcycles for its variable $category.
In detail:

  • The base URL is just the URL of the page – in this case, https://intranet.transmat.com/display/SALES/Sales+Report
  • ? separates the base URL from the parameters
  • run_1=run tells the 1st {run} macro on that page to execute (i.e. the user is not required to click on “Run” first)
  • run_1_category=Motorcycles tells the 1st {run} macro on that page that its variable “category” should have the value “Motorcycles”

You can use this to create “quick links” to reports, like so:
run_7.jpg
This makes it easy and mistake-proof for your users to select the reports they need.

Tip: Variable-passing doesn’t work when you hyperlink using the [spacekey:pagetitle] convention, for example [SALES:Sales Report?run_1=run&run_1_category=Motorcycles]. You must use an absolute URL, including the “http” or “https”, as shown above.

Creating Dynamic Hyperlinks with SQL Queries
We can improve on this by making the creation of the hyperlinks themselves dynamic. In Retrieving Data Into Tables we learned how the output=wiki parameter causes the {sql} macro to return results as wikimarkup instead of the usual table format. The wikimarkup that the SQL returns then gets parsed by the Confluence wiki renderer before being shown to the user. We can use this technique to create an SQL query that constructs hyperlinks that contain values for our variables. This saves us from having to create and maintain all the hyperlinks manually.
Take the “sales report” hyperlinks in the example above. We can create the same list using SQL:
run_8.jpg
The SQL returns wikimarkup, which gets parsed by Confluence into the table on the right. You can alternatively have your SQL return HTML syntax (e.g. “select ‘<a href…”) and remove the output=wiki parameter – which markup format you should use will depend on what you need to do.
The benefit of constructing hyperlinks through SQL is that when someone adds or changes a category in the future (say, when Transmat inevitably starts selling unicycles), we don’t need to worry about it – our new category will appear in the list of report links, with a correctly-formatted hyperlink, automatically and immediately.

Tip: The double-pipe (“||”) in the example above is the database’s concatenation operator, which simply concatenates two strings. Microsoft SQL Server and Sybase use “+” instead, and MySQL users may need to use the CONCAT() function to get the same result. Check with your database administrator to find out what your concatenation operator is.

Multilevel Reports
It gets better. With some moderately clever SQL, we can create a single report page that displays a top-level aggregate report (“All Sales”) and allows drilling into category-specific reports on the same page. This makes for a very compact but dynamic and powerful report.
Remember how {run} substitues the values you pass in on the URL-line for variables on the page? Check out the neat trick in this SQL:
run_9.jpg
The where clause is saying “give me the sum of netsales where EITHER the category that you pass in is ‘All’, OR the category you pass in matches the value in the ‘category’ column of the ‘sales’ table”. If $category is ‘All’, the test will always return true and all rows will be included in the results. If $category is something else, though, then only rows that match that $category will be included in the results.
So for example, if we use this URL to access the page:
https://intranet.transmat.com/display/SALES/Sales+Report?run_1=run&run_1_category=All
{run} substitutes ‘All’ for all occurrences of $category before rendering the page. The where clause of the SQL statement thus becomes:
where (('All' = 'All') or (category = 'All'))
Which is always true (because ‘All’ will always equal ‘All’), so we’ll see the sum of all sales regardless of their category. However, if we use this URL:
https://intranet.transmat.com/display/SALES/Sales+Report?run_1=run&run_1_category=Motorcycles
Then the where clause of the SQL statement becomes:
where (('Motorcycles' = 'All') or (category = 'Motorcycles'))
‘Motorcycles’ will logically never be equal to ‘All’, but the second expression will be true for rows that have a category of ‘Motorcycles’, and those rows (and no others) will therefore be included in the results.
The Bronze Star for SQL Cleverness was awarded to the BA who devised this technique. Sadly, the award was posthumous, the BA in question having been trapped in a fierce nested inner join.
Defaults FTW
What if we access the page using its “normal” URL?
https://intranet.transmat.com/display/SALES/Sales+Report
Well, if we’ve set up the {run} macro to have a default value of ‘All’ for $category using replace=category:All, and told {run} to execute when the page loads with autoRun=true, then {run} just uses the default value of ‘All’ for $category. As we saw before, this returns all sales regardless of their category.
At Atlassian, we often use this technique to create reports that show “top-level” company-wide data by default and allow the user to focus on specific products or groups by drilling into hyperlinks on the page.
Bringing it all together
We’re nearly at the end of our time with {run}. So far we’ve learned:

  • How {run} substitutes user input for variables on the page
  • How to pass variables on the URL-line
  • How to use SQL to dynamically construct variable-passing URLs
  • How to use those variables as criteria in SQL queries
  • How to set a “top-level” default for {run} and make it execute automatically

If you’ve got a datasource configured in your Confluence instance, you should now be able to create some pretty cool dynamic reports using {run}.

Check out the full {run} and scripting chapter online at the Confluence Reporting HOWTO.

In the next post we’ll cover the scripting plugin, which opens up a whole new set of possibilities. Look for the next post in a week or so at http://blogs.atassian.com.


Fresh ideas, announcements, and inspiration for your team, delivered weekly.

Subscribe now

Fresh ideas, announcements, and inspiration for your team, delivered weekly.

Subscribe now