Bubbeling data

Here's an interesting problem I ran into last week:


Date                Code    Emplid Name of the Employee
2012/12/01 --  DTA -- 123 --  John
2010/12/10 -- XFR -- 123 -- John
2012/12/05 -- DTA -- 456 -- Bert
2012/05/08 -- DTA -- 567 -- Pascal
2000/06/04 -- DTA -- 567 -- Pascal
2000/05/01 -- XFR -- 567 -- Pascal



Right. My boss asked me, in the above list, to show him the transfers for people that got transferred, and for everyone else, he needed their most recent situation.

The tricky part is : you can't filter on a code and you can't filter on a date.

Part of the solution is of course to be found in the article where I showed you how to get the two most recent items. That is, we're going to turn our date into a number :

=(year([Date])*10000)+(monthnumberinyear([Date])*100) + daynumberinmonth([Date])

First, we need to create a break on Emplid of course.

CalcDate        Date             Code    Emplid Name of the Employee
20121201 -- 2012/12/01 --  DTA -- 123 --  John
20101210 -- 2010/12/10 -- XFR -- 123 -- John
20121205 -- 2012/12/05 -- DTA -- 456 -- Bert
20120508 -- 2012/05/08 -- DTA -- 567 -- Pascal
20000604 -- 2000/06/04 -- DTA -- 567 -- Pascal
20000501 -- 2000/05/01 -- XFR -- 567 -- Pascal


And then, we can create a rank on CalcDate and get our most recent items. Easy. BUT. There was an additional requirement. If the employee had a transfer, we need that person's most recent transfer.

Easy as pie :

=CalcDate * if([Code]="XFR") then (1000) else (1)

Of course, this messes up the date-look of Calcdate a bit, but it gets the job done. When you rank on it, you get :


CalcDate            Date             Code    Emplid Name of the Employee
20101210000 -- 2010/12/10 -- XFR -- 123 -- John
20121205       -- 2012/12/05 -- DTA -- 456 -- Bert
20000501000 -- 2000/05/01 -- XFR -- 567 -- Pascal

Mission accomplished :)

Cheers

Peter

Forecast formula in Business Objects

I noticed someone searching for the following : target sales revenue formula

Obviously, it's rather simplistic, but here's a formula to try calculate a future number, when you already have some numbers from the past.

Let's say we had numbers for 4 quarters and 2 years.

2010 Q1 1000
2010 Q2 500
2010 Q3 900
2010 Q4 800

2011 Q1 1250
2011 Q2 800
2011 Q3 800
2011 Q4 700

and we want to figure out, if that trend continues, what the numbers for 2012 will look like. Here's how:

=(sum([Sales] where ([Year]="2010"))  * 0.25) +(sum([Sales] where ([Year]="2011"))  * 0.75)

For Q1 2012, this would give : 250 + 937.5 = 1187.5

Of course, you could add a fraction, based on your own business knowledge, but it's a start.

Random selection of 300 in Business Objects

Now that was cool.

My boss said. I need you to make me a Business Objects Query. The KUL University asked us to provide a random selection of employees.

They need the selection to have the right percentage of Age groups in it.
The group should have the right amount of Men and Women in it
It should represent our departments and job functions proportionately.

You'll probably need two days to set this up.

I don't know much about statistics, but I do know that a random selection of 300 out of 9000, if the selection is truly random, I don't have to bother with the rest of that stuff, the groups will be properly represented.

So, I went ahead and made a query that gave me the Company's population.

9000 people
47% Women
53% Men

Then, I applied the following little trick -- and this is the purpose of this very article:

In Business Objects Query panel, I chose to write custom SQL and I then put this little piece of code there: at the very bottom of the SQL.

ORDER BY dbms_random.value (only for Oracle)

This will randomize the list.

Next, in the query's options, I set the option to only return 300 results.

15 minutes after my boss told me to start doing a 2-day job, I finished it.

Of course, I made the same percentage calculations and obviously, they returned the same results as the master list.

If you need to do this in other DBMS systems, the code is:



MySQL:

ORDER BY RAND()
LIMIT 1


Microsoft SQL:
ORDER BY NEWID()

Enjoy

The Activity Universe

Some of the most common questions people ask themselves, can be solved quite easily, by using the Auditing universe by Business Objects themselves.

How long does a report take on average to refresh?
In which reports do we use a certain objects?
Maybe, when a report fails to refresh, you want to know about it?
Maybe, when a report runs a LOT faster than usual, you want to know about it?(because chances are, the report will be empty then)

That's when you use the Activity Universe.
...

The Activity Universe exists in every Business Objects Environment, but you need to have auditing switched on. Many environments do have this.

Ask about it to your local Business Objects Administrator :)

Peter

The versions of Business Objects

Like me, you probably get a teeny tiny bit confused with the various versions of Business Objects.

So, let's list them :


When I first ran into Business Objects, it was at version 5. That was back in 2003, but version 5 had been around for quite a while by then.

Version             DeskI             WebI
5.0                      Yes (5.0)        Yes (2.7 and 2.8)



