Sum a record field using base reports

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
RapidSuccessCoach
Posts: 3
Joined: Thu Nov 22, 2007 7:33 pm

Sum a record field using base reports

Post by RapidSuccessCoach »

Hi ...

I'm trying to figure out how to sum a field within a set of records using the reporting tool.

I've created a base database and a simple report.

I can't seem to figure out how to do this !!!

Any help would be appreciated.

... Ron :)
markt
Posts: 5
Joined: Thu Nov 22, 2007 10:37 pm

Re: Sum a record field using base reports

Post by markt »

This can be done in the sun report builder

Insert a formatted field into your report
add a function into your report
change the function name to SUM
In the formula put [SUM]+ [RECEIPTS]
Set the initial value to 0
then select the function name SUM as the data source for the formatted field.

This will create a "total " field - unfortunatly it appears to to miss the first record out of the calculation. Which could be a bug
compuwatch
Posts: 24
Joined: Sat Jan 12, 2008 1:36 am

Re: Sum a record field using base reports

Post by compuwatch »

Instead of 0 you have to set initial value in your example to [RECEIPTS] then it works fine. Can anybody tell me how to calculate the tax with this tool?
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Sum a record field using base reports

Post by DrewJensen »

Well, you can set function variables at either of two levels in a Report Builder, report. The report level or the Group level - if you set it at the group level then it resets its initial value whenever the group changes - in this case it might be for every customer.

You can get an idea from this perhaps:

http://wiki.services.openoffice.org/wik ... amples/sum

Then again that is clear as mud perhaps...so maybe a better example wold help...but look at that and let me know.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Hacker John
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm

Re: Sum a record field using base reports

Post by Hacker John »

I found a way to sum fields in a report built with the wizard. It's a bit long-winded but it works.
  • First, create a query that sums the required fields from your source table/query.
    Create another query that combines your source table/query with the just-created sum query.
    Create a report in the wizard based on this new "compound" query (my term - not official OO).
    Open the report in edit mode and place one formatted field control per summed field
    Right-mouse click on one formatted field control and choose "Form" from the pop-up
    Change the data source of the form from "SQL Command" to "Query" in the Content Type field of Form Properties
    In the Content field, select the name of your compond query.
    In the Properties dialog for each your formatted fields, set the Data Field to the required summed field
I built a purchase ledger report this way. I've not got much data so I'll have to see how it stands up to more data.
Post Reply