[Solved] Group data in report by month, and page break

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

[Solved] Group data in report by month, and page break

Post by qwertyjjj »

I have the following data from a SQL query:

Name Type Num Date Month Total
I need to split the report for each customer and group on the month.
This is a statement of all unpaid invoices grouped by month.
The report should also then give totals for each month and a grand total.

The initial SQL just brings in all the data, so how do I do the groupings by month while still listing individual invoice numbers and dates?
Also, I used the report wizard but it does something very odd with the grouping when I tell it to group by Month.

SELECT "Sheet1"."Customer" AS "Customer",
"Sheet1"."Type" AS "Type",
"Sheet1"."Num" AS "Num",
"Sheet1"."Date" AS "Date",
SUBSTRING ( "Date", 4, 2 ) AS "Month",
CONCAT( RIGHT( "Date", 4 ), CONCAT( '-', CONCAT( SUBSTRING ( "Date", 4, 2 ), CONCAT( '-', LEFT( "Date", 2 ) ) ) ) ) AS "ISO",
"Sheet1"."Total" AS "Total"
FROM "Sheet1" "Sheet1"
WHERE "Customer" <> ''
ORDER BY "Sheet1"."Customer", "Num"
Last edited by qwertyjjj on Tue Nov 22, 2011 3:33 pm, edited 2 times in total.
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: group data in report by month, and page break

Post by r4zoli »

Attachments
groups_month.odb
Grouping in report by month
(36.52 KiB) Downloaded 444 times
Last edited by r4zoli on Wed Nov 16, 2011 4:29 pm, edited 1 time in total.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: group data in report by month, and page break

Post by qwertyjjj »

The grouping by Month doesn't seem to work.
It prints 09 and 10 randomly:

Image

Just tried in design view.
How can I group this by month?

Image
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: group data in report by month, and page break

Post by r4zoli »

See my example file uploaded to the earlier post, set "Group on" property to Month.
Attachments
group.PNG
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: group data in report by month, and page break

Post by qwertyjjj »

r4zoli wrote:See my example file uploaded to the earlier post, set "Group on" property to Month.
Still doing something odd with the grouping. Confused. Even if I create a RealDate value in the excel spreadsheet using =DATEVALUE, I still cannot group on that by the Month in the report.
I have attached the table data.

Image

Image
Attachments
a.xls
(14.5 KiB) Downloaded 402 times
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Group data in report by month, and page break

Post by r4zoli »

Put your grouping field into "Month Header" part of report, in design view.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Group data in report by month, and page break

Post by qwertyjjj »

r4zoli wrote:Put your grouping field into "Month Header" part of report, in design view.
Still not working for some reason. It repeats for 8 pages like there is a duplicate grouping or inner join somewhere. ALso, it only shows 1 customer instead of all of them in the report.
New SQL:

Code: Select all

SELECT "Sheet1"."Customer" AS "Customer", "Sheet1"."Type" AS "Type", "Sheet1"."Num" AS "Num", "Sheet1"."Date" AS "Date", MONTH( "Sheet1"."RealDate" ) AS "Month", "Sheet1"."Total" AS "Total" FROM "Sheet1" "Sheet1" WHERE "Customer" <> '' ORDER BY "Sheet1"."Customer", "Num"
Image
Image
Image
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Group data in report by month, and page break

Post by chrisb »

Hello qwertyjjj.

Download the attached database to see how its done.

You have obviously thought through and are clear in what you wish to achieve.
You need to understand the use of Sorting & Grouping, Report Navigator & Function Building in order to create this report..

If you need help with the Navigator or functions CLICK HERE.

IF YOUR GOING TO CREATE ANYTHING BUT VERY BASIC REPORTS THEN ITS VITAL TO UNDERSTAND THE USE OF GROUPING.
WHAT FOLLOWS IS A BRIEF GUIDE WHICH GOES FROM THE BEGINNING UP TO AND INCLUDING THE GROUPING STAGE.

My table 'Sheet1' emulates yours and contains the fields:-
'ID' INTEGER primary key,
'Customer' VARCHAR_IGNORECASE 40,
'Type' , VARCHAR_IGNORECASE 5
'Num', VARCHAR_IGNORECASE 15
'Date', DATE
'Total' DECIMAL 12,2

