You just need a new sub summary report with some new fields.
a) cExpenseMonth-calculation field=MonthName(ExpenseDateField)
b) sCountExpenses-summary field=Count(PrimaryKeyFromYourTable)
c) gYear-global text field. You can either tie this into a value list of years, and let the user pick from a drop down, or let the user type in a year(I'd rather pick from dropdown)
On the new layout, based on clientsExpenses
1. create a sub summary part sorted by cExpenseMonth (above). Put the cExpenseMonth field and the sCountExpenses (totals by month) field in this part (usually as merge fields)
2. In the body part, put the clients name and any other detailed info
3. In the footer, put another instance of sCountExpenses (totals for the year)
4. In the header put gYear in the header, dropdown. Attach an OnObjectExit script trigger with the following script parameter:
Table::ExpenseDateField //you'll have to pick this from your database
and the following script:
Set Error Capture [On]
Enter Find Mode [ ] //uncheck pause
Set Field [Table::ExpenseDateField; Get(ScriptParameter)]
Perform Find [ ] //make sure this is cleared out
Sort Records [ ] //sort by cExpenseMonth (based on value list-create a value list with the Month Names in chronological order), then sort by client name, and any other ways you want to sort
Go to Record/Request/Page [first]
Thanks Steve I will definitely try this and get back to you.
For cExpensesMonth, I would define it as:
ExpenseDateField - Day (ExpenseDateField) + 1 //calculates the date of the first day of the same month, year
and select "date" as the result type. When I sort on this value I'll get January, not April as the first group of expenses and I can put this field field in my sub summary part, but formatted to only display the month name to show the name of the month for each year. This also allows you to use a report that might not start with January and end with December and you'll still get your groups of records in the correct chronological order.
I was wondering why that happened, hence making the value list to sort by...thanks Phil....