Logarithmic Trendline in Business Objects

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

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

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

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

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

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

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

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

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

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

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

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

=LnX – AverageLnX in Month

=Y – AverageY in Month

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

=LnX-AverageLnX * Y-AverageY

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

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

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

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

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

Next, we divide those two.

And we have our Direction Coëfficient.

Next up, we need the interception.

For this one, we need to subtract from AverageY

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

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

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

And then, we convert this baby to a chart.

And, it has the typical Logarithm curvature

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

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

Next up : Exponential Trendline

Have a nice day !!

Binabik

Trendlines in Business Objects

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

 

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

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

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

Our first type of trendline : A LINEAR TRENDLINE

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

and this

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

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

Step1:

Calculate the average on both X and Y.

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

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

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

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

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

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

Next, we need Y – AverageY

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

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

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

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

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

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

Now that we calculated b, we can proceed to calculate

This translates to :

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

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

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

y = mx + b

m = direction coëfficient

b = intercept

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

So, now we transform this to a chart :

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

 

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

Isn’t that – entirely – identical Lovely.

Cheers

Binabik

Logarithmic Scales in Charts

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

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

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

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

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

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

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

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)

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

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

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

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

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