[Tutorial] Defining User defined Functions in Reports

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Defining User defined Functions in Reports

Postby GowerMick » Thu Mar 10, 2011 9:59 am

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 5.2.5.1 (x64)
Oracle Report Builder
Windows 10 Home
GowerMick
 
Posts: 100
Joined: Mon Feb 28, 2011 10:19 am

Re: [Guide] Defining User defined Functions in Reports

Postby BobKnipper » Fri Dec 07, 2012 6:23 pm

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
BobKnipper
 
Posts: 1
Joined: Mon Dec 03, 2012 11:32 pm

Re: [Tutorial] Defining User defined Functions in Reports

Postby Koa » Wed Jun 07, 2017 6:25 pm

Thanks. Interesting and useful.

BTW, it seems that something like Ifnull() can roughly be replaced with
Code: Select all   Expand viewCollapse view
IF(ISBLANK([x],0,[x])
.
LibreOffice 5.2.5.1 on Linux Jessie 8.6
Koa
 
Posts: 11
Joined: Sun Jan 15, 2017 11:53 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest