• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Grouping / Record Selection Question for Crystal Reports XI

Hello Experts,

I am working on a Crystal Report (version XI) that queries an Oracle database and is grouped by the following fields:

Court Date (for each day)
Judge Name
Case Number
Charge Number

The report is run for a date range that covers several months, so case numbers appear on the report multiple times if there are multiple court dates/judges for a single case number within the report date range. I'm wondering if there is a way, while maintaining the current grouping, to display case numbers only once for the most recent court date.

Additionally...I don't know if this is relevant to my question above...but the report is only displaying cases in which specified fields have null values. This is accomplished by using the following formula:

numberVar Nulls;
if (isnull({PTYCHRG.DCSN_CD}) and isnull({PTYCHRG.DCSN_DT})
and isnull({PTCGDSP.DSP_DT}) and isnull({PTCGDSP.CG_DSP_CD}))
then Nulls := 0
else Nulls := 1

and then using the following group selection statement to suppress records:
Sum ({@Nulls}, {CASE.DSCR}) = 0

Thank you!!

Janice Smith
Janice Smith
2 Solutions
Check out the Oracle Analytic Functions. You could write a SQL Command looking something like:

select * from (
    select date
        , max(date) over (partition by judge_name, case_number, charge_number) as max_date
) where date=max_date
Are you looking to show the case only for the last activity?

Do you need the information from the rest of the case for summaries or other analysis?

Janice SmithSystems AnalystAuthor Commented:
I need the report to group by the court date and Judge for the last court date available in the database for the case.  I don't need any other court date and Judge information.
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Janice SmithSystems AnalystAuthor Commented:
The other charge information that I'm using in the report will be the same regardless of the court date and Judge.
Have you tried using the Group Select with  TOP 1 based on the date?

Janice SmithSystems AnalystAuthor Commented:
No, I have not tried that yet.  I'll see how that works.
You could try creating a SQL Expression, I'm not good on oracle syntax but it woudl be something like

select maximum(A.court_date)
from table A
where A.case_number= table.case_number

you can then add this to your record selection formula to filter the correct records i.e.

table.court_date = {%MaxCourtDate}

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now