Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Performance rpt-unwanted records in a field Post Reply Post New Topic
Author Message
cr2user
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United States
Online Status: Offline
Posts: 4
Quote cr2user Replybullet Topic: Performance rpt-unwanted records in a field
     Posted: 20 Jan 2009 at 1:30pm
Hello All,
I need to create a performance for quality measures report for each of our physician.
The outcome should show the number of qualified diabetes patients (18-75yrs and diagnosis code = ic9-250* with the office visit between certain time period), # of Performed, # of Not performed and # of not documented per doctor. .

The goal is to ensure that when a qualified patient comes in for a visit, the doctor is compliant, by checking either the "performed or not performed" box, which records the information in the obsvalue field.

I'm pulling the information from an EMR system, therefore, any notation (ie: vitals signs, symptoms, observations) that the doctor enters, gets recorded in the observation table(rptobs.hdid). Consequently, in one visit, the hdid can contain multiple data. In my report, I'm only looking for hdid value = 150,125.00 then show if either the value is performed or not performed. However, if the doctor did not check the box, then the 150,125.00 value will not exist at all. And if this is the case, I need to report this instance as "not documented" for this visit.

Currently, the way my report is setup, I'm picking up all of the hdid value, my question is how do I eliminate the non 150,125.00 value and still be able to report the ones that are non compliance?

Please let me know if this make sense or if you have any questions..Thank you in advance.

Here's my SQL query (from the show sql query screen):

 SELECT "PERSON"."SEARCHNAME", "PERSON"."DATEOFBIRTH", "PERSON"."PSTATUS", "PERSON"."HOMELOCATION", "PERSON"."LASTNAME", "USRINFO"."LASTNAME", "DOCUMENT"."DOCTYPE", "DOCUMENT"."CLINICALDATE", "PERSON"."SEX", "RPTOBS"."OBSDATE", "RPTOBS"."HDID", "RPTOBS"."OBSVALUE"
 FROM   (("ML"."DOCUMENT" "DOCUMENT" INNER JOIN "ML"."USRINFO" "USRINFO" ON "DOCUMENT"."USRID"="USRINFO"."PVID") INNER JOIN "ML"."PERSON" "PERSON" ON "DOCUMENT"."PID"="PERSON"."PID") LEFT OUTER JOIN "ML"."RPTOBS" "RPTOBS" ON "DOCUMENT"."SDID"="RPTOBS"."SDID"
 WHERE  "DOCUMENT"."DOCTYPE"=1 AND "USRINFO"."LASTNAME"='DRTEST MD' AND "PERSON"."LASTNAME"<>'TEST' AND "PERSON"."HOMELOCATION"=1.52986014400732e+015 AND "PERSON"."PSTATUS"='A' AND "PERSON"."SEX"='F'
 ORDER BY "USRINFO"."LASTNAME", "PERSON"."SEARCHNAME"

 

Sample Output:
                             # of Qualified   Performed  Not             Not

                                Patients                  Performed  Documented                 
DRTEST1 MD                    6                0           0                       6

DRTEST2 MD                    4                1           2                       1

 


 



Edited by cr2user - 21 Jan 2009 at 9:04am
cr2user
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jan 2009 at 3:24pm
Hi cr2user,
I think I understand your question of "how do I eliminate the non 150,125.00 value and still be able to report the ones that are non compliance" but let me double check.
You are fine with your list of patients or total N coming into the report,
you have to many records appearing because there are mutiple lines of data around the rptobs.hdid join, but if none of those records have the hdid value = 150,125.00 then count this as "Not documented" correct?
Here is one way but it assumes that each patient can only have an 1 instance of "Performed" or 1 instance of "Not performed" or 1 instance of "Not documented":
Create group1 as Doctor
Create group2 as Patient Place
Make a formula field to sum the instances of hdid value = 150,125.00 and documented.
Something like this:
if {table.hdid}= 150,125.00 and whatever means "performed" then 1 else 0.
Create a summary Sum of that formula field.
Place it next to group header 2 as "Performed".
Create another formula
if {table.hdid}= 150,125.00 and whatever means Not Performed then 1 else 0.
Create a 3rd formula
if Sum(formuala1,group2) + Sum(formula2,grouip2)=0 then 1 else 0 to show Not documented total.
You can then sum on these for your doc levels.
Hope this makes sense as it is a bit convoluted Unhappy
 
