Sum a record field using base reports

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

Sum a record field using base reports

Postby RapidSuccessCoach » Thu Nov 22, 2007 7:42 pm

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 :)
RapidSuccessCoach
 
Posts: 3
Joined: Thu Nov 22, 2007 7:33 pm

Re: Sum a record field using base reports

Postby markt » Thu Nov 22, 2007 10:40 pm

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
markt
 
Posts: 5
Joined: Thu Nov 22, 2007 10:37 pm

Re: Sum a record field using base reports

Postby compuwatch » Sat Jan 12, 2008 1:44 am

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?
compuwatch
 
Posts: 24
Joined: Sat Jan 12, 2008 1:36 am

Re: Sum a record field using base reports

Postby DrewJensen » Sat Jan 12, 2008 7:12 am

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.
The Document Foundation member
Apache OpenOffice Incubator podling PPMC member
LibreOffice & OpenOffice.org on Ubuntu 11.04 + Windows XP - 7
User avatar
DrewJensen
 
Posts: 1733
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Sum a record field using base reports

Postby Hacker John » Sun Jan 13, 2008 9:51 am

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.
Hacker John
 
Posts: 25
Joined: Thu Dec 06, 2007 1:17 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest