| Author |
Message |
elamantia
Newbie
Joined: 20 Aug 2008
Online Status: Offline
Posts: 24
|
   Topic: Count Unsuppressed records
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
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
elamantia
Newbie
Joined: 20 Aug 2008
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
elamantia
Newbie
Joined: 20 Aug 2008
Online Status: Offline
Posts: 24
|
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 {@All} = Next ( {@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 {@All} <> Next ( {@All}) then the total count changes to 9,125.
The total should be around 3100.
|
|
minnie_eye
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Apr 2009 at 9:00am |
Try a running total as a DistinctCount on the {@All} field using the formula of
{JRF_TBLREFERRAL.REFERRALTYPEID} <> 2
|
|
IP Logged |
|
elamantia
Newbie
Joined: 20 Aug 2008
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
elamantia
Newbie
Joined: 20 Aug 2008
Online Status: Offline
Posts: 24
|
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
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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,"")
Edited by DBlank - 06 Apr 2009 at 1:22pm
|
|
IP Logged |
|
|
|
