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.