Jump to content

SubSummary Sort Criteria?


Recommended Posts

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:

JAszRaK.png

 

Here's a sample of my table data:

aw0sOMX.png

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:

dkVQs9O.png

And here's how my sort criteria are set:

F0CuTNO.png

 

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!

 

Link to post
Share on other sites
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.

 

  • Like 1
Link to post
Share on other sites
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.

Link to post
Share on other sites
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. 

 

  • Like 1
Link to post
Share on other sites
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?

Link to post
Share on other sites
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.

 

  • Like 1
Link to post
Share on other sites
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?

Link to post
Share on other sites
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.

 

  • Thanks 1
Link to post
Share on other sites
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. 🙂

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.