counting records formula
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=18965
Printed Date: 29 Apr 2024 at 7:27am
Topic: counting records formula
Posted By: sanchezgmc06
Subject: counting records formula
Date Posted: 28 Jan 2013 at 12:58pm
|
hello,
I have a report where I am listing all principal diagnosis. I have a formula which counts if the clients have a specific diagnosis.
formula:
if ({history_diagnosis.axis_I_diag_code_1} startswith ["291","292","303","304","305"]
or {history_diagnosis.axis_I_diag_code_2} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_3} startswith ["291","292","303","304","305"] or{history_diagnosis.axis_I_diag_code_4} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_5} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_6} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_7} startswith ["291","292","303","304","305"] or{history_diagnosis.axis_I_diag_code_8} startswith ["291","292","303","304","305"]) then 1 else 0 now i get a 1.00 if the client has one of the above diagnosis. I can do a sum on this formula however if a client was open to multiple programs and has one of the above diagnosis for those programs the client is displayed twice and counted twice. if the client is listed more than once i just want to count the client once.
I tried a running total and that did not work
I tried doing a distinct count on client_id however this only gives me a distinct number of clients regardless if they meet the above diagnosis.
help
|
Replies:
Posted By: Sastry
Date Posted: 28 Jan 2013 at 5:50pm
|
Hi You add some condition to your distinct count like : If {@formula} > 0 Then Distinctcount({Client}) or Insert a group on Client, insert summary on you group footer. This will give you 0, 1 or more than 1, now you write one more formula like : If sum({@formula},{Client}) > 0 then 1 else 0 Now you can insert a summary on the above formula ------------- Thanks, Sastry |
Posted By: DBlank
Date Posted: 29 Jan 2013 at 4:11am
|
or use a running total to do a distinct count of the clientid with an evaluation formula as
({history_diagnosis.axis_I_diag_code_1} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_2} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_3} startswith ["291","292","303","304","305"] or{history_diagnosis.axis_I_diag_code_4} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_5} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_6} startswith ["291","292","303","304","305"] or {history_diagnosis.axis_I_diag_code_7} startswith ["291","292","303","304","305"] or{history_diagnosis.axis_I_diag_code_8} startswith ["291","292","303","304","305"]) reset=never make sure to set the formula editor to use defualt values fo null |
Posted By: sanchezgmc06
Date Posted: 31 Jan 2013 at 8:25am
thank you (it worked) 
|