Variance not created as a footer in DeskI

This is a minor bug that exists in BusinessObjects XI and Business Objects 6.

When you add a variance to a table, the variance column isn’t marked as a footer.

I add the variance :

So far, so good.

But when I add the sales revenue in a column behind it, it doesn’t aggregate. It just shows the last one.

Solution :

In the table-properties, manually activate the table footer.

Peter De Rop

Creating a KPI in PerformancePoint

So far, it’s been a love/hate relationship with SharePoint, but a tool like PerformancePoint does get my full attention. So far, I had some trouble getting it to do what I want, so, here I share what I found so far.
1) Deploying a demo-cube
Once I had downloaded the AdventureWorks2008 database, I found a great procedure right here:
http://techpunch.wordpress.com/2008/09/08/sql-server-2008-how-to-build-and-deploy-adventureworks-olap-cubes/
2) Creating the connection:

I provided the server and instance name. Note the backslash to enter the instance name. Not a forwardslash, which was one of the errors I made.
Secondly, I selected the Database and Cube.
Next, on the properties tab, I provided a name:

And last but not least, the Time-tab. So far, it eluded me somewhat.
To get this to work, you need a time-hierarchy in your OLAP cube. Fortunately, the AdventureWorks database has one, in case you want to try it out.

For the Time Dimension, I chose to use Date.Date.Calendar, which points to a time-hierarchy in the AdventureWorks OLAP cube. Secondly, I browsed to a child item I wanted to use as a reference. In this case, I took January,1,2002. And I defined the level to be Day, because the above selection is in fact a day.
Underneath, you then have to choose from a calendar, to what date this particular item maps. I guess Performance Point is trying to work out what format the date is in and I’ve seen more intuitive interfaces to accomplish this.
Below that, you now have the chance to tell Performance Point, which item in your OLAP cube maps to the Year, the Semester, the Quarter the Month, the Day, the hour and the minutes if you have them.
There, that’s the tricky part.
3) Creating the KPI
First, you choose, which measure in your database contains the number you want to show. In my case, that’s going to be the Sales Amount measure. (note the button at the bottom: “Change Source”) Took me a while to locate that one

For the filter, I created a Time Inteligence filter. And I entered Year-8 This will return data for the current year,-8 years. My first mistake was to enter Year-1 because I thought the date I had chosen at the datasource, would be used as a reference, not true. It takes the current date.
So, Year-8 it is.
I then did the same for the target. By the way, I renamed them to be This year and last year.

And I entered Year-9 for that one. This way, I could compare 2003 to 2002 figures.
After clicking OK, I then proceeded to make a scorecard:
I just dragged the KPI (from the right pane) into the Scorecard and disco :

No numbers, but lo and behold, when you press save or ctrl-s, they do emerge:

Our KPI tells us we did 37% better than last year. Cool.
Last step, we could add the quarter by dragging it again from the right pane:

Nice
More to come

Binabik

@DPValues and @DPEnd

A nice little piece of nothing this one.
I noticed on the forums, someone asking about these functions. So, I did some digging.
I built a first query that list stores :
The query became something like :
SELECT
 
Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma.Store_name
FROM
  Agg_yr_qt_mt_mn_wk_rg_cy_sn_sr_qt_ma
Then, I based a query on it:

the query became :
SELECT
 
Article_lookup.Family_name
FROM
  Article_lookup,
  Outlet_Lookup,
  Shop_facts
