My External Memory

A #WaterFall Chart in #BusinessObjects

2012-01-08T19:47:00.001+01:00

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

2012-01-04T14:56:00.001+01:00

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

2012-01-04T10:17:00.001+01:00

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

2011-12-21T18:17:00.004+01:00

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

2011-12-21T15:38:00.001+01:00

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

2011-12-14T13:24:00.001+01:00

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

2011-12-14T12:04:00.001+01:00

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

2011-12-09T09:57:00.001+01:00

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

2011-12-06T17:20:00.001+01:00

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

2011-11-30T14:12:00.001+01:00

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

2011-11-23T09:05:00.001+01:00

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

2011-11-18T10:07:00.001+01:00

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

2011-11-18T09:30:00.001+01:00

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

2011-11-16T10:57:00.001+01:00

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

Logarithmic Trendline in Business Objects

2011-11-13T21:09:00.001+01:00

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

2011-11-10T19:28:00.001+01:00

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

Logarithmic Scales in Charts

2011-11-02T16:37:00.001+01:00

This is an underused feature, as far as I’m concerned. Every time when I mention these things, people’s eyes go all glazy, but it is a pretty cool feature of charts.
First off, what are logarithms, for those of you who don’t already know.
The logarithm (and I mean those that are based on the number 10) are the power to which number 10 needs to be raised to reach a certain number.
So, Log(10) = 1 because 10^1 = 10 and Log(100) = 2 because 10² = 100. In fact, until the 1970’s, logarithms were used to multiply and divide. Because if you subtract two logarithms, you divide and when you add two logarithms, you do, in fact, a multiplication.
So, it can be a way to write down large numbers.
When used in charts, they can be very useful when you’re looking at numbers that are very far apart.
Take for example this table:

It contains Sales revenue and Margin for a period of three years, with numbers varying from -173 to 205000. If we would put this on a decimal chart, the result would be this :

The good months stand out quite clearly. We did extremely well in September. But as a result, the bad results are masked. They just don’t show up, because near the 0-level, the scale is too large. A Logarithmic scale solves this :

September still stands out, but now, it is in proportion. August, which is extremely low, also shows up a lot clearer.
So, when looking at your numbers, remember to use Logarithmic scales if you want to get a clearer picture of what your data is really trying to tell you.
Binabik

Logarithmic Scales

2011-10-30T22:33:00.001+01:00

At the moment, I'm learning a bit more about Logarithms. It's an interesting concept, especially because Business Objects has it and so far, I have never explained it, except how to switch it off.

First off, I never had much math in school, so I had to find out what Logarithms are and so I did, and it's great stuff.

I didn't know that Logarithms used to be the way (up to the 1970's) how people multiplied or calculated Powers. All done by using Logarithm tables.

In case you are one of those people who don't know what Logarithms are. Here's the deal.

In fact, there are several types of Logarithms, but the most common one is 10-based. (If you ever hear someone mention Natural Logaritms, that's another one, based on 'e').

A logarithm is the power 10 has to be calculated with, in order to reach a given number.

So, Log(100) = 2 because 10 to the power of 2 = 100.

What this gives you is a way to reduce numbers and keep their proportions. But that was not entirely what I was after. I wanted to know how a Logarithmic scale worked.

On a decimal scale, 1, 2, 3 all have the same interval: 1. So you would get a straight line.

On a Logarithmic scale, the difference between 1,2 and 3 is calculated by proportion.

When a number goes from 1 to 2, it has actually doubled, when it goes from 2 to 3, it has been multiplied by 1,5 from 3 to 4, it has been multiplied by 1,3. So in stead of a straight line, you would get a curve that indicates the proportional difference between numbers.

This is especially useful when you're looking at numbers that are far apart. Stock market information for instance can be displayed much more accurately with Logarithmic scale charts.

But I'll be looking into this a lot more in the near future.

Binabik

stdDev and stdDevP turned practical

2011-09-06T13:39:00.001+02:00

Like many of you, I took statistics in school. And, if you’re like me, stDev sounded interesting, but you were not yet able to turn it into a practical use. Here’s how :

First, there’s two stdDev functions : stdDev and stdDevP.

If the list of data you’re performing this function on is complete, then you use stdDevP, otherwise, when you only selected a part of the data, use stdDev.

The way it is calculated is this :

First, every number is subtracted from the average (mean) and squared.

5,3,7 average = 5

5 – 5 = (0)² = 0

3 – 5 = (–2)²= 4

7 – 5 = (2)² = 4

Next, an average is taken and square-rooted.

sqrt(8/3) = 1,632 and that’s how much, on average, those three numbers vary from eachother.

So, if a number is higher than the average + stddev, it is statistically significatly higher than the average.

If the number is lower than the average – stddev, it is statistically significantly lower than the average.

So, now you can create charts, alerters etc using those two numbers :

Above Avg+stddev = Green

Between Avg+stddev and Avg-stddev = white

Below Avg-stddev = Orange

Soundex

2011-09-06T11:05:00.001+02:00

At the moment, I’m brushing up a bit on my SQL knowledge. It’s paper-thin and building reports in Reporting Services does require a pretty decent bag of tricks.

The latest trick I learned is Soundex.

select firstname, LastName, SOUNDEX(lastname)
from SalesLT.Customer
where SOUNDEX(lastname) = SOUNDEX('Brown')

There you have it, the Soundex function is able to find names in a database that sound the same.

In this case, SQL will come back with names like : Brown, Brian and Bruno.

Nifty.. really nifty

odbc

2011-07-12T09:34:00.001+02:00

I keep forgetting this one :

The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

Windows 7 64bit has two of these... and guess what, the 64bit one only shows SQL server.. and I need to connect to access occasionally, for which I need the other one :)

Binabik

A hairy java bug in Business Objects XIR3

2010-10-22T13:59:00.003+02:00

Java has always been a source of bugs in Business Objects. I still remember, with version 1.2.4, the amount of errors we received at a customer. Business Objects XI R3 seems to suffer from a nice little bug too.

If you want to solve this problem, you can go to Control Panel, Java and select this option :

Hide Block feature in Business Objects

2010-08-24T16:57:00.001+02:00

A very obscure feature in Business Objects is the "Hide Object" Checkbox. As far as I know, there is no course, where you show that particular feature. Here's how it works.

Or maybe I should start by saying : don't just switch this feature on without filling in the condition window that's right next to it. If you do, the object, on which you check it, will just disappear, which is a bit daft. In case you did that, go to View>Structure, to locate the item, on which you activated it and switch it back off.

This feature only works well, when you also enter a condition. That condition should return a boolean.

Double-click on the block, on which you wish to activate the feature, and go to “Appearance”. Check the checkbox and enter a function, under which condition you wish to hide the block. e.g. : =Sum(<Sales revenue>) < 5000000

Notice, this function will return a true or false. It can be necessary to use contexts to calculate the right number.

The result is that the sections where I wish to see the table, will show the table, the others will show up collapsed, because there’s no content in them. Nice feature – probably underused.

Binabik

Signing Powershell scripts

2010-05-27T15:21:00.001+02:00

Phew. I did it. Thank you Bruce Payette for the great book you wrote.

If you want to read the book it’s on Amazon.com.

Three reasons why you would want to get it.

1) It’s very well written (which is rare among books about programming)

2) It’s a good and (at times) a funny read. (which is next to impossible to find)

3) This book is written by the person who helped design Powershell.

But let me get to the code-signing bit :

You’ll need the Windows SDK, because it contains ‘makecert.exe’

I just copied the makecert file in my Powershell directory to make the commands a bit shorter.

If you’re under Windows 7, make sure you start Powershell ‘as Administrator’. Otherwise, some commands will fail.

in your Powershell window, type :

./makecert –n “CN=PowerShell Local Certificate Root” –a sha1 –eku 1.3.6.1.5.5.7.3.3 –r –sv root.pvk root.cer –ss Root –sr localMachine

This will pop up a dialog to enter passwords. And we have just created a Local Certificate Authority.

Next, we ened to make a signing certificate :

./makecert –pe –n “CN=¨PowerShell User” –ss MY –a sha1 –eku 1.3.6.1.5.5.7.3.3 –iv root.pvk –ic root.cer

great. that’s that. Now, all we need to do is sign our scripts with that key. Here’s how :

First change the Execution policy to allsigned :

set-executionpolicy AllSigned

Next, we load our certificate into a variable :

$cert = @(Get-ChildItem cert:\CurrentUser\My -Codesigning)[0]

then we sign the file :

Set-authenticodeSignature test-script.ps1 $cert

you should be able to execute your script now.

Binabik

Being careful in Business Objects (DeskI)

2010-03-09T09:38:00.001+01:00

When you’re running a query, one of the things you should consider is that you might forget a filter and get way too much data from the database.

Secondly, that query might run for multiple hours and you don’t have multiple hours because you want to get to the part where you layout the report before running the query and returning all the data.

Here’s a few things you can do :

1) Create the query, save the query but don’t run it yet.

In the query panel, instead of blindly hitting Run, Hit Save and Close. This will save the query, but you won’t get data. Next, you can start modifying the layout of the report (use View>Structure) and when you’re ready, refresh the report to get your data (e.g. before leaving the office)

2) Limit the amount of data that can be returned by the query

If you don’t know in advance, how much data you’re going to get, maybe it’s a good idea, at first run, to limit the returned data to the first 10 or 100 lines, in stead of getting the lot. Especially, if you’re in doubt which kind of data will be returned by each of the fields.

When you click the options button, you get to choose how many lines you would like to get from the database:

this will of course return ‘Partial results’.

There you go folks – for the sake of prudence, don’t get all your data at once.

Binabik

A Document Map in Reporting Services

2010-03-03T09:46:00.001+01:00

This topic deals with Report Builder 2.0 – it’s also possible to do this through Visual Studio, but that will have to wait for later.

Instead of setting all sorts of filters, you can choose to get all the data from the server and make it more accessible by creating a table of contents for it.

So, when you click the table of contents in the left column, you jump to that particular part of the report.

That’s what this particular article is about. How do you create a ‘Document Map’.

First, you need to insert a ‘list’

Next, rightclick the left bar of the list, change the tablix properties to make the list refer to the right dataset.

Subsequently, rightclick the left bar and change the group-properties

In those group properties, on the advanced-tab, choose, which field you would like to have as a document map.

Drag the field in the list and drag any other items inside that list. Tables or Other lists will do fine.

Nested lists can also have nested document maps – works great.

Enjoy

Binabik

Relative positioning in Business Objects

2010-02-23T12:47:00.001+01:00

When you’re building reports in Business Objects, sooner or later, you’re going to have a table where the length not fixed. So, one day, you have 10 lines, the next day, you have 50 lines. Tables that are located behind them are then suddely overrun by that table.

The properties of the table allow you to choose how tables behave.

(the name of that table can be configured also in ‘Table format’

Cheers,

Binabik

Day of the week of first day current year

2010-02-18T11:50:00.001+01:00

I noticed on Google analytics that someone was looking to calculate in Business Objects, what day, the first day of the year was.

Here's how :

=DayName(ToDate(Year(CurrentDate()) & "/1/1" , "yyyy/mm/dd") )

If you want the number of the day

=DayNumberOfWeek(ToDate(Year(CurrentDate()) & "/1/1" , "yyyy/mm/dd") )

Yet someone else was looking for an equivalent of Instring in Business Objects. Look no further.. it’s Pos()

pos(“abcdefg”, “g”) = 7, so, g is located in the 7th position.

Easy as pie

Binabik

Showing the average on a chart in Business Objects

2010-02-18T11:01:00.001+01:00

In my latest courses, lots of folks had questions about the way Contexts work. So, here’s another way, contexts are used :

You want to show the average of something on a chart, as a horizontal line. That means, you will have to create a column with that average in a table, where the same number is shown in the entire column.

Month	N° visitors	Average
1	25	25
2	100	100
3	72	72
4	97	97
5	93	93
6	103	103
7	111	111
8	23	23
9	87	87
10	98	98
11	39	39
12	111	111
Average	79,9

It’s pretty clear, the formula =Average(<N° visitors>) won’t do the trick. It just returns the number itself, because it operates in the context of the month. To solve this, we can use 2 formulas :

Average(<N° visitors>) in Report

Average(<N° visitors>) forall(<Month>)

The first one is absolute, so it will always return the same number – no matter in which table you put it. The second one is relative – it depends on the dimensions already in the table.

One way to do this is by choosing : Data> Define as variable and choosing Evaluate the formula in its context.

Both of the above will return :

Month	N° visitors	Average
1	25	79,9
2	100	79,9
3	72	79,9
4	97	79,9
5	93	79,9
6	103	79,9
7	111	79,9
8	23	79,9
9	87	79,9
10	98	79,9
11	39	79,9
12	111	79,9
Average	79,9

Now, we turn this into a chart :

Not exactly what we need, but, getting there.

Rightclick the chart and choose “Format Chart”

On the Series tab, click “Add” and make it a line chart, next, you drag the average from the other group to the new group.

And we get :

Cheers folks

Binabik

Calculating average in Business Objects

2010-02-16T23:26:00.010+01:00

At a customer, someone asked this particular question : In a crosstab, if certain lines return empties, how do you calculate average while evaluating the empties as 0 ?

As it is, when you calculate the average with the regular function, BO will not take into account the missing information.

	2007	2008	2009
Dog	10	10	10
Cat	10		10
Horse	10
Pony	10		10
Average	10	10	10

where it should be

Average

10(40/4)

2,5(10/4)

7,5 (30/4)

The solution to this problem is : contexts.

First, we need to count the number of animals

Count

performing a regular count will not give us the right result.

Count(<Animal>) will return a different number for each column. So, we force the count with "in" Count(<Animal>) in Report -- that will return 4 in each column.

Count

So, now we have our formula :

Sum(<Number of Animals>)/Count(<Animal>) in Report

Cheers folks

Binabik

three things I would like in RS

2010-02-16T12:39:00.005+01:00

1) Sorting Picklists

On RS, when you build a picklist, you select your field and the values don't get sorted, and there is no option to do so. I found a workaround, but it's not very clean.

2) an option to remove the item(Select all) in multivalue parameters

When you set a picklist to multi-value, RS automatically adds the option "Select all"

Since a query is very often limited to 999 inlist values, you run into trouble pretty fast, and it's a big performance issue, if people just select the lot.

Would be nice to have this option.

3) some way to pre-test scripting.. the way it is now, scripting is very under-developped. In fact, it's just a window where you can enter text.. period -- wouldn't mind some intellisense :) .

Binabik

Sorting picklists in RS Reportbuilder 2.0

2010-02-16T12:36:00.004+01:00

To sort a picklist in reportbuilder, when working with a model, you can use the following workaround :

* add the field twice

This is pretty daft : you can't just sort the list. In t-SQL, it would be sooo easy, but on a model, your hands are tied. Once you add that same field a second time to a query : disco, it's sorted.

Took me a while to figure out that it was actually the addition of a field a second time that sorted the thing.. and not any function that was applied to the field.

Gotta love RS.

Binabik

List field and paths from an RDL file

2010-01-22T16:06:00.005+01:00

For a customer, I wrote this particular script. On a report based on a Model, it generates an Excel file listing all the fields and paths that have been used in the RDL file. All you have to do is store the rdl files in a directory somewhere on your c-drive, put the below script in a textfile, alter the path and save the file with a VBS extention :

==========================================================================

targetfolder = "C:\foldername\"
set fso = createobject("scripting.filesystemobject")
set objfolder = fso.getfolder(targetfolder)
set fc = objfolder.files
set objexcel = createobject("Excel.Application")
objExcel.visible = true
objExcel.WorkBooks.Add
ObjExcel.Cells(1,1).Value = "Fieldname"
ObjExcel.Cells(1,2).Value = "Path"
ObjExcel.Cells(1,3).Value = "Table"
ObjExcel.Cells(1,4).Value = "Report"
z = 2
for each x in fc
set file = fso.opentextfile(targetfolder & cstr(x.name))
dim strLijn
do until instr(strLijn, "/Hierarchies") > 1
strLijn = file.readline
if instr(strLijn, "<Grouping Name=") > 1 then

objExcel.Cells(z,1).Value = mid(strLijn, 28, len(strLijn) - 32)

do until instr(strLijn , "/Path") > 1
strLijn = file.readline
if instr(strLijn, "!--") > 1 then
strpad = strpad & "\" & mid(strLijn, instr(strLijn, "&")+7, len(strLijn) - (instr(strLijn, "&")+7) - 5 )
strlocatie = mid(strLijn, instr(strLijn, "&")+7, len(strLijn) - (instr(strLijn, "&")+7) - 5 )

end if
loop
objExcel.Cells(z,2).Value = strpad
objExcel.Cells(z,3).Value = strlocatie
objExcel.Cells(z,4).Value = x.name
z = z +1
strpad = ""
end if
loop
strLijn = ""
next

==========================================================================

use with caution :)

Binabik

Reporting services Picklists based on models

2010-01-22T15:53:00.005+01:00

To force a picklist to have a fixed length :

1) In the picklist-query, add the field a second time (you'll use this one as label on your parameter) -- change the code for that field by right-clicking it and changing the formula (on the query panel)

for the example, I'll use a string-field that should be 7 characters long and should get leading zeroes -- a specific case I used this for.

2) change the formula to :
left("0000000"; 7 - length([nameofthefield])) & [nameofthefield]
if the field is numeric :

left("0000000"; 7 - length(text([nameofthefield]))) & text([nameofthefield])

That also sorts the picklist, but if you simply want to sort, just add the same field a second time.

Binabik

Default values in SQL Reporting

2009-08-20T08:58:00.001+02:00

At the moment, I'm building some reports in SQL Reporting Services. A nice task, as always, and it does provide for some unexpected challenges once in a while.

Here's what happens in Reporting services when you use parameters – in fact, I had this particular issue quite a few times – I only just found how to get around it.

Problem description: When using a multivalue report parameter with all values selected by default, in fact, none is selected.

Solution: add the filter IS NOT NULL

I can't begin to explain how hard I hit my forehead with the palm of my hand when finding this one out J

Binabik

Scripted BO 5 excel export

2008-12-04T10:39:00.001+01:00

A customer asked me to make the following script -- some time at google and some writing later, here's what I put together. It takes all the files in a given directory and converts all the BO reports into excel files (no charts though) each tab in each report is of course copied into the appropriate excel file.

Use with care..

'----------------------------------------------------------------------

dim busobj
dim strnomFichier
dim objrep
dim objExcel
dim boEditPopup
dim xlworksheet
dim strname
Dim BOApp
Dim strFilename

set BOApp= createobject("BusinessObjects.Application")
BOApp.LoginAs "USER", "PASS", False
BOApp.visible = True

