Hello I am unsure how to do this or even if it is possible at all. I have a report that reports the Myeloma (Bone marrow cancer) subtype diagnoses corresponding to the year. I want the report to look like and print like this:

       Year        Myeloma        MGUS           Myeloma-LC

Ex.   2010          4                    4                         2

Total                 4                     4                          2

 

Now the subtype is in a field called diagnosis and there is a count= count(pk_dx) that counts the diagnosis. I attached a screenshot how the report looks currently. I just want the diagnoses as headers with the years going down vertically. Thanks for the help!

 

Google cross-tab report. It's not "built-in" to Filemaker, but it can be done.

This is possible only if the number of columns is known in advance. Filemaker is not flexible in the horizontal direction and will not add columns dynamically.

If you have (or can have) a value list of the diagnoses, you can define a repeating calculation field (result is Number) =

Let ( [
i = Get ( CalculationRepetitionNumber ) ; 
diagnoses = ValueListItems ( Get ( FileName ) ; "Diagnoses" )
] ;
Extend ( Diagnosis ) = GetValue ( diagnoses ; i ) 
)

Set the result to Number and the number of repetitions to the number of values in the "Diagnoses" value list. Then add a summary field = Total of the above calculation field, summarizing repetitions individually. Place this field in the sub-summary by Year part.

I will try this out! Thank you. 

Hello Comment,

I have done exactly what you said but my calculation is returning a 0. I changed the naming on the calculation as well to match the names of my fields and value list. Does it matter if I am using server and if there are hyphens for line spaces in my value list? Does the value list have to be based on a field rather than a custom one? Thank you

It's okay forget that, turned out I didnt check calculate as needed. It works perfectly thank you! Is it possible to aggregate the total of for example 3 repeats.

For example. I have the Dx:  Myeloma, Myeloma-LC, and Myeloma-NS in the headers and your calculation gets the total of each (which is great). I was wondering if there was a way to add each of these to get the total of these 3 diagnoses per year? Thanks again!

You need another summary field for that.

Is there a way to summarize like 3 of the repetitions similiar to above or is it done a different way. I know usually summary fields are based on a find and sort. 

You can use a calculation field to sum the repeating summary field, but it will not work for sub-summary values. Just use another summary field, counting the original values. This will work correctly for both sub-summaries and the grand total - and it's still just one more field.

Sorry Comment, I dont think you understand what I meant. My explanation wasn't that great. So in my value list that is summarized now using your method I have the following Diagnoses:

 

1. Amyloid
2. Solitary Plasmacytoma
3. MGUS
4. Smouldering Myeloma
5. Myeloma
6. Myeloma-LC
7. Myeloma-NS
8. Plasma Cell Leukemia
9. POEMS
10. W.M

The Number corresponding to the order in the value list. Now If you notice #'s 5, 6, 7 are all part of a big "Myeloma" grouping. I would like to have a field in the horizontal row that adds 4+5+6. Is this possible? Right now the summary calculation calculates all of the repetitions and depends on the repetition number. Is there a way to total repetitions 4,5 and 6 only into one group? Sorry for all of the questions 

 

 

I see. For the subsummary values, you could do =

Sum (
GetSummary ( sTotalCountR[5] ; Year ) ;
GetSummary ( sTotalCountR[6] ; Year ) ;
GetSummary ( sTotalCountR[7] ; Year )
)

where sTotalCountR is the repeating summary field. But this will not work for the grand total. If you need that too, then either add another calculation field =

Sum (
sTotalCountR[2] ;
sTotalCountR[3] ;
sTotalCountR[4]
)

or (preferably, IMHO) start with a calculation field =

Let (
diagnoses = ValueListItems ( Get ( FileName ) ; "Diagnoses" )
;
not IsEmpty ( FilterValues ( Category ; MiddleValues ( diagnoses ; 5 ; 3 ) ) )
)

and add a summary field to summarize (Total) it.

 

Comment, you are a genius! Thank you very much!