OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How2 add a sum field in Report Builder [SOLVED]

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
InfoSysArchi
General User
General User


Joined: 17 Oct 2007
Posts: 19

PostPosted: Thu Dec 06, 2007 8:38 am    Post subject: How2 add a sum field in Report Builder [SOLVED] Reply with quote

Hi,

There are many occurence of the question below, but I haven't seen any reply that works. So here it is again, in the hope it'll get more successful.

I used to be a former MS Access application developer, and am developing a Base application on OOo 2.3 french version.
[Please note, in this post, some french OOo names are translated to english, such as DataSource, Add-in, etc. They may not reflect the exact english wording, sorry about it.]

This post relates to Sun Report Builder, not the built-in report Wizard.

The purpose is to create a list from a table, and display a sub-total. Say
- a list of purchases : product, productType, purchaseAmount
--> display the total amount purchased by product type.

I performed the following operations :
1 Add the fields [product] , [purchaseAmount] in the Details area
2. Create a group on the [productType] field
3. Create a fonction in the Report Explorer - Say fnTotalByType - Tried different formulas to sum the [Amount] field
4. Add a field in the Group Footer, say fldTotalByType
5. Assign fnTotalByType to the DataSource property of this field

But whatever formula is entered in the function, an error #522 (or other number) get displayed in the field, when the report is run.

So here are the questions :

1. At which level should the function in the explorer be created ? Report level, Group Level, or it doesn't matter ?

2. Assuming the field to sum in the Details section is [purchaseAmount], what should the exact syntax be for the function's formula ?

3. Has anyone a working example of a Base Report Builder including a subtotal ? If yes, would it be possible to get the .odb Base File sample ?

Please note, the purpose of this post is to understand if the native Report Builder functions work, and if yes how. Workarounds, like creating a query that returns the sum and assign it to the sum field data source, will be fine if no other way, but this is probably not the way the Report Builder is intended to work.

Thank you

Best Regards

Marc


Last edited by InfoSysArchi on Thu Dec 13, 2007 5:10 am; edited 1 time in total
Back to top
View user's profile Send private message
InfoSysArchi
General User
General User


Joined: 17 Oct 2007
Posts: 19

PostPosted: Sun Dec 09, 2007 10:41 am    Post subject: Reply with quote

Hi,

No reply ?

Does that mean that no one has ever been able to add a sub-total field on a Report Builder report ?

Or else, where to get more help ? Is there a Report Builder specific forum ?

Thanks for your help.

Best regards
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun Dec 09, 2007 11:09 am    Post subject: Reply with quote

Marc:

You asked:
Marc wrote:
Does that mean that no one has ever been able to add a sub-total field on a Report Builder report ?


Perhaps, these to links might help 'answer' your question.
  1. Functions and Syntax - http://wiki.services.openoffice.org/wiki/SUN_Report_Builder
  2. The Sun Report Builder supplies the following functions: http://wiki.services.openoffice.org/wiki/Base/Reports/Functions
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
InfoSysArchi
General User
General User


Joined: 17 Oct 2007
Posts: 19

PostPosted: Thu Dec 13, 2007 5:10 am    Post subject: Thank you - Wiki updated Reply with quote

Thank you.

I had seen the two links you mentioned, but had not been able to sort things out. The explanations where very light.

But eventually, I made it work. I added an illustration in the wiki page you mentioned, in the hope it will help others.

Thanks and best regards.

Marc
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Dec 13, 2007 8:44 am    Post subject: Reply with quote

Marc:

I saw the addition you made to the WIKI at: http://wiki.services.openoffice.org/wiki/SUN_Report_Builder

Good job, thank-you very much ( merci beaucoup ). That should be helpful for others

Sliderule
Back to top
View user's profile Send private message
InfoSysArchi
General User
General User


Joined: 17 Oct 2007
Posts: 19

PostPosted: Fri Dec 14, 2007 1:22 am    Post subject: Reply with quote

Sliderule wrote:
Good job, thank-you very much ( merci beaucoup ).

You welcome. Avec plaisir. Wink

Marc
Back to top
View user's profile Send private message
oldhawkeye
Newbie
Newbie


Joined: 14 Dec 2007
Posts: 3

PostPosted: Fri Dec 14, 2007 9:24 am    Post subject: Report Builder Sums Reply with quote

Thank you for the example in Wiki. It solved my problem with summing a column. I was very much on the right track, but what is not intuitive is that the name of the column to be summed goes in the Starting Value. To me it should be zero. Zero in that field misses the first record in each group. Is this a Report Builder bug?
Back to top
View user's profile Send private message
InfoSysArchi
General User
General User


Joined: 17 Oct 2007
Posts: 19

PostPosted: Sat Dec 15, 2007 1:06 pm    Post subject: Re: Report Builder Sums Reply with quote

oldhawkeye wrote:
Thank you for the example in Wiki.

You are welcome.

oldhawkeye wrote:
...what is not intuitive is that the name of the column to be summed goes in the Starting Value. To me it should be zero. Zero in that field misses the first record in each group. Is this a Report Builder bug?

One might argue that, as long as it is documented and works as documented, it is not a bug. Confused

But for me, what would really make things intuitive is to be able, as is the case with other report builders, as well as in SQL, to :

    - merely enter sum([fieldName]), min([fieldName]) and so forth as the data source (or function called in the data source) for the sum, min, etc fields in the group or report footers.
    - use report fields (and not only table fields) in the functions formula.

Maybe this should be posted as a suggestion for new versions of Report Builder, if not done yet ?

Best regards

Marc
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group