.NET User Function Library

Crystal syntax gives you many built-in functions and even lets you write custom functions, but what if your project has an existing library of business logic that needs to be used in your reports? Will you need to rewrite these rules from scratch using Crystal syntax? Fortunately, the answer is no. Crystal Reports lets you create user function libraries in .NET.

A user function library (UFL) is a set of custom functions developed in another language and accessible with the Crystal Reports designer. Your report can call those functions and display the return values on the report or use them in formulas.

Creating a UFL for Crystal Reports requires following certain rules. They are as follows:

• The project name has to be prefixed with “CRUFL”.
• The project has to have both an interface class and implementation class. Both of which will have Com and GUID attributes.
• The assembly is added to the Global Assembly Cache so that it can be visible to Crystal Reports.

Let’s walk through the steps to create a UFL. We’ll create a custom function that is passed a month and it returns the company’s fiscal month. We’ll call the function GetFiscalMonth().

Open Visual Studio .NET and create a new project. Use the Class Library template. Name the project CRUFL_CorpFiscalMonth.

Delete the Class1.cs file that is created by default.

Right-click on the CRUFL_CorpFiscalMonth project name and select Properties. Click the Signing tab and click the option Sign the Assembly.

Click on the drop-down list below the checkbox and select New. For the Key File Name property, enter any unique name. For this example, I’ll use “uniquekey”.

Uncheck the option Protect My Key File With a Password and click the OK button to save your changes.

If you’re using VB.NET, click on the Compile tab. If you’re using C#, click on the Build tab. Scroll down to select the Register For COM Interop option.

Right-click on the CRUFL_ CorpFiscalMonth project name and select Add > Class. Name the class IFiscalMonth and click the Add button. This will be the interface that defines the function’s signature.

Copy the code from Listing 7-1.

Listing 7-1. IFiscalMonth code.
[VB.NET]
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Runtime.InteropServices
_
Public Interface IFiscalMonth
Function GetFiscalMonth(ByVal month As Integer) As Integer
End Interface
[C#]
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
namespace CRUFL_CorpFiscalMonth
{
[ComVisible(true), InterfaceType(ComInterfaceType.InterfaceIsDual), Guid("ABBBB510-D2A0-4cab-BAA8-73040F4056FA")]
public interface IFiscalMonth
{
int GetFiscalMonth(int month);
}
}

The key element of this code is that it adds a reference to the System.Runtime.InteropServices library. Secondly, it adds the COM and GUID attributes to the class.

In the code, I specify a GUID that you can use. You should always create a new GUID string using Visual Studio .NET. Click the menu options Tools > Create Guid. This opens the Create GUID dialog box. Click the Registry Format option and select Copy. This copies the GUID to the clipboard. You can then copy and paste it to your programming code. Remember to remove the curly brackets at the beginning and end of the GUID. If you’re using VS.NET 2005, you don’t have the Create GUID program installed by default. You can run it by going to the Tools folder within the Visual Studio installation directory and running GuidGen.exe.

After entering the code in Listing 7-1, right-click on the CRUFL_ CorpFiscalMonth project name and select Add > Class. Name the class FiscalMonth and click the Add button.

Copy the code from Listing 7-2.

Listing 7-2. FiscalMonth code.
[VB.NET]
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.Runtime.InteropServices
_
Public Class FiscalMonth
Implements IFiscalMonth
Private FirstFiscalMonth As Integer = 7
Public Function GetFiscalMonth(ByVal month As Integer) As Integer Implements IFiscalMonth.GetFiscalMonth
Dim fiscalMonth As Integer = 0
If month > FirstFiscalMonth Then
fiscalMonth = month - fiscalMonth + 1
Else
fiscalMonth = month + fiscalMonth - 1
End If
Return fiscalMonth
End Function
End Class
[C#]
using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
namespace CRUFL_CorpFiscalMonth
{
[ComVisible(true), ClassInterface(ClassInterfaceType.None), Guid("FF7EBBE2-BB1D-4959-9779-7A0ABFC7A643")]
public class FiscalMonth : IFiscalMonth
{
private int FirstFiscalMonth = 7;
public int GetFiscalMonth(int month)
{
int fiscalMonth = 0;
if (month > FirstFiscalMonth)
{
fiscalMonth = month - fiscalMonth + 1;
}
else
{
fiscalMonth = month + fiscalMonth - 1;
}
return fiscalMonth;
}
}

This code defines the FiscalMonth class and inherits the IFiscalMonth interface. It adds code to the GetFiscalMonth() method to calculate the fiscal month based on the FirstFiscalMonth constant.

Save your project and build it using the menu items Build > Build Solution. This compiles a DLL and creates a type library file (.TLB) with the class signature in it. If you navigate to the project’s Bin/Debug folder, you should see three files with these extensions: .DLL, .TLB, and .PDB.

For Crystal Reports to know that the library exists, you need to put the DLL into the Global Assembly Cache (GAC). To do this, go to the Visual Studio command prompt by clicking the Windows Start button and then selecting All Programs > Microsoft Visual Studio 2008 > Visual Studio Tools > Visual Studio Command Prompt.

Change the directory to where the DLL is located (using the ‘C:\CD foldername’command).

Once in the directory enter the following command: gacutil /if CRUFL_CorpFiscalMonth.dll and press the Enter key.

You will get a confirmation message that the assembly has been added to the cache. You are now ready to go into Crystal Reports and call it from the Formula Workshop.

Open any existing project that uses Crystal Reports and open the report in design mode.

Right-click on the Formula Fields node and select New. Enter a formula name and click the Use Editor button. This opens the Formula Workshop.

Within the Formulas Workshop editor is the Functions window. Scroll down to the Additional Functions node and open it. Within it you’ll see a new tab called Visual Basic UFLs. Open it and you will see any custom functions that you’ve created. This is shown in Figure 7-15.

Figure 7-15. The list of custom user functions.

Notice that the name for each function is a combination of the project name, class name and the function name. It also lists the arguments that should be passed to the function.

You can now use this function in your reports and it will call the DLL that you built with Visual Studio .NET. This lets you take the business logic in your .NET application and implement it directly within your reports.