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

Calculating average in Business Objects

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

4

1

3

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

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

Count

4

4

4

So, now we have our formula :

Sum()/Count() in Report

Cheers folks

Binabik

three things I would like in RS

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

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

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

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

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

 

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

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

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