Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Tuesday, September 02, 2014 
 
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 78553 times

Rating: - 5.0 out of 5 by 2 users
 

Creating a Crystal Reports Custom Function Library

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.


Extending Crystal Reports via COM

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.


A Working Example

It's time to put the idea in action. Here are the steps to create a UFL in Visual Basic:

  • In Visual Basic, select File then New Project from the menu. Select ActiveX DLL from the New Project window.
  • Name the project CRUFLtst. This name can really be CRUFL followed by any three characters, but for our test we'll name use tst.
  • Make sure the instancing property of Class1 is set to MultiUse (It should default to this)
  • Add a public function to Class one named UFLTest1 and another function named UFLTest2 that takes a string parameter.
  • Add some code to the UFLTest1 and UFLTest2 functions as follows:

    Public Function UFLTest1() As String
        UFLTest1 = "Hello, World"
    End Function
    
    Public Function UFLTest2(sVal As String) _ As String
        UFLTest2 = "Hello, " & sVal
    End Function

  • Compile the DLL. Normally you would need to register it also, but the VB IDE does that for you when you compile the DLL
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).


UFL Pitfalls

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.


Ease of the UFL

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.


Flash Forward Eight Years

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

 

About the Author

  Ryan Farley
(SalesLogix Business Partner)
Customer FX Corporation

Ryan Farley is the Director of Development for Customer FX and creator of slxdeveloper.com. He's been blogging regularly about SalesLogix since 2001 and believes in sharing with the community. He loves C#, Javascript, Python, web development, open source, and Linux. He also loves his hobby as an amateur filmmaker.

View Ryan's SalesLogix Mobile Seveloper Series
View Ryan's SalesLogix SData Developer Series
View Ryan's Git for the SalesLogix Developer series



View online profile for Ryan Farley
 

[ back to top] [ send to a friend]  

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] 
 
Author Article Comments and Discussion
Koichi Yanaga
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 1/30/2008 12:40:31 PM
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?
 
Ryan Farley

slxdeveloper.com Site Administrator
slxdeveloper.com Forum Top 10 Poster!

Re: Creating a Crystal Reports Custom Function Library
Posted: 1/30/2008 4:43:27 PM
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.
 
Leo
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 4/9/2008 6:33:08 PM
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.
 
prasad
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 1/20/2009 4:45:27 AM
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?
 
anna
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 2/5/2010 1:00:47 PM
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?
 
David Willis
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 5/10/2010 1:25:11 AM

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
 
mygush
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 7/3/2013 8:45:30 AM
 
mygush
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 7/3/2013 8:54:33 AM

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!

 
mygush
 

Re: Creating a Crystal Reports Custom Function Library
Posted: 7/3/2013 9:55:05 AM

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! Embarassed

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...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2014 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 9/2/2014 1:37:40 AM