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.