Calculate Age

I need to caluclate the age using the birth date. how do i go about calculating the age using reporting service?

your regrads
Angela Goh

Hello:
There are two(2) ways that I use and they are as follows - but I prefer the second method.....
1st method - in Report Properties "paste" the following code:
Public Function Age(ByVal Date_Parm)
Dim date1 as string
date1 = Date_Parm.insert(4,"/")
Dim date2 as string
date2 = date1.insert(7,"/")
Dim DateOfBirth as DateTime
DateofBirth = Date2
Dim AgeCalc as integer

Dim intDOB As Integer = _
DateOfBirth.Day + DateOfBirth.Month * 100 + DateOfBirth.Year * 10000
Dim intToday As Integer = _
DateTime.Now.Day + DateTime.Now.Month * 100 + DateTime.Now.Year * 10000
AgeCalc = (intToday - intDOB) / 10000

Return AgeCalc
End Function

Then within the field in Layout where you want to display AGE use =Code.Age(whatever the birthdate field name is)
But I prefer to (not trying to create any cotroversy here) do everything within Stored Procedures and limit any custom code within Reporting Services - So I
Create a User Defined Function within SQL - the code is as follows:
CREATE function dbo.fn_GetAge
(@.in_DOB AS datetime,@.now as datetime)
returns int
as
begin
DECLARE @.age int
IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
else
IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)
IF datepart(d,@.now) >= datepart(d,@.in_DOB)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1
RETURN @.age
end

Then within the stored procedure I use as the data set within Reporting Services my SQL example is:
Select PM_Birth_Date, PM_Age from Patient_Master
Where PM_Age =
Case when isdate(Pm_Birth_Date) = 1 then dbo.fn_getage(PM_Birth_Date, getdate()) else '999' end
The isdate determines if PM_Birth_Date is a valid date and if so I execute the UDF GetAge to compute the age, if Birth date is not a valid date I assign 999 as an invalid age.
Just my personal preference, but I prefer to do everything within Stored Procedures within SQL and present all data, calculations, etc. to reporting services. The only conditional formatting I conduct in RS is color of fields.
Hope this helps!
Best Regards - Joe

|||

hi Joe,

for the 1st method, where do you paste the code into the report properties, at the custom code?

your regards

Angela

|||Yes,|||

hi joe,

i try using ur SP method. but encounter some error. so like to check with you did u create a field in the table for the age? because for my db i don't store age i jux need to generate the age.

thanks

Angie

|||Hello:
Yes, we keep all kind of things in the DB (kind of inherited) which we re-compute on the "fly".
The following will work in your situation and Age ends up being a calculated "work field"
Select PM_Birth_Date,
Age = Case When isdate(PM_Birth_Date) = 1 then dbo.fn_getage (PM_Birth_Date, getdate()) else '999' END
From Patient_Master
Best Regards,
Joe|||

hi joe,
thanks for the sp. i try another way by calling the function and not using the case to check the date. bcause i encounter some error if i try using case to check for the date. therefor i skip that step n use the function directly.
thanks
Angela