Base report grabd total

[jbyuriko]

Trying to make a report that has a total payment(a sum of fields )in the detail section. At the bottom of the report, would like to get grand total( sum of “ sum of fields) . Grand total can be done in the group footer or page footer. Attempt 1) Tried to make a field with a function. Whenever I try to choose “sum” or “accumulation function”, Base collapses. Attempt 2). Prepared the grand total in a new query. But I could not figure out how to refer two queries to create a report. So, both attempts did not work. Any suggestion or ooint out whT I am doing wrong will be helpful! Thank youJB

The image is like this
January 2018
Customer A. Total pay. $ xxxx
Customer B Total pay. $ xxxxx
Customer C. Total pay ＄xxxx

---

Gr@nd total payment. $ xxxxx

[Ratslinger]

Hello,

Totals are not too difficult. What may be more complex is as to why your Base “collapses” (probable a crash). You have not stated what version of LO you are using, where you got this from or the OS you are on.

For the totals look at my answer in this post → How to reuse an accumulated value in a Report?. You can create various detail totals, running totals and group or report totals.

Another post with a simple sample .odb → help with grand total.

EDIT 2018-04-30:

Thank you for the sample. This is always a plus for solving problems.

The best way to solve your problem (and probably the easiest/cleanest way) is by basing the report on a query. The query can calculate the extend amount and the calculation is not needed in the report builder. You can then accumulate this field for totals.

The query:

SELECT "PRODUCT ", "QUANTITY", "PRICE ", "QUANTITY" * "PRICE " AS "LINETTL" FROM "Table1"

A note here. You were not too careful in designing the table. Notice PRODUCT and PRICE fields contain a space at the end. This makes it extremely difficult to work with (and find)!

The attached contains a revised version of your report. Notice the final total is on a Report footer and not a Page footer. Page footer is typically for page numbering and such. Totals on a Page footer don’t make much sense since a group can encompass multiple pages.

Revision of your sample: RevisedReport.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[jbyuriko]

Thank you for your revised file. I simply used the accumulation function by group for the sub-total field and by report for the grand -total. It worked. I realized how a carelessness upfront of the procedures can cause the issue later on. The immediate problem was solved but I still do not know what to do to get a grand total when you cannot source all the fields from a single table or query. In other words, I do not know how to make a report based on multiple number of tables or queries.

[Ratslinger]

When using Report Builder, only one query or table is allowed as input. The exception to this is another is allowed to create a chart. So the key to using more than one table for information is the Query itself. Queries range from single line statements to a huge number in corporate situations. I personally have statements with a few dozen lines. The key here is SQL. This is where you combine info from different tables into just a single query which is then the input to reporting…

[Ratslinger]

If you are not too familiar with SQL you should learn more about it. It is a key in any database environment. Here is a site which may help → w3schools.com. Learning SQL is like learning a programming language. It takes some time especially for the more complex capabilities.

[jbyuriko]

Hi Raislinger,
Thank you for reference information to look up. For the crash issue, the system crashed every time I typed the open parenthesis, (, after a function such as sum, average, e.t.c. I had the LIbreoffice version 6.0.2 . Now I downloaded 6.0.3. The crash seemed stopped. Not only that, now I see an option of arguments to enter when I typed ‘sum’ in a formula field.

Nonetheless, I still cannot get a sub-total and grand-total calculations on Report. The attached is a sample file that I have been playing with. On the Sales Report, I would like to get ‘total sales’ as the sum of the sales by product, and ‘grand total sales’ as the grand total sales across products.
New Database.odb

If anyone can point out what I am doing wrong, I’d appreciate it very much!

Thank you

[Ratslinger]

Answers should be used for answer to original question only. Please use comment or modify original question with additional information using Edit.