dinsdag, februari 23, 2010

Relative positioning in Business Objects

When you’re building reports in Business Objects, sooner or later, you’re going to have a table where the length not fixed. So, one day, you have 10 lines, the next day, you have 50 lines. Tables that are located behind them are then suddely overrun by that table.

onetableaboveother

onetableaboveother_2

The properties of the table allow you to choose how tables behave.

verticalpositionexplained

(the name of that table can be configured also in ‘Table format’

 

Cheers,

Binabik

donderdag, februari 18, 2010

Day of the week of first day current year

I noticed on Google analytics that someone was looking to calculate in Business Objects, what day, the first day of the year was.

Here's how :

=DayName(ToDate(Year(CurrentDate()) & "/1/1" , "yyyy/mm/dd") )

If you want the number of the day

=DayNumberOfWeek(ToDate(Year(CurrentDate()) & "/1/1" , "yyyy/mm/dd") )

Yet someone else was looking for an equivalent of Instring in Business Objects. Look no further.. it’s Pos()

pos(“abcdefg”, “g”) = 7, so, g is located in the 7th position.

 

Easy as pie

Binabik

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.
define_as_variable


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 :

chart_average

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.

averageonlinechart

And we get :
correctaverageonchart

Cheers folks

Binabik

dinsdag, februari 16, 2010

Calculating average in Business Objects

At a customer, someone asked this particular question : In a crosstab, if certain lines return empties, how do you calculate average while evaluating the empties as 0 ?

As it is, when you calculate the average with the regular function, BO will not take into account the missing information.
2007 2008 2009
Dog 10 10 10
Cat 10 10
Horse 10
Pony 10 10
Average 10 10 10


where it should be
Average 10(40/4) 2,5(10/4) 7,5 (30/4)


The solution to this problem is : contexts.

First, we need to count the number of animals

Count 4 1 3


performing a regular count will not give us the right result.

Count() will return a different number for each column. So, we force the count with "in" Count() in Report -- that will return 4 in each column.
Count 4 4 4


So, now we have our formula :

Sum()/Count() in Report

Cheers folks

Binabik

three things I would like in RS

1) Sorting Picklists

On RS, when you build a picklist, you select your field and the values don't get sorted, and there is no option to do so. I found a workaround, but it's not very clean.

2) an option to remove the item(Select all) in multivalue parameters

When you set a picklist to multi-value, RS automatically adds the option "Select all"

Since a query is very often limited to 999 inlist values, you run into trouble pretty fast, and it's a big performance issue, if people just select the lot.

Would be nice to have this option.

3) some way to pre-test scripting.. the way it is now, scripting is very under-developped. In fact, it's just a window where you can enter text.. period -- wouldn't mind some intellisense :) .

Binabik

Sorting picklists in RS Reportbuilder 2.0

To sort a picklist in reportbuilder, when working with a model, you can use the following workaround :

* add the field twice

This is pretty daft : you can't just sort the list. In t-SQL, it would be sooo easy, but on a model, your hands are tied. Once you add that same field a second time to a query : disco, it's sorted.

Took me a while to figure out that it was actually the addition of a field a second time that sorted the thing.. and not any function that was applied to the field.

Gotta love RS.

Binabik