Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Limiting the number of records in groups and sub-reports

Limiting the number of records in groups and sub-reports

Limiting the number of records in groups and sub-reports

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

RE: Limiting the number of records in groups and sub-reports

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


Hope this helps

Cheers, Pete

RE: Limiting the number of records in groups and sub-reports

Thank you Pete. This worked.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close