如何创建一个水晶报表用户自定义函数库 UFls (Creating a Crystal Reports Custom Function Library)

 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 FunctionPublic Function UFLTest2(sVal As String) _ As String    UFLTest2 = "Hello, " & sValEnd 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 BooleanPrivate Sub Class_Initialize()    UFPrefixFunctions = FalseEnd 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.

原文地址：http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=103

作者：Ryan