[Solved] Group data in report by month, and page break
[Solved] Group data in report by month, and page break
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"
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.
Re: group data in report by month, and page break
Use report builder: http://extensions.services.openoffice.o ... portdesign
- Attachments
-
- groups_month.odb
- Grouping in report by month
- (36.52 KiB) Downloaded 506 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
Hungarian forum co-admin
Re: group data in report by month, and page break
The grouping by Month doesn't seem to work.r4zoli wrote:Use report builder: http://extensions.services.openoffice.o ... portdesign
It prints 09 and 10 randomly:
![Image](http://img854.imageshack.us/img854/8591/sp3220111116141335.jpg)
Just tried in design view.
How can I group this by month?
![Image](http://img406.imageshack.us/img406/430/sp3220111116143109.jpg)
Re: group data in report by month, and page break
See my example file uploaded to the earlier post, set "Group on" property to Month.
AOO 4.0 and LibO 4 on Win 8
Hungarian forum co-admin
Hungarian forum co-admin
Re: group data in report by month, and page break
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.r4zoli wrote:See my example file uploaded to the earlier post, set "Group on" property to Month.
I have attached the table data.
![Image](http://img405.imageshack.us/img405/3724/sp3220111117101937.jpg)
![Image](http://img17.imageshack.us/img17/848/sp3220111117095925.jpg)
- Attachments
-
- a.xls
- (14.5 KiB) Downloaded 463 times
Re: Group data in report by month, and page break
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
Hungarian forum co-admin
Re: Group data in report by month, and page break
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.r4zoli wrote:Put your grouping field into "Month Header" part of report, in design view.
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](http://img805.imageshack.us/img805/8171/sp3220111117115724.jpg)
![Image](http://img713.imageshack.us/img713/885/sp3220111117115717.jpg)
![Image](http://img214.imageshack.us/img214/2575/sp3220111117120650.jpg)
Re: Group data in report by month, and page break
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.
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 517 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
Re: Group data in report by month, and page break
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](http://img15.imageshack.us/img15/8268/sp3220111117135452.jpg)
![Image](http://img812.imageshack.us/img812/3622/sp3220111117140327.jpg)
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](http://img15.imageshack.us/img15/8268/sp3220111117135452.jpg)
![Image](http://img812.imageshack.us/img812/3622/sp3220111117140327.jpg)
Re: Group data in report by month, and page break
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
Very often I am saving, and then when I reopen the db, all the changes have been lost
Re: Group data in report by month, and page break
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]<>"")
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]<>"")
Re: Group data in report by month, and page break
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.
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
Hungarian forum co-admin
Re: Group data in report by month, and page break
I changed those - thanks.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.
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]<>"")
Re: Group data in report by month, and page break
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. If you select the function then its properties will be displayed in the Properties Pane. 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.
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. If you select the function then its properties will be displayed in the Properties Pane. 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
Re: Group data in report by month, and page break
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':-
|
change the where clause in the query to :-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.
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
Re: Group data in report by month, and page break
I added an edit to my original; question.chrisb wrote:I uploaded the attachment in the hope that you would look and learn from it.
EDIT: FOUND user functions
Thanks.
there is no query, it is loading data directly from the table.chrisb wrote:qwertyjjj saidchange the where clause in the query to :-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.Code: Select all
where "Customer" is not null
Re: Group data in report by month, and page break
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.
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].
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
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