Another Suppress Question
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9183
Printed Date: 27 Apr 2024 at 2:54pm
Topic: Another Suppress Question
Posted By: hugo69
Subject: Another Suppress Question
Date Posted: 20 Feb 2010 at 2:00pm
|
Let me begin by saying thank you for all your help. This is a great forum! Here is my new problem. These are the fields: {Audit_Trail.FieldName} {Audit_Trail.AfterValue} {Audit_Trail.BeforeValue} {Audit_Trail.DateStamp} In the report it shows this: Field Name: Temporary_Out Before Value: 0 After: 1 Date: 02/04/2010 Then when inmate comes back: Field Name: Temporary_Out Before Value: 1 After: 0 Date: 02/08/2010 If I do a (date parameter) of say 02/06/10 I do not want the inmate to show in the report. But if I do a date parameter of 02/09/10 I want to see him. And there are multiple other Temporary_Out ‘s for the inmate. So let’s say the inmate has not come back in yet there will not be a Temporary_Out listed yet, until he is back in then the value will change from 1 back to 0. How do I create a formula to suppress this type of inmate but show all others that do not have the Field Temporary_Out? The formula that I was working on was something like this: if {Audit_Trail.FieldName} = "Temporary_Out" and {Audit_Trail.AfterValue} = "1" and {Audit_Trail.DateStamp} <= {?date} then 1 else 0 But I get all the other Temporary_Out <= {?date} and the inmate that is out has nothing. I can’t figure this out. There has to be something with an in between or something with the dates. Thanks for the help in advance. |
Replies:
Posted By: hugo69
Date Posted: 22 Feb 2010 at 2:36pm
| Basically how can I do a date parameter to search between two different date fields? |
Posted By: DBlank
Date Posted: 22 Feb 2010 at 2:51pm
| The issue is that the dates are on differnent rows of data. If you can write a stored proc/view to combine the two related rows into one then it would be that straight forward for the date param but this is more complicated. |
Posted By: hugo69
Date Posted: 22 Feb 2010 at 4:51pm
|
I believe the fields that I am pulling from are from a view table. What would be needed after that? |
Posted By: hugo69
Date Posted: 23 Feb 2010 at 7:09am
|
Ok so I got to thinking last night, what if I can kind of trick the system. Is there anyway to select odd numbers?
What I was thinking is this.
If {Audit_Trail.FieldName} = "Temporary_Out"
and {Audit_Trail.DateStamp} <= {?date} then 1 else 0 Then do an Insert Summary as a Sum of the above and that would give me the numbers. If the numbers were odd that meant they were out of the jail. If even they would be back in. So I how would you select the odd numbers, by the {?date} and do you think that would work?
|
Posted By: DBlank
Date Posted: 23 Feb 2010 at 7:13am
|
Well it is not just using a view but rather creating a view that would combine your two matching rows into a single row.
There may be another approach but a few questions first t make sure i understand your set up.
1. everytime a inmater gors 'out' a row is created with aftervaule=0 and everytime they retunr a new row is created with aftervalue=1, correct?
2. If an inmate has never had a temp_out are they in the table at all, and if not do you need the name to appear in the report (because they are currently "in").
3. since you are using a date param here are you trying to show a list for that day or since that day? or eaxample if you enter 1-5-2010 and an inmate was out from 1-1-2010 to 1-15-2010 and then out again on 2-1-2010. the inmate was gone on your param date but also returned and left again. Do you include or excldeu them and why?
|
Posted By: DBlank
Date Posted: 23 Feb 2010 at 7:13am
| Sorry, just read your odd row question. Depends, please see my post and explain how you are handling the data. |
Posted By: hugo69
Date Posted: 23 Feb 2010 at 8:16am
|
1. Yes - when they go out I get a row beginvalue = 1 and aftervalue = 0 with the datestamp and then the opposite for when they return - beginvalue = 0 aftervalue = 1
2. yes they are in the table and yes I need them in the report.
3. show a list for that day or since that day? For that day
The reseason we exclude them is because they are not in the jail because of either they are on a furlough ( not our respondsiblity ) or are in another jail for court. And then that jail has to count them as being there. All has to do with state statues. If they are physically in your facility you count them.
|
Posted By: DBlank
Date Posted: 23 Feb 2010 at 8:22am
|
OK and yuck. So if they have never been furloghed you would have a NULL in the {Audit_Trail.FieldName}?
Is there another field in the table to identify a furlogh 'row pairing' or is is just the flip on the 0/1 value?
|
Posted By: hugo69
Date Posted: 23 Feb 2010 at 10:49am
| That would be correct about the NULL and it just flips on the 0/1 value. |
Posted By: DBlank
Date 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?
|
Posted By: hugo69
Date 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? |
Posted By: DBlank
Date Posted: 23 Feb 2010 at 1:34pm
sorry, that is what I get for a cut and paste 
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})
) |
Posted By: hugo69
Date 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. |
Posted By: hugo69
Date Posted: 24 Feb 2010 at 6:01am
| I tried it this morning and get 0's in all fields. |
Posted By: DBlank
Date 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'?
|
Posted By: hugo69
Date 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}
|
Posted By: DBlank
Date Posted: 24 Feb 2010 at 10:54am
| Do you have an inmate ID # in the table? |
Posted By: DBlank
Date 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?
|
Posted By: hugo69
Date Posted: 24 Feb 2010 at 12:24pm
| I think that is why I got confused? Thank you for all your help!! |
Posted By: hugo69
Date Posted: 24 Feb 2010 at 12:55pm
| Ok one last question?? Now that I have the information. How would I then exclude those inmates or subtract from the Total Inmates Confined? |
Posted By: DBlank
Date Posted: 24 Feb 2010 at 12:58pm
|
If you got the correct Running total for Inmats that are out just subtract it from a DistinctCount of all inmates. Create a formula something like:
DistinctCount(Table.inmateID})-{#RT}
Place this in the report footer.
|
Posted By: hugo69
Date Posted: 24 Feb 2010 at 2:30pm
|
I was miss spoke, looking at all the inmates it still shows if the temp_out<= date param the RT still shows 1. But if the dateparam is before the Temp_out it will show 0. Somethings missing? {Audit_Trail.FieldName} = "Temp_Out" and {Audit_Trail.BeforeValue}= "1"
and {Audit_Trail.DateStamp} <= {?date} and (next({Audit_Trail.FieldName})<>{Audit_Trail.FieldName} or (next({Audit_Trail.FieldName})={Audit_Trail.FieldName} and next({Audit_Trail.DateStamp})>{?date})) |
Posted By: DBlank
Date Posted: 24 Feb 2010 at 2:49pm
|
Make sure your Running Total Reset is set to "Never". It should be a distinctCount of the InmateID. It will incriment by 1 everytime it finds an new 'Out' row for a unique inmate that has
the OUt row date <param and the next row (in) >param (these colored examples below) or an inmate that has a row out with a date < param and the next row is for a new inmate (these colored examples below)
You need not be worried about counting ROWS, you need to be worried about counting Inmates.
Using a Param of 2-5-10 some fake data should be something like
Inmate Before_V After_V Date Running Total
Inmate 1 0 1 1-22-10 0
Inmate 1 1 0 1-23-10 0
Inmate 1 0 1 2-4-10 1
Inmate 1 1 0 2-6-10 1 (repeated)
Inmate 2 0 1 1-5-10 2
Inmate 3 0 1 1-27-10 2 (repeated)
Inmate 3 1 0 1-29-10 2 (repeated)
Inmate 4 0 1 1-27-10 3
Inmate 4 1 0 2-12-10 3 (repeated)
Inmate 5 0 1 1-4-10 3 (repeated)
Inmate 5 1 0 1-8-10 3 (repeated)
Inmate 5 0 1 2-1-10 4
Note in the lines that are noted with (repeated) are showing that the RT ignored them as they donot meet the criteria but will still have the previous RT value since is is at Reset=NEver.
Does this make sense? |
Posted By: DBlank
Date Posted: 24 Feb 2010 at 2:55pm
|
In the above example if you did a distinctcount on Inmate you would get 5.
Your RT of inmates that were out on your param date of 2-5-10 ends up being 4.
If you create your formula of DistinctCount(inmates)-RunningTotal you get 5-4=1 for you total inmates that are currently 'In' (e.g. only inmate#3 was "in" for the example)
|
Posted By: hugo69
Date Posted: 24 Feb 2010 at 6:23pm
|
That was my confusion about the numbers coming up like that. I thought I was doing something wrong. I will try that in the morning and let you know if it worked. |
Posted By: hugo69
Date Posted: 25 Feb 2010 at 7:15am
|
Ok I think we are almost there but the only problem now is that it skips over an inmate. I used todays date and should have been 3 total with 3 out.
DateStamp BeforeValue AfterValue
Lesa
02/08/2010 08:08:54 1 1 0 In_This_Facility 1
Margaret 02/12/2010 05:22:19 2 1 0 In_This_Facility 1 Suzann 02/24/2010 11:23:41 2 1 0 In_This_Facility 2 Total Inmates 3 2 |
Posted By: DBlank
Date Posted: 25 Feb 2010 at 7:20am
|
In the RT seelct formula in the upper right hand there is a select option that defaults to 'Exceptions For Nulls'. Did you change that to 'Default Values For Nulls'? |
Posted By: hugo69
Date Posted: 25 Feb 2010 at 8:16am
| I am not seeing that option in my Formula Workshop- RT Conditional Formula? |
Posted By: hugo69
Date Posted: 25 Feb 2010 at 8:38am
| Never mind found out where it was and changed it to "Default Values for Null' and did not work. |
Posted By: DBlank
Date Posted: 25 Feb 2010 at 9:02am
|
In your sample above with Lesa Marg and Susan, make sure you do not have any suppression on your detail rows. Suppressing will not impact the RT (it still evaluates the suppressed row) but will confuse you whne you are trying to validate the RT.
What is the exact next row after the one it skipped but should have counted?
If you can also do some testing by creating formula fields for each part of the RT formula and test to see if a certain part fails or not.
Make one formula for part 1:
{Audit_Trail.FieldName} = "Temp_Out" and {Audit_Trail.BeforeValue}= "1"
and {Audit_Trail.DateStamp} <= {?date} and another for part 2:
(next({Audit_Trail.FieldName})<>{Audit_Trail.FieldName} or (next({Audit_Trail.FieldName})={Audit_Trail.FieldName} and next({Audit_Trail.DateStamp})>{?date})) Place then on the details section and you will see it evaluate True or False for each part and maybe you can figure out what part of the formula is screwing your count up.
|
Posted By: hugo69
Date Posted: 25 Feb 2010 at 10:50am
|
I have no suppression going on.
And this is the next row that skips. This is under Margert
Field Name BV AV Date Part1 Part2
In_This_Facility 1 0 02/12/2010 05:22:19 True False
Current_Image_Link null 26611 02/16/2010 23:20:51 False True
|
Posted By: DBlank
Date Posted: 25 Feb 2010 at 11:22am
|
Well based on the testing it is the second part of the statement that fails (Part 2 returned a FALSE on your first row, it has to TRUE in both parts for the field to be counted).
It is really hard to figure this out away from the data...
Maybe adding in another OR option to fix the second formula?
(next({Audit_Trail.FieldName})<>{Audit_Trail.FieldName}
or Next(table.BV_field)<>0
or
(next({Audit_Trail.FieldName})={Audit_Trail.FieldName} and next({Audit_Trail.DateStamp})>{?date})) |
Posted By: hugo69
Date Posted: 25 Feb 2010 at 12:49pm
|
We are this close and I have this one that turns and should not. Do you think it has something to do with the period in the AV before the row? It is not even during the dateparam. There was been 2 after this date? Field Name BV AV Date Part1 Part2
Curr_Cell_Number M/A . 01/25/2010 07:55:41 2 False False
In_This_Facility 1 0 01/25/2010 07:55:41 3 True False
|
Posted By: DBlank
Date Posted: 25 Feb 2010 at 1:00pm
|
Did you update the Running Total formula with what nows seems to be working for part2 formula?
The fact that it is a FALSE in part2 for that row means it should not have 'counted' it. That is really weird...
|
Posted By: hugo69
Date Posted: 25 Feb 2010 at 1:15pm
| (Part 2 returned a FALSE on your first row, it has to TRUE in both parts for the field to be counted). That is weird because the ones that should be counted do not have the True in both parts? Would they be on the same row? Or true in one row (Part1) and true in another row (Part2)? |
Posted By: DBlank
Date Posted: 25 Feb 2010 at 1:21pm
|
Unless I have worked myself into a circle ont his they both should be TRUE on the same row to be counted in the RT. If either or both are False it should ignore it.
|
Posted By: hugo69
Date Posted: 25 Feb 2010 at 1:57pm
|
I think I have it. It may have been because of the wrong ID I was using. I have the numbers now coming out right. Today I should have 15 inmates not In This Facility. So heres a question? Now that I have the distinct number of 15. How do I minus them from the total count? The total count is 79 and when I do this:
{#All Inmates Confined} - Distinctcount ({Audit_Trail.FieldName}) = 28???
should it not be 79-15=64??
|
Posted By: DBlank
Date Posted: 25 Feb 2010 at 2:01pm
|
if you do just the Distinctcount ({Audit_Trail.FieldName})
...asuming that the {Audit_Trail.FieldName} field is the unique identifier for each inmate)...and palce it on the report footer what is the number displayed?
|
Posted By: hugo69
Date Posted: 25 Feb 2010 at 2:03pm
|
Nevermind the above!!! Long day!!
{#All Inmates Confined} - {#Not In This Facility}
But now how can I without the distinct count (15) figure out between Males and Females?
|
Posted By: DBlank
Date Posted: 25 Feb 2010 at 2:17pm
|
Create 2 more RTs with the same conditional formulas but just include one more condition for you gender. table.gender='Male'
and
(
...existing formula
)
|
Posted By: hugo69
Date Posted: 01 Mar 2010 at 1:04pm
| Thank you DBlank for all your help. I got the report finally worked out. |