4/19/2024 4:29:36 PM
slxdeveloper.com
Now Live!
|
|
| |
| Creating a Crystal Reports Custom Function Library |
|
| Description: |
You don't always have to massage your data with complex formulas to report on it. Complex calculations in a report can be difficult at times. Not only that, but can cause your report to run unbearably slow. Custom function libraries allow you to pull the complex calculations & functions out of the report and in to an environment where you can better control their output.
|
| Category: |
Miscellaneous SalesLogix Articles
|
| Author: |
Ryan Farley
|
| Submitted: |
9/21/2007
|
| |
|
| Stats: |
Article has been read 204518 times
|
Rating:
  - 4.5 out of 5 by 21 users |
|
|
|
fiogf49gjkf0d
You don't always have to massage your data with complex formulas to report on it. Complex calculations in a report can be difficult at times. Not only that, but can cause your report to run unbearably slow. Custom function libraries allow you to pull the complex calculations & functions out of the report and in to an environment where you can better control their output.
I originally wrote this article in 1999 for another publication focused on Visual Basic. I am posting this here by request. The concepts described here still apply to building custom function libraries for Crystal Reports today. The article discusses building custom function libraries using Visual Basic 6, although see the comments at the end of an article for details about doing this from .NET and deploying via .NET Extensions.
Crystal Reports allows you to create custom function libraries with little effort. These custom libraries, known as User Function Libraries (UFL), have been an option since even some of the earliest versions of Crystal Reports. Custom DLL's could be created which extended Crystal's libraries. However, these DLL's were impossible to create in Visual Basic due to the complex specifications for the DLLs and limitations in Visual Basic. The original SDK for creating a UFL was developed mainly for C programmers and is still provided on the Crystal Reports CD. However, this tutorial is going to take a much easier approach.
Since version 6 of Crystal Reports, Seagate extended the ability of creating a UFL to any language that can create a COM application. Creating COM applications in Visual Basic is a breeze since the VB IDE does most of the work for us. By creating a UFL COM application you are able to extend the functionality of Crystal Reports and greatly enhance your reports. Remove complex functions from your report and put it in a COM application. You save yourself a headache by not having to find some tricky way to implement the function, but also your report may run faster by allowing the function to run as compiled code. The functions from your UFL are accessed in Crystal Reports the same way you access Crystal's built in functions so little effort is required to use your custom functions in a report. There are few limitations as to what you can in your COM application. You could perform a calculation, use ADO to retrieve data from another data source, or even retrieve information from the Internet. The sky is the limit.
Let's take a quick look at how your COM application interacts with Crystal Reports. One of the DLL's installed by Crystal Reports is a file called U2LCOM.DLL. This DLL acts as a gateway between your COM application and Crystal Reports. U2LCOM.DLL looks for any COM applications that follow Crystal's specific naming convention for UFL's. It then displays any public functions from your COM application in the "Additional Functions" category in the Crystal formula editor window. That is really all there is to it. However, there are a few limitations & rules you must follow. First, your COM application's file name must follow a strict naming convention. It must be exactly eight characters in length and prefixed with "CRUFL". Second, the COM application must not contain any public functions by the same name as existing Crystal Report functions. Finally, the U2LCOM.DLL from versions 6 & 7 supports only 63 functions. The U2LCOM.DLL from version 8 has a limit of 300 functions. This is the total number of functions from all UFLs, not just yours alone.
It's time to put the idea in action. Here are the steps to create a UFL in Visual Basic:
Now we are ready to test using the UFL in Crystal Reports:
- Launch Crystal Reports and start a new report.
- In design mode, go to Insert then Formula Field. Then click New to create a new formula field.
- In the functions window, expand the Additional Functions folder. The functions in Class1 from our UFL will be named tstClass1UFLTest1 and tstClass1UFLTest2 (See figure 1). You can probably tell from the name that the U2LCOM.DLL named our functions as follows: The last three characters of the COM application name, then the Class name, then function name (If you don't like how it names your functions, there is a trick that I'll talk about later to change this). First select the tstClass1UFLTest1 function and add it to the report. Then repeat the process to add the second one. Notice that the second one shows that it accepts a parameter.
- That's it. Now just run the report to see the output
I mentioned that there is a trick to change the way that the U2LCOM.DLL names your functions. It is simple. Add either a property or a public Boolean variable to your class named UFPrefixFunctions. Then set it to false in the Class_Initialize subroutine.
Public UFPrefixFunctions As Boolean
Private Sub Class_Initialize()
UFPrefixFunctions = False
End Sub
What this does is tell U2LCOM.DLL to not display the prefix and class names with the function. Add the code above to your project and re-compile it. Now only the function names are displayed in the formula editor as UFLTest1 & UFLTest2(sVal).
Wasn't creating a custom UFL easy? Sure. However, there are a few pitfalls to remember. First, your function's return type must be one of the data types supported by Crystal which are: Integer, Long, Single, Double, Currency, Date, Boolean, & String. This means that variants are out. If you try to return a variant, your function just won't show up in the additional functions list in the formula editor. Why is this a big deal? Remember that in Visual Basic if you don't explicitly state the return type of your function then VB assigns it as a variant type by default. So, remember to always declare your function like:
Public Function MyFunction() As String
Instead of just:
Public Function MyFunction()
Second, mentioned earlier was the number of functions that the U2LCOM.DLL allows. If you try to add more than the allowed number of functions (63 for versions 6 & 7, 300 for version 8) then Crystal has been known to produce a page fault. Typically the message will be something like Error: "Invalid Page Fault occurred in module Unknown". Lastly, keep track of your COM application DLLs, if you end up with two versions of the same DLL registered on your computer then conflicts will occur. Changing the name of your DLL or making sure that older versions are removed can avoid this.
The functions from our UFL were as simple as they come. We left it this way to not distort the fact that the creation of a UFL is extremely easy. Adding functions that accept parameters are just as easy. Crystal shows you the parameter list in the formula editor window. You could then pass the function a field from your database, the result from another calculation or whatever you want just the same as using a built in function such as count() or trim().
Each time you add a function (even in another class module) it will show up in the Additional Functions list ready for use. Imagine the possibilities of what you could do in a report using a custom UFL. Take report building to the next level. Weigh the benefits of the time it will take to implement a calculation in a Crystal formula compared to the time it could take to implement the same calculation in Visual Basic where you have more control. Think of the time it may take to run a complex formula in a report compared to running the same operation as compiled code. Save all of your complex reporting tasks for your UFL and Crystal won't notice the difference, but you sure will.
As I mentioned at the beginning of this article, I originally wrote this 8 years ago. While the concepts haven't changed too much since then, what we now have available in the SalesLogix development world certainly has (note: this was not originally written for a SalesLogix audience).
There are many cases where this sort of thing could be useful for SalesLogix. The one that jumps to mind is if you encrypted values in the SalesLogix database (See Encrypting Values in SalesLogix on SalesLogixBlog.com) and wanted a special Crystal Report where these values would appear in their text form, unencrypted. You could easily implement this one as a custom UFL for Crystal. Implementing these as a .NET assembly is valid, as long as you register it for COM interop. While you could build an installation and distribute it to your users, you could also create a .NET Extension. The extension itself doesn't matter much, but you'd add the UFL DLL as a support file to the extension and then have the extension copy the file to a more permanent home and register it for COM (by calling RegAsm or other methods). A great way to get something like this out to the dsktops.
Until next time, happy coding!
-Ryan
|
|
|
|
|
|
Rate This Article
|
you must log-in to rate articles. [login here]
|
|
|
|
Please log in to rate article. |
|
|
|
Comments & Discussion
|
you must log-in to add comments. [login here]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 1/30/2008 12:40:31 PM | fiogf49gjkf0d Is there an example of implementing a UFL with C#? I understand the sdk help file documents this, but I'm have problems registering the component in the GAC. It is successfully installing the dll to the windows\assembly directory, but it is not appearing under Visual Basic UFLs folder. ActiveX components compiled via VB show up no problem however. Is there a different process to register .net components? | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 1/30/2008 4:43:27 PM | fiogf49gjkf0d Hi Koichi. To be honest, I don't know. I've not built a UFL for many years (I wrote this article in 1999) and never did attempt with .NET.
Good luck. | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 4/9/2008 6:33:08 PM | fiogf49gjkf0d Yes, it can be done. The instructions to do it using Visual Studio 2005 are on MSDN: http://msdn2.microsoft.com/en-us/library/ms227603(VS.80).aspx.
It can also be done on Visual Studio .NET 2003, but it takes a little bit more work when signing your assembly with a strong name. | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 1/20/2009 4:45:27 AM | fiogf49gjkf0d Hi I have read this article and I request you to help me out on my problem also. I have some dlls lying in my appserver and there are some functions exposed by those dlls which i need to call from crystal reports . How can this be achieved through UFL? What should I do/develop for this? | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 2/5/2010 1:00:47 PM | fiogf49gjkf0d I've created a UFL using Visual Studio 2005 but its only working on the development machine. I have copied U2lcom from the development machine to the target machines, I have registered the UFL dll using com on the target machine but nothing is working. Please help, how do I deploy my UFL? | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 5/10/2010 1:25:11 AM | fiogf49gjkf0d
Hi Ryan,
I hope you can help me with this. I had created couple of function using VB.net and complied to DLL and had used the prefix "CRUFL". Also when I complie from VB
I placed the check mark for Register for COM introp. As you mention above that DLL acts as a gateway between your COM and applicationa dh Crystal Reports. I can see my
function at the crytal report additional functions on the u2lcom.dll list. But what if I doesn't want to use COM and can see my own function in the additional functions?
or all the UFLs has to go thur COM?
Thanks
David | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 7/3/2013 8:45:30 AM | fiogf49gjkf0d | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 7/3/2013 8:54:33 AM | fiogf49gjkf0d Hello,
Sorry for my blank comment, it's an error.
I wan't to know if it's possible de open à Crystal Report note save in the database SLX but in a repertory on the server as a "CrystalReportViewer" in a .net application. In fact, I wan't to preview and print a report in a click action button on my form. To print the informations of my form, I add a parameter in my report and set the value in my code as this :
Sage.Entity.Interfaces.IRequestSampleDoc rsd = form.CurrentEntity as Sage.Entity.Interfaces.IRequestSampleDoc;<br />
if(rsd != null)
{
ReportDocument report = new ReportDocument();
report.Load("E:\\Saleslogix\\Reports\\DemandeEchDoc.rpt");
CrystalReportViewer crystalReportViewer1 = new CrystalReportViewer();
crystalReportViewer1.ReportSource = report;
report.SetParameterValue("idRequest", rsd.Id);
ParameterFieldDefinitions crParameterFieldDefinitions ;
ParameterFieldDefinition crParameterFieldDefinition ;
ParameterValues crParameterValues = new ParameterValues();
ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();
crParameterDiscreteValue.Value = rsd.Id;
crParameterFieldDefinitions = report.DataDefinition.ParameterFields;
crParameterFieldDefinition = crParameterFieldDefinitions["idRequest"];
crParameterValues = crParameterFieldDefinition.CurrentValues;
crParameterValues.Clear();
crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);
CrystalDecisions.Web.CrystalReportViewer crystalReportViewer1 = new CrystalDecisions.Web.CrystalReportViewer();
crystalReportViewer1.ReportSource = report;
crystalReportViewer1.RefreshReport();
}
But my report will not open and I don't have any error that can help me for debug...
Can you help me please ?
Thanks in advance for your response! | |
|
| Re: Creating a Crystal Reports Custom Function Library
Posted: 7/3/2013 9:55:05 AM | fiogf49gjkf0d Hi,
I'm really sorry but I did not pay attention that I wasn't on the forum to post this question !!!If you can, please delete these comments.Yet once excuse me!  Have a good day.
| |
|
|
|
|
|
Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
|
|
|
|
|
|