All the data required in order to build this report is held in a single table and can be used as is.
No need for a query.
From the field 'Date' we can extract the day 7, Sat or Saturday. For the month 3, Mar or March.
All sorting/grouping will be done by Report Builder.

It's easier to create this report in Design View. We avoid clutter and add fields as necessary.
Under 'Database' hit 'Reports'
Under 'Tasks' hit 'Create report in Design View'.
Report Builder edit window opens.

menu:>Edit>Delete Page Header/Footer. These are the last things to do if at all.

All we have on view now is the 'Detail' pane. Click in the grey area below it.
If necessary hit 'F4' to show the 'Properties' pane.
Hit the 'Data' tab.
'Content type' = 'Table'
'Content' = 'Sheet1' (table name).
A small window named 'Add field: Sheet1' containing all our field names will open up.

SORTING & GROUPING:
qwertjjj wants two levels of grouping.
The outer group will be 'Customer' and the inner group (the month of the year) which will be extracted from the field 'Date'.

Hit (Ctrl+G) to open the 'Sorting and Grouping' window.
It will be blank with the properties blanked out.

CUSTOMER GROUP:
Hit the list box arrow below 'Field/Expression' and select 'Customer'.
A new pane will appear called 'Customer Header' and the properties in the 'Sorting and Grouping' window are now enabled.

'Sorting' = 'Ascending'

qwertjjj wants to see a grand total for each customer.
To display this total we must enable the 'Customer Footer'.
Hit the list box arrow to the right of 'Group Footer' and select 'Present'.
A new pane will appear called 'Customer Footer'.

'Group On' = 'Each Value'. We want to display each and every customer once.
'Keep Together' = 'Whole Group'.
This will ensure each customers details whenever possible will not be split over more than one page.

MONTH GROUP:
Click the list box below 'Customer'. Hit the arrow and select 'Date'.
A new pane will appear called 'Date Header'.
'Sorting' = 'Ascending'

qwertjjj wants to see a grand total for each month so we need a group footer.
'Group Footer' = 'Present'

Hit list box arrow to right of 'Group On'. You are presented with a list of options. Select 'Month'.
The field name is still 'Date' but the grouping is based on Month('Date'). The appearance of 'Date' is down to formatting.
That’s the 'Sorting and Grouping' done. Close the window.

You can now begin to insert your data.
Attachments
GroupDataByMonthInReport.odb
(55.56 KiB) Downloaded 452 times
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Group data in report by month, and page break

Post by qwertyjjj »

Excellent - thank you for all of that. I'd still like to know how to do it from a query but this should do for the moment.
Some slight problems in the attached pictures.
1/ I cannot seem to get a total by Month nor a Total by customer. I put in the SUM(Total) into the relevant sections but it seems to be blank. EDIT: FOUND user functions.
2/ Also, there seems to be an overall statement of account right at the top of the spreadsheet with blank rows - not sure why that is there EDIT: It seems the spreaqdsheet has 1 row of just totals. crap data. I thought of using a conditional expression on the customer name lnegth > 1 but it doesn't seem to work.

Image
Image
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Group data in report by month, and page break

Post by qwertyjjj »

seem to be some dodgy bugs in Base or the extension.
Very often I am saving, and then when I reopen the db, all the changes have been lost
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Group data in report by month, and page break

Post by qwertyjjj »

I put in

NOT([Customer]="")

in the conditional expression but it still lists a grouping at the top where the table has a blank row except for a total (it's crap data but I have to deal with it.

Also tried ([Customer]<>"")
User avatar
r4zoli
Volunteer
Posts: 2882
Joined: Mon Nov 19, 2007 8:23 pm
Location: Budapest, Hungary

Re: Group data in report by month, and page break

Post by r4zoli »

Your screenshot shows me that you use in "Group on" property: "Each value", this setting will print each value separated, This needs to be set to "Month" as in my sreenshot I proposed.

The MONTH function gives numbers and the field not formatted correctly, possibly set as Date.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Group data in report by month, and page break

Post by qwertyjjj »

r4zoli wrote:Your screenshot shows me that you use in "Group on" property: "Each value", this setting will print each value separated, This needs to be set to "Month" as in my sreenshot I proposed.

The MONTH function gives numbers and the field not formatted correctly, possibly set as Date.
I changed those - thanks.
ANy ideas how I can remove a blank row from the report? I could fix it in the spreadsheet link with a macro but it's always going to come back.
I tried this but it's not working:

NOT([Customer]="")

Also tried ([Customer]<>"")
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Group data in report by month, and page break

Post by chrisb »

I uploaded the attachment in the hope that you would look and learn from it.

In Report Builder 'sum()' does not work.
In order to sum the field 'Total' you need to define an accumulation function.
One function provides one result in one group.
When we defined our groups we activated three group footers, why? in order to display the results of three accumulation functions.

Below is a clip of the Report Navigator.
It shows where the function is created and where the matching result is displayed.
NavFunctions.PNG
If you select the function then its properties will be displayed in the Properties Pane.
ReportFooterFunc.PNG
ReportFooterFunc.PNG (13.52 KiB) Viewed 25576 times
You do not need the field 'Month' anywhere in your report or query.
We use only one date field the field on which the group is based in your case that appears to be 'RealDate'.
Therefore select the text box which contains the field 'Month' go to the properties pane hit the 'Data' tab and next to 'Data Field' select 'Real Date'.

We now need to format this text box.
Hit the 'General' tab.
Hit the '….' button to the right of 'Formatting.
In the 'Category' list box select 'Date' and choose a format from the 'Format' list box.

I defined my own format by:-
click in the text box below 'Format code'.
Hold down (Ctrl+A).
Hit the delete key.
Type MMM YYYY.
Hit 'OK'.
This gives a 'three letter month' a space then a four digit year.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Group data in report by month, and page break

Post by chrisb »

 Edit: 19/11/2011 18:32 SORRY!!! PLEASE DISREGARD THIS POST.
Having re-read the previous posts I now realise that I missed the following 'EDIT':-
qwertyjjj said
EDIT: It seems the spreaqdsheet has 1 row of just totals. crap data.
 
qwertyjjj said
ANy ideas how I can remove a blank row from the report? I could fix it in the spreadsheet link with a macro but it's always going to come back.
change the where clause in the query to :-

Code: Select all

where "Customer" is not null
Last edited by chrisb on Sat Nov 19, 2011 8:34 pm, edited 1 time in total.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
qwertyjjj
Posts: 67
Joined: Sun Jun 20, 2010 4:57 am

Re: Group data in report by month, and page break

Post by qwertyjjj »

chrisb wrote:I uploaded the attachment in the hope that you would look and learn from it.
I added an edit to my original; question.
EDIT: FOUND user functions
Thanks.
chrisb wrote:qwertyjjj said
ANy ideas how I can remove a blank row from the report? I could fix it in the spreadsheet link with a macro but it's always going to come back.
change the where clause in the query to :-

Code: Select all

where "Customer" is not null
there is no query, it is loading data directly from the table.
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Group data in report by month, and page break

Post by chrisb »

Hello qwertyjjj,
I’ve overwritten what was previously contained in this post. It was rubbish.

What follows will hopefully redress the problem of blank lines.

Code: Select all

select "Customer", "Type", "Num", "Date", "Total" from "Sheet1" where "Customer" is not null
1. Copy the code above.
2. Open report for edit.
3. Click in the grey area near bottom of window.
4. Hit 'F4' to show properties pane. Report properties will now be visible.
5. Place the mouse over left edge of properties pane and widen the pane by dragging it over to the left.
6. Hit 'Data' tab.
5. Hit arrow to right of 'Content type' and select 'SQL command'.
6. Click in text box to right of 'Content' then press (Ctrl+V) to paste the code.
The field names are those provided by you in your initial post.
If you have since changed them then edit the select clause to reflect those changes as necessary.
7. Hit 'Enter'.
8. Execute report.

If this works then please let me know by editing the title in your original post to add [SOLVED].
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
Post Reply