WHERE
  ( Outlet_Lookup.Shop_id=Shop_facts.Shop_id  )
  AND  ( Article_lookup.Article_id=Shop_facts.Article_id  )
  AND  (
      @dpvalues('
Outlet_Lookup.Shop_name',9,1)@dpend
  )
at the next iteration, it became:
@dpvalues('
Outlet_Lookup.Shop_name',9,7)@dpend
and when I tried filtering for a number, it became:
@dpvalues('
sum(Agg_yr_qt_rn_st_ln_ca_sr.Sales_revenue)',536870916,12)@dpend

This tells me, it’s not a user-function. It’s an internal function. The user is expected to use a subquery instead.
Or, as Andreas stated on the forums :  query on query, which allows you to filter based on a different datasource.

Binabik

Table titles, a well hidden feature

Imagine that, a feature that is so well hidden, that I only found it after teaching Business Objects for four years. In fact, it has been in Business Objects ever since version 5 as far as I know.

Here’s the deal :

We want to show two values in a cross-tab. In this case, I took Sales revenue and Margin.

The way it shows, you can’t really make out what each number is. But there is a solution. Both in WEBI and DESKI, you can set an option. So, right-click the table and choose “Format Crosstab” if you’re in DESKI.

Now, you check: “Show Variable Header”.

There, the title is showing. (In a minute, I’ll show you how to center the year above those columns) But first, how do you do this in WEBI ?

In WEBI, on the Properties tab, you check “Show Object Name”.

So, how do we center the title ? Easy.First, we add a break to the year.

Next, we delete the extra columns, but because of a BUG, you can’t delete these columns if you try it in Rich Client. In stead, go to the properties and deactivate the footer on the break.

In DeskI, just delete the columns of course.

The last step is to Center values across the break as shown above. In DeskI, this is a property of the break, which you can set in Slice and Dice.

The result :

There, a clean table

Cheers

Peter De Rop

A Pareto Chart

While I was writing the article about “Control charts”, I found out about “The Seven Basic Tools of Quality”. It was the first time I learned about that – and I realised that at least 4 of those seven tools can be built in Business Objects DeskI and WebI.

Those four tools are :

• A Control chart (described in a previous article)
• A Pareto chart
• A Histogram
• A Scatter Diagram

In this particular article, I wish to show you a Pareto Chart.

Basically, it is a chart that shows a bar and a line, where the line illustrates the importance of each of the bars.

This is accomplished by sorting the values from high to low and presenting them on a bar-chart. And then, adding the RunningSum/Total on a linechart.

The above chart shows that 50% of our reservations are made in 3 months April, August and February.

I realise that this isn’t the typical sort of data on which you use this particular chart, but I do believe this can be a useful way of presenting pretty much any kind of number. Where you want to compare your number to a % in total.

The example used on WikiPedia is various sources of absenteism. In an other source, I found the possible deseases among cattle, in order to find the deseases you want to tackle first.

So, enough talk. Without any further ado: The Pareto Chart.

Step 1. List your data in a table and sort it Large to Small :

Step 2 :

Calculate the Runningsum and divide by the total:

=RunningSum([Future guests])/Sum([Future guests]) in Report * 100

I multiply times 100 to make it a bit cleaner on the z-axis of the chart.

As soon as you do this however, any sorting on the table is gone, so we’ll have to re-sort.

Step 3: Click View structure, sort the measure you want to have sorted:

Second problem, the scale is wrong. It should be from 0 to 100. On the properties tab, make sure you set it to be 0 to 100. Given the type of number we have here, it will never be anything else than 0 to 100.

 

And we have ourselves a PARETO-chart.

Peter De Rop

A Control Chart in Business Objects

This week, I read about Control charts on the Business Objects forum. I had not heard of the notion before, but of course, it didn’t take long to find more about it. WIKIPEDIA is your friend in times like these.
As it turns out, it is a line chart, enriched with a series of constants being:

• The mean or average
• The mean + and – twice the stdDev()
• The mean + and – three times the stdDev()

All of which can be done very easily.
When writing these functions, there are only two things you need to add to your calculations : “in body” and “in report”. I’m going to do this in Rich Client, but this works just as fine in any other version of SAP BusinessObjects.
The function for the mean looks like this :
=average([Sales revenue] in ([Month]) in Report (a variable which I call AverageSalesRevenue)
the function for the Standard deviation looks like
=stdDevP(<Sales revenue> in ([Month])) in Report (a variable which I call deviationRevenue)
please note that I’m using the stdDevP function and not the stdDev function, but that’s because I have all data accounted for. In case you made a random selection, you will need to use stdDev instead.

Basically, all you need to do is create 4 variables using the before mentioned functions.
1) [AverageSalesRevenue]+ ([deviationRevenue]*3) –> [AveragePlus3Deviations]
2) [AverageSalesRevenue] + ([deviationRevenue]*2) –> [AveragePlus2Deviations]
3) [AverageSalesRevenue] - ([deviationRevenue]*2) –> [AverageMinus2Deviations]
4)[AverageSalesRevenue] - ([deviationRevenue]*3) –> [AverageMinus3Deviations]
In View structure, it’s easy to add them to the linechart like such :

The rest is easy, just drag the documentation cells in place with formula :
="UCL:"+[average plus 3 deviations]
="Center line:"+[Average]
="LCL:"+[Average minus 3 deviations]

There, hope you enjoyed that,

Peter De Rop

Colouring alternating lines in Business Objects DeskI

This functionality exists by default in WEBI, but DeskI simply doesn’t have it:

But, as you can see, you can have it, if you really want to. Here is how :
First, we need to create a variable called linenumber, with the function :
=LineNumber() –1
The –1 is because linenumber also counts the header and therefore, starts at 2.
The second variable I create is called Even and the function is :
=Even(<linenumber>)
The rest is easy, create an alerter on that Even variable :

Easy
Enjoy


Peter De Rop

A Mini Chart in Business Objects WEBI

This is a nifty little trick you can pull with Business Objects WEBI. Yes, only WEBI-users will be able to do this, the new functions were only added in the WEBI version of Business Objects (but that includes Rich Client of course).

