Previous Topic  Next Topic

Using User-Defined Formula Functions

When you create formulas in a catalog, if the built-in functions do not satisfy your requirements, you can design your own formula functions by using the User Defined Function Editor or importing them from Java files. This topic describes how you can create and import user-defined functions and use them in formulas.

This topic contains the following sections:

• Creating User-Defined Functions in a Catalog
• Importing User-Defined Functions from Java Files
• Using User-Defined Functions
  • Viewing Formula References of Imported UDFs
  • Example: Using Imported UDF

You cannot apply user-defined functions saved in a catalog in dynamic formulas. If you want to do this, you need to create the user-defined functions via the dynamic resource list.

Creating User-Defined Functions in a Catalog

1. Open the catalog.
2. In the Catalog Manager, expand the data source to create the function, then do either of the following:
   • Select the User Defined Functions node or any existing function in the data source and select New Function on the toolbar.
   • Right-click the User Defined Functions node in the data source and select New Function on the shortcut menu.
3. In the Enter Function Name dialog box, provide a name for the function and select OK. Designer displays the User Defined Function Editor dialog box.

   

4. Compose the function by selecting the required fields from the Fields panel (including DBFields, formulas, summaries, and parameters in the current catalog data source, and some special fields), functions from the Functions panel (including built-in functions and other user-defined functions), and operators from the Operators panel. When the predefined user-defined functions, summaries, and parameters cannot meet you requirement, you can create new function, summary, and parameter using the New XXX option on the toolbar. Designer saves the newly created objects into the same catalog data source as the function. You can also write the function by yourself.

   The function syntax is:

   arguments: VariableType1 VariableName1, VariableType2 VariableName2, ...;

   For example, arguments: integer age, string name;

   An argument works the same as a local variable except that you cannot assign any value to it, like arguments: integer age=10;. For more information, see Formula Syntax.

5. Save the function and close the editor. Designer adds the function in the catalog resource tree.

• When you refer to any field in a user-defined function, you need to add the "@" symbol as the prefix of the field's reference name. If the field name contains spaces, you need to quote the reference name with double quotation marks (""). For example, if the field name is Customer Name, the reference name is @"Customer Name".
• When user-defined functions reference display names or mapping names, the names should not contain any of following characters if you do not quote the names using double quotation marks:

  "~", "`", "!", "@", "#", "$", "%", "^", "&", "*", "(", ")", "-", "+", "=", "{", "}", "[", "]", "|", "\\", ":", ";", "\", " ' ", "<", ",", ">", ".", "?", "/"

  Examples:

  • Expression @Customer#; causes a syntax error, but @"Customer#" is correct.
  • If a field has the display name Category.Measure, when you add it to a user-defined function, quote it as "Category.Measure" or "Category"."Measure".
• You should not use more than 97 "else if" clauses in an "if-else" statement in a formula.

Back to top

Importing User-Defined Functions from Java Files

To import a user-defined function, you first need to develop a Java file to implement a function, and then import it as a class. You can use the following data types for interacting with the Report products (passing parameters and returning parameters): DbBigInt, DbDouble, fCurrency, DbBit (using for Boolean type), DbChar (using for String), DbDate, DbTime, DbTimestamp, DbBinary, fText (using for Long VarChar), fImage (using for Long VarBinary), fIntArray, fNumArray, fCurArray, fBoolArray, fStrArray, fDateArray, fTimeArray, fDateTimeArray, fBinaryArray, fTextArray, fImageArray, fIntRange, fNumRange, fCurRange, fBoolRange, fStrRange, fDateRange, fTimeRange, and fDateTimeRange. All the data types that start with "f'" belong to the "jet.formula.*" package; the other data types belong to the "jet.connect.*" package. For more information, see the jet.formula and jet.connect packages in the Report Java API Documentation.

