The Crystal Reports offers a very user-friendly and handy feature in the form of Custom Function. The custom function fosters reusability of computation that would otherwise require multiple formula to handle this situation within the Report. Sometimes there is a scenario that requires one to display dynamic values for which the use of Report formula would lead to complexity. Under these circumstances, the right approach would be to utilize the custom function. Custom functions are procedures that can be created in Crystal Reports to evaluate, process and generate results. When a custom function is used in a formula, all the operations within its definition are performed without the explicit need to specify logic in the formula itself. Thus the custom functions provides a way for you to reuse and share the logic within multiple formula that saves the time and effort on repetitive formulation and better logic integrity can be ensured as everything is properly formulated in a single custom function instead of multiple formula.
Custom function can be added to the Report from Crystal Repository, an exising formula or Report Custom Function. A custom function can be included in a Report formula directly as a part of formula logic in the Formula Editor or it can be chosen to supply formula logic through the Extractor in the Formula Expert.
In order to create custom function, the following action needs to be performed:
- Open the Formula Expert from the Report menu. Right click on Custom Function and select ‘New’.
- In the Custom Function Name Dialog enter the desired name e.g. ‘Process Interval’ and choose the ‘Editor’.
- In the Editor type the syntax that corresponds to the formula that you wish to reuse. Save and close the formula.
In order to create formula that use the custom function, the following action needs to be performed:
- Open the Formula Expert from the Report menu. Right click the Formula icon and select ‘New’.
- In the Custom Function Name Dialog enter the desired name e.g. The illustration consists of the formula with the name ‘Interval 1’.
- Within the list of Function panel, click on the Custom Function ‘Process Interval’ and enter the names of database fields in the arguments for which you need to calculate the duration. Save and close the formula.
- Drag the formula field on the Design tab at the required position in the Report.
- Follow the same process in creating another formula with the required name. e.g. The illustration consists of the formula with the name ‘Interval 2’.
- Reuse the Custom Function ‘Process Interval’ in the same manner but use different set of database fields.
- Drag the formula field on the Design tab at a different position in the Report than the earlier formula.
The definition of custom function and two formula created thereafter has been illustrated in the screen capture below:
Custom Function (Process Interval)
Function (datetimevar in_time,datetimevar out_time)
interval := datediff(“s”,in_time,out_time); //get the seconds between in_time and out_time
hrs := Truncate(Truncate(interval/60)/60); //get the interval in hours between in_time and out_time
min := Remainder(Truncate(interval/60),60); //get the minutes in hours between in_time and out_time
sec := Remainder(interval,60); //get the interval in seconds between in_time and out_time
hhmmss := totext(hrs,”00″) + “:” + totext(min,”00″) + “:” + totext(sec,”00″); //concatenate into total time
Formula [ (Interval 1), (Interval 2) ]
// Interval 1
// Interval 2
In the below illustration that consists of the Preview tab of the Report achieved on the Refresh, the values under Column Header ‘Work Time (Mon)’ represents the outcome of the first formula containing the custom function created in the beginning. The values under Column Header ‘Work Time (Tue)’ represents the outcome of the second formula containing the custom function created in the beginning.
Thus, it can be realised that a calculation from a Custom Function can be reused in multiple formula. In the process, it is possible to save the time and effort by avoiding duplication in designing multiple formula and its unnecessary maintenance.