Adding Function(s) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Adding Function(s)

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-09-29 : 12:43:55

I have 2 questions.

1. Where do I create a function that will be used by multiple reports? (intead of putting in Report's properties)

2. How do I convert following MS-ACCESS function to SSRS function? I am sure there is better way to write this as I just inherited this project where I have to convert all the ACCESS reports to SSRS.

Function wbdose(Sex, Ovaries, Testes, Breasts, Marrow, uli, Lung, _
Stomach, Bladder, Liver, Thyroid, bone, Brain, smi) As Single

If Sex = "M" Then
dose1 = Testes * 0.2 + Marrow * 0.12 + uli * 0.12 + Lung * 0.12 _
+ Stomach * 0.12 + Bladder * 0.05 + Liver * 0.05 + Thyroid * 0.05 _
+ bone * 0.01 + (Brain * 0.33 + smi * 0.67) * 0.05
wbdose = dose1
End If

If Sex = "F" Then
dose1 = Ovaries * 0.2 + Marrow * 0.12 + uli * 0.12 + Lung * 0.12 _
+ Stomach * 0.12 + Bladder * 0.05 + Liver * 0.05 + Thyroid * 0.05 _
+ bone * 0.01 + (Brain * 0.33 + smi * 0.67) * 0.05 + Breasts * 0.05
wbdose = dose1
End If

End Function

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-29 : 13:11:11

You can check great article here for help:
http://www.sqlteam.com/article/user-defined-functions

Functions (like Stored Procedures) are listed and stored under Programability on the Database. The above article shows how to create and how to use.

If more questions about functions, these forums have more knowledgable folks...

http://www.sqlteam.com/forums/forum.asp?FORUM_ID=23
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=30

John

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-09-29 : 13:15:05

Hmm. I thought custom functions are to be stored in the reporting and not in the database cause it can cause extra work for database server. More like, even out the codes or business logic. and not everything goes in the database engine. hmm. confused. seems like there are two school of thoughts here.

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-09-29 : 15:43:37

Hmmm, I have not seen how that would be done... Because a function is specific to the tables and structure of the database, that is why it is stored there.

John

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-09-30 : 11:47:23

The function heres refering to is a 'Case' statement in select query.

-------
If Sex = "M" Then
dose1 = Testes * 0.2 + Marrow * 0.12 + uli * 0.12 + Lung * 0.12 _
+ Stomach * 0.12 + Bladder * 0.05 + Liver * 0.05 + Thyroid * 0.05 _
+ bone * 0.01 + (Brain * 0.33 + smi * 0.67) * 0.05
wbdose = dose1
End If
-------

case when (Sex = 'M') THEN Testes...............' ELSE 'Input' END AS dose1

Thanks

quote:
Originally posted by JCirocco

Hmmm, I have not seen how that would be done... Because a function is specific to the tables and structure of the database, that is why it is stored there.

John

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-01 : 10:11:04

Great. I will try the case statement.

Thanks for your input.

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-05 : 12:56:21

Thanks. worked perfectly. Here is the function that works.

CREATE
FUNCTION wbDose (@Sex varchar(1), @Ovaries float, @Testes float, @Breasts float,
@Marrow float, @Uli float, @Lung float, @Stomach float,
@Bladder float, @Liver float, @Thyroid float, @Bone float, @Brain float, @Smi float)

RETURNS FLOAT

AS
BEGIN

declare @Dose float

SELECT @Dose = CASE @Sex
WHEN 'M' THEN
@Testes * 0.2 + @Marrow * 0.12 + @uli * 0.12 + @Lung * 0.12
+ @Stomach * 0.12 + @Bladder * 0.05 + @Liver * 0.05 + @Thyroid * 0.05
+ @Bone * 0.01 + (@Brain * 0.33 + @smi * 0.67) * 0.05
--wbDose = @Dose
WHEN 'F' THEN
@Ovaries * 0.2 + @Marrow * 0.12 + @uli * 0.12 + @Lung * 0.12
+ @Stomach * 0.12 + @Bladder * 0.05 + @Liver * 0.05 + @Thyroid * 0.05
+ @Bone * 0.01 + (@Brain * 0.33 + @smi * 0.67) * 0.05
WHEN NULL THEN
0
ELSE
0
END

return @Dose

end

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-05 : 13:18:28

I am having 2 issues when using the function.

select wbDose('M',0.007969166, 0.000051414, 0.090128693, 0.128432101, .028586168, 0.353728126,
0.138354998, 0.001233935, 0.215887267, .030539899, 0.217121203, 0.001799489, 0.022930631 ) wd
from Exposure

1. It's giving me 2 rows with the same answer.
2. The answer should have 16 digits after decimal. but now only 12 digits after decimal.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-05 : 14:17:36

1. its not because of the function, its because exposure table has 2 rows
2. change the @dose and return datatype to decimal(24,16)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-06 : 13:07:41

Kool. Thanks Rohit....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources