[Solved] Grouping by a field and then subtotaling each group

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

[Solved] Grouping by a field and then subtotaling each group

Postby manderson » Tue Sep 05, 2017 12:03 am

Hi all,

I'm a bit of a novice at this kind of thing, so sorry if there is a really obvious solution, but i'd appreciate any help.

I currently have a database with 4 fields (Date, Expense, Cost and Code). The 'Code' field is where I input a category for the item I'm putting into the database (e.g if I was logging a train ticket I would use the code 'TT'. I would like to create a report which groups all of the items with the same code together, then shows a subtotal at the bottom of each group (this would be the sum of all of the items in the 'Cost' field) I'd also then like a total to be displayed at the bottom of the report which would be a sum of everything in the 'Cost' field.

This is so that I could print a report of all of my expenses with different categories for each type of expense I'm claiming, so I could easily see how much I've spent in each category.

Thanks again for any help :D !
Last edited by manderson on Tue Sep 05, 2017 4:59 pm, edited 1 time in total.
OpenOffice 4.1.2 on Windows 10
manderson
 
Posts: 2
Joined: Mon Sep 04, 2017 11:50 pm

Re: Grouping by a field and then subtotaling each group

Postby Sliderule » Tue Sep 05, 2017 3:31 pm

manderson:

Welcome to the OpenOffice / LibreOffice Base forum.

Please go to the link below . . . to see a similar question and answer by Sliderule, including a demo database to show it can be accomplished.

If you have not already done so, please download the Base Extension, Oracle Report Builder ( ORB ).

Next, please download the sample Base database file ( *.odb ) from the link below. It demonstrates using functions within ORB to calculate sub-totals exactly as you describ. The sample will calculate totals for each country in a continent, and, also, for all countries. :bravo:

https://forum.openoffice.org/en/forum/viewtopic.php?f=42&t=70217&p=313938

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.
User avatar
Sliderule
Volunteer
 
Posts: 1099
Joined: Thu Nov 29, 2007 9:46 am

Re: Grouping by a field and then subtotaling each group

Postby Villeroy » Tue Sep 05, 2017 3:50 pm

Create a query which groups by one or more categories and sums up the numbers. Then create a report from that query.
A query may look like this in SQL view:
Code: Select all   Expand viewCollapse view
SELECT YEAR("Date") AS "Year", MONTH("Date") AS "Month, "Category", SUM("Value") AS "Sum"
FROM "Some Table"
GROUP BY YEAR("Date"), MONTH("Date"), "Category"

This shows the sum of values for every combination of year, date and category.

You can also open a spreadsheet and create a pivot table directly from the database table:
Data>Pivot Tables>Create...
Use the table of your registered database
Drag the category and the date to "Row Fields".
Drag the values to "Data Fields".
[OK]
Click any date cell and call Data>Group>Group... group by year and month.
You may also layout the categories as column fields which gives a nice cross table for charts.
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: Grouping by a field and then subtotaling each group

Postby manderson » Tue Sep 05, 2017 4:58 pm

Sliderule wrote:manderson:

Welcome to the OpenOffice / LibreOffice Base forum.

Please go to the link below . . . to see a similar question and answer by Sliderule, including a demo database to show it can be accomplished.

If you have not already done so, please download the Base Extension, Oracle Report Builder ( ORB ).

Next, please download the sample Base database file ( *.odb ) from the link below. It demonstrates using functions within ORB to calculate sub-totals exactly as you describ. The sample will calculate totals for each country in a continent, and, also, for all countries. :bravo:

https://forum.openoffice.org/en/forum/viewtopic.php?f=42&t=70217&p=313938

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.


Thanks for your help! That works just the way I want it to! :D
OpenOffice 4.1.2 on Windows 10
manderson
 
Posts: 2
Joined: Mon Sep 04, 2017 11:50 pm


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest