For Loop or Not For Loop
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22575
Printed Date: 30 Apr 2024 at 1:38pm
Topic: For Loop or Not For Loop
Posted By: JWaldmann
Subject: For Loop or Not For Loop
Date Posted: 13 Apr 2018 at 8:52am
Database with clients who have dates associated with activities.
Client Date
1 1/1/9999
2 5/5/2017
2 1/1/9999
3 5/5/2016
3 10/5/2017
I need to exclude all records for clients who have any record with a date of 1/1/9999. I have tried to grab Max date to no avail. (My actual data has 9999 as the year.)
So, with the data above I would only return Client #3.
If anyone could help I will call you a hero!
|
Replies:
Posted By: Valert16
Date Posted: 15 Apr 2018 at 11:06pm
Group on Client.
Create a group summary of the Date Field. Use the Maximum function.
Any group that has a max = 01/01/9999 must be excluded, so add a group select condition like
Maximum ({Table.DateField}, {Table.Client}) <> DateValue(9999, 01, 01)
Hide group header and footer if needed.
|
Posted By: JWaldmann
Date Posted: 16 Apr 2018 at 2:08am
Thank you, so much, for the reply. I think it is great that there is a place with people so un-selfish with their knowledge that I can ask a question and have it answered!
I did as you suggested on my first attempt; however, if you notice that client #2 has a 9999 date AND a 2017 date. My report does exclude the Client 2 record with the 9999 date, but I still show the 2017 date record. I need to remove ALL clients that have EVER had a 9999 date.
I hope this makes sense.
|
Posted By: DBlank
Date Posted: 16 Apr 2018 at 2:57am
|
Did you do a group select criteria?
|
Posted By: JWaldmann
Date Posted: 16 Apr 2018 at 2:58am
|
Just last Friday, late. I am playing with that this morning. I seemed to have to put the statement in the Group header and footer.
|
Posted By: DBlank
Date Posted: 16 Apr 2018 at 3:17am
It has to be in the select expert.
The is a Record Selection or Group Selection option inside the select expert. You will be doing a group selection.
|
Posted By: JWaldmann
Date Posted: 16 Apr 2018 at 7:20am
This worked great! Thank you!!
Also, as promised; you are a hero!
|
Posted By: DBlank
Date Posted: 16 Apr 2018 at 7:49am
Please be careful of any other summary formulas you are doing in this report.
The summary values are going to be executed before the group select (hence the ability for the group select to happen). If you need to do other sums or counts you have to use running totals or variable formulas to exclude the values you are excluding via the group condition.
Good luck :)
|
|