[Solved] How to calculate a grand total?

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

[Solved] How to calculate a grand total?

Postby lachass » Fri Feb 13, 2015 7:46 pm

I have calculated a total for every client by multiplying the quantities with the price of the different items in a query (subtotal). After that I have accumulated all the items in a report which gives me a total per night. Then I have multiplied that with the total of nights a person stays at our place which gives me a total for the invoice.
Now I want to add all those totals ([Accumulationsubtotalclient ID]*[number of nights]) to make a grand total of all clients put together.
Is there a way to do that?

Regards,
Lachass
Last edited by lachass on Mon Feb 16, 2015 8:31 pm, edited 1 time in total.
OpenOffice 4.0.0 on windows 8.1
lachass
 
Posts: 16
Joined: Wed Jan 28, 2015 5:02 pm

Re: How to calculate a grand total?

Postby chrisb » Sat Feb 14, 2015 4:21 am

Hello lachass,

you are not providing sufficient detail to enable others to help you,
this post is obviously related to your previous topic '[Solved] How to calculate with a calculated field?' posted to this forum on Wed Feb 11, 2015 4:56 pm.

based on the previous topic i will assume that we have 5 fields:-
'CUSTOMER' varchar, 'AMOUNT' decimal, 'TAX' decimal, 'DAYS' integer, & 'ADVANCE' decimal.

I further assume that the question is “i use report builder. my query contains the above fields. how can i show the sum total for ('AMOUNT' + 'TAX') * 'DAYS' – 'ADVANCE'?”

the answer is YOU NEED TO DEFINE A FUNCTION.
this post shows how: https://forum.openoffice.org/en/forum/viewtopic.php?p=210650#p210650

follow the steps 1 to 12 with these exceptions.
2. our function needs to access the entire record set so we create it under 'Report'
6. Name = SumCalculatedField.
7. Formula = [SumCalculatedField]+([AMOUNT]+[TAX])*[DAYS]-[ADVANCE].
8. Initial value = ([AMOUNT]+[TAX])*[DAYS]-[ADVANCE].
9. Drag out a text box in the report footer.
12. Function = [SumCalculatedField].

I hope this helps. it may not be exactly what you wanted but should point you in the right direction.
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 148
Joined: Mon Jun 07, 2010 4:16 pm

Re: How to calculate a grand total?

Postby lachass » Sat Feb 14, 2015 1:23 pm

Hello chrisb,

Thank you for your help.
I have tried all the steps for my fields, but it doesn't seem to work.
I will attach my database so you can see the problem. The language is Dutch for the fields and French for the labels. It concerns the report "tva uitreksel". The field with the label 'total' needs to be accumulated. And after that the field 'total TVA' needs to be accumulated as well.

Thanks in advance for your patience.
Cheers,
Lachass
Attachments
lachass tva uitreksel 2.odb
(61.47 KiB) Downloaded 127 times
OpenOffice 4.0.0 on windows 8.1
lachass
 
Posts: 16
Joined: Wed Jan 28, 2015 5:02 pm

Re: How to calculate a grand total?

Postby Sliderule » Sun Feb 15, 2015 3:20 am

Please look at the attached file, and, tell me/us what you think, that is, does it meet your requirements?

lachass tva uitreksel 2.odb
(100.69 KiB) Downloaded 134 times

Explanation: What I did was:
  1. Changed the SQL Query you used as the source of the data in the Report . . . please look at Query by the name of: tva uitreksel_02 . The reason . . . the Query performs some additional calculations, so, the Report Writer does not have to.

  2. New report is named: tva uitreksel_02

  3. Removed use of GROUP and functions . . . since . . . they are performed more efficiently in the Query, in the Detail line. :)

  4. Added two ACCUMULATION functions . . . to SUM the needed values in the new Page Footer, according to what you asked for.
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Last edited by Sliderule on Sun Feb 15, 2015 7:08 pm, edited 2 times in total.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: How to calculate a grand total?

Postby chrisb » Sun Feb 15, 2015 6:13 am

Hello lachass,

i am just about to make a further post & see that Sliderule has contributed to this topic. i have not downloaded his example but suspect it is far better than mine.
having done the work i will post anyway.

please look at the attachment 'lachass tva uitreksel_chrisb.odb'

i have made some changes to your query 'tva uitreksel' on which the report is based.
two queries have been added,
'qTVA_UITREKSEL_Direct_SQL' saved as direct SQL to preserve the formatting .i.e. make it easily readable. the report will not run from this query.
'qTVA_UITREKSEL_For_Report' exactly the same functionally but can be edited in design view & is used to generate the report.
no calculations have been made & no calculated fields have been used. no sorting has been made .i.e.(order by).