1. Develop your own Java program that implements the functions you need.
2. Compile the Java program and add it to the ADDCLASSPATH variable of setenv.bat/setenv.sh in <install_root>\bin of both Designer and Server.
3. Start Designer and open the Formula Editor or User Defined Function Editor dialog box.
4. Import your class file using either of the following two methods.
   • Using the "import" statement

     import instName from "ClassName";

     Where,

     • instName is the instance name that you give when you load the class. It is a global variable. You can use it in formulas and user-defined functions in the current catalog data source.
     • ClassName is the class name defined in your class file. If you put the class file in the jet.formula.javaformula package, you only need to provide the class name. However, if it is in your own package, you must specify the qualify name of the class.

       For example, the following declaration assumes the MyFunctions class is in the jet.formula.javaformula package.

       import myfunc from "MyFunctions";

       And the following declaration allows user-defined UDF class in any package, supposing the class is com.mycom.MyFunctions.

       import myfunc from "com.mycom.MyFunctions";

     

     • You should write the "import" statement only without anything else in a separate formula or user-defined function so as to avoid importing unwanted things.
     • You should avoid giving the same instance name when importing the same UDF.
   
   
   • Using dialog box
     1. In the Formula Editor or User Defined Function Editor dialog box, navigate to Menu > File > Import UDF Classes. Designer displays the UDF Classes dialog box.

        

     2. Select Add , then in the Import UDF Class dialog box, type the class name with full package path and select OK.
     3. Designer displays the class with the package path in the UDF Classes dialog box. Select OK.

Back to top

Using User-Defined Functions

In the Functions panel of the Formula Editor and User Defined Function Editor, you can find the user-defined functions created using the User Defined Function Editor under the User Defined Functions node and those imported from Java files under the UDF node. You can then call the functions when creating formulas or user-defined functions by double-clicking them.

Suppose a user-defined function function1 is arguments: integer age, string name;, you can call it as follows:

• @function1(25, "John Smith");
• @'function1'(25, "John Smith");
• @"function1"(25, "John Smith");

For imported UDFs, you can also call them using the following statement:

instName.MethodName(parameters);

Where, MethodName is the method name in your class. You can call methods in your class as many times as you want by loading this class only once. For example, a user-defined function function2 calls an imported UDF first.

Import a from jet.formula.javaformula.UDF;
arguments: string countries;
return a.getvalue(@country, @amount, countries);

Then, a formula calls function2.

@function2("USA, China")

Viewing Formula References of Imported UDFs

You can view the formula references of an imported UDF in the Formula Editor.

1. Select the function.
2. Do either of the following:
   • Navigate to Menu > Formula > Formula References on the toolbar.
   • Right-click the function and select Formula References on the shortcut menu.
3. Designer displays the UDF References dialog box, showing all the formulas that reference the imported UDF if there are.

Example: Using Imported UDF

In this example, we use a demo Java program MyFunctions.java which is put in the jet.formula.javaformula package, to illustrate how to write record data to a file using UDF functions. You can get the Java source file from <install_root>\help\samples\APIUDFormula\jet\formula\javaformula.

Take the following steps:

1. Compile the Java file MyFunctions.java to generate the class file (make sure that the path of JREngine.jar is before that of report.jar).

   javac -classpath "<install_root>\lib\JREngine.jar;<install_root>\lib\report.jar;" MyFunctions.java

2. Edit the setenv.bat batch file in <install_root>\bin to add the class path. You just add the root path. For example, suppose the class file is located in C:\LogiReport\Designer\help\samples\APIUDFormula\jet\formula\javaformula, you can append C:\LogiReport\Designer\help\samples\APIUDFormula to the ADDCLASSPATH variable in the batch file.
3. Start Designer.
4. Create a new formula to load this class and open a file on disk by calling the methods in MyFunctions.java. Here we define the formula fmlA to load the class and call the method to open a temp file.
   

   PageNumber;
import myfunc from "MyFunctions";
global integer filehandle = myfunc.openfile("e:\\test\\data.txt", false);

   If the class file is in your own package com.mycom other than jet.formula.javaformula, you need to import the class in a formula.

   import myfunc from "com.mycom.MyFunctions";

5. Create fmlB to call the method to write data into the temp file.
   

   string contents ="";
contents = @"Customer Name"+"\t"+ @Country+"\t"+ @Phone + "\t"+ PageNumber + "\n";
myfunc.write(filehandle, contents);

   The return value of the last statement is the formula result.

6. Create another formula fmlC to close the temp file.
   

   PageNumber;
myfunc.closefile(filehandle);

   When you add PageNumber in a formula, Report Engine executes the formula after the page break, meaning, Report Engine controls the calculation point.

7. We have now defined three formulas, fmlA, fmlB, fmlC. You can use them in a report as with a normal one.

Designer does not require the "8859-1" encoding anymore and the UDF functions you define should be able to return the correct Unicode strings. Thus, if you have UDF functions created in previous Designer versions which still return "8859-1" encoded strings, you may need to modify them to make them return Unicode strings.

Back to top

Previous Topic  Next Topic