Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Another Suppress Question Post Reply Post New Topic
<< Prev Page  of 5 Next >>
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Feb 2010 at 11:24am
I am not sure how you will be able to use a select statement to exclude the data but you should be able to get the values you want by using a variable formula or a Running Total (my preferred method) and comparing it against your full record set.
group on inmate (ID preferrable so you do not group 2 people with the same name)
Sort by {Audit_Trail.DateStamp}
Create a Running Total as "OutCount"
Field to summarize = Inmate ID
Type of Summary=Distinct Count
Evaluate=Use a formula
{Audit_Trail.FieldName} = Temporary_Out and {Audit_Trail.BeforeValue}=0 and {Audit_Trail.DateStamp}: 02/04/2010 <= {?date param}
and
(next({table.inmateid})<>{table.inmateid}
or
(next({table.inmateid})={table.inmateid} and
next({Audit_Trail.DateStamp})>{?date param})
Reset=Never
Place thi sint he detail section and you should see it it count up once per inmate that was out on the date you enter for the param.
plac eit in the report footer for a total.
You can use it for suppressing names and also to subtract from your disticnt count of all to get a number that were 'in' vs. 'out'
Does this work?
IP IP Logged
hugo69
Groupie
Groupie


Joined: 15 Feb 2010
Online Status: Offline
Posts: 53
Quote hugo69 Replybullet Posted: 23 Feb 2010 at 1:29pm
{Audit_Trail.DateStamp}: 02/04/2010 <= {?date param} says it is not part of the formula. And why the date 02/04/2010?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Feb 2010 at 1:34pm
sorry, that is what I get for a cut and paste  Embarrassed
Try this:
{Audit_Trail.FieldName} = Temporary_Out and {Audit_Trail.BeforeValue}=0 and {Audit_Trail.DateStamp} <= {?date param}
and
(next({table.inmateid})<>{table.inmateid}
or
(next({table.inmateid})={table.inmateid} and
next({Audit_Trail.DateStamp})>{?date param})
)
IP IP Logged
hugo69
Groupie
Groupie


Joined: 15 Feb 2010
Online Status: Offline
Posts: 53
Quote hugo69 Replybullet Posted: 23 Feb 2010 at 2:08pm
I will try this in the morning. I don't have access to the database right now. Off duty and having a beer.... long day. Too many whinny inmates.
IP IP Logged
hugo69
Groupie
Groupie


Joined: 15 Feb 2010
Online Status: Offline
Posts: 53
Quote hugo69 Replybullet Posted: 24 Feb 2010 at 6:01am
I tried it this morning and get 0's in all fields.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Feb 2010 at 6:56am
I would start to deconstruct the evaluate formula inthe RT and then re-run it each time to see if your values change to try and figure out where the problem is at.
Try the first set of conditions to see if they work:
{Audit_Trail.FieldName} = 'Temporary_Out' and {Audit_Trail.BeforeValue}=0 and {Audit_Trail.DateStamp} <= {?date param}
then try the next set.
Also change the setting from 'Exceptions for NUlls' to 'Default Values for Nulls'.
Does either part of the formula work 'correctly'?
IP IP Logged
hugo69
Groupie
Groupie


Joined: 15 Feb 2010
Online Status: Offline
Posts: 53
Quote hugo69 Replybullet Posted: 24 Feb 2010 at 10:52am
Ok I played around with it. What worked was this:
{Audit_Trail.FieldName} = 'Temporary_Out' and {Audit_Trail.BeforeValue}=0 and {Audit_Trail.DateStamp} <= {?date param}
and then set in Reset On Change Field to {Audit_Trail.FieldName} and now I get the Number 1 for all <= date param
 
So now how do we limit it to between the time that the inmate was out{Audit_Trail.BeforeValue} and {Audit_Trail.AfterValue}
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Feb 2010 at 10:54am
Do you have an inmate ID # in the table?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Feb 2010 at 11:07am
unless I misunderstand your data set you have to comapre it to the next row but also account for that there may not be a next row to 'close' that pair (meaning they are still out on furlough as of today).
Hence the use of the NEXT() function in the second part of my original formula.
(next({table.inmateid})<>{table.inmateid}
// this means count it becasue the pair is not closed and they are still out
or
(next({table.inmateid})={table.inmateid} and
next({Audit_Trail.DateStamp})>{?date param})
)
// this means the next row is for the same inmate which means that logically it is a return row and so the datefield must be > the param in order to count.
 
You should always only see a count on an 'out' row and never on a 'return' row.
Ultimately you do not want to reset your RT at all in order to get a real count.
Does this help with what I was trying to get your set up to do?
 


Edited by DBlank - 24 Feb 2010 at 11:08am
IP IP Logged
hugo69
Groupie
Groupie


Joined: 15 Feb 2010
Online Status: Offline
Posts: 53
Quote hugo69 Replybullet Posted: 24 Feb 2010 at 12:24pm
I think that is why I got confused? Thank you for all your help!!
IP IP Logged
<< Prev Page  of 5 Next >>
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.