cb.jpgThis post is sixth in a series on using Confluence as a Business Intelligence tool.
In our previous post we covered how you can pass parameters into your dynamic pages using the {run} macro, which is pretty cool. In this post we’ll take “dynamic” to the next level with Confluence’s scripting capabilities.
The scripting plugin opens up a whole new set of possibilities. Instead of writing a page in wikimarkup, you write a script that writes a page in wikimarkup. This makes your reports more compact and maintainable, and gives you access to your chosen language’s libraries from within a Confluence wiki page. You can generate wikimarkup from BeanShell, Groovy or Jython scripts. At Atlassian we mostly employ {beanshell}, so we’ll be using it in all of our examples here.
Beanshell is a programming language that you can use inside your wiki pages. It uses a “scripty” Java-like syntax, so if you happen to be familiar with Java you’ll feel right at home, but don’t worry if you aren’t. We won’t be doing anything too complicated with it in this tutorial, so you should be able to follow along and learn from the examples here.
You’ll need to have the scripting plugin installed in your Confluence instance in order to use {beanshell}.

Before you enable the scripting plugin, you need to be aware of the security implications of doing so. A brief way to describe these implications is “anyone who has access to the scripting plugin can do anything they want with your system”. This includes shutting down your instance of Confluence at will and, potentially, even nastier things. You must mitigate these risks to your satisfaction before you enable the plugin. Please see our chapter on security for more.

Print!
The most basic and commonly used beanshell statement is print. It simply tells beanshell to print that line to the screen. For example:

{beanshell:output=wiki}
print("Hello Beanshell World!");
{beanshell}

simply prints the text “Hello Beanshell World!” on the page. The output=wiki parameter simply tells Confluence to run the script’s output through the wiki renderer before showing it to the user. Notice the parentheses and the semicolon at the end of the line. These are essential Java-esque elements of beanshell’s syntax, and you’ll get a Java-esque error if you leave them off.
Iterating Through Arrays: A Beanshell Primer
The first useful thing we’re going to do with Beanshell is iterate through an array. An “array” is just a list of things, and to “iterate” through it is to go through it one-by-one. In this section we’re going to summarize how lists work in Beanshell, which is the same way they work in Java, so if you’re already familiar with Java you can probably skip ahead.
In Beanshell you define an array of strings like this:

String[] someWords = new String[] {"foo", "bar", "baz"};

What this means is “A thing of type String[], named someWords, is a new thing of type String[] that has these items: ‘foo’, ‘bar’, and ‘baz'”.
To iterate through someWords, you use “for”:

{beanshell}
String[] someWords = new String[] {"foo", "bar", "baz"};
for (item : someWords) {
print(item);
}
{beanshell}

The for statement simply means “For each thing (which we will call item) in someWords, print item”. The output of this script is simply “foo bar baz”.
String[] works great for lists of “one-dimensional” items. But if your items have more than one attribute, each item becomes a list in itself. You’ll need to use an ArrayList, which is simply a “list of lists”, to hold all the lists.
For example, say you had a list of people with their titles and locations:

  • Alice is a Director in San Diego
  • Bob is a Chairman in Austin
  • Darth is a Sith Lord in Las Vegas

Each individual person’s attributes (a name, a title and a location) can be represented in a String[] list. All of the String[]s can be collected together in a single ArrayList:

List people = new ArrayList();
people.add ( new String[] {"Alice", "Director", "San Diego"});
people.add ( new String[] {"Bob", "Chairman", "Austin"});
people.add ( new String[] {"Darth", "Sith Lord", "Las Vegas"});

Here we’ve defined a new ArrayList called “people”, and then added three new String[]s in turn.
You can refer to an item’s position in an array using a number in square brackets, e.g. people[0] refers to Alice’s list, and within Alice’s list, postion [0] is the string “Alice”. This is known as “positional notation”. You use a for statement to iterate through the String[]s in an ArrayList, just like you do with String[]:

for (person : people) {

As you iterate, you use positional notation to refer to the items within a String[]:

print(person[0] + " is a " + person[1] + " in " + person[2]);

Put together, the complete {beanshell} script looks like this:

{beanshell:output=wiki}
List people = new ArrayList();
people.add ( new String[] {"Alice", "Director", "San Diego"});
people.add ( new String[] {"Bob", "Chairman", "Austin"});
people.add ( new String[] {"Darth", "Sith Lord", "Las Vegas"});
for (person : people) {
print(person[0] + " is a " + person[1] + " in " + person[2]);
}
{beanshell}

The result:
AliceBobDarth.jpg
Feel free to try it out in your own Confluence. Printing arrays by themselves isn’t usually very useful, though, so next let’s combine beanshell with {sql} in a real-life example.
Example: Using Beanshell Arrays and SQL to Construct Decks
Let’s say our company, Transmat, sells vehicles – cars, trucks, motorcycles and unicycles. For each product line, we’re interested in knowing how many units we sell each week, and we also want to compare unit sales across product lines.
Remember back in the section on Charts how we used the {deck} macro to make a tabbed “stack” of charts? That’s what we’ll create now – a stack of charts, one for each product line, that will allow us to view and compare sales across product lines.
To start with, you should decide how you want to see the data on a graphical chart. For this example, we’ll use a simple bar chart with a fixed rangeAxisUpperBound so that the charts “line up” with each other (this is the same technique we used when we introduced {deck} in Charts and Formatting).

{chart:type=bar|height=300|width=550|dataOrientation=vertical|yLabel=Quantity|xLabel=Week|categoryLabelPosition=up45|dataOrientation=vertical|rangeAxisUpperBound=1000}

You also need an SQL query that returns the data you’re after. We’ll use the same query we worked with earlier in the {run} section, except that each category has a numeric rather than textual identifier:

select Week,
sum(unitsales)
from sales
where category = 1
group by Week

The numeric category identifier will vary depending on the category: 1 = cars, 2 = trucks, etc.
Since we want to display a chart for cars, trucks, motorcycles and unicycles, we need to define an ArrayList to hold the data about each category. We have to use an ArrayList rather than a simple String[] list because we need to keep track of more than one attribute for each category. Let’s say each category has three attributes: The category’s name; the category’s numeric identifier for use in the SQL where clause; and the color to use in the category’s chart (in hexidecimal).

{beanshell:output=wiki}
List categories = new ArrayList();
categories.add ( new String[] {"Cars", "= 1", "#468cc4"});
categories.add ( new String[] {"Trucks", "= 2", "#d7561f"});
categories.add ( new String[] {"Motorcycles", "= 3", "#769810"});
categories.add ( new String[] {"Unicycles", "= 4", "#bca902"});
{beanshell}

Once we’ve set up the array, we tell Beanshell to iterate through it and print the wikimarkup that generates the {deck} and its {card}s. This includes printing the code for the other macros we use, {card} and {sql}. This is exactly what we said we were going to do at the outset – we’ve written a program that writes the program that shows us the data we want to see!

{beanshell:output=wiki}
List categories = new ArrayList();
categories.add ( new String[] {"Cars", "= 1", "#468cc4"});
categories.add ( new String[] {"Trucks", "= 2", "#d7561f"});
categories.add ( new String[] {"Motorcycles", "= 3", "#769810"});
categories.add ( new String[] {"Unicycles", "= 4", "#bca902"});
print("{deck:id=unitSales}");
for (category : categories) {
print("{card:label=" + category[0] + "}");
print("{chart:type=bar|height=300|width=550|dataOrientation=vertical|yLabel=Quantity|xLabel=Week|categoryLabelPosition=up45|dataOrientation=vertical|rangeAxisUpperBound=1000|colors=" + category[2] + "}");
print("{sql:datasource=sales}");
print("select Week,");
print("sum(unitsales)");
print("from sales");
print("where category " + category[1]);
print("group by Week");
print("{sql}");
print("{chart}");
print("{card}");
}
print("{deck}");
{beanshell}

The script above produces a deck that looks like this:
deck.jpg
We built a script that loops over an array of data to produce the wikimarkup that produces our page. That wasn’t as hard as it sounds, was it?
You could accomplish the same thing without using {beanshell}, but you’d find yourself with a very long page with a lot of mostly-identical queries repeated over and over. If you needed to change any part of it you would have to make the exact same change in many places, which means it’ll take you many times as long and you’ll be many times more likely to make mistakes. {beanshell} makes the page much more concise and easy to maintain.
At Atlassian, our most complex dashboards have over 100 SQL statements each. Maintaining these without scripting would be madness, but by using {beanshell} we’re able to reduce them to just a few iterators.
Believe it or not, we’ve just begun to scratch the surface of what you can do with scripting. The possibilities are literally without end. In this tutorial, though, we’re only going to cover a few more things that we think are especially pertinent, then leave the rest to your creative talents and imagination.
Combining Beanshell with {run}
Last week we learned how to pass variables with {run}. You can use {run} to substitute variables inside of a script just as easily as you can elsewhere. This lets you add another dimension to your report while keeping it compact. You might be surprised to find just how little work it takes to do this.

{run:replace=period:Week|autoRun=true|hideparameters=true|hiderun=true}
{beanshell:output=wiki}
List categories = new ArrayList();
categories.add ( new String[] {"Cars", "= 1", "#468cc4"});
categories.add ( new String[] {"Trucks", "= 2", "#d7561f"});
categories.add ( new String[] {"Motorcycles", "= 3", "#769810"});
categories.add ( new String[] {"Unicycles", "= 4", "#bca902"});
print("h5. Unit sales by $period");
print("{deck:id=unitSales}");
for (category : categories) {
print("{card:label=" + category[0] + "}");
print("{chart:type=bar|height=300|width=550|dataOrientation=vertical|yLabel=Quantity|xLabel=Week|categoryLabelPosition=up45|dataOrientation=vertical|rangeAxisUpperBound=1000|colors=" + category[2] + "}");
print("{sql:datasource=sales}");
print("select $period,");
print("sum(unitsales)");
print("from sales");
print("where category " + category[1]);
print("group by $period");
print("{sql}");
print("{chart}");
print("{card}");
}
print("{deck}");
{beanshell}
{run}

If you’ve been paying attention, you should be able to tell that the script above allows you to specify the period as a parameter in a hyperlink, but uses “Week” as its default value. All you’d need to do is provide your users with a list of links like this one:

h5. Unit Sales Reports
* [Daily|https://intranet.transmat.com/display/SALES/Unit+Sales+Report?run_1=run&run_1_period=Day]
* [Weekly|https://intranet.transmat.com/display/SALES/Unit+Sales+Report?run_1=run&run_1_period=Week]
* [Monthly|https://intranet.transmat.com/display/SALES/Unit+Sales+Report?run_1=run&run_1_period=Month]

And they’d be able to view the data on whichever timescale they needed to. What you’ve effectively done is add another “dimension” to the report (i.e. period) without much work at all.
Adding an “All” series to an ArrayList
Remember in the section on {run} how we showed you a neat SQL trick to create an “All” series?

where (('$category' = 'All') or (category = '$category'))

You can get the same effect when you’re iterating through an ArrayList in Beanshell by using where clause criteria that returns all rows. Each item has an attribute for its SQL where clause criteria. You might have been wondering why I bothered to include the “=” in this attribute, instead of simply making the “=” part of the template SQL statement. The reason why is so we can use things besides “=” in the SQL where clause if we want to:

List categories = new ArrayList();
categories.add ( new String[] {"All", "in (1, 2, 3, 4)", "#02afc8"});
categories.add ( new String[] {"Cars", "= 1", "#468cc4"});
categories.add ( new String[] {"Trucks", "= 2", "#d7561f"});
categories.add ( new String[] {"Motorcycles", "= 3", "#769810"});
categories.add ( new String[] {"Unicycles", "= 4", "#bca902"});
categories.add ( new String[] {"Everything But Unicycles", "!= 4", "#000000"});

When the iterator creates the SQL statement for “All”, the where clause becomes “where category in (1, 2, 3, 4)”. This will sum unitsales for all of those four categories into one series. And if, by chance, you wanted to view the four categories as separate series in a stacked bar chart, all you would need to do is add a stacked=true parameter to the chart macro and change the SQL statement’s group by clause to be “group by $period, category”. This is another good example of how using beanshell makes pages easier to maintain. Even though you now have a deck of six charts, you only need to make changes in one place.
The “Everything But Unicycles” element shows how you can use different comparison operators (“!=”) if you want to.
Further Reading
You should now be able to:

  • Use {beanshell} to print simple strings;
  • Define arrays using String[] and ArrayList;
  • Iterate through those arrays to construct reports;
  • Combine scripting with {run} to add further dimensions to reports; and
  • Include where clause parameters and other attributes as elements of an array.

We’ve tried to show you the scripting techniques that will best help you create great reports with Confluence. Again, we’ve only scratched the surface of what scripting makes possible. With the scripting plugin, you can literally do anything that Beanshell, Jython or Groovy’s available libraries let you do.
This power comes at a cost. Before you enable the scripting plugin, you need to be aware of the security implications of doing so. A brief way to describe these implications is “anyone who has access to the scripting plugin can do anything they want with your system”. This includes shutting down your instance of Confluence at will and, potentially, even nastier things. You must mitigate these risks to your satisfaction before you enable the plugin. Please see our chapter on security for more.
Confluence’s dynamic reporting capabilities have been contributed by the Confluence community. Bob Swift in particular stands out as the author of nearly all of the plugins we’ve used in this HOWTO. You can find a wealth of community-contributed reading on confluence.atlassian.com:

We hope that you’ll join the discussion and contribute your work to the Confluence community, as others have done.

Wondering what all the big warnings are about? Tune in next week for Security.

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

Subscribe now