counting only displayed values

[Ruune]

Does anyone know how to do this? I've suppressed all but the maximum date values in a group, and I want to count only the records that pertain to those dates.

 

[jdemmi]

can't you use the same logic used to suppress the date in your count formula?

-- Jason
"It's Just Ones and Zeros

 

[Ruune]

I tried that, but it still counts up all of the fields- including the ones that are suppressed. I've tried working with running totals too(using MAXIMUM()), but you can use a summary or running total in a formula.

 

[jdemmi]

post the formula used to suppress..

also post the basic structure of the report.

tbls, groups, etc.

-- Jason
"It's Just Ones and Zeros

 

[Ruune]

basic structure of the report-
records are selected by discharge date, so we are selecting patients that are discharged within a given date range. In this case, Jan 2006.

patient records are grouped by patient id ("patid"), so all records that are selected are contained in that group.

each patient has a number of commital_status_values (yep- its a mental institution), and an effective_date that corresponds with it. What the facility needs is the last commital_status_value- i.e. the one that was active at the time of the patients discharge. So, I suppressed the unwanted records with this suppression formula:

IF {#RTotal0} = {COMMITMENT.EFFECTIVE_DATE} THEN FALSE ELSE TRUE

Also, #Rtotal0 is comprised of the following:
Maximum of COMMITMENT.EFFECTIVE_DATE
Evaluate: for each record
Reset: On change of group PATIENT_DATA.PATID

Now, this works for suppressing the fields I need. But, I use the following to try and count a specific committal_status_value, with a maximum effective_date, and the result is always 0:

IF {#RTotal0} = {COMMITMENT.EFFECTIVE_DATE} and {COMMITMENT.COMMIT_STATUS_VALUE} = "xxxxxxxxxxxxxxxxx"
then count({TX_COMMITMENT.COMMIT_STATUS_VALUE}) else 0

stumped... I've also removed the "else 0" clause, and it had no effect.

Oh- I dont think it matters, but this is reporting from an Oracle 9i DSS, which is replicated nightly from a Caché flat file database.

 

[jdemmi]

I think this should work....

Create a new running total.

Field to summarize = COMMITMENT.COMMIT_STATUS_VALUE
Type of summary = COUNT
Evaluate for: USE A FORMULA
maximum(COMMITMENT.EFFECTIVE_DATE)
Reset on: DEPENDS ON, if this count is per the patient, per date or for the whole report.

-- Jason
"It's Just Ones and Zeros

 

[dgillz]

Why are you suppressing rows instead of using a record selection formula?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"making predictions is tough, especially about the future" - Yogi Berra

 

[Ruune]

jdemmi- thats still returning a 0 :-(

dgillz- because I'm trying to get the records corresponding with the maximum effective_date for each grouped patid, and I dont know how to do that with record selection, other than grab the maximum effective_date for the entire dataset. does that make sense?

 

[jdemmi]

where in the report are you placing the new running total?

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

oops....and what did you set the "RESET" to?

-- Jason
"It's Just Ones and Zeros

 

[Ruune]

in the report footer, but I tried it in the group footer and in the details section and it still returned a 0. freakin' weird.

 

[lbass]

You are making this more complicated than it needs to be. You could just go to report->selection formula->GROUP and add this formula:

{COMMITMENT.EFFECTIVE_DATE} = maximum({COMMITMENT.EFFECTIVE_DATE},{PATIENT_DATA.PATID})

This would return only the most recent date per patient. Then you can insert running totals that will count only the displayed values without having to specify any criteria to exclude the non-displayed values.

For the running total, you would use something like count of patient ID, evaluate using a formula:

{TX_COMMITMENT.COMMIT_STATUS_VALUE} = "your specific value"

Reset never if at the report level. At least it sounded like you wanted to count some specific value for this field, not the field itself.

-LB

 

[Ruune]

AWESOME... completely forgot about group selection. I do have a nasty habit of overcomplicating things!
Thanks!