Julian Johnson wrote:
If I made calculated fields to return Month and Year I could sort by those but the subsummary only shows when sorted by one field.
That's not entirely correct. You could sort your records by Year and by Month, and have the sub-summary show when records are sorted by Month.
However, there is a simpler way; use only one calculation field (result is Date) =
YourDatefield - Day ( YourDatefield ) + 1
This will work for the sort as well as for the sub-summary.
Thanks very much - nice elegant solution!
What 'comment' has advised you is correct.
I saw, however, no evidence that it was clear to you that it is possible to have many sub-summary parts... either/both above and below the body part. Nor was it clear to me whether you knew much of the power these SS Parts give you over a report... so here is my 2 cents worth.
You could eg. add a SS Part ...by Year ... and the part could be empty... but be set (in the Part Definition dialog) to start a new page or restart the page numbering or such.
You could then have another SS Part ... by Month Number ... that displayed the field values for MONTH YEAR. (Sorting by Month Number will order them correctly whereas Month Name will not)
The third SS Part might be for Date so after being sorted by year then month num the days fall in sequence.
Assuming you have more than one record per day there is one SS part left ... if you want a more versatile report. These records are often automatically relegated to the body part. This is a waste... because no matter how you sort it... the body always shows. For this reason I generally sort by recid or something else in the record that is unique to the record... after all the other sorting of Year MonthNo Date... It could be an ID or a Customer ID or anything that is always populated. ... and base the SS Part on it as a sort order item.
Then... delete the Body Part.
You now have a report that can be sorted by Year for Annual breakdowns, Year MonthNo for Monthly breakdows, Year MonthNo Date for Daily breakdowns or Year MonthNo Date ID for per-record breakdowns... which essentially allows you to hide or show the detail simply by sorting.
Splitting a report in this way also allows you to have Headings... using a Leading SS Part and SubTotals/Totals ... using Training SS Part.
It also allows you to compare the business across years... eg by sorting by MonthNo Year you will be able to compare April this year to multiple other Aprils.
You need to create subsummary parts for each field that you want to sort by. In this case you need subsum parts for month and year too
You need to create subsummary parts for each field that you want to sort by.
I don't think so.