Limiting the number of records in groups and sub-reports 1

[socalvelo]

Using CR 2016
SQL database
Trying to show 4 or more arrests per person.

Dataset is comprised of booking records of arrested individuals over a two year period throughout the whole county. There are over 1 million arrests per year in this county. Some people have 50 or more arrests. Most have 1 arrest.

We are reporting on the top 10% of repeat arrests. Each individual arrest has a booking number {BookingTable.booking_No}.
Group 1 - Precinct/Station
Group 2 - Top 10% Arrested persons that have been booked at that precinct. Using group sort expert, top percentage = 10%, includes ties. {BookingTable.PersonID}
This group has a sub report which lists the arrested persons, but lists arrests from every precinct. So for this reason it is grouped by {BookingTable.booking_No}. Additional fields are inserted on Group-1 and all other sections are suppressed. I don't think I want a conditional formula or running total on Group 1 or 2 because I want arrests from all precincts/stations to appear in the sub report.

Although I want the top 10% for each precinct, I only want persons with 4 or more arrests showing in the sub report. In other words, the precinct wants to know who is being booked the most in their district, but we also want to list this person's arrests county wide.

I tried setting up conditional formulas to suppress the sub reports with individuals with under 4 arrests but this did not work. distinctcount({BookingTable.booking_No})>3. I will still see persons with 3 or less arrests.
I tried using running totals but not familiar how to set these up to make the sub report suppress.


Thanks in advance

 

[pmax9999]

I am not sure I understand the data structure so it is a bit hard to be certain, but I think the use of a Group Selection formula is what you need.

In the sub-report, go to Report => Selection Formula => Group... and try entering:

[Code Group_Selection]
distinctcount({BookingTable.booking_No})>3
[/Code]

Hope this helps

Cheers, Pete

 

[socalvelo]

Thank you Pete. This worked.