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

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

Calculating Current Month

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

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 :

<user username="username" password="yourpassword" roles="admin,manager"/>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Teaching with a clean desktop

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)

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

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

Small BO 6.5 Bug

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

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

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)

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

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

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

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

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)

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

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

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

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.