What you describe is called a summary report. The exact details in how to set this up require knowing the design of the table or tables from which your data will be reported.
I'm going to assume that your report shows that there are 200 records in the "enrolled" category for the month of may and another 250 for the month of June. I will also assume that you have a date field that records the date the individual enrolled, dropped, cancelled or whatever.
Let's say you have these fields in a table called StatusChange:
StatusChange (text: Enrolled, dropped, cancelled, etc.)
cMonth (calculation that returns date: DateOfchange - Day ( DateOfChange ) + 1
sCount (Summary field, Count of DateofChange)
On a list view layout based on Satus change enter layoutmode:
Double click the part label for the body to bring up part setup.
Change the body into Sub Summary when sorted by cMonth.
Select Part setup from the layout menu and add a second sub summary part below the first: "When sorted by StatusChange".
Place cMonth or DateofChange in the upper sub summary part and use data formatting to set it up to only display the month name
Place StatusChange and sCount in the lower sub summary part.
Enter Browse mode: (Your layout will be blank)
Sort your records by cMonth and StatusChange to produce the report you've requested.
Does it have to be done by date of change? Is there a script that can go through each record and and sort it based on the month number? Also I dont understand how this would seperate the find into catagories like: enrolled, dropped, cancelled. Thanks so much for the reply!
It's done by cMonth, a calculation that returns a common value (the date of the first day of the month) so that all records from the same month and year can be grouped by sorting. You could use a calculation that returns the month number but that can create issues when you have records dated for the same month, but different years. Sorting by month number then will group January 2011 and January 2012 records together where sorting by cMonth will not group them together.
I dont understand how this would seperate the find into catagories
The sort order I suggested sorts the records by cMonth, then by statusChange. The first field groups, for example, all records with a January 2012 date into a single group. the second specified sort field then sorts these into groups by statusChange. The report only reports group sub totals. A summary field placed in the upper sub summary part will give you a total of all recors for that month and year. Placing the same field in the second sub summary part gives you a total for that statusChange category for the given month and year.
Okay my layout does have fields for an enrolled date, dropped date, cancelled date, re-enrolled date. It's not like a drop down list. When I search these things go to the records and go into find mode and put in the start date. This is how I'm getting my reports. I'm just not sure how to connect the table you are talking about to my own datatbase. I'm sorry I'm kind of a novice. Thanks for your help.
I would suggest that you change the design of your table so that one date field is used in all cases as this will make reporting easier. If you fully describe the current design of your table, I can suggest both short and long term "fixes" to get a better structure of your data.
Okay, here you can see everything that I use to find the stats. I use start date to find out when the record shows a start. Then a branch field to show where the record originates. I just can't find anyway around multiple searches to get a snapshot of the year on a month to month basis.
Define a calculation field as Max ( Enroll Date ; Start Date ; last Date Att ; Drop date ; ReEntry Date ; Grad Date )
Select "Date" as the return type for this calculation field.
Use this calculation field in place of the "Dateofchange" date that I originally suggested.
When you perform a find to pull up records for a specific range of dates specify the date range in this field.
Okay - When I try and do the Max calculation field ,with "Date" as the return type, it comes up with an error... it says there are more "("'s than ")"'s - There's not. Then FileMaker says it wants a *, -, /,ect..
Please copy and paste the exact expression you attempted into your next reply. Also, just to be safe, what version of FileMaker are you using?
I am using FileMaker Pro Advanced 11.04v
name your field something like "MaxDate". Define its calculation as:
Max ( Enrollment Date ; DropDate ; AUD101Startdate ; ReEntryDate )
Max is a function that returns the maximum value of all the fields listed in the parentheses.