This is the end-result. In the column to the right of your numbers, you get some sort of a chart that shows the proportional importance of a number (to get this to work with negative numbers, you’ll have to complicate things somewhat)
In Excel, the function is =rept(“|”, yournumber) – in Business Objects, that function is called : Fill()
In this case, I also divided my numbers by 10000 to keep the charts inside the column.
So: =fill(“|”;[Sales revenue]/10000).
Ah, I set the fontsize to 5, to make it even better.
Short and sweet, don’t you think
Enjoy,


Peter De Rop

Stopping gaps in Business Objects Charts

This is a classic.When you’ve been creating charts in Business Objects, you know about this one. Let’s start by looking at the data we would want to present in a chart:

As you notice, there are a few months missing. They’re simply not there because we didn’t sell that particular product in that particular month at all. Still, we wish to present the data in a line-chart and display a line across those missing months, without leaving a gap.

For you WEBI users, we have a solution.

The only solution is adding a second query to your report, where you have all 12 months.

In your query editor, click : Add Query and select only the month dimension.

Once you did that and you added the month to the chart (instead of the month from the first query)

your Chart will look like :

Not much of an improvement.

This is what it looks like in WEBI

and with the extra query added :

 

For now, in DeskI, this is how far you could get. The only Solution that remains for DeskI is to calculate the rest of the chart by hand (which is what i will show in a next blogpost. In WEBI however, we have a much better solution : the function “interpolation”.

=Interpolation([Sales revenue])

Obviously, the numbers we get this way are fictious. They just serve to close the gap on the chart. It’s clearly the purpose to use the previous display of the data, but we use these numbers for our chart – this results in :

And we have a much cleaner chart.

Hope you find this useful.

Oh, and Mind the Gap

Cheers,

Binabik

An Exponential Trendline in Business Objects

This is going to be the third type of Trendline and I just noticed, Excel has six of them. I’m not sure how many really are used, or if there is a specific need for any in particular. So, for now, I’m gonna keep this at three, unless there is a demand for the other three. (Polynomial, Power and Moving Average).

I am going to add those three eventually, just for fun, but I’m going to keep them for a cold winter evening. Or when the question arises.

So, here’s the Exponential Trendline.

The magic number for the Exponential trendline is e. I’ve been reading about the constant e on Wikipedia and it’s a great story. You should read it too. One of the things I noticed is the amount of history that lies at the base of these trendlines. Amazing.

First, the formula:

Where c and b are constants. Each time when they say that (and here is where I find that info) I’m a bit pissed off. Especially, because they’re not constants at all. They’re calculated values. All of them.

c, as usual, is the intercept point but this time, it uses the Natural Logarithm of Y and the value (non Logarithm) of X. In fact, the calculation uses the EXP function, which is the inverse of a Natural Logarithm.

So, if i = ln(x), then exp(i) = x.

b is the coëfficient which we calculated in both other trendlines before, but this time, it uses the ln of  y.

(ah, on a sidenote, I wrote 2011 in my report, but on the test-databases that come with Business Objects, the numbers are identical for 2003 or 2006 –amazing huh )

So, I take the ln on Sales Revenue and I call it LnY

The formulas are:

=ln(<Sales revenue> in body) in report (which is called LnY)

=Average(<LnY> in body) in Report (which I call AverageLnY)

Like in the linear trendline, we calculate X-AverageX where X is a Runningcount of the Month.

=((RunningCount(<Month>) In <Month>) - <AverageX>) In <Month>

=(<LnY> - <AverageLnY>) In <Month> becomes the variable LnY-AverageLnY

and we multiply those two :

=<LnY-AverageLnY>*<X - average X>

After which we add them up. As before, we now have the top of our division:

=Sum((<LnY-AverageLnY> * <X - average X>) In  Body) In Report

For the bottom part of our division, we need the Square of X-AverageX added up.

=Power(<X - average X>, 2) In <Month>

Now we divide those numbers

There, that’s our b-variable which we can use to calculate our c variable.

=<LnY>-(<LnCoeff>*<xvalue>)

There we go, we have both variables to put into our formula.

e = 2.71828, I found that on Wikipedia.

Now, one more thing needs to be done, and it’s probably why this is called the exponential trendline, we need to take the EXP of our intercept point (our c-variable)

=Exp(<InterceptLn>) In Report

=<ExpIntercept>*(Power(2.71828 ,(<LnCoeff>*<xvalue>)))

this is the formulat shown at the top, turned Business Objects. We add that one and convert it to a table. That gives us the following chart:

And excel does this :

Again, a perfect match.

There. That gives you Linear, Logarithmic and Exponential trendlines to add to your charts.

Enjoy

Binabik