The Second best

Again, on the BOB forum, I stumbled into this question.

This person wanted to show the second highest value in a list. The following procedure will allow you to pick which one you want to show.

The formula I will be using here is Rank

This is my starting table. I want to show only the second best of each year.
I create a variable, using the rank function

=Rank([Sales revenue])

This one won't cut it, because, when you filter on it, it will actually show you Q2 2004. Because the filter will actually operate in the context of the report.

The correct function is therefore :

=Rank([Sales revenue];Top;([Year]))

This will show the rank of each quarter, for each year. Next, we need to set a filter on that :

 

There, only the second best shows. Yes, the rank-value now shows 1 everywhere, since the item that still shows is now the only one to still be available.

To solve that one : 

=NoFilter(Rank([Sales revenue];Top;([Year])))

Now, it shows the number 2.

Enjoy,

Peter

The Awesome TimeDim function

Sometimes, you think you know BusinessObjects, and then, you find a function you wonder.. what on earth is that.

One of those is : TimeDim

It's awesome.

In short : it completes a list of dates.

So, if you have data for 2012/01/01, 2012/01/05, 2012/01/06 -- it completes the list by adding 2012/01/02, 2012/01/03 and 2012/01/04

This is a VERY useful function indeed.

In the screenshot above, the left table does not have timedim on it.. the right one, the function is : =timedim([Invoice Date])

Oh.. by the way.. the help on this function DOESN'T WORK. So, you have to figure it out for yourself :)

Cool huh.

And it gets better.

 

The left table in the above picture, is what our database returns. But I want to show the table to the right.

Again, The TimeDim function can help.

In the above tables, I used the following functions :

=Year([Invoice Date]) 

="Q"+Quarter([Invoice Date]) 

In the right table, I used :

=Year(TimeDim([Invoice Date];YearPeriod))  

and

="Q"+Quarter(TimeDim([Invoice Date];QuarterPeriod))

 As it turns out, this particular function was added in Service Pack 4 of Business Objects XI R3

Cheers,

Peter

My undo button does not work

This is a nice bit of frustration you might run into in BusinessObjects XI R3 WEBI.

Here's the situation : You have a report -- any report. When you move a table, delete a column, delete a cel, the undo button does not light up.

It's not a bug really, it's normal behaviour.

If you accidentally click the Show/Hide Filter toolbar button (to the left of the Drill-button) -- for some reason, the undo-function is de-activated.

So, if you run into this issue -- just de-activate that filterpane button.

Weird

Peter

Only showing lines with one or more empties

I noticed this question on the BOB forum -- and I thought I'd have a stab at it. Here goes:

Consider the following table :

 

Some months have data for all years, some only have data for one or two of the years. The person on the BOB forum asked, if it's possible to only show those where one or more of the years does not have data for one of the months.

Our first step is, we need to determine, just how many years there are.

=Count([Year] ) in Report - we call this calculation TotalNYears

Next, we need to determine, the number of years present for each of the months.

=Count([Year] )in ([Month]) - we call this one NYears

Next, we need to subtract those two.

=[TotalNYears] - [NYears] and we call that one : DifferenceNyears

and last but not least,

we filter that table for [DifferenceNYears]>0

 

Nice, isn't it ;) If you set the filter to : differenceNYears = 0 then, you only have those where each year has data. In fact, I remember writing a blogpost a few years back where I showed that last thing, and how to do it at the query level. It involved combining multiple queries.. this method is MUCH faster.

Peter

A Cumulative Index versus a Target

Let's assume, you need to attain a certain target each month. Yeah, I know, sounds farfetched - um - not.

At the end of the year, you need to attain the cumulated total of the monthly objectives.

So, every month, you would like to know, how well you did, so far, compared to the objectives of every month so far, and the total objective for the entire year.

Sounds like a mouthful -- believe me, it is. But it's fun -- you'll see.

The function we'll be using is : RunningSum()

Here's the table I'm starting with :

As you can see, it has numbers for each year and a target line. Now, we'll be adding a line underneath our Target line, that calculates the variance between 2012 and 2011.

The formula to do this is :

=(sum([Sales revenue]) where([Year] = "2012") /sum([Sales revenue]) where([Year] = "2011"))*100
(you could leave the *100 away and format the number as a percentage.. works just as fine)

 

There, that part is done. Now, we calculate the variance compared to the Target. That shouldn't be too difficult, since the formula is basically the same as the previous operation :

 =(sum([Sales revenue]) where([Year] = "2012") /[Target 2012])*100

 

Perfect.Next, the cumulative Index compared to the target.

The formula is almost the same :

 =(Runningsum([Sales revenue]where([Year] = "2012") ) /Runningsum([Target 2012]))*100

the MAJOR difference, is that you need to put the Where inside the brackets of the runningsum.

 

And, we have our cumulative total. Looking good. Last but not least, we could leave that first cel of our Cumulative variance empty, since it's the same as the line above.. so we elaborate our formula a little

=If ([Month]>1) Then ((CumulatieveSom([Sales revenue] Where([Year] = "2012")) / RunningSum([Target 2012])) * 100)

Lovely isn't it :)

Enjoy !

Peter

STRConv function in Reporting Services

You learn something every day. This particular function, I had not heard of before, but then again, I had not needed it before either.

STRCONV

Of course, as a VBScript user, I’m aware of functions like UCase() and LCase, which will turn a text into a uppercase or lowercase text, but for this particular report, I needed the text to be Capitalized.
So, the first letter of each word needed to be in Caps. In my case : Peter De Rop. But one source provides the text in all caps, another in mixed. They have to aggregate at the report-level.

I found the explanation here

StrConv has three functions. Uppercase, Lowercase and Capitalize. It’s that third one we’re after:

StrConv(“AbC dEfG”,1) will show : ABC DEFG
StrConv(“AbC dEfG”,2) will show : abc defg
StrConv([fieldname],3) will show the data Capitalized.(Abc Defg)

Other uses of this function include conversion to Japanese and Chinese charactersets

Sweet

Peter De Rop

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

@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

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

Logarithmic Trendline in Business Objects

Right, looks like I overestimated this one. After doing the previous one, Logarithmic just sounded a bit more scary. It really isn’t. In fact, it was the Microsoft site that got me a bit going. They mentioned something called “Linear Regression”. I looked up the math and it looked impressive. I had no idea, that was what I had already done in the previous article.

That has been my experience so far. A series of terms is unknown to me. This makes it fun to figure out how this stuff works because I really have to figure it out for myself.

Like the function in excel to calculate the Intercept point. On one site, I found this construction :

=index(linest(A1:A20;B1;B20);1)

Then, I found that the intercept function does exactly that. =intercept(A1:A20;B1:B20)

Ah well. it’s an amazing journey – and I’m enjoying every stop along the way.But without further ado, here’s the Logarithmic Trendline.

First off, the math is exactly the same as we had in the Linear trendline except that we don’t use x but ln(x). which is the natural logarithm of x.

for this, I use the function =Ln(runningcount(<Month>)) in <Month>

As with last time, I try to avoid using the month in a calculation, although it would work just the same.

Next up, we calculate the average of that column. In fact, that’s the only difference with the linear trendline.

=Average(LnX in Body) in Report is the function I use for that one.

Like with the linear trendline, we then need to make the difference between X and average X (this time with the logarithms of course) and Y- averageY.

=LnX – AverageLnX in Month

=Y – AverageY in Month

Next, we multiply those two with eachother and add them up.

=LnX-AverageLnX * Y-AverageY

=Sum(LnX-AverageLnX*Y-AverageY in body) in report

That last one is going to be the first part of our division.

Like last time, we now need to Square X-AverageX, but this time, it has to be the Ln version. So, the formula would be:

=sum(ln(x)-AverageLnX * ln(x)-AverageLnX  in body)in report

That’s going to be the second part of our division.

Next, we divide those two.

And we have our Direction Coëfficient.

Next up, we need the interception.

For this one, we need to subtract from AverageY

=<AverageY> – (<AverageLnX>*<LnCoëfficiënt>)

This formula, again, is the same as Linear trendline, but we use the one where we calculate the logarithm of X.

the next formula is : (Coefficient*LnX)+ InterceptLn

And then, we convert this baby to a chart.

And, it has the typical Logarithm curvature

Here is the version Excel comes up with – and again, they are identical.

There. A logarithmic trendline is perfectly possible in Business Objects. One less reason to convert your Business Objects Report into an Excel sheet.

Next up : Exponential Trendline

Have a nice day !!

Binabik

Trendlines in Business Objects

This is what we would want.A trendline in Business Objects.

 

It is not possible to let Business Objects insert a trendline in a chart automatically. It’s a feature that is simply missing. This is what I have been living with until now, it simply isn’t possible, end of story. And that makes me rebel. I want to make it possible.

Two problems : I’m not a real good mathematician. The math to achieve trendlines isn’t easy math either.

But, good news, I figured it out – and it works. Over the next few articles, I will hand you the math to add trendlines to your charts.

Our first type of trendline : A LINEAR TRENDLINE

The functions Excel uses to calculate that trendline looks like this :

and this

That’s the sort of math that makes my head spin – but after 5 minutes, I figured out how it works and then it’s easy. In case you don’t know, the x and y with a dash over their heads are averages.

The first function gets you the “direction-coëfficiënt”, the second gives you the interception point. Both are needed to calculate the trend.

Step1:

Calculate the average on both X and Y.

In this case, I used the formula : Average(<Sales revenue) in Report. If you don’t know what the “in Report” stands for, you should have a look at the articles about contexts. It’s not an easy subject.

With that, we have the average of Y and we also need the average of X. This might be a bit strange. Basically, you just need to find the middle of the X-axis of your chart.

So, we need to count how many items there are and find the middle of that. In fact, for our 12 months the middle is not 6 but 6,5 because it’s an even number of lines. So, what I end up doing is using a runningcount function :”=RunningCount(<Month)” Which I then take an average on.

the function at the end is : =Average((RunningCount(<Month>) )) In Report. which I define as variable <AverageX>

This means, that at this point, we have the two numbers with the dashes over their heads. Next, we need to subtract the X and Y values from it.

For X, this is : =((RunningCount(<Month>) In <Month>) - <AverageX>) In <Month> which I define as a variable X – AverageX

Next, we need Y – AverageY

=(<Sales revenue> - <AverageY>) In <Month> which I define as Y – AverageY

Next, we need to multiply those : =<X - average X>*<Y - AverageY> and add them up.

This gives us the first part of our division, specified at the beginning of this document.

For our second part, we need to Square x-divisionX and add them up.

that’s :=Sum(Power(<X - average X> , 2) In  Body) In Report

Last but not least, we need to divide them to get the direction Coëfficient.

Now that we calculated b, we can proceed to calculate

This translates to :

=<AverageY> - (<AverageX>*<coefficient>)

and that’s our “Intercept” which amounts to the Intercept function in Excel.

Now we can calculate the values for our Trendline. The function in this case (for a linear Trendline is:

y = mx + b

m = direction coëfficient

b = intercept

the x-Value is our runningCount on the month (remember)

So, now we transform this to a chart :

I had a great time trying to figure this one out, so I hope it’s useful to anyone. Next up Logarithmic trendlines. It won’t be easy, I can promise you that

 

ah.. last but not least, this is what that trendline looks like in Excel

Isn’t that – entirely – identical Lovely.

Cheers

Binabik