Then, some time later, I had my first encounter with version 6


6.0                      Yes (6.0)        Yes (6.0)
It was quite buggy -- a very telling fact that you have version 6, is when you go to Data > New Dataprovider in a blank document and you only have one (non-functional) radio button.

Later on, version 6.5 was introduced -- it was much more stable

6.5                      Yes (6.5)        Yes (6.5)
This version, to me, is still the reason why so many people use BusinessObjects. It's stable -- both the WebI version and the DeskI version are rock-solid.

Then, Business Objects, the French company, bought Crystal Reports. Some time later, they brought out version XI. It was a new WebI and many people were confused, since it didn't include a DeskI of BusinessObjects.

XI                      No         Yes (XI)

Of course, this could not remain for long, and some time later, they brought out version XI R2

XIR2                      Yes(XI R2)         Yes (XI R2)

In fact, from this time forward, we started talking about Desktop Intelligence.
XI R2 was a bit limited -- linking dataproviders didn't work, ranking didn't work yet.. so, no surprise, soon thereafter we had XI R3

Version                  DeskI                 WebI               Rich Client
XIR3                      Yes(XI R3)         Yes (XI R3)    Yes (XI R3)

It still included a DeskI version, but from this point onwards, new features were included in WebI and not in DeskI. Point and fact : Tracking. WebI came with a feature that allowed you to see if a piece of data had changed since last refresh. So, anything in red had descended, anything green had increased and anything with a line across it had vanished.

XIR4                      No         Yes(XI R4)

And now, since December 2010, we have BI4 as Business Objects XI R4 is referred to. It no longer includes Desktop Intelligence although there is a plug-in that exists, to allow DeskI XI R3 reports to be published onto Webi XI R4.

Cheers

Peter

The two most recent items (Business Objects)

This is going to be an easy one. Just a tip.

Let's assume, you want to know information for the two most recent pieces of data. Issue is, how do you get those most recent ones.

First, you need a measure -- based on a date.

Year([your_date]) this will get you the year, which is more than unique enough.
+Monthnumberinyear([your_date])/12

to the year, we add 1/12 of our month.

+daynumberinmonth([your_date])/31

and to that, we add 1/31 of the day.

The thing is, we don't want

15/March/2012

to be bigger than

1/April/2012

if we divide, we end up with numbers that stay in proportion.

An alternative, and probably an even better way to do this, is to multiply:

(Year([your_date])*10000) +    'that gives us 20120000
(Monthnumberinyear([your_date]) * 100)+   ' that gives us 201212
daynumberinmonth([your_date])  ' that should make 20121224

Interesting, it keeps the date readable. Which is always useful.



So, now we have our measure to base our ranking on -- now it's easy to get the two most recent ones isn't it.

Cheers.

Peter

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

Grouping data

Time for a quite basic use of functions and formulas. This is stuff that used to be very automated in Desktop Intelligence -- with WebI, that has changed.

The way in BusinessObjects to group data, is the if function. Yes, I said it right, it's a function.

Lets say we wanted to group the following data :

ProductCode   Items sold
ACA               100
BAC               150
DTA               120
XCA               130
RRA               170
UUA              135

The first three are product group "Alpha", the last three are the product group "Beta". The number in the last column is, say, the number of items sold.

The syntax would therefore be :

=if([ProductCode] inlist("ACA";"BAC";"DTA")) then("Alpha") else("Beta")

The syntax somewhat changes, if you want it to be three groups.

=if([ProductCode] inlist("ACA";"BAC";"DTA")) then("Alpha") elseif([ProductCode] inlist("ACA";"BAC";"DTA")) then("Beta") else("Gamma")

As for you Dutch speaking people -- be warned, the translation of if then else in dutch is incorrect.

