Group report by months

[qwertyjjj]

I am trying to run a report called AllStatementsNew from this data.
It used to separate the data by month but has stopped working due to a computer crash and some of the macros being lost in the excel file so I think it is a date formatting problem but no matter what I change in the source excel file, I cannot get the BASE report to separate the data by month again.
It prints August in the top group but then puts all August and July invoices into the same group.
Any ideas what I should change?

dates.PNG (12.58 KiB) Viewed 5093 times

[Nick N,]

Pls see attachments.

Regards

[qwertyjjj]

It is already in date order in my odb showing July first.
The grouping should work without that anyway no since it groups by Month name.

The report is supposed to show a header with July and all the invoices, then a header with August and all the August invoices and then any other months if they were there.
But what it does is show a header with August and then all invoices with no grouping or separation.

Edit: I just tried ordering Sheet1 in the tables both directions and the report shows the same result no matter which way it is ordered.

[Nick N,]

Very sorry but couldn't find your topic in the list. Ah, ok, here it is...
Couldn't anyway fully open your odb. That's why i builded a small odb draft.

Check your month format as criterion or in the sql statement.

Regards

Nick

[qwertyjjj]

Is text currently. Shouldnèt it group on the text?

[Villeroy]

A pivot table does this for spreadsheet data: https://ask.libreoffice.org/uploads/sho ... 4i3NPb.ods
First, create the pivot table with the dates as row field.
Then click any date in the pivot table and call menu:Data>Group>Group (F12) and group by month and year.

[Nick N,]

Thanks for having helped.

[chrisb]

@qwertyjjj,

i created a fresh attachment because i needed to know that my report would handle multiple customers and multiple years.

your report considers the months but ignores the YEARS which results in garbage whenever the data spans more than a single year.

you have images in the page header.
for me using images at the top of every page is a none starter (unless absolutely essential) because it interrupts the data flow.
instead you could use a report header which would display images once at the head of the report.

take a look at the attachment first and see how you feel about it.
if you drag and drop the report into your db it should work.

ReportGroupingDemo.odb

(54.61 KiB) Downloaded 204 times

ReportGroupingDemo.ods

(21.99 KiB) Downloaded 199 times

[qwertyjjj]

A pivot table does this for spreadsheet data: https://ask.libreoffice.org/uploads/sho ... 4i3NPb.ods
First, create the pivot table with the dates as row field.
Then click any date in the pivot table and call menu:Data>Group>Group (F12) and group by month and year.

Unfortunately, it needs to be in the formatted report I attached not a pivot table

[qwertyjjj]

@qwertyjjj,

i created a fresh attachment because i needed to know that my report would handle multiple customers and multiple years.

your report considers the months but ignores the YEARS which results in garbage whenever the data spans more than a single year.

you have images in the page header.
for me using images at the top of every page is a none starter (unless absolutely essential) because it interrupts the data flow.
instead you could use a report header which would display images once at the head of the report.

take a look at the attachment first and see how you feel about it.
if you drag and drop the report into your db it should work.
ReportGroupingDemo.odb
ReportGroupingDemo.ods

yes but the data doesn't span more than a year currently.
I guess my question is why ios the grouping I have set in the report not split the data. That's the whole point of grouping

[chrisb]

i first wish to advise users that qwertyjjj uses libreoffice and libreoffice reports do not work with openoffice.
secondly, all of the reports in all of the above attachments are not fit for purpose because sorting accurately by month requires the month number and not the month name.

@qwertyjjj,
using your database(odb) and spreadsheet(xls) i am unable to accurately sort your report in alphabetical order using the text field "RealDate" which contains one of two values 'July' and 'August'.

using my database(odb) and your spreadsheet(xls) i can accurately sort your report using the text field "RealDate".
it's likely that your report is corrupted.
i never use the report wizard, all of my reports are built from scratch in design mode.

i upload 'Requires_Unpaid.xls.odb'
drop it into the same folder as your spreadsheet 'Unpaid.xls' then open it.

Requires_Unpaid.xls.odb

(54.8 KiB) Downloaded 194 times

i also upload 'GroupingDemo_odb.odb' and 'GroupingDemo_odb.ods'
drop them both into the same folder and then open open the odb.

GroupingDemo_odb.odb

(54.65 KiB) Downloaded 192 times

GroupingDemo_odb.ods

(25.78 KiB) Downloaded 194 times

both reports are sourced by queries because we need to extract the month number from the date field.
i used libreoffice so these reports will NOT work with open office.

if you want to prove to yourself that ORDER BY TEXT_FIELD is not buggy then simply create another ODB preferably using design mode.