I'm trying to work out the best way to create a particular sales report.
I've attached a mockup of how I'd really like the report to look, if possible. (Please note, any duplicated data is just because it's a mockup).
As you can see from the mockup , the rows are grouped by month with each order listed within that month and a sum for the month. I'd like to have this year and last year side-by-side with a total for each year at the bottom. Does that make sense?
All the data comes from one table: ORDER(order_id*, order_date, client, ref, price). There are additional fields but I've removed them here to keep it simple
I have some experience in the past of creating reports but I have no idea how to do this one. I've managed to avoid pivot tables so far but would I need them for this?
Thanks for any advice!