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 !
[Solved] Grouping by a field and then subtotaling each group
[Solved] Grouping by a field and then subtotaling each group
Last edited by manderson on Tue Sep 05, 2017 4:59 pm, edited 1 time in total.
OpenOffice 4.1.2 on Windows 10
Re: Grouping by a field and then subtotaling each group
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.
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.
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.
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.
Re: Grouping by a field and then subtotaling each group
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:
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.
A query may look like this in SQL view:
Code: Select all
SELECT YEAR("Date") AS "Year", MONTH("Date") AS "Month, "Category", SUM("Value") AS "Sum"
FROM "Some Table"
GROUP BY YEAR("Date"), MONTH("Date"), "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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Grouping by a field and then subtotaling each group
Thanks for your help! That works just the way I want it to!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.
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.
OpenOffice 4.1.2 on Windows 10