Hi all
How can I use an user defined functions (SQL server) in a report?
I am using CRXI
OLE DB, SQL Server, Visual Object
Can somebody give me an example of a built custom function in CR?
Thanks
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.
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
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:
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.