Set fso=Wscript.CreateObject("Scripting.FileSystemObject")
Set f=fso.GetFolder("i:\")
Set fc=f.files

For each file in fc
    strFilename = file.name
    If Right(strFilename,3) = "rep" Then
    Set objrep = BOApp.Documents.Open("i:\"&strFilename)
    Set boEditPopup = BOApp.Application.CmdBars(2).Controls("&Edit")
    Set objExcel = createobject("Excel.Application")
    objExcel.Workbooks.Add
        objExcel.visible = True
        intreports = 1
        startnumber = BOApp.ActiveDocument.reports.count
     For i = startnumber To 1 Step -1
        Set myrep = BOApp.ActiveDocument.reports.item(i)
        myrep.activate()
        boEditPopup.CmdBar.Controls("Cop&y All").Execute

        Set xlWorkSheet = objExcel.Worksheets.Add()
        strname = BOApp.ActiveDocument.reports.item(i).name
        strname = Replace(strname, ":", "") ' Can't contain this character
        strname = Replace(strname, "\", "") ' Can't contain this character
        strname = Replace(strname, "/", "") ' Can't contain this character
        strname = Replace(strName, "?", "") ' Can't contain this character
        strname = Replace(strName, "*", "") ' Can't contain this character
        strname = Replace(strName, "[", "") ' Can't contain this character
        strname = Replace(strname, "]", "") ' Can't contain this character
        strName = Left(strname, 31)
        xlWorkSheet.Name = strname
        xlWorkSheet.Paste
        Set xlFormatPopup = objExcel.Application.CommandBars(1).Controls("F&ormat")
        Set xlColumnPopup = xlFormatPopup.CommandBar.Controls("&Column")
        xlColumnPopup.CommandBar.Controls("&AutoFit Selection").Execute
     Next
     End If
     If Right(strFilename,3) = "rep" Then
         xlWorkSheet.Saveas "i:\"&strFilename&".xls", True
     End If
     Next

'-----------------------------------------------------------------

My new laptop

2008-10-29T14:10:00.001+01:00

At the bottom of one of my cupboards, I found my old Toshiba laptop. It isn't much, it's got some ram.. not a lot.. some diskspace, not a lot.. and it's got Windows XP.

As a decent computer user, I prefer to only put legal software on my computer. Of course, that software has to function correctly and give me the features that I need.

I'm not a rich man, so I decided to go for the Open Source software that's around.

The first thing I decided on installing was office. I'm not planning to spend too many of my precious Euro's on office -- I won't be using it much on this laptop -- only to consult files that I receive from people mostly.

Open Office -- of course. A swift download, an install, and I have Spreadsheet, Wordprocessor, Image editing soft -- the lot.

Site : http://www.openoffice.org/

So.. what other software do I need. I develop websites -- so I need a webserver -- a database server -- content management -- blogs.. the choice is quite easy : MoWes (fka WoS)

Site : http://www.chsoftware.net/en/useware/mowes/mowes.htm

Great -- now I have a complete development environment with PHP, MySQL and all the nice stuff that comes with it -- but how will I be writing my webpages..

I searched the web over and found a long list of indivual tools like : NVU, Aptana, Selida -- none come close to a decent webdesign tool -- Aptana is coding -- I'm not planning to code my pages by hand if I can help it. Selida offers layers, but you can't do something simple like drag the layer to the desired location..

Site : 25 webdesign tools

A browser. I love Firefox, but the almost nihilistic interface of Chrome got me. Especially because in chrome, you can save a shortcut to a website on your desktop or in a folder. When you open the icon, you get that website in a window without navigation -- in practice, it looks like a windows program has been opened and not a website.

Site : http://www.google.com/chrome

Now I have my e-mail as an icon on my desktop -- I surfed to GMail with Chrome and saved the site as an icon -- it looks and acts like a windows program, with an important downside -- you have to be online to read your mail.

I did the same with the google calendar, google Picasa and google notepad. To store my office documents in a place where I can always reach them, I have also added Google docs -- easy.

site : www.google.com

I blog. So a decent blogging tool would come in handy : Windows Live. It contains LiveWriter in which I'm writing this article, live mail.. live messenger -- Live mail will come in handy to read those mails that are not accessible over a website.

site : http://get.live.com/writer/overview

Next Month

2008-09-19T14:59:00.001+02:00

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",2,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Can’t believe I wrote that function.

=dateadd(“d”, –1, dateadd(“m”, +2, cdate(year(today()) & “/” & month(today()) & “/1”)))

that’s the expression that gives you the last day of the next month in Reporting Services

Calculating Current Month

2008-09-19T14:59:00.000+02:00

function GetLastDayNextMonth()
Dim dt
Dim firstDay
Dim lastDayNextM
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastdayNextM = DateAdd("m",1,firstDay)
lastday = DateAdd("d",-1,lastdayNextM)
GetLastDayNextMonth= lastDay
end function
function GetFirstDayNextMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayNextMonth()),month(GetLastDayNextMonth()),1)
GetFirstDayNextMonth= firstDay
end function

Installing BOXIR3

2008-04-23T11:11:00.002+02:00

The installation of Business Objects XI R3 seems pretty straight forward. Double click the setup file.. next next next.. but.

After the installation, when I tried to surf to any of the pages on my Tomcat server, I got Error 404 application not found.

After a bit of surfing, I found an entry on a forum.

It was a discussion between two people that entirely pictured my problem.. what follows is a howto based on their conversation.

When trying to surf to http://servername:8080/CmcApp, I got an error 404 saying CmcApp not found.

1) go to

C:\Program Files\Business Objects\BusinessObjects Enterprise 12.0\java\applications

and check if the WAR files are there -- if not -- reinstall

2) go to C:\Program Files\Business Objects\Tomcat55\conf and change the tomcat-users.xml file to contain a user (currently logged user f.i.) with rights

like such :

don't forget to restart the tomcat server (service)

3) surf to http://servername:port/manager/html (Tomcat Manager) and login with the username and password you just created

4) add all of the war-file (bottom of the page) from the directory you found in step 1.

5) try starting the CMC again

(this worked for me)

Into the blogging thing

2008-01-11T14:25:00.001+01:00

I have been blogging a short while now -- just a couple of years. Only now, the whole process seems to be streamlining itself. My first articles, I wrote in the webinterface of the blogging website. harsh -- inspiration does not come at a given time.. so I would gather my stuff before the article and then write it on the site.

Then, I discovered BlogJet -- an easy, straightforward tool to blog. Configure it with login and password.. and go ! A big step forward.

Now, some time later, I'm writing in Live Writer from Microsoft -- and it is a definite step forward. The thing I like most is the fact that you can connect directly to the site -- and it copies the template you are using. this means, that you write the article inside a window -- and it should be what you see is what you get. Furthermore, you can connect to multiple blogs.. just select the right one -- the screen changes to the template of that site -- and go ! the article is published.

I'm a fan of Windows Live Writer.

Automatic BO Refresh Script

2007-10-26T10:25:00.000+02:00

Store this in a textfile.vbs and change the folder in which your reports
or shortcuts to reports are stored (yes, shortcuts are ok too)This script will refresh them all (mind the object definition
for a BO 6 system.


download refreshscript

Business Objects Scripted refresh

2007-10-25T14:47:00.000+02:00

a student gave me the following script to automate refresh of a report :

Sub RefreshBO()
Dim objBO, objrep
Application.DisplayAlerts = False
'Open Business ObjectSet objBO = CreateObject("BusinessObjects.Application")
' (when update to version 6)' Set objBO = CreateObject("BusinessObjects.Application.6")
objBO.LoginAs "username", "password", False' (fill in username and password)
'open the report
objrep = objBO.Documents.Open("c:\myfile.rep")

'Show BO
objBO.Visible = True

'Refresh Report
objrep.Refresh

'Save Report
objrep.Save

'Close Report
objrep.Close

great !!

SAP buys Business Objects

2007-10-09T12:24:00.000+02:00

It has happened -- SAP is buying Business objects. Making them larger than Oracle (the other possible buyer who bought Hyperion instead). SAP will integrate their products with BO and Crystal Reports better -- as it seems. I wonder how this will evolve.

Business Objects Query Drill

2007-09-19T17:09:00.001+02:00

Query drill (or apply drill filters) is a somewhat hidden feature of Business Objects. In fact, I rediscovered it when I was teaching BO Webi.

It got me somewhat curious, to find out if it also existed in BO 5 and 6.5 — and in fact, it exists in every single one of those versions.

1) Where do you find it :
In webi, it is located in the document properties — you check “query drill”
In full client, it is located in Tools>Options and on the tab Drill, check “Apply Drill filters on drill through”

2) What does it do
In fact, drilling is a tricky business — you navigate through your data and little by little, you get your data and you extend your query. Start with 10 records, and end up with a query of over 50000 records.

Because — that is what happens, you want more details, so you drill for an extra column using drill through and you end up multiplying the data in your dataset.

Apply drill filters on drill through will actually limit your query to the data you filtered for using drill by setting conditions on the query.
So, you drill down on year and get quarters (2006). Then you drill through to months on Q3 — your query is now adapted and filtered for 2006/Q3.

This results in a number of things :

1) Less data (only the data you drilled for is shown)2) Functions aggregated on the database level are more likely to be calculated correctly3) A speed-up (or a slow down) compared to regular drill will occur (depending on the nature of the fields you filter on !

This last point asks for some extra explanation :

Lets assume you have a date field which is indexed at the database level. On the universe, the designer (made the error and ) created a field with function year(datefield) and now youare drilling for a given year. This will filter on a function applied on a date-field — the result is desastrous for performance. (if the year were a physical (indexed) field on the database, the
performance would be very good)

enough already,

Back to reporting

My favorite functions

2007-09-13T16:15:00.000+02:00

In a previous message, I wrote about some functions on time. Here are my favorite functions... much better than the previous ones

function GetLastDayPreviousMonth()
Dim dt
Dim firstDay
Dim lastDay
dt = now()
firstDay = DateSerial(Year(dt), Month(dt), 1)
lastday = DateAdd("d",-1,firstDay)
GetLastDayPreviousMonth = lastDay
end function
function GetFirstDayPreviousMonth()
dim firstDay
firstDay = DateSerial(year(GetLastDayPreviousMonth()),month(GetLastDayPreviousMonth()),1)
GetFirstDayPreviousMonth = firstDay
end function

Sillyness.. absolutely :

=dateadd(“d”, –1, dateadd(“m”, -1, cdate(year(today()) & “/” & month(today()) & “/1”)))

will give you the last day of the previous month.

the first day is easier still :

=dateadd(“m”, -1, cdate(year(today()) & “/” & month(today()) & “/1”))

function convertTime(intMinutes)
dim iTimeInHours, iTimeMinutes,strTimeMinutes, strTimeInHours, strTimeInHoursAndMinutes
iTimeInHours = intMinutes \ 60
iTimeMinutes = intMinutes mod 60
if (iTimeInHours < 10) then
strTimeInHours = "0" & cstr(iTimeInHours)
else
strTimeInHours = cstr(iTimeInHours)
end if
if (iTimeMinutes < 10) then
strTimeMinutes = "0" & cstr(iTimeMinutes)
else
strTimeMinutes = cstr(iTimeMinutes)
end if
strTimeInHoursAndMinutes = strTimeInHours & ":" & strTimeMinutes convertTime = strTimeInHoursAndMinutes
end function
function calculateTime(eDt as datetime, sDt as datetime, eLu as Int32)
dim iTime
iTime = DateDiff("n",eDt,sDt) - eLu calculateTime = iTime
end function

Time in Reporting Services

2007-06-26T12:20:00.001+02:00

Putting time in a table is easy... format as hh:MM -- but ! This time I had to put time in a correct format (hours:minutes) and the given data was in minutes. Maybe there is an easy way, but I haven't found it yet - this is my solution :

function convertTime(intMinutes as int32)
'prepare variables
dim arrValues, numMinutes, strNumHours, strNumMinutes
dim numHours, numValue, strTime,strIntMinutes
strNumHours = ""
strNumMinutes=""

'get the time
if intMinutes > 0 then
strIntMinutes = cstr(intMinutes/60)
if instr(strIntMinutes,",") > 0 then
arrValues = split(strIntMinutes ,",")
strNumHours = cstr(arrValues(0))
strNumMinutes = cstr(cint(cint(left(arrValues(1),2))/100*60))
else strNumHours = cstr(strIntMinutes)
strNumMinutes = "00"
end if
else
strNumHours = "00"
strNumMinutes = "00"
end if
if len(strNumHours) = 1 then
strNumHours = "0" & strNumHours
end if
if len(strNumMinutes) = 1 then
strNumMinutes = strNumMinutes & "0"
end if
strTime = strNumHours & ":" & strNumMinutes
convertTime = strTime
end function

put this in the code (properties of the report) and call it with
code.convertTime(Field!whateveritmaybe)

cheers

The kind of stuff people want in RS

2007-06-22T13:22:00.001+02:00

What if the date is stored as date/time and you want to offer a prompt that allows a user to select a date. But!! The date has to be equal to the date in the database.

select * from Orders where cast(CONVERT(varchar(8), OrderDate, 112) AS datetime) = '2004-08-25'

this is how.

Reporting services has problems showing the data in the data-tab though.. it only works in the preview.

Dates in Reporting Services

2007-06-22T08:39:00.001+02:00

A colleague of mine asked for a parameter on a report. The report had to run from the first of the previous month, until the last day of the previous month. I solved this with this function :

function fnGetLastDayOfMonth(datum as date)
fnGetLastDayOfMonth = Day(DateSerial(Year(datum), Month(datum) + 1, 0))
end function

I then called this function in this way :

startdate

=cdate(iif(month(now())=1,year(now())-1,year(now())) & "/" & iif(month(now())=1,12,month(now())-1) & "/01")

enddate

=cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,12) & "/" & code.GetLastDayOfMonth(cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,1) & "/" & "1")))

can’t believe I wrote that function – this will do the trick just fine.

=dateadd(“d”, –1, dateadd(“m”, +1, cdate(year(today()) & “/” & month(today()) & “/1”)))

from the parameter. It would then feed the first day of the previous month to that function and there you have it.. the last day. Cool.

Linking universes

2007-06-14T12:37:00.001+02:00

A customer asked me : how can I link two dataproviders if the common field has an un-equal number of digits.
In one universe, the customernumber is 6 digits, in the other 8 digits - the two digits are two leading zeroes, so the zeroes can be removed without problem.

The formula I used was :
=if(Length(<customernumber>8) then SubStr(<customernumber>,3,Length<customernumber>)) Else <customernumber>

inserted into the field where the customer number was, the leading zeroes would be removed and BO would automatically agregate the data.

there is an other solution, which would involve creating a user defined object on the universe, but this would render the documents unusable to colleagues on different machines.

SSRS MultiValue and Nulls

2007-05-14T17:02:00.000+02:00

When you create a multivalue parameter and you also want to include the null values when doing a filter (a select all), you got two options 1) create a union query to include null. Or (like me) -- you can modify the parameter with code. In the report properties, on the code tab, add the code below (it is probably not the most efficient code, but it works):

function addnulltoparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function

next, in the properties of you dataset, click the tab filters, create a filter on a field, choose in for the operator and in the value type :
code.addnulltoparameter(Parameters!name_of_the_parameter.Value) -- make sure you don't leave (0) at the end or you will only send the first element of the array.

The result is that a null value is concatenated into the parameter. If you want, you can also create a Boolean parameter, that allows you the choice to include nulls.

then the code for the filter would be
=iif(parameters!includenull=True,code.addnulltoparamater(Parameters!someparameter.Value),Parameters!someparameter.Value)

Complex tables do not export

2007-05-03T13:24:00.001+02:00

At the moment, I'm creating some complex tables in Reporting services (2005).

In fact, the only two tables you can have are vertical tables and matrix tables. It is possible to create a horizontal table, but you have to rework a matrix to get to it.

The table I'm currently building in Reporting services involves nesting a vertical table inside a matrix.

As it seems, the table works fine, it exports well to pdf -- but excel won't work. It seems to be impossible to export complex nested tables to Excel.

Date input selectors in Reporting Services

2007-04-05T10:23:00.001+02:00

When you create a parameter in Reporting Services which has Date/Time as a format, it uses the a date format which is based on the settings of internet explorer. Go to tools / internet options to set the language. If you set the language to English US, then the format will be : mm/dd/yyyy, if you set it to dutch/belgium it will default to dd/mm/yyyy

Printing in Reporting Services

2007-04-05T10:21:00.001+02:00

In the Report Properties dialog if I set Page width to 8.27in and Page height to 11.69in then the Page Setup dialog of ReportViewer defaults to A4.

otherwise, it defaults to letter. This has to be in inches.

Building reports

2007-03-26T08:36:00.000+02:00

Currently, I'm quite busy writing reports for a customer. The tool to use is Business Objects Version 5. The reports are predefined by the user in an excel format, which simplifies things, and an even greater luxury is the fact that he has provided previous reports (in excel), to verify the results. The result is a very stimmulating project.

At this time, I find myself quite stuck at a problem.

The customer has defined groups of data which do not occur in the database. say : groups of products. group 1, group 2 and group 3. Each product has a target to attain, for a given period of the year, say q1, q2,q3 and q4. The total of all groups of products, again has a specific target. Each of those targets has to be formatted according to a set of conditions. a less or equal to t : green a larger than t and a less than t*2 : blue a larger than or equal to t * 2 : red.

The targets do not exist in a database and so I coded them in an excel sheet.

So, the database contains a unique id and name for each product, but not the groups, and the targets are set by group.

Still thinking on it since linking does not seem to work.

Complex Query's

2006-12-11T17:15:00.000+01:00

A participant of one of my courses provided me with the following intriguing question. For 1 given year, you need to know which stores both sold product A and B.

When you create the query where you choose both stores and the product and create a crosstab, you will also get results where one store sold something, and the other did not.

This picture shows what happens. There are occurences where one store sold something, and the other did not. Business Objects then shows “discontinued”.

The solution I found, is situated at the query level, and includes a combination of subquery’s and union query’s.

First, I created a query where I specify my first source :

it includes the name of the stores, the number of the product and the sales revenue. All of this is done using the eFashion universe, interesting for demonstration purposes only. The real work goes on in the conditions.
Storename and year are filtered as usual.
SKU number is filtered using a subquery. in this case, listing only the SKU numbers that are the result of that subquery.

The subquery looks like this :

The result of this query is a list of all SKUs sold in the other store. So we only get products in store A that have been sold in store B.

It is now possible to select both stores, but if we do that, then we will get some products for store B that were not sold in store A.. so not a good idea.
Next, we need to create a Union query, to also show data about the second store.

Click the icon. It will add a second query, and you repeat the previous steps, but now for the other store. This means : the first query is then for Boston, the subquery for austin. The result looks like this :

Toilets Brussels

2006-09-27T16:13:00.000+02:00

Teaching with a clean desktop

2006-07-18T17:26:00.000+02:00

Simple trick I learned today :

Right-mouse click on the desktop > Arrange Icons By > uncheck show desktop icons. All the icons on the desktop are now hidden… cleaner to teach programs. To get them back — do this procedure again.

cheers

Data Integrator (1)

2006-07-06T11:55:00.000+02:00

I took a nosedive into Data Integrator a couple of weeks ago.

The first thing I discovered was : they went to a lot of trouble trying to make an interface as un-intuitive as possible. The first two encounters with the product resulted in shouting from my part -- and just not working on the side of Data Integrator.

So -- I persisted.

A couple of weeks later, the product is becoming clearer. Projects contain Jobs (check) -- Jobs contain WorkFlows (check) WorkFlows contain DataFlows (check).

My conclusion so far — Data Integrator is not the easiest of products to use – but luckily, I succeeded in putting my hands on a good “core” tutorial pdf file…

Small Bug in BO XI R2

2006-07-06T11:46:00.000+02:00

Strangely enough, the small bug I talked about in the previous article, persists in BO XI R2..

Small BO 6.5 Bug

2006-07-06T11:43:00.000+02:00

A program always show more bugs when released and shown to the public.

Here is a strange one.

1) create a ranking on any field. check the “top” checkbox and a textbox appears where you can enter the top-howmany you want… it says 3 by default.

2) put the cursor next to the 3 (left) and enter the number 2 … next, use the delete-key to remove the 3…

what remains after clicking ok… is a top-23…

strangely enough, BO does not really remove characters removed from that textbox by means of the delete-key.

.NET on Linux

2006-05-05T08:43:00.000+02:00

At a certain point I was reading an article about .NET – and they stated that .NET was meant to be “cross-platform” and I thought “yeah right”.

Some two weeks ago, I stumbled across the “Mono project”. It is a .NET framework, sponsored by Novell that runs on Linux, Solaris, Mac OS X, Windows and Unix. And I thouht “yeah right”. I seem to think that too much.

So I installed it on my Ubuntu machine and stumbled from one amazement to the other. Currently, I’m learning C# on my Ubuntu machine. The book I use is entirely based on Windows, but all the examples seem to work on Mono — all except the ones that address the graphical interface — it should work, but I’m not that far in the book yet.

Anyway. This is something to keep an eye on. Pretty soon the only thing needed to have an app on Windows Mac and Linux is a recompile.

cheers

Ubuntu upgrade to Breezy

2006-04-14T13:24:00.000+02:00

Today, I upgraded my Ubuntu system to Breezy -- as I said before, I'm not a Linux knowitall, so I heavily depend on Google and thelike -- again today. I had no clue where to start, to upgrade Hoary to Breezy -- but I really wanted to -- I saw the installation of Breezy in a Virtual Machine, and it looked even more sophisticated and complete than the previous version.
Luckily, I found an easy to follow guide to upgrading my Ubuntu Linux machine :

http://www.ubuntuforums.org/archive/index.php/t-83123.html

I must say -- I didn't follow every step in that article -- the fellow who explains it seems rather squeemish (is that how you write it ?) anyway -- I did do the part where he mounts an iso file -- then I used Synaptic Package manager to upgrade my system -- and in fact -- you do have to remove all packages that have to do with Open Office to get the new version to install. After a first install, I switched on the original repositories and performed another upgrade - and the installation of Open Office 2 of course. I must say -- it looks very 'complete' -- all my powerpoints work perfectly in it -- but it does not translate Word documents very well. Anyway -- back to playing with my Linux machine.

Expand/Collapse (Business Objects)

2006-04-11T16:20:00.000+02:00

This is the place where I store the things I tried once, found interesting and choose to forget again… to avoid loosing the info altogether, I write it down. Here is a perfect example of such a fact.

At a certain point, I was wondering what “expand” and “collapse” in the analysis menu were used for. I figured it out — but at the time I didn’t have this Blog yet — so I forgot all about it pretty soon after.

Let’s assume you have a table with Year and Average Revenue (which is a variable, calculated from “Sales Revenue” and “Quantity Sold”). Next, you enter drill-mode. You can now drill on the year and look at the quarter, to month etc… but what about the individual figures which were used to calculate the average revenue… well — there you go. Still in Drill Mode, you go to the Analysis menu and click “expand”.

Automatically, both measures are shown in colums next to the average Revenue. When you choose collapse, they disappear again.

Nifty little trick.

Couldn’t find this in the Core Reporting, the Intermediate or the Advanced Reporting courseware. But it is in the product-documentation — unlike some other stuff.

Filters in Business Objects

2006-02-27T16:24:00.000+01:00

It keeps amazing me just how many types of filters there are in Business Objects.

First : there are the Query filters. The most limiting filters, which have an effect on the datacube - everyone knows (I hope) about these filters, and uses them. They depend a lot on the database and have a big influence on the speed of the report (depending on indexing in the database).
Second : there is the report-filter. Few documented, easy to use, a filter set on a report only applies on the report and on all tables in it, but not on the other reports.
Third : the table-filters. Commonly used to filter inside a table, each table in a report can have its own filters. (first and second can be found in Format>Filters

These are the ones you see in a basic BO class (2 days) and continue with in the advanced class (1 day) But there is a fourth (little known) way to filter, and I found it most useful in some situations.

That fourth filter is what I would call : the object-filter -- it means that you can filter out a certain object based on a given condition. How :

In the properties of a table, a chart, a section and some other stuff, you can activate the "Hide Block" checkbox. If you do so, and confirm with ok, then the block is gone, and you can only get it back if you switch BO into structure view (View > Structure) -- so don't do that. But you might have asked yourself.. what is that box underneath ? well, here you can enter a condition - but you have to enter it manually in this format : =function(<object>) operator value. e.g. =Sum(<sales>) < 8000000

The result of the comparison has to be a boolean value (true/false). e.g. on a chart you could count how many years you have available and only show the chart if you had more than 1 year. e.g. =count(<year>) > 1. The chart will remain hidden as long as you don't have more than 1 year. When you refresh and an extra year was added to the result of the query, then the chart would show up. Pretty nice trick.

No File menu in Business Objects

2006-02-08T20:54:00.000+01:00

This is a bug I came across some time ago.

Here is the solution :

Follow these steps:
       1. Go to Microsoft Visual studio in Business objects
       2. Press Ctrl + g
       3. copy paste this command
"Application.CmdBars.ActiveMenuBar.Visible = True" in newly opened window
i.e. immediate window
       4. Press 'Enter' button
found this on ittoolbox website

Customised List of Values

2006-02-01T16:26:00.000+01:00

Business Objects has many features. In previous articles I already explained some of them — but this next one is quite interesting and well hidden.

Imagine the following situation. You create a query on a database using Business Object. You are an account manager for a certain number of customers, and you only draw information from the database for those customers. But – the list of customers is long. Each time, you have to select YOUR customers from that list. A lot of work. You can create your own list of values, containing only your own customers, so you don’t have to select them from the endless list. Here is how :

Let’s say you had this kind of List of Values(LOV) :

It is a long list, and you only use specific products. You could then create your own list of values, to display only the products YOU work with. First of all, you would need to put those names or numbers in a textfile.

The first line of this file is the title. (If you don’t put anything here, then BO will call the list F1. Not very nice.) Once the file is ready, (containing the customers/products/customercodes), you can then link it to the universe you use by opening Business Objects. Go to the “Tools” menu and choose “Universes”. From the list of universes, select your own Universe.

.
And click the button “Lists of Values”

Of course, you need to select :“Personal Data”, which automaticaly puts you in the “file selection” dialog :

here, you browse to the right place on your computer, choose the right delimiter (I used an Enter..so I chose character) and don’t forget to select “First row contains column names”. Confirm with OK. Done.

From now on, when you ask for a list of values on categories, you get :

Easy — no ?

Scripting Word

2005-11-22T14:41:00.000+01:00

Sometimes you write a simple piece of code, and you just look at it… and it is beautiful ;-) Here is how to use a template word document and modify it on-the-fly from ASP.. problem is you need the Word object on the Server, which is why I didn’t use it, but in stead.. I used an even greater tool — but I didn’t write that one myself.. more about that later.

<%
const wdReplaceAll = 2
Set objWord = server.CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(server.mappath("/files") & "/inputfile.doc")
Set objSelection = objWord.Selection
objSelection.Find.Text = "strname"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE
objSelection.Find.Replacement.Text = "Peter De Rop"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
objSelection.Find.Text = "strtitle"
objSelection.Find.Replacement.Text = "Modifying Word"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
objSelection.Find.Text = "strdate"
objSelection.Find.Replacement.Text = date
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
objDoc.SaveAs server.mappath("/files") & "/outputfile.doc"
objWord.application.Quit False
%>

Context operators in Business Objects (part 4)

2005-10-05T11:25:00.000+02:00

Foreach and Forall revisited
=Average(<Sales revenue> ForEach <Quarter>) ForAll <Year>
What does this formula do ?
To understand it, first consider the following tables :

Imagine, we want to show the number 2,023,954 in the first table (so we can compare the Sales revenue per year to the Average Sales Revenue per quarter for that year. With the standard calculations in Business Objects, such is impossible. This is the situation where you need Context Operators. When adding the formula =Average(<Sales revenue) to the table to the left, the result would be the same number as the column with the Sales Revenue itself, since in that table Business Objects can only consider that value to calculate the average. So we say the context of the average in that case is just the “sales revenue”. In case we wish to include the <Quarter> also into that calculation, we would need to include an input context : Foreach or In. Both will do the same in this case, the only difference is the way they work. Foreach is able to add dimensions to the calculation (key word is ADD) This means that in a Foreach, the dimensions already in the table are always taken into account. The formula would then become : =Average(<Sales revenue> foreach <Quarter>) This tells the average function to include the Quarter in the calculation together with Year which is already in the table. The “In” Operator can do the same, but then the function would have to be : =Average(<Sales Revenue> in (<Year>,<Quarter>)) this needs to be done because In ignores the dimensions already in the table. The resulting table :
This is what we call an Input Context. This kind of context operator is added inside the brackets.
If we needed to show the number at the bottom of the above table, (3,032,267) then we would need an output context, since we want to show the average for the entire Year. the formula would then be the one I stated at the beginning of this article : =Average(<Sales revenue> ForEach <Quarter>) ForAll <Year>
This second operator is used in this case as the Output Context operator The same is possible with “In Report” as the output context. The resulting table :

That should be the end of this topic.

Did this information help you ? Look.. a 'Donate' button on the right side :)

Dreamweaver, Ubuntu PHP and MySQL

2005-07-26T15:55:00.000+02:00

First, let me remind you that I’m not an expert at Ubuntu, so the following is again, the result of a couple of buckets of sweat !

Have been fiddling around with PHP and MySQL for a while — didn’t work out at first.

then I found why I couldn’t get them to work properly…

1) MySQL is setup in such a way, you cannot connect to it from the outside.

solution : change the my.cnf file (on my system, it was located in /etc/mysql/)

find a line that says “skip-networking” (without the quotes) and put a # in front of it.
there, now you can connect to MySQL from a graphical shell like MySQL Administrator on a Windows machine ;-) of course, MySqlCC on Linux is just as good.

2) PHP is set up to ignore MySQL completely

solution : un comment two lines in php.ini (on my system it was located in /etc/php4/apache2/) watch out though … they give some sample lines, but one of them has
a spelling mistake in it… it should be extension=mysql.so and extension=gd.so for some reason, one of those lines is written msql.so.

3) rights have to be set correctly

I used these pages as a reference : sourceforge and my favorite dev.mysql.com

4) Dreamweaver functions have to be installed on the server to be able to test your connection

