Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using user defined functions in reports 3
- Thread starter codrutza
- Start date
-
1
- #2
-
1
- #3
smcnulty2000
Technical User
You can use a user defined function in sql server quite easily. You define the function on the server and then in crystal you right click on 'Sql Expression Fields', give it a name, like "testme".
When the formula window opens you plug in your sql server user defined function name with the owner information up front.
dbo.mypersonalsqlfunction(datatobeprocessed)
It won't turn blue as it would if you built the function inside of crystal.
To build a function inside of crystal, you open up the formula window (click on report/formula workshop). In the tree to your left at the top is 'Report Custom Functions'. Right-click here and you get to name your function. You'll get this kind of thing:
Function ()
Fill it in with a variable you are going to plug in...
Function (numbervar value) ...where value is a variable name.
Build it as you would any other formula, with the last line being the value you want to return.
You are limited in that you can only use local variables inside of the function. You cannot call particular fields because the purpose is to make a function that is portable to other reports.
To call it, in some other formula you just treat it like any other function.
testme(datatobeprocessed);
an example;
Function (datevar value)
local stringvar strReturnvalue;
strReturnvalue:=totext(value,"MM/dd/yyyy") + "... is the date";
strReturnvalue;
This illustrates the use of other functions, and the use of variables inside the function.
scottm.
When the formula window opens you plug in your sql server user defined function name with the owner information up front.
dbo.mypersonalsqlfunction(datatobeprocessed)
It won't turn blue as it would if you built the function inside of crystal.
To build a function inside of crystal, you open up the formula window (click on report/formula workshop). In the tree to your left at the top is 'Report Custom Functions'. Right-click here and you get to name your function. You'll get this kind of thing:
Function ()
Fill it in with a variable you are going to plug in...
Function (numbervar value) ...where value is a variable name.
Build it as you would any other formula, with the last line being the value you want to return.
You are limited in that you can only use local variables inside of the function. You cannot call particular fields because the purpose is to make a function that is portable to other reports.
To call it, in some other formula you just treat it like any other function.
testme(datatobeprocessed);
an example;
Function (datevar value)
local stringvar strReturnvalue;
strReturnvalue:=totext(value,"MM/dd/yyyy") + "... is the date";
strReturnvalue;
This illustrates the use of other functions, and the use of variables inside the function.
scottm.
- Thread starter
- #4
Hi
Thanks for the reply
Unfurtunatelly I don't understand. I did not work with user defined function at all
The function is allready built in SQL Server, and not by me
Let's say is called function1
I do SQLExpression field testme. I write there
dbo.function1 and next ?
I don't understand what do you mean by plug in the function
and I have no idea what to put in (datatobeprocessed)
Sorry
Thanks for the reply
Unfurtunatelly I don't understand. I did not work with user defined function at all
The function is allready built in SQL Server, and not by me
Let's say is called function1
I do SQLExpression field testme. I write there
dbo.function1 and next ?
I don't understand what do you mean by plug in the function
and I have no idea what to put in (datatobeprocessed)
Sorry
-
1
- #5
Dear Cordrutza,
ScottM directly answered the question you posed, quite well.
A User Defined Function is one that is built in SQL Server by someone ... whether it was you or a dba. It would not be a built in function.
What Scottm was trying to explain is that if you have a User Defined Function that already exists in SQL Server you call it in a SQL Expression and pass any expected parameters.
A parameter in this context is not a Crystal Parameter, but the "value(s)" expected by the User Defined Function. If you do not know the values expected by the Function, you need to speak to your DBA before trying to use it in Crystal.
A User Defined SQL Server Function is called from a SQL Expression you create in Crystal and has the syntax of:
functionowner.functionname(paremetersincommadelimtedlist)
You would need to:
Know the owner of the function and the function anme, usually dbo but can be others so check with your dba.
Know the parameters that the function takes. For Example, here is a function that I use:
dbo.dhms(Table.FieldWithSeconds)
This formats any field for me in a DD:HH:MM:SS style string (that is days, hours, minutes, and seconds).
Make sure you have EXECUTE rights on the function.
If I were you, I would speak to your DBA or someone knowledgeable about your database and the purpose of any User Defined Functions in your database and the arguments (parameters) they expect.
Regards,
ro
Rosemary Lieberman
rosemary-at-microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
You will get answers more quickly if you read this before posting: faq149-3762
ScottM directly answered the question you posed, quite well.
A User Defined Function is one that is built in SQL Server by someone ... whether it was you or a dba. It would not be a built in function.
What Scottm was trying to explain is that if you have a User Defined Function that already exists in SQL Server you call it in a SQL Expression and pass any expected parameters.
A parameter in this context is not a Crystal Parameter, but the "value(s)" expected by the User Defined Function. If you do not know the values expected by the Function, you need to speak to your DBA before trying to use it in Crystal.
A User Defined SQL Server Function is called from a SQL Expression you create in Crystal and has the syntax of:
functionowner.functionname(paremetersincommadelimtedlist)
You would need to:
Know the owner of the function and the function anme, usually dbo but can be others so check with your dba.
Know the parameters that the function takes. For Example, here is a function that I use:
dbo.dhms(Table.FieldWithSeconds)
This formats any field for me in a DD:HH:MM:SS style string (that is days, hours, minutes, and seconds).
Make sure you have EXECUTE rights on the function.
If I were you, I would speak to your DBA or someone knowledgeable about your database and the purpose of any User Defined Functions in your database and the arguments (parameters) they expect.
Regards,
ro
Rosemary Lieberman
rosemary-at-microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
You will get answers more quickly if you read this before posting: faq149-3762
- Thread starter
- #6
smcnulty2000
Technical User
This happens even to native english speakers too, codrutza. Especially with technical stuff.
No problem.
scottm.
No problem.
scottm.
- Status
Similar threads
- Locked
- Question
- Locked
- Question