Count Unsuppressed records
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=5980
Printed Date: 04 May 2024 at 10:56pm
Topic: Count Unsuppressed records
Posted By: elamantia
Subject: Count Unsuppressed records
Date Posted: 02 Apr 2009 at 4:14pm
|
I have a report that I'm grouping under individual ID number. I have suppressed all the records that have a 2 for a referral number, using the detail section expert with a formula. I would like to get a count of who is left. All I get is the total including the suppressed records. I have tried the three formula method (with a few variations) using the following:
//Reset counter
WhilePrintingRecords;
NumberVar Participants := 0; //Summary counter
WhilePrintingRecords;
NumberVar Participants; Participants := Participants + 1; //Display counter
WhilePrintingRecords;
NumberVar Participants;
Participants; I will place the "reset" formula in the group header, the "summary" in the detail section and the "display" in the group footer. I still get a count of all records!
 This seems like it should be easy!! I'm using CR XI ------------- minnie_eye |
Replies:
Posted By: DBlank
Date Posted: 02 Apr 2009 at 5:27pm
| You can use a running total as a count set to use the opposite formula of the condition that you are suppressing (referral #<>2) on and resetting it your group level. |
Posted By: elamantia
Date Posted: 06 Apr 2009 at 8:00am
|
I neglected to say that I have also suppressed duplicate records.
Your solution works nicely for referrals <> 2, but it's counting the other suppressed records.
All suggestions are welcome!!
Thanks for your time. ------------- minnie_eye |
Posted By: DBlank
Date Posted: 06 Apr 2009 at 8:06am
|
How did you suppress those records? Using a previous statement?
Can't you just that add that criteria in the running total formula?
SOmething like:
referral #<>2 or previous (field) <> field
|
Posted By: elamantia
Date Posted: 06 Apr 2009 at 8:28am
|
My report shows an individual ID # and a referral type. In the Detail Section Expert I used the following statement to suppress certain records:
{JRF_TBLREFERRAL.REFERRALTYPEID} = 2 or
mailto:%7b@All - {@All } = Next ( mailto:%7b@All - {@All }) The formula for 'All' is: {JRF_TBLREFERRAL.INDIVIDUALID} & {JRF_TBLREFERRAL.REFERRALTYPEID}
The running total count is 4,177.
If I change the Running Total Condition Formula to:
{JRF_TBLREFERRAL.REFERRALTYPEID} <> 2 or
mailto:%7b@All - {@All } <> Next ( mailto:%7b@All - {@All }) then the total count changes to 9,125. The total should be around 3100. ------------- minnie_eye |
Posted By: DBlank
Date Posted: 06 Apr 2009 at 9:00am
|
Try a running total as a DistinctCount on the mailto:%7b@All - {@All } field using the formula of
{JRF_TBLREFERRAL.REFERRALTYPEID} <> 2
|
Posted By: elamantia
Date Posted: 06 Apr 2009 at 12:10pm
That did it!! Just one more thing.. 
It is not counting the records that have a null referral type.
Thanks again! ------------- minnie_eye |
Posted By: DBlank
Date Posted: 06 Apr 2009 at 12:51pm
|
They are probably omitted from your formula All results, because the {JRF_TBLREFERRAL.REFERRALTYPEID} is the null item correct?
If so try changing your formula to handle these as:
{JRF_TBLREFERRAL.INDIVIDUALID} & (if isnull({JRF_TBLREFERRAL.REFERRALTYPEID}) then "NULL" else {JRF_TBLREFERRAL.REFERRALTYPEID})
This will insert a dummy value of the word "NULL" whenever that field is null and should include them in the count.
If this wasn't the issue can you give me a bit more of a description.
Thanks.
|
Posted By: elamantia
Date Posted: 06 Apr 2009 at 1:16pm
|
Yes that is the issue. The {JRF_TBLREFERRAL.REFERRALTYPEID} on some of the records is null. When I tried the new formual I received "a string is required here" error for {JRF_TBLREFERRAL.REFERRALTYPEID} after the else. ------------- minnie_eye |
Posted By: DBlank
Date Posted: 06 Apr 2009 at 1:22pm
|
if these are numeric fields you can either insert a zero instead of the null or convert the whole thing to text (don't use a zero if that is a valid response for that IDfield).
I also personally would add a "dash" to know where the two items meet like below. This also assumes the individualid is numeric and needs to be altered to text:
totext({JRF_TBLREFERRAL.INDIVIDUALID},0,"") + "-" + (if isnull({JRF_TBLREFERRAL.REFERRALTYPEID}) then "NULL" else totext({JRF_TBLREFERRAL.REFERRALTYPEID}),0,"")
|
Posted By: elamantia
Date Posted: 06 Apr 2009 at 1:45pm
No error now. But also no change in the records or the total. 
Thank you for sticking with me on this. This scenario is one that I will use often. ------------- minnie_eye |
Posted By: DBlank
Date Posted: 06 Apr 2009 at 2:04pm
|
THis is probably due to the formula in the running total where you are looking for the ID<>2. If there are nulls in there it is probably omitting them. If this is the case you can try 2 approaches. Change the formula to: isnull(table.IDfield) or table.idfield<>2Not sure if that will work or not. If not try and create a formula to handle it by converting nulls then using that field as your condition.
Formula as "ConditionalCount":
if isnull(table.idfield) then 0 else table.idfield
Then use this in your running total conditional count as:{@ConditionalCount}<>2
Hope these work
 then in your running total
|
Posted By: elamantia
Date Posted: 06 Apr 2009 at 2:13pm
|
Yeah!! The first way worked! (isnull(table.IDfield) or table.idfield<>2)
It seems so simple now. Thank you so much!
------------- minnie_eye |
Posted By: DBlank
Date Posted: 06 Apr 2009 at 2:14pm
|