somewhere on your windows computer, you should be able to find a folder called _mmServerScripts. This folder needs to be copied into the folder where your PHP application will be created (I don’t remember if DWMX2004 did it automatically — but I remember copying it manually).

Some useful commands :

to restart mysql (you need to restart it when adding or changing userrights)
sudo killall mysqld (to stop it)
sudo mysqld start (to start it)

when changing ini files and conf files you need to restart apache … without restarting linux, this is done with :
sudo /etc/init.d/apache2 restart

The movie copi�r

2005-07-18T14:28:00.000+02:00

I buy a lot of movies. For years, I bought tons of VCR cassettes. Being a real collector, I watched them each over a dozen times over. And then disaster struck. My VCR would destroy a video when you watched it and during the time you were watching pressed rewind. I do that all the time — missed something, so a quick rewind to rehear what that person was saying. of course — you don’t know the video was destroyed until you watch it a second time.

I found myself in the DVD Era pretty soon after that hideous mishap.

In the mean time, my DVD collection is growing — I buy a lot of DVD’s. And again, as before, I watch them regularly. I salvaged some of my VCR’s as DIVX some time ago, but I never got round to putting them on a DVD — so I was happy to find that it is quite easy to do. I found the procedure to convert DIVX into DVD on Afterdawn. (You will need a powerful machine — and a lot of time to get the job done though)

After the mishap with my VCR collection, I have grown somewhat paranoid.. So for the moment I’m creating backup DVD’s of some of my most loved DVD’s… I didn’t know copying a DVD was so simple.

For those who want to know :

download two software packages :

DVDdecryptor : which reads and writes DVD’s (free)
DVDShrink : which allows you to throw away the things you don’t need (subtitles and audio in albanian or Arabic or any other language you don’t understand) (free)

First use DVD decryptor in “File” Mode to get all of the files from the DVD decrypted and zone removed onto the HD. Next use DVD Shrink to backup or reauthor the files on your disk … last, write the ISO file which is the result of DVD Shrink back to a DVD… using DVD decryptor in Write ISO mode…

easy as 123

SQL Reporting Services

2005-05-11T13:22:00.000+02:00

Last week, my coach told me, it would be a good idea to have a look at SQL Reporting Services. The Microsoft equivalent (as he called it) of Business Objects.

First thing I did was install a VMWare machine with SQL and the Reporting Services. Compared to installing Business Objects, this is quite straightforward. The installation procedure is very much like every installation procedure by Microsoft. The result was, that I had reporting services up and running in about 5 minutes. During this installation, the system asks you if you want to use the demo database.

Visual Studio.NET is required to create the reports — a definite difference with Business Objects.

My Conclusions. SQL Reporting Services has nothing to do with Business Objects. They do somewhat the same, but their target audience is very different. In business Objects, an end user can build a report
and use it as he or she wishes. In SQL Reporting Services, all a user can do with a report is set some filters and drill down on data. Building a report is done by a specialised service — or some
well trained people who design the RDL files. Thorough knowledge of SQL queries is a must, thorough knowledge of the database is a must — knowledge of Visual Studio.NET helps out a lot.
In business objects, the designer of a BO Universe gives objects logical names and groups objects together — in these RDL files, the designer has to choose all of the fields from the database — fields can be located anywhere.

To me it looks like SQL Reporting Services is the kind of thing where a central design team designs all of the reports.. users have to nag them to get reports changed — BO is the kind of
program where users can create their own reports and work a lot more flexibly/dangerously(they can make their own analysis errors) — but at a cost. Business Objects is not exactly a cheap tool.

anyway, learning more about SQL Reporting services as we speak.

SubQuery's in Business Objects

2005-04-17T20:40:00.000+02:00

The title may seem complex, but the issue is simple.

Lets say you wanted to see the Sales Revenue per Store, but only for Stores located in a State that had a Sales Revenue over 7.000.000 $.

In that case, you would have to produce a query that would return all of the States over 7.000.000 $, and based on that query, obtain all of the stores you needed. The Stores by themselves don’t have such a high Sales Revenue, so you can’t get this in one go.

Here is how :

This would return us the Stores and their Sales Revenue (all stores). So next, we need to perform a Filter on the State, setting a Sub-query as a source.

The result is as follows :

The stores listed only come from states with a Sales Revenue over 7000000$ — just as required.

Great stuff isn’t it ?

Beware : Universes have a parameter that limit the maximum inlist values. Furthermore, some databases have limits on the maximum inlist values.

The most common number is 999. The parameter on the universe that sets this is : MAX_INLIST_VALUES and is set, by default to 256.

topcom wireless lan on Linux

2005-04-11T14:34:00.000+02:00

Here is how to install a topcom wireless lan module under Ubuntu Linux (Hoary)
I’m not a Linux specialist, so this is the result of a couple of buckets of sweat.

Installation succeeded on Hoary (Ubuntu Linux) 2.6.10-5-386
First make sure you have all necessary packages.
Under Ubuntu Linux, use Synaptic, to install :
linux-wlan-ng
wireless-tools
linux-386
linux-kernel-headers (for the version of kernel you have -- mine was 2.6.10-5-386)
gcc
gcc-3.3
gcc-3.3-base
libgcc1

yeah -- it's a long list ... but you will need it to compile...

next, surf to :
http://linux-lc100020.sourceforge.net/
to get it working, I downloaded version 0.13 -- I also tried
it with version 0.14, but to no avail
once you got the tar.gz file into a folder, unpack it (tar zxvf zd1201-0.13.tar.gz)
don't compile yet, it won't work... you need more.

Next... tricky one...

