Summary of a sub-summary?

[Darren Emery]

Every once in a while I come up against an issue that reminds me how little I really know about Filemaker.  Creating summary reports is often the issue.

I think what I want to do is possible, but I'm stumped.

We track violations by address. Every time a violation is found at an address, and new record is created.   I would like to create a report that tells us how many times an address is cited once, twice, three times, etc. in a given time frame.

I can pretty easily create the summary report that groups by address, and then tells me how many times that address is cited.  I need to take it one further step. 

In a given year, we might have 2k violations.  The vast majority of these are one time per address.  The report I'm trying to generate would tell me:

Number of single violations per address:  1,937

Number 2x violations per address:  47

Number of 3x violations per address: 14

etc.

This seems like a summary of a summary.  Is this possible?

 

[eos]

I don't think you can do that with just a summary field; you need either a recursive field/function, or – the easier variant – a script.

Unless you already have it (its pretty useful to have!), create a summary field that counts a guaranteed non-empty field (best choice: the table's primary key).

Then use this script:

Sort Records [ Specified Sort Order: Violations::addresss; ascending; reorder based on summary field: Violations::sCountAll ]
Go to Record/Request/Page [ First ]
Loop
  Set Variable [ $groupSize; Value:GetSummary ( Violations::sCountAll ; Violations::addresss ) ] 
  Set Variable [ $nextStopAt; Value:Get ( RecordNumber ) + $groupSize ]
  Set Variable [ $newLine; Value:$groupSize ≠ $prevGroupSize ]
  Set Variable [ $counter; Value:$counter * not $newLine + 1 ]
  Set Variable [ $resultLine; Value:$groupSize & ": " & $counter ]
  Set Variable [ $resultList; Value:
    Case (
      $newLine ;
      List ( $resultList ; $resultLine ) ;
      LeftValues ( $resultList ; ValueCount ( $resultlist ) - 1 ) & $resultLine
    )
  ]
  Exit Loop If [ $nextStopAt > Get ( FoundCount ) ]
  Set Variable [ $prevGroupSize; Value:$groupSize ]
  Go to Record/Request/Page [ $nextStopAt ] [ No dialog ]
End Loop

At that point, the figures are in $resultList

[dwdata]

Also, you can compile the data for this report into a temp table with the following fields:

Address
Violation_Qty

Script the report like the following:
- Reset Temp Table (Show All Records and DELETE ALL Records)

- Export your Incidents Summarized by Address (include a QTY field) to CSV file maybe to the TEMP Directory.

- Import the exported file back into your TEMP TABLE.

- After data is imported back into the TEMP TABLE, you should have all the entities to design a Summary Report by "Violation_Qty".

See attached file.

Hope this helps!

dwdc_SumSums.zip