IP IP Logged
cr2user
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United States
Online Status: Offline
Posts: 4
Quote cr2user Replybullet Posted: 20 Jan 2009 at 7:16pm
Yes, each patient can only have one instance of performed or not performed; and if the patient is qualified & has an ofc visit within the specified timeframe, but the doctor did not check either the performed or not performed box, then I need to count it as a "not documented" instance.
I will give your suggestion a try and let you know.
Thank you so much!
cr2user
IP IP Logged
cr2user
Newbie
Newbie
Avatar

Joined: 06 Oct 2008
Location: United States
Online Status: Offline
Posts: 4
Quote cr2user Replybullet Posted: 21 Jan 2009 at 9:28am
DBlank -
I created the 3 formulas - the 1st two works great (count_perf & Count_notperfm); but I got an error message: "This field cannot be used as a group condition field" on the

COUNT_NOTDOC formula, so I took out the condFld portion - got rid of the error, but all I'm getting is a 0 value.

The other question is, how do I just count one instance of Not Documented?  Just in case...I'm included some sample data
 
Here's the sample data:
PATIENT NAME   Visit Dt        HDID               VALUE
TEST, Patient1     1/13/2009 150125.00      performed
TEST,Patient1      1/13/2009 15800,024.00 regular
TEST,Patient1              1/13/2009 18,332.00                  Reviewed - no change
TEST, Patient2            1/16/2009       56.00                    Negative
TEST, Patient2            1/16/2009      60.00                     Oral
TEST,Patien2             1/16/2009       1,060.00                well nourished
TEST, Patient2           1/16/2009       53.00                    108
TEST,Patient3             1/12/2009      4,763.00               intact to touch
TEST,Patient3            1/12/2009     150,125.00             not performed
TEST,Patient3            1/12/2009      15,800,024.00      Strengthin the upper
 
SUMMARY OUTPUT should be:
                  # of QLFD   Performed  Not Performed   Not
                  Patients                                                   Documented
TESTMD        3               1                    1                       1 
 
 
COUNT_PERF

if {RPTOBS.HDID} = 150125.00  and {RPTOBS.OBSVALUE} = "performed" then 1 else 0

COUNT_NOTPERF

if {RPTOBS.HDID} = 150125.00  and {RPTOBS.OBSVALUE} = "not performed" then 1 else 0

 
COUNT_NOTDOC

if sum({@COUNT_PERF },GroupName ({PERSON.SEARCHNAME})) + sum({@COUNT_NOTPERF},GroupName ({PERSON.SEARCHNAME})) = 0

then 1 else 0

COUNT_NOTDOC -without the condfld

if sum({@COUNT_PERF }) + sum({@COUNT_NOTPERF}) = 0

then 1 else 0


Thanks!
cr2user
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Jan 2009 at 9:53am

Since you cannot use the last formula, probably because the order in which the items are calculated the following might work but I am not sure, it may run into the same problem.

Calculate the total # of patients using a summary function as a distinct count of patient at the doctor group level. This would give you a total of how may of all 3 options (performed, not performed and missing) should exist for each doc.
Use the summary function to create a sum of ({@COUNT_PERF }) at the doc group level.
Use the summary function to create a sum of ({@COUNT_NOTPERF }) at the doc group level
Use all 3 of these summaries to get a single count per patient of the "not documented" and place it in group doc header. Something like:
Summary(distinctcountpatient,docgroup)- (sum(performed,docgroup) + sum(notperformed,docgroup))
Theoretically you have a distinct count of patients, a distinct count of performed and a distinct count of not performed so the difference would be the "not documented" patients. I beleive that by using the Summary function to get these totals you will be able to use them again in the formula without the error but am not sure. I still get tripped up on how far along I can use calculated fields without getting these type of errors.
Let me know if it works for you or not and good luck Wink


Edited by DBlank - 21 Jan 2009 at 9:53am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.