SubSummary Sort Criteria?

[RavingLuhn]

I've got another question about subsummary parts and how to ensure that they present data for reports as expected. My layout is based on the most atomic table for the data to be displayed, but I'm having some trouble getting sub-summaries to be grouped by month. What I expect to see is this:

• Year
  • January
    • Department List - numerical data
  • February
    • Department List - numerical data
  • ..etc. for each month of the year

What ends up happening is that my data is grouped by each year, then one seemingly random month is shown, but the departmental breakdown shows as intended. This is what I actually see:

 

Here's a sample of my table data:

My sub summary parts do coincide with the fields contained. The first part is set for year, the second for month, the third for sub department. This is the layout for the report:

And here's how my sort criteria are set:

 

So I guess I have two questions:

1. How do I know when I have to sort by fields on a layout versus fields in the table? In this instance, records will sort by year regardless of the source, but in order for the sub department sort to work I have to choose the field from the layout and not the source table.
2. Why is the report not creating sections for each month?

Thanks in advance!

 

[comment]

6 minutes ago, RavingLuhn said:

Why is the report not creating sections for each month?

Hard to tell from screenshots. What do you have selected as the calculation result type for the c_TaskMonth field?

Note that if you try to sort by month name, your months will be sorted in alphabetical order. If you want them to sort chronologically, use month numbers. Or - preferably, IMHO - calculate the date of the first day of the month (with the result type being Date). 

 

13 minutes ago, RavingLuhn said:

How do I know when I have to sort by fields on a layout versus fields in the table?

Sorting takes place at data level, so this question is rather meaningless. However, do note that the GetSummary function will not work with a break field coming from a related table.

 

[RavingLuhn]

34 minutes ago, comment said:

What do you have selected as the calculation result type for the c_TaskMonth field?

It had been set as text, since the field returns the month name. However, changing it to a number makes the report sort by month; albeit in alphabetical order.

Do you recommend I make another calculated field to return the first day of the month?

36 minutes ago, comment said:

Sorting takes place at data level, so this question is rather meaningless.

I just thought it was odd that there was an option; I hadn't noticed the capability to sort by fields on a layout before.

[comment]

4 minutes ago, RavingLuhn said:

t had been set as text, since the field returns the month name. However, changing it to a number makes the report sort by month; albeit in alphabetical order.

I am afraid that makes no sense to me. Why don't you post a simple demo file we can take apart.

 

6 minutes ago, RavingLuhn said:

I hadn't noticed the capability to sort by fields on a layout before.

The option refers to selecting a field, not to sorting. You can select a field for a variety of purposes - and for your convenience  Filemaker lets you select from fields on the layout or from fields in the table. Once you have made your selection, it doesn't matter anymore. 

 

[RavingLuhn]

13 minutes ago, comment said:

I am afraid that makes no sense to me. Why don't you post a simple demo file we can take apart.

Wait a minute; I misspoke. Originally I had a calculation field with the function 'MonthName' set to generate a numerical result. The report didn't function that way. Changing the calculation result to text solved the issue.

Do you recommend I make another calculated field to return the first day of the month?

[comment]

16 minutes ago, RavingLuhn said:

Changing the calculation result to text solved the issue.

It may have solved the issue of creating a sub-summary for each month - but it created a new issue of months being sorted in alphabetical order.

You need to either calculate the month number (as a number) using the Month() function or the first day of the month (as a date). I usually prefer the latter, but if you are also sorting by the year, then it doesn't make much difference.

 

[RavingLuhn]

7 minutes ago, comment said:

It may have solved the issue of creating a sub-summary for each month - but it created a new issue of months being sorted in alphabetical order.

You need to either calculate the month number (as a number) using the Month() function or the first day of the month (as a date). I usually prefer the latter, but if you are also sorting by the year, then it doesn't make much difference.

Either way, I still need to have two fields? One for the month number and one for the text value?

[comment]

9 minutes ago, RavingLuhn said:

I still need to have two fields? One for the month number and one for the text value?

No. To display the month name, place the actual date field (or the calculation field returning the first day of the month) in the sub-summary part and format it to display only the month name.

 

[RavingLuhn]

15 minutes ago, comment said:

No. To display the month name, place the actual date field (or the calculation field returning the first day of the month) in the sub-summary part and format it to display only the month name

Wow, that's incredible! Thanks for sharing!

I appreciate your willingness to help people learn. 🙂