Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: For Loop or Not For Loop Post Reply Post New Topic
Author Message
JWaldmann
Newbie
Newbie


Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
Quote JWaldmann Replybullet Topic: For Loop or Not For Loop
    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!

Edited by JWaldmann - 13 Apr 2018 at 10:16am
IP IP Logged
Valert16
Groupie
Groupie
Avatar

Joined: 21 Mar 2016
Location: Spain
Online Status: Offline
Posts: 49
Quote Valert16 Replybullet 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.
IP IP Logged
JWaldmann
Newbie
Newbie


Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
Quote JWaldmann Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8945
Quote DBlank Replybullet Posted: 16 Apr 2018 at 2:57am
Did you do a group select criteria?
IP IP Logged
JWaldmann
Newbie
Newbie


Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
Quote JWaldmann Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8945
Quote DBlank Replybullet 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.
IP IP Logged
JWaldmann
Newbie
Newbie


Joined: 01 Dec 2016
Online Status: Offline
Posts: 13
Quote JWaldmann Replybullet Posted: 16 Apr 2018 at 7:20am
This worked great! Thank you!!

Also, as promised; you are a hero!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 8945
Quote DBlank Replybullet 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 :)
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.