download ipw2100-1.1.0.tgz from http://sourceforge.net (go through search and type ipw2100)
untar it.. (tar zxvf ipw2100-1.1.0.tgz
copy the files
ieee80211.h
and
ieee80211_crypt.h
into the headerfolder : /usr/src/linux-headers-2.6.10-5/drivers/net/wireless
(this step is required for this specific wireless module)
remember the file you untarred... zd1201-0.13.tar.gz ?
go back to it and modify zd1201.c (yeah you really have to)
replace every instance of ieee802_11 with ieee80211 -- shitty job...
run make
run make install
copy the zd1201.fw and zd1201-ap.fw files into the
/lib/hotplug/firmware folder and
the /usr/lib/hotplug/firmware folders...
run depmod -a
run insmod zd1201.ko

(added thanks to Stephen Pike)

or -- if that gives an error on symbols
use modprobe -v zd1201 instead

I then rebooted the system (yeah -- not really required)
System>Administration>Networking should now list your wireless connection.
change the properties to make it active and choose the right accesspoint
use the iwconfig, dmesg and ifconfig commands to check your configuration..
ifup wlan0 and ifdown wlan0 should allow you to start and stop the wlan module..

lots of luck !

Guestbook babysite

2005-03-28T22:07:00.000+02:00

The guestbook is open.

http://users.skynet.be/binabik

Website of Jarne

2005-03-27T00:14:00.000+01:00

I put a small website online for our son Jarne. There are some pictures from his birthcard there, plus a photogallery. Enjoy.

Yeah Baby !

2005-03-26T09:54:00.000+01:00

On 24 March 2005 (as in the day before yesterday) my baby son was born.
His name is Jarne, he weighs 3Kg and measures 50cm.
One of these days I will post some pictures.

The best Raidlevel (Part 2)

2005-03-22T13:27:00.000+01:00

So as it turns out, Raid 5 is a very good Raidlevel, but it is not very good at writing. Raid 1+0 is very good, but it is rather expensive. Is that all there is to it ? No. If you have read one of my previous articles, you might have read that a harddrive’s speed is influenced by Latency and Seek. If you combine that story with this particular story, then you might find out there is more to it.

The two main ways of communicating with a disk are Sequential and Random. In a sequential environment, large chunks of data are being transferred (e.g. : 64K or 128K or bigger) This occurs most on file and print servers. In a random environment, larege amounts of small chunks of data are being transferred to the disk (e.g. 4k) This occurs most on a database server. If you think a little further on this, you might consider that Raid 5 has to do about twice the work for writing a chunk of data compared to Raid 1+0 (Raid 5 has to read existing data, read existing parity, calculate the parity, write new data, write new parity).

In that case you might come to the same conclusion as Database manufacturers and database gurus. Raid 5 is not a good thing to combine with a database. It will flood the Raid 5 with small chunks of data and that poor Raid 5 has to do twice the work a Raid 1+0 has to do at writing.

A clear conclusion is : Avoid Raid 5 in a database environment. But most people forget that Exchange is also a database.

My recommendation : If you are going to install a server, and you are considering to install 3 disks in Raid 5 and put your exchange server on it — why not consider a little further, add 1 extra disk, use Raid 1+0 in stead. The result is remarkable : Twice the speed, Twice the fault tollerance, twice the speed at reconstruction, more than twice the speed during a disk failure.

Good and Bad Popups

2005-03-22T10:55:00.000+01:00

So you want to use popups ? Of course you do, opening a window is a right for everybody — but you might have discovered, that there are things around called popup blockers.

Both Internet Explorer and Mozilla Firefox include tools to block popups, furthermore, software exists that blocks all popups. As a programmer, it is impossible to take these last into account.

So, what is the difference between good and bad popups.

When an html document is opened in the browser, much depends on the zone where a page is opened. If a document is opened in the zone of the local computer, there is a small difference between Internet Explorer and Firefox. Internet Explorer will consider all scripting in a page that is launched in the Local Computer Zone as a potential hazard, and will show a warning.

Firefox does not do this. Once the page is put on the internet, this problem does not exist (example).

A good popup, is launched by a click-event. or a doubleClick event

A bad popup is launched by an onLoad event, onMouseOver event, onContextMenu event or onMouseOut event

This means that most “accidental” calls of popups are blocked. The only one that surprised me somewhat is the onContextMenu. But then again, right clicking a link is something we do to force
the opening of a link in a different target window, so it makes sense.

The best Raidlevel (Part 1)

2005-03-18T13:46:00.000+01:00

Ask 10 system engineers, which is the best Raid level and 8 will answer : Raid 5. The other two will ask the smart question :“for which application ?”

This is the start of a somewhat long article I think. The discussion is long and interesting.

When you try to evaluate the different Raid levels, you should be concerned with the kind of data transfers that will occur on it. Is it going to be a system with mostly Sequential transfers like a File-Server ? or is it going to be a system with mostly Random transfers like a Database Server ? Or is it going to be a mixture, and in that mixture, which kind of transfer occurs most ?

First, lets assume we are going to perform 400 Writes and 600 reads on a series of Raidsets. This means that the system will be working with mostly Reads, but still quite some Writes to deal with as well.

What would be the result for a Raid 0 ? (afterwards we can compare with Raid 1+0 and Raid 5

Let’s assume we have three Raid 0 Raidsets. each totalling in 72 GB Net Storage

In a Raidset with 2 disks, the total number of Reads and Writes gives you the number of IO’s so in this case, the number of IO’s (for 400 writes and 600 reads) is 1000 IO’s
When you only have 2 disks, you split those IO’s across the 2 disks, giving a total of 500 IO’s /disk

When we have 3 disks, we gain some speed because the total number of IO’s can be split across more disks. This gives you now 250 IO’s / disk

With 8 disks, the result will be 125 IO’s / disk

2 disks of 36GB = 1000/2 = 500 IO’s per disk
4 disks of 18GB = 1000/4 = 250 IO’s per disk
8 disks of 9GB = 1000/8 = 125 IO’s per disk

And what about Raid 1+0 ?

In a Raid 1+0, everything that has to be written has to be written twice. Reads can be performed across all disks.
Since we now have to take into account the writing, the number of IO’s to process is (400*2)+600 = 1400 IO’s / 4 = 350 IO’s /disk.
With 8 disks, this will give you 175 IO’s /disk With 16 disks : 87 IO’s /disk

4 disks of 36GB = 1400 /4 = 350 IO’s per disk
8 disks of 18GB = 1400/8 = 175 IO’s per disk
16 disks of 9GB = 1400/16 = 87 IO’s per disk

And Raid 5 ?

Raid 5 is a different story. In raid 5, each time you write something, the parity has to be calculated and written to the disks. But more importantly, to be able to write the parity and the data, a Raid 5 has
to read the data and parity that exists on the disk already – change it and write the data. The result is that the number of IO’s on Raid 5 for this same writing is much higher.

For 600 Reads and 400 writes, Raid 5 will take (4 * 400) + 600 = 2200 IO’s

3 disks of 36GB = 2200 / 3 = 733 IO’s / disk
5 disks of 18GB = 2200/5 = 440 IO’s / disk
9 disks of 9 GB = 2200/9 = 244 IO’s / disk

So, it is true, that 3 disks in Raid 5 is not as expensive to have fault tollerance… but is it really cheap ? If I add 1 extra disk to it and create a Raid 1+0 in stead of a Raid 5, I get the same capacity – I double my speed and I double my fault tollerance. (in a Raid 1+0 with 4 disks, when you have a second disk failing, you still have a 2/3 chance your system is up and running. With Raid 5, your system is down.

Raid 0+1 is not 1+0

2005-03-15T15:31:00.000+01:00

For years I’ve been trying to explain the difference between these two, only to find a lot of people thinking it is the same thing. Here is the difference:

In a Raid 0+1 system, first a Raid 0 is applied on the disks. The result of this operation is speed. Next, on top of the Raid 0 (usualy done by software) a second Raid is applied, which is a Raid 1. The result of this second Raid is for security. When you look at this configuration, you might think everything is nice and dandy, but this is actually the worst kind of config, and is never put on hardware Raid controllers. This is very bad. First : Security – When one disk fails, the system has 2 disks down (both halves of a Raid 0) this means that only the remaining Raid 0 is still on-line. If a disk fails in that Raid 0 you are in SH.T. This means that only the second disk of the failed Raid 0 can fail (1 chance out of 3) and your system is still working. Second : Speed. Again, if the system goes bad, and a disk fails, you are working at half the speed. And when you restore, the system has to copy the RaidSet from one Raid 0 onto the other Raid 0 (all disks have to work).

A much cleaner way to do this is Raid 1 + 0 :

Two Raid 1 are created. Across those two Raid 1, a Raid 0 is created. The result may not be obvious at first, but if you fail a disk, the system still works on 3 disks(faster) if a disk fails, 2 chances out of 3, your system is still ok. When you are rebuilding, it just copies 1 disk (of the failed mirror) to the other.

This is the kind of Raidset which is usually implemented in hardware Raid controllers (if yours does not… get another).

HP (Compaq) has been advertising for years that they were using 0+1 whereas they were really using 1+0 which is far superior.

You can tell very easily if your system is using 0+1 or 1+0 .. if you remove a disk and put it back.. if only 2 disks are working to perform the restore : 1+0 .. otherwise 0+1.

Context operators in Business Objects (part 3)

2005-03-14T17:23:00.000+01:00

In Body and In Report.

A nice quirk in Business Objects, is the calculation in %. It adds an extra column to your table, which contains a formula :

=<Sales revenue>/Sum(<Sales revenue>) ForAll <Year>

which is based on the ForAll operator. The result is the following table :

When you add a dimension to this table (let’s say the Quarter)

I don’t know, but I think my counting still works.. it now says 100 % where it should be closer to 450 %. This is due to the fact that the context operator is still the same. It should now be
=<Sales revenue>/Sum(<Sales revenue>) ForAll (<Year> , <Quarter>). But Business Objects does not update the formula. What you can do now is remove the column with the calculation and reinsert it. But hey, there is a better way :

Insert the following formula :
=<Sales revenue> In Body /Sum(<Sales revenue>) In Report

This means that BO will now take the sales revenue in the Body of the table which is 2.6 million for the first, 2,27 million for the second item etc. and divides that number by the total Sales Revenue. I wonder why the people who built this program did not use this particular formula. I found it in their own documentation. Oh.. before I forget..here is the result.

Context operators in Business Objects (part 2)

2005-03-11T13:38:00.000+01:00

Foreach and Forall allow you to add or remove dimensions to or from a calculation.

When you look at the table to the left, you will see that the third column shows the same as the column next to it. The third column contains Min(<Sales revenue>), still it returns the same, which is logical. The context of the calculation is just “Year”. This means that The minimum Sales Revenue based on one number returns only that number.

With the ForEach operator, we can now add a dimension to the context. The formula is now : Min(<Sales revenue> foreach <Quarter>). The minimum is now calculated per Quarter as well. Returning the following table :

This means, that (for the calculation) the quarter has been included in the calculation.

Forall is the exact opposite. If you have a table containing Year, Quarter,Month, Sales Revenue, then min(<Sales Revenue> forall <Month>) will remove Quarter from the equation and return the minimum by year and Quarter only.

Next issue :

In Body and In Report, which are interesting when you use percentages.

Context operators in Business Objects (part 1)

2005-03-08T09:33:00.000+01:00

This is quite a topic. It is not covered in any of the courses, but the possibilities it offers are quite impressive.
To start with, BO offers 7 context operators :

in
Foreach
Forall
in Body
in Block
in Report
currentpage

In this first part, I shall be explaining the In operator.
Let’s say you show a table (based on the e-Fashion Universe) containing Year, Quarter and Sales Revenue. As a default, the Sales Revenue is shown “in the context” of Year and Quarter. If you were to create a table with only the year and Sales Revenue, then the context of the Sales Revenue would be the Year. This is what we call the “Input Context”.
When you create a table, containing Year, Quarter and Sales Revenue and you create a Break on the table. next you create a sum on the Sales Revenue, then BO will write a sum at the end of each Year. This is called the “Output context”.

Let’s now say that you need to show that minimum in an extra column, so you can compare the Sales Revenue to the Minimum Sales Revenue for that Year.
Then the formula would be :
Min(<Sales Revenue> In (<Year>,<Quarter>)) In <Year>
The first “In” indicates that the Minimum Sales Revenue has to be calculated in function of Year and Quarter, but has to be shown by Year, which is taken care of by the second “In” operator.
In a next item : Foreach and Forall

Bug in Business Objects 6.0

2005-03-04T15:38:00.000+01:00

If you read previous articles, you may know that I play around a lot with Business Objects. Once and again, I run into strange bugs, and when I do, I write a little article about it. Here is another weird bug in BO 6.

When you save a document (including different reports) as HTML, all works out nicely. But when you open that report in Internet Explorer, and click the download link in the bottom right corner, to lauch BO to view the document, somehow it messes up the entire layout of the buttonbars. Each Buttonbar shows on its own line. Probably one of those things that need solving in version XII -- version XI does not include a Windows base reporting tool -- only web.

Dreamweaver and RSS

2005-03-03T12:58:00.000+01:00

In a previous article, I wrote about the RSS features of Firefox. Of course, being into webdesign, cool tools like a tool to integrate RSS and Dreamweaver gets me fantasizing. Going to test it soon, but if you can't wait

go to the download site

Ever wanted to syndicate web content fast and easily? MX RSS Reader-Writer is a 2 in 1 solution. First of all, it allows you to import RSS (Really Simple Syndication) files and integrate them in your website design and secondly it allows you to export database information into RSS format. This way you will be able to increase the traffic on your site by gathering and distributing your news in a wisely manner.

Calculating the speed of harddisks (part 6)

2005-02-28T15:18:00.000+01:00

What about Raid you might ask ?

Indeed, when you put a harddrive together with a couple of other drives, it
improves the performance. The best way, by far, is Raid. Some Raidlevels will give you better performance, while others improve security above all else.

Raid 0

Raid 0 is of course the most dangerous Raidset, and should be avoided in any situation where the data is critical. But when it comes to performance, it is the best there is. Multiply the speed of one disk, times the number of disks. If that number does not exceed the speed of your scsi bus, then you are smiling.

Raid 1 and 1+0

Raid 1 and 1+0 are often referred to as being the most expensive Raidsets. This is partly true, since you have to buy twice the number of HD's to get half the capacity. But they deliver outstanding performance.

consider this :

1 disk delivers 20MB/s. When configured in a mirror, the system is able to do "Split Seeks" which means that it can read from two disks at the same time. So 2 disks give you 40MB/s when reading. When writing, the speed will not go up, since everything has to be written twice. If your diskset performs 60 writes and 40 Reads, then you get 180 IO's. On a Raid 0 this would have been 100 IO's.. but on Raid 5, it would require 220 IO's -- I will clarify on a next installment. Cheers !

Calculating the speed of harddrives (part 5)

2005-02-24T13:17:00.000+01:00

So, what about ATA disks. The thing with ATA disks, is that they are what they call "blocking" devices. This means, that only one disk per channel can communicate with the controller at the one time. Since most computers have two channels, two devices can talk to the controller. (SCSI is also referred to as "Non-Blocking device"). This is the reason why programs like Nero burning rom will recommend you to put your primary CD on one channel and your writer on a different channel.

An average ATA harddrive has a very good latency, but a rather high seek. This means that sequential transfers can be faster than on SCSI disks. But when you run a database on such a thing, it turns bad.
Secondly, ATA harddrives have a very low MTBF (Mean time between/before Failure), which means that the harddrive will not work for very long without critical errors. This is the reason why servers containing ATA harddrives usualy have only 1 year of waranty... and if it says 3 years, usualy this waranty does not include the Harddrive. But as long as you are running sequential transfers... ATA can a good solution. Don't burden your HD with a database though... certainly not on the harddrives used in portables, which have an even higher seek.

Cool Software for PocketPC

2005-02-23T09:27:00.000+01:00

I installed DockWare on my Pocketpc, it turns my iPaq into a standing calendar… the freeware is nice, no nag-screens, but you have to start it manualy and it is not as configurable. Fun software, which makes your pocket pc next to your computer actualy serve a purpose.

PocketPCFreewares - DockWare

DockWare is a calendar/clock that you can use while your PPC is cradled. It allows you to use your pictures or drawings as a background and/or as a slide show.
There is a "Pro" version ($9.95) available allowing you to add a time interval to the picture rotation, to manage the text and background colors and to add a password option.

Calculating the speed of harddrives (part 4)

2005-02-22T13:42:00.000+01:00

So, what can we conclude. SCSI is a nice thing, because the multitasking allows you to work with multiple harddrives at the same time. One harddrive is very fast, but not the speed of your SCSI bus. To get the most out of your SCSI bus, you need to combine multiple HD's e.g. with Raid. When you have an environment which is mostly sequential, then setting the blocksize higher than 64KB will give you very good performance. Combining 3 or 4 HD's will multiply the speed.

Random environments will need to take into account that the speed of the controller is not an issue when looking at the speed of your datatransfers. The speed of the harddrives is so low, that any controller can accomodate the transfer..

Calculating speed of harddrives (Part 3)

2005-02-21T16:04:00.000+01:00

So, what about Random transfer ? As I mentioned earlier (Part 1), the speed of a harddrive depends on 2 factors : Average Latency and Average Seek. On an average SCSI harddrive, the Seek = 3 Milliseconds. But when you need to look for your data on a totally different place on the harddrive, both latency and seek need to be taken into account. So 6ms for each access to the HD in case of Random. 1 I/O takes 6ms, in 1 second, 1000/6 = 166 IO's/second. As I mentioned earlier, databases transfer small chunks of data lets say : 4KB. 166*4 = 664KB/s. you would need some 640 harddisks to reach the maximum speed of Ultra 4 SCSI.

Calculating speed of harddrives (Part 2)

2005-02-21T10:39:00.000+01:00

So what does this add up to? When you want to know the speed, you need to know what data transfer type you are using. Sequential ? or Random ? An average fileserver and printservers use Sequential transfer, which occurs most when you transfer big files in one go. Database servers, like Oracle, SQL or Exchange, use Random transfer. They transfer small chunks of data (a zip code being updated, a mail message of 2KB being transferred.

In a sequential environment, only the Latency of the harddisk really counts. So lets imagine, we are going to transfer 64KB Blocks to a harddrive, with a Latency of 3 Milliseconds. The harddrive can have a maximum of 1 I/O (input or output) every 3 Milliseconds, so in 1 second, that is : 1000/3 = 333 IO's/second. 333 * 64KB = 21312 KB/s, which is equal to 20MB /s. This harddrive has a peak transfer rate of 20MB/s. You will need to attach 16 of them to reach 320MB/s (Ultra 4 SCSI).

Calculating speed of harddrives (Part 1)

2005-02-19T21:05:00.000+01:00

How fast is a harddisk? Some people will answer the speed of the bus to which the harddrive is attached. Ultra ATA should give you some 133MB/s, Wide Ultra 4 or Ultra 320 SCSI should give you
320MB/s, but never in your life will you get that kind of speed from only one harddisk. So, what is the speed then.

To find this, you need to know two things about the harddisk : Latency and Seek.

Latency is the speed at which any next spot on a track of the harddrive is brought to the head. This means, while the head remains passive, Latency is influenced by the rotational speed and determines the speed
with which the harddrive can read consecutive blocks of data. Usualy, SCSI and ATA harddrives score very well at this. Some ATA harddrives will even outperform SCSI harddrives at this.

Seek is the speed at which any spot on the harddrive can be located. Including data that is stored on a different track. This includes moving the head of the harddisk.
This is typically something at which SCSI harddrives are much better then ATA harddrives.

Nice Little bug in Business Objects 6 (end-user)

2005-02-17T17:07:00.000+01:00

This bug has existed in BO v6 ever since it came out.

When starting a new report, from the data menu, select “New Data Provider”. The screen that now appears, has one radio button.

Why is this radio button here… it does not serve a purpose, since creating a query based on another one, implies that you already created one. If you check it, you cannot uncheck it.

The only thing that works is to click the Begin button.

RSS Feeds in Mozilla Firefox.

2005-02-15T16:29:00.000+01:00

As I wrote earlier, in one of the articles on this blog, it is possible to put Live bookmarks on your buttonbar at the top of Firefox. But, this works easiest if you have the icon in the bottom right corner (see previous article). What to do however when you want to bookmark a site where you don’t have that icon, but only a hyperlink with an xml file at the end.

The solution is easy. Create a bookmark on a site where you do have an icon in the bottom right corner, then, in bookmark manager, change it’s properties to match the XML file on the site with interesting articles. Set a different title, and disco, you are done.

Example : ZDNET does not offer that icon; but when you first surf to theregister.co.uk, create a bookmark, change the properties of that bookmark to the url of ZDNET, then it works nice and dandy ;-)

Main differences between BO 5 and version 6.5 (client)

2005-02-15T14:42:00.000+01:00

From the client’s point of view, the amount of differences available in BO 6 are not that big. That is, if we are talking about a basic user. Of course, a great addition, is the fact that you can now save your document to Excel. This includes Charts and tables on different reports. Existing reports are saved as Worksheets in your workbook.

A second addition, worth mentioning is the fact that you now have a search window in the Query Panel. When you work with big universes, this is no luxery. The same addition was introduced in the designer module.

So far for the basic stuff.

A cool new addition is the fact that you can now query webpages as well. This means, that you can select a table on a webpage (e.g.:stock info on yahoo) and configure BO to query that page automatically. BO can then change the query automatically, based on the info required.

New in Business Objects XI Designer

2005-02-15T12:16:00.000+01:00

Nice novelty in the Designer program of Version XI, is the possibility, to set rights
on the level of objects. To do this, you select the object, and click the icon on the toolbar… next, you choose the user for which you want to assign the rights. These objects will not show in case the user does not have the right.

Second new thing are the derived tables. You can actually create a table based on a sql query and use that as a datasource… quite useful.

Upgrading BO 5 to BO 6 (first installment)

2005-02-11T17:02:00.000+01:00

Since my motto is : don’t believe it until you see it, I’m installing a BO 5.1. The purpose is to get it up and running, and then — for fun — upgrade it to BO 6.5.

To get me started, I first set up a VMWare session with W2K3, and put SQL 2000 on it. So far so good. The installation of BO 5 went quite nice. Now I wonder how the upgrade to version 6 will work out.

Keep you posted !

Firefox Live bookmarks

2005-02-09T19:56:00.000+01:00

You probably already know Firefox … if not (get it here); I’m a great fan of that thing. Especially the live bookmarks. Here’s what they look like :

What happens is, that you get a very specific icon in the bottom right corner ()when you surf to a site with live bookmarking (zdnet, theregister, this blog and many more) . When you click this icon, it adds a live bookmark to your list of bookmarks… so far nothing special. But when you open the list of bookmarks (Bookmarks > Manage Bookmarks — I think that’s what it’s called.. I use the Dutch version) now scroll down to the location of your new bookmark and drag it to the toolbar at the top (as in the screenshot) The result is very nice… you see the new things on your favorite newssite immediately.

Differences between v5 and v6 of Business Objects

2005-02-09T15:48:00.000+01:00

There is a lot of talk going on about the differences between BO 5 and BO 6 ... here is a list some are trivial.. others ... less trivial:
differences exist
-in rouding
-between values 0.00 and -0.00
-in the number of lines in a report
-in sort
-in complex calculations(filters,multicubes)
-in timestamp
-in headings
-in the option "refresh when opening document"

Er is nogal wat interesse naar de verschillen tussen BO 5 en BO 6... hier is een lijst. Sommigen zijn triviaal... anderen... minder triviaal :
verschillen bestaan bij
-afronding
-de waarde 0.00 en -0.00
-aantal lijnen op het rapport
-sort
-complexe berekeningen(filters,multicubes)
-timestamp
-hoofdingen
-de optie "refresh when opening document"

I'm getting more info on the exact nature of each difference of course.