[Solved] Sum a calculated field

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Sum a calculated field

Postby Nocton » Wed Nov 23, 2011 5:37 pm

I have a calculated field in the detail of a report. How can I sum it in the group footer?

This would be easy in Access, but I cannot see how to do it in OO, because the calculated field name does not appear in the list of items which can be summed/accumulated.

Regards, Nocton
Last edited by Nocton on Wed Nov 23, 2011 9:31 pm, edited 1 time in total.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 494
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Sum a calculated field

Postby chrisb » Wed Nov 23, 2011 8:33 pm

Hello Nocton,

I assume you use Report Builder.

I also assume that the question is:-

My report contains the fields 'Field1' and 'Field2'.
In the detail of my report I display the result of the calculation 'Field1' * 'Field2'.
I now wish to display the sum total of this calculation in my group footer.
Because this calculation is made within the report and not the query I cannot initialise an accumulation function.
Please Help!!

You need to define a function.
1. Open the Navigator
2. Under 'Groups' click the '+' to the left of the relevant group. (In the clip this group would be 'StartDate')
3. Right click 'Functions' and select 'New Function'.
4. Your new function will appear immediately below. Its called 'Function'.
5. Click on 'Function' and its properties will be shown in the properties pane. (F4 to toggle on/off).
6. Name = SumCalcFields
7. Formula = [SumCalcFields]+([Field1]*[Field2]).
8. Initial value = [Field1]*[Field2].
9. Drag out a text box in the group footer.
10. Back to properties. Hit 'Data' tab.
11. Data Field Type = User-Defined Function.
12. Function = [SumCalcFields]

A word of warning. The fields which contain the values used these calculations must NOT contain null values.
Attachments
Prop&Nav.PNG
Prop&Nav.PNG (23.7 KiB) Viewed 10889 times
Last edited by chrisb on Wed Nov 23, 2011 8:45 pm, edited 1 time in total.
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: Sum a calculated field

Postby squenson » Wed Nov 23, 2011 8:35 pm

A workaround: I usually avoid using calculated fields in reports as they sometimes require different syntax and it makes the debugging much more difficult. I prefer to create a query with all the fields, including some calculated fields, and create a report based on the query as a source, not the table. This has an additional advantage: you can get your data in a table format for easy copy/paste to Calc.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Sum a calculated field

Postby Nocton » Wed Nov 23, 2011 9:31 pm

Thank you, chrisb, that solution works fine. I had not previously used user-defined functions, but this opens greater flexibility for my reports. If the fields did have Null values, is there any way to allow for that? I made an earlier post 'Averages on Report' http://user.services.openoffice.org/en/forum/viewtopic.php?f=42&t=45551, to which I have not had a reply, concerning this problem of null values and calculated fields.

Thank you, squenson, also for your suggestion of using a query. The problem is that because of the lack of a subreport feature in OO Base, my report query is already very complex. My calculated field is relatively complex also, containing two IF statements. It would be more difficult to do the calculation in a query, but if it were on a form, where I can have a subform with the data coming from just that calculation query, then I should probably do it that way.

Regards, Nocton
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 494
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: [Solved] Sum a calculated field

Postby chrisb » Fri Nov 25, 2011 10:07 pm

'ISNUMBER()' is your friend.

The code below would do the job here.
Code: Select all   Expand viewCollapse view
[SumCalcFields] + IF( AND( ISNUMBER( [Field1] ); ISNUMBER( [Field2] ) ); [Field1] * [Field2]; 0 )

Yes it can be cumbersome but its also effective.

The bulk of the data in a report is printed as is.
NULL values only become a problem when present in a field on which we are required to perform a calculation.
The result of any calculation involving a NULL is always NULL.
The same can be said for SQL. Its only the aggregate functions (column related) which deal with the problem for you.
NULL * 5 = NULL in both SQL and Report Builder.

When an accumulation function in Report Builder encounters a NULL then the value it contains becomes NULL.
It will begin to work as expected when it comes to the next row but with a starting value of zero.
This applies equally to both user defined functions and the built in function 'Accumulation'.

ROW 1 = 5
ROW 2 = NULL
ROW 3 = 4
TOTAL = 9
Accumulation function = 4.
open office 4.1.6 & LibreOffice 6.0.7.3 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 177
Joined: Mon Jun 07, 2010 4:16 pm

Re: [Solved] Sum a calculated field

Postby Nocton » Sat Nov 26, 2011 6:26 pm

Thank you, chrisb, for the additional code and explanation of what happens with NULL values.
OpenOffice 4.2.0 on Windows 10
Nocton
Volunteer
 
Posts: 494
Joined: Fri Nov 05, 2010 10:27 am
Location: UK


Return to Reporting

Who is online

Users browsing this forum: No registered users and 4 guests