A #WaterFall Chart in #BusinessObjects

One of my students asked me if it is possible to build a Waterfall chart in BusinessObjects. After some research, I can now tell you : yes, it is possible, and here is how.
First off, here’s our endresult. The chart displayed here is the version in DeskI, but WEBI can do it just as fine. In fact, I’ll be showing the procedure with Rich Client.

In Rich Client, I’m making a query that retrieves the data :

It lists a number of components that comprise our revenue. Next, I’m using the procedure I found here. The person who wrote that, shows how to make a waterfall chart in Excel. And it works just fine for Business Objects too.
Like for Excel, we’re going to calculate: Datum, Padding Plot and CrossOver.

First, datum: =RunningSum([Actual value]) – we won’t be displaying this in the end-chart, but it’s needed to calculate the others.

Second, Crossover : =If((([datum]-[Actual value])*[Actual value])<0 And (Abs([Actual value])-Abs([datum]-[Actual value]))>0) Then( [datum]-[Actual value]) Else(0)

Third, padding : =If([Crossover]<> 0) Then (0) Else(If(([datum]-[Actual value])*[Actual value]>=0) Then([datum]-[Actual value]) Else([datum]))

And last but not least, Plot : =If(([datum]-[Actual value])<>0 And [Crossover]=0) Then( If([datum]<0) Then(-1) Else(If([datum]-[Actual value]<0 )Then (-1) Else(1)))*Abs([Actual value])+[Crossover] Else (If([datum]<0)Then(-1)Else(1))*Abs([Actual value])+[Crossover]
For DeskI, all you need to do is replace the “[” and “]”  symbols by “<” and “>”.
So far, so good.
Next, we convert our table into a stacked chart :

Please note that the actual value and datum are not part of this chart.
Now for the trixy part. Padding needs to be made transparant. In the Properties pane of the chart, change the colour for padding to 240,240,244 – the next two colours need to be set identical. They’re the colour of your actual chart.

Ah, and last but not least, in DeskI, stacked charts don’t show negatives by default. So, in DeskI, you’ll need to change that :

There.. check the checkbox “Show Negative values”.

Looking good
Peter De Rop

Presenting a conference in Lync

These are the steps I had to do, in order to get my Lync session up an running.

Step 1) Invite people into the session:

At the top, make sure you select it to be an online-meeting. This automatically creates the mail and the necessary hyperlinks. In the options of the meeting (Meeting options) it is possible to state that the other attendees should have their mikes muted – which might be the better choice. Of course, the presenter can unmute the mikes if necessary.

Step 2) Recording

The makers of Lync made it somewhat hard to locate the record button.It’s located under the double arrow icon on the chatwindow. Press it. When you with to stop recrding, the stop recording button is also here, after which Lync will ask where you wish to save the .WMV file that is the product of this recording.

Step 3) Sharing your desktop. From the top menu, choose Share desktop and start presenting yoru material with Powerpoint/applications you wish to explain.

There, now I’m all set for my first online presentation next week

 

Cheers

Peter De Rop

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