[Solved] How to calculate a grand total?

[lachass]

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

[chrisb]

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: 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.

[lachass]

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

[Sliderule]

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 364 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.

[chrisb]

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".

[chrisb]

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.

[Villeroy]

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

[lachass]

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

[evetrins]

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

[Sliderule]

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