[Tutorial] Defining User defined Functions in Reports

[GowerMick]

I have a report which adds activities and produces an Invoice on a company by company basis.

In the footer for each Company, I show the accumulated time in minutes (Duration) worked for the company during the period in a textBox, this guide shows how to convert the accumulated time into 'Hours' and 'Minutes' for display in the report.

Right Click Report and select Edit
Press F5 to open Navigator
Press F4 to show Properties Box if not already visible.

Find the list of Functions in Navigator
Right click 'Functions' title, and select 'New Function'
Create two new functions called 'Hours' and 'Minutes'

Find and highlight the Function created by the Wizard to get TotalDuration.
From the 'Formula' field of the property box, copy the formula to the clipboard. '[Duration]+[TotalDuration]'

Click on 'Hours' function
In the 'Formula' field of the property box, click on ... button to right of drop down list, to open the 'Function Wizard'.
Find the Function you want to use (ABS in this case) and double click it.
This will open a prompt to show you one parameter is required.
Copy the formula from the clipboard into the parameter box and enclose it in round brackets then add '/60' (to convert from minutes to hours)
i.e([Duration]+[TotalDuration])/60

When you press enter, the full formula is entered into the 'Formula' property box of the 'Hours' function as:-
ABS(([Duration]+[TotalDuration])/60)

Now click on the 'Minutes' function.
In the 'Formula' field of the property box, click on ... button to right of drop down list, to open the 'Function Wizard'.
Find the Function you want to use (MOD in this case) and double click it.
This will open a prompt to show you that two parameters are required this time.
Copy the formula from the clipboard into the first parameter box and enclose it in round brackets. i.e. ([Duration]+[TotalDuration])

Enter 60 into second parameter box and press enter to add formula to the 'Formula' property box as:
MOD(([Duration]+[TotalDuration]);60)

Having created the functions, assign them to the appropriate text boxes on the report. For each text box, in the property box, set Data Field Type to 'User-Defined Function' and set 'Function' field to 'Hours' or 'Minutes' as appropriate.

Set the other 'General' properties of these two text boxes to suit.

Job done.

[BobKnipper]

Just wanted to say thanks for the guide. Didn't answer my problem directly but had enough information for me to resolve my problem.Thanks again.

[Koa]

Thanks. Interesting and useful.

BTW, it seems that something like Ifnull() can roughly be replaced with

Code: Select all

IF(ISBLANK([x],0,[x])

.