[Solved] Group data in report by month, and page break
Posted: Wed Nov 16, 2011 3:22 pm
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"