=Als([ProductCode] inlijst("ACA";"BAC";"DTA") then("Alpha") else("Beta")

they forgot to translate the then and else in dutch.

Now, lets consider the And operator :


ProductCode  Code    Items sold
ACA                  A          100
BAC                  A          150
DTA                  A           120
XCA                  A          130
ACA                  B           170
UUA                  A           135

If we performed the previous code, we would get some incorrect data.. so :

=If([ProductCode] InList("ACA";"BAC";"DTA") And [Code]="A") Then("Alpha") Else("Beta")

Enjoy

Peter

Common sense UI behaviour in #BO

So far, Business Objects has always behaved in a very common sense like way. This is the sort of behaviour, people have gotten used to over the years -- and I think, it was rather silly to remove some of it, in the latest installment of BusinessObjects WEBI and Rich Client.

Behaviour	DeskI XIR3	Webi &Rich Client XIR3	WEBI BI4	Rich Client BI R4
Double click the corner of a table to:
Get it's properties	Yes	No	No	No

I agree it is debatable, but:
In DeskI, a table had a table dialog to modify it’s properties and it was accessible through a double-click. Webi R2 and R3 did not have this dialog, since all properties had gone into a properties tab – that’s common sense.

With BI4 however, the properties-tab has disappeared again and has been replaced by a dialog. Then it would be common sense to have the double-click behaviour return as well.
I do like the improved dialog when entering a cell. The fact that the fx button is right there and then, is a charm.

Behaviour	DeskI XIR3	WEBI XIR3	WEBI BI4	Rich Client BI R4
Double click a tab of a report to
Rename the tab	Yes	Yes	No	No

A small thing, I agree, but it’s these sort of inconsistencies that can drive people mad.

Behaviour	DeskI XIR3	WEBI XIR3	WEBI BI4	Rich Client BI R4
Drag a tab of a report to
Move the tab	Yes	No	Yes	Yes

Nice addition. The visual que is a bit low-key, but it works fine.

Behaviour	DeskI XIR3	WEBI XIR3	WEBI BI4	Rich Client BI R4
Right-click a column to
Add a calculation	Yes	No	Yes	Yes
Add a sort	No	Yes	Yes	Yes
Manage sorts	No	No	Yes	Yes
Remove a Column	Yes	Yes	Yes	Yes
Remove the contents of a column	Yes	Yes	Yes	Yes
Add a Column	Yes	Yes	Yes	Yes
Add a Row	Yes	Yes	Yes	Yes
Remove a Row	Yes	Yes	Yes	Yes
Remove the header	Yes	Yes	No	No
Get the properties of a table	Yes	No	No	No
Get the properties of a cel	Yes	No	Yes	Yes

Very happy with calculation in the right-mouse being back from the dead, but. It used to be stored in the Calculation Folder, which made sense. In Insert is just less intuitive (to me). I’m sure I’ll get used to that.

Adding the Sort to the right-mouse is a very nice addition – especially because it’s hard to find with the tabs. It’s located under Analysis. And if you had been adding Formatting rules (Alerters) before, it’s hidden behind that tab as well. (No, I don’t like the tab-menus.)

VERY HAPPY WITH MANAGE SORTS TO BE ADDED UNDER THE RIGHT MOUSE!!!

Getting the properties of a table means that you HAVE to right-click on the border. Right-clicking somewhere in the table won’t give you the properties of the table. Again something that can drive people mad.

Why the Circle-shape handles at the corners of a selected table ? That implies the table can be dragged larger at the corners – it can’t.

Behaviour	DeskI XIR3	WEBI & Rich Client XIR3	WEBI BI4	Rich Client BI R4
Right-click a table to
Get the properties of a table	Yes	No	Yes	Yes
Align tables	Yes	Yes	Yes	Yes

The only objection I have here is the fact that you can no longer drag a selection. This is rather a step backwards. Ctrl-selecting two tables is a pain. Dragging is so much easier. I mean, it was even possible in the WEBI version of XI R3.. Why would you remove a behaviour that is SOOO standard.

Behaviour	DeskI XIR3	WEBI & Rich Client XIR3	WEBI BI4	Rich Client BI R4
Drag a column
To remove it	Yes	Yes	Yes	Yes
To move it	Yes	Yes	Yes	Yes
To set is as a header for crosstab	Yes	Yes	No	No

The delete button now removes, not the content of a cell, but the entire column. And the fact that you no longer can convert a table to a crosstab with the good old Click and drag is a bit sad. It used to dazzle my students who were used to the unwieldyness of Pivots in Excel.

Behaviour	DeskI XIR3	WEBI & Rich Client XIR3	WEBI BI4	Rich Client BI R4
Right click a header
To delete it	Yes	Yes	No	No

This is pretty annoying. If you want to remove a Header, you have to do it through the properties of the table. Or the buttonbar at the top. Yuck. And the funny thing is, it was there in Rich Client R3 and Webi R3. A further.. imperfection.. is the fact that it always says “Show header”and never: “Hide header” but I can live with that

Behaviour	DeskI XIR3	WEBI & Rich Client XIR3	WEBI BI4	Rich Client BI R4
The most important button should be BIG and in plain sight
Refresh	Yes	Yes	No	No
Modify Dataprovider	Yes	Yes	No	No

90% of the time,  people who use any kind of BI-tool will want to refresh their data. The button to do that job should at least be easy to find for people with glasses.

I’m not a big fan of the new Tab-interface – it feels more like a game of Memory to me but I’ll learn it. The most important buttons, the ones you need to have under your fingers all the time, should be available without entering into tabs. It should be at the top-level. Modify Dataprovider should be easy to find and big. (same for Bold, Italic, Underline and change colour tbh.)

Oh, and we lost the ability to create a query based on a Universe when not connected to a server. That too, seems to be something that should be fixed.


Conclusion

All in all, for a road-worn, weather-hardened Business Objects user, this new interface is quite a change. It has always had it’s own feel (remember the days when pressing delete on a table didn’t work and you had to press Shift-Delete?), but I think the devs have taken this one to a new level. Some changes (the tabs) seem to be a change for the sake of change. Others (like managage sorts) are quite a treat.

Still trying to get my head around #BI4, but so far, it’s been nice making it’s acquaintance, Even if I’m experiencing quite some resistance.

Peter

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