[Tutorial] Defining User defined Functions in Reports

Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
GowerMick
Posts: 114
Joined: Mon Feb 28, 2011 10:19 am

[Tutorial] Defining User defined Functions in Reports

Post by 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.
Mick
LibreOffice 7.2.6.2 (x64)
Oracle Report Builder
Windows 10 Home
BobKnipper
Posts: 1
Joined: Mon Dec 03, 2012 11:32 pm

Re: [Guide] Defining User defined Functions in Reports

Post by 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.
LibreOffice 3.5.1.2
Windows 7
Koa
Posts: 12
Joined: Sun Jan 15, 2017 11:53 pm

Re: [Tutorial] Defining User defined Functions in Reports

Post by 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])
.
LibreOffice 5.2.5.1 on Linux Jessie 8.6
Post Reply