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

Seats open for basic training BusinessObjects

Still some seats open for the Basic training Business Objects of 19 and 20 january 2012.

Find the registration form here

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

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