because this forum is for english speaking users who may also find this topic useful i have used aliases to make the fields more descriptive.
only the fields which are necessary to enable the report to function have been selected.

the report itself has been written from scratch. i have called it 'rbTVA_UITREKSEL_2'.
only one function has been created an accumulation function named 'SumActualCost' which is located in the group 'Arrival'.
the formula is:- [CostPerNight]*[Customers]*[NumberOfNights].
the tax rate is 10% so where necessary this is simply calculated (amount*.1)
you will notice that there are blank areas at the bottom of some pages that is because the groups have been kept together in order to aid readability.

click on the text boxes in the footer of group 'Arrival' & hit the data tab in properties to view the functions/formulas or view same using the navigator.

finally the report is sorted first by 'Name' then 'Arrival'.
the names are stored in your table as 'John Doe' that is christian name followed by surname.
you might find it better to use 2 fields & store as 'FirstName' & 'LastName'. this along with sorting aids retrieval.
it is simple to concatenate first & last names when desired ("FirstName" || ' ' || "LastName".
Attachments
lachass tva uitreksel_chrisb.odb
(101.43 KiB) Downloaded 96 times
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 148
Joined: Mon Jun 07, 2010 4:16 pm

Re: How to calculate a grand total?

Postby chrisb » Sun Feb 15, 2015 3:42 pm

hello lachass,

i realised early this morning after reading the post by Sliderule that i had not properly answered your question.
i have now included a summary of gross values in the report footer.
by the way if you want to view the report in french or dutch all you need to do is edit the labels.
Attachments
lachass tva uitreksel_chrisb_with_gross_values.odb
(111.1 KiB) Downloaded 111 times
Last edited by chrisb on Mon Feb 16, 2015 2:32 am, edited 1 time in total.
open office 4.1.4 & LibreOffice 5.4.2.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.0 (Split) on Windows 10
chrisb
 
Posts: 148
Joined: Mon Jun 07, 2010 4:16 pm

Re: How to calculate a grand total?

Postby Villeroy » Sun Feb 15, 2015 4:03 pm

I would use a Calc pivot table as "report engine" which provides simple and efficient ways to group and aggregate record sets from plain sheets and from all kinds of data sources. You get a grand total for free and you get a non-static report. You can sort/filter/rearrange the report directly on the sheet.
Register your database and create a simple SELECT ... FROM ... JOIN ... WHERE so you have all wanted data in a flat unsorted record set.
Calc menu:Data>Pivot>Create...
[X] From registered data source, specify the source and the query
Start with a first simple layout where you drag fields to be grouped into the [Row Fields] box and all numeric fields to be aggregated (sum, count, average, etc) into the [Data Fields] box.

http://wiki.services.openoffice.org/wik ... /DataPilot
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24592
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] How to calculate a grand total?

Postby lachass » Tue Feb 17, 2015 12:25 pm

Hello Everybody,

Thank you for your responses.
In the end I have used the solution of Sliderule (thanks!) for my purpose. I have changed the query with a GROUP and a SUM. After that I could accumulate the grand total in the report.
Thanks Villeroy and chrisb for your help too. It was a bit advanced for a novice like me, but it also helped.

Cheers,
lachass
OpenOffice 4.0.0 on windows 8.1
lachass
 
Posts: 16
Joined: Wed Jan 28, 2015 5:02 pm

Re: [Solved] How to calculate a grand total?

Postby evetrins » Mon Feb 22, 2016 12:43 pm

Hi,

The solution is not working in my case.
I have to sum calculated fields which are based themselves on the group subtotals:
AtlBD: =IF([fABD]>[fABK];[fABD]-[fABK];0)
AtlBK: =IF([fABK]>[fABD];[fABK]-[fABD];0)
where
fABD, fABK are accumulated values
So, i cannot "open the brackets" and do grant total calculation right from the query fields, i need to sum calculated group subtotals.
Is it doable?

Regards,
Edgar
OpenOffice 4.1.2 on Windows7 with Oracle Report Builder 1.2.1
evetrins
 
Posts: 1
Joined: Mon Feb 22, 2016 12:04 pm

Re: [Solved] How to calculate a grand total?

Postby Sliderule » Mon Feb 22, 2016 5:06 pm

Edgar:

I have no idea what you are talking about in your post above, therefore, I cannot help. Perhaps, if you start a new topic, since, this one is solved, and, you should both reference this post, and, include your database, maybe then, perhaps someone in the forum could help.

Sliderule
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 3 guests