## donderdag, februari 18, 2010

### Showing the average on a chart in Business Objects

In my latest courses, lots of folks had questions about the way Contexts work. So, here’s another way, contexts are used :

You want to show the average of something on a chart, as a horizontal line. That means, you will have to create a column with that average in a table, where the same number is shown in the entire column.

 Month N° visitors Average 1 25 25 2 100 100 3 72 72 4 97 97 5 93 93 6 103 103 7 111 111 8 23 23 9 87 87 10 98 98 11 39 39 12 111 111 Average 79,9

It’s pretty clear, the formula =Average(<N° visitors>) won’t do the trick. It just returns the number itself, because it operates in the context of the month. To solve this, we can use 2 formulas :
Average(<N° visitors>) in Report
Average(<N° visitors>) forall(<Month>)
The first one is absolute, so it will always return the same number – no matter in which table you put it. The second one is relative – it depends on the dimensions already in the table.
One way to do this is by choosing : Data> Define as variable and choosing Evaluate the formula in its context.

Both of the above will return :
 Month N° visitors Average 1 25 79,9 2 100 79,9 3 72 79,9 4 97 79,9 5 93 79,9 6 103 79,9 7 111 79,9 8 23 79,9 9 87 79,9 10 98 79,9 11 39 79,9 12 111 79,9 Average 79,9

Now, we turn this into a chart :

Not exactly what we need, but, getting there.

Rightclick the chart and choose “Format Chart”
On the Series tab, click “Add” and make it a line chart, next, you drag the average from the other group to the new group.

And we get :

Cheers folks

Binabik