Duplicating value in Crystal 10 - Technical User

[maruja]

Greetings:

I am a user from a medical facility. I have been asked to write a report (Crystal 10) showing all physicians who consulted on cases where the DRG was 82. (DRG's enable specific forms of reimbursement to hospitals.) Also specific charges or 'activity codes' were included in the select clause. When I run the report each consultant 'repeats ' for as many activity codes as there are. For example, if there were 4 codes and 4 consultants I will have a total quantity of 16. I've tried grouping the consultants, inputting them on a subreport, but to no avail. I've checked my links and I believe they are OK. Please advise. The 4 views I have brought into the report are visit (basic patient info), activity (charges), pract_cons (Physician consult info), smd_consult_v (consult priority, consult date time, etc).

Here is my SQL Query:

SELECT DISTINCT "c_cmb_vst_v_20050303"."pt_id", "c_cmb_vst_v_20050303"."pt_name", "c_cmb_vst_v_20050303"."adm_date", "c_cmb_vst_v_20050303"."med_rec_no", "c_cmb_vst_v_20050303"."dsch_date", "c_cmb_vst_v_20050303"."drg_no", "c_cmb_actv_v_20050303"."actv_cd_desc", "c_cmb_actv_v_20050303"."actv_tot_qty", "c_cmb_vst_v_20050303"."drg_serv_line", "c_cmb_vst_v_20050303"."hosp_svc", "c_cmb_vst_v_20050303"."len_of_stay", "c_cmb_vst_v_20050303"."tot_chg_amt", "c_cmb_vst_v_20050303"."prim_pyr_cd", "c_cmb_actv_v_20050303"."actv_cd", "c_cmb_vst_v_20050303"."prim_pract_name", "c_cmb_vst_v_20050303"."orgz_cd", "c_cmb_vst_v_20050303"."vst_type_cd", "c_cmb_vst_v_20050303"."dsch_year", "c_pract_dim_cons_v"."pract_name", "c_smd_consult_v"."smd_consult_dtime", "c_smd_consult_v"."smd_consult_dr_prio"
FROM ("SMSPHdssp0r0"."smsdss"."c_cmb_vst_v_20050303" "c_cmb_vst_v_20050303" INNER JOIN "SMSPHdssp0r0"."smsdss"."c_cmb_actv_v_20050303" "c_cmb_actv_v_20050303" ON "c_cmb_vst_v_20050303"."vst_key"="c_cmb_actv_v_20050303"."vst_key") LEFT OUTER JOIN ("SMSPHdssp0r0"."smsdss"."c_pract_dim_cons_v" "c_pract_dim_cons_v" INNER JOIN "SMSPHdssp0r0"."smsdss"."c_smd_consult_v" "c_smd_consult_v" ON "c_pract_dim_cons_v"."pract_no"="c_smd_consult_v"."smd_consult_dr_no") ON ("c_cmb_vst_v_20050303"."pt_id"="c_smd_consult_v"."pt_id") AND ("c_cmb_vst_v_20050303"."orgz_cd"="c_smd_consult_v"."orgz_cd")
WHERE NOT ("c_cmb_vst_v_20050303"."drg_serv_line"='behavioral health' OR "c_cmb_vst_v_20050303"."drg_serv_line"='rehab' OR "c_cmb_vst_v_20050303"."drg_serv_line"='ungroupable') AND "c_cmb_vst_v_20050303"."hosp_svc"<>'hsp' AND "c_cmb_vst_v_20050303"."len_of_stay"<>0 AND "c_cmb_vst_v_20050303"."tot_chg_amt"<>0 AND NOT ("c_cmb_vst_v_20050303"."prim_pyr_cd"='j24' OR "c_cmb_vst_v_20050303"."prim_pyr_cd"='k01' OR "c_cmb_vst_v_20050303"."prim_pyr_cd"='n15') AND "c_cmb_vst_v_20050303"."vst_type_cd"='I' AND "c_cmb_vst_v_20050303"."drg_no"=82 AND ("c_cmb_actv_v_20050303"."actv_cd"='20431011' OR "c_cmb_actv_v_20050303"."actv_cd"='20431029' OR "c_cmb_actv_v_20050303"."actv_cd"='20431037' OR "c_cmb_actv_v_20050303"."actv_cd"='20431078' OR "c_cmb_actv_v_20050303"."actv_cd"='20431086' OR "c_cmb_actv_v_20050303"."actv_cd"='20431094' OR "c_cmb_actv_v_20050303"."actv_cd"='20431169' OR "c_cmb_actv_v_20050303"."actv_cd"='20431177' OR "c_cmb_actv_v_20050303"."actv_cd"='20431219' OR "c_cmb_actv_v_20050303"."actv_cd"='20431227' OR "c_cmb_actv_v_20050303"."actv_cd"='20431235' OR "c_cmb_actv_v_20050303"."actv_cd"='20431243' OR "c_cmb_actv_v_20050303"."actv_cd"='20431250' OR "c_cmb_actv_v_20050303"."actv_cd"='20431359' OR "c_cmb_actv_v_20050303"."actv_cd"='20431367' OR "c_cmb_actv_v_20050303"."actv_cd"='20431375' OR "c_cmb_actv_v_20050303"."actv_cd"='20431599' OR "c_cmb_actv_v_20050303"."actv_cd"='21700059' OR "c_cmb_actv_v_20050303"."actv_cd"='21700067' OR "c_cmb_actv_v_20050303"."actv_cd"='21700562' OR "c_cmb_actv_v_20050303"."actv_cd"='21700786' OR "c_cmb_actv_v_20050303"."actv_cd"='21701057') AND "c_cmb_vst_v_20050303"."orgz_cd"='P0B0' AND "c_cmb_vst_v_20050303"."dsch_year"=2005
ORDER BY "c_cmb_vst_v_20050303"."pt_id",
"c_smd_consult_v"."smd_consult_dr_prio"

Thanks, Maruja

 

[Madawc]

You haven't been very specific about just what the report is doing that you don't want it to do.

If you group by consultant, then you could suppress the details and just show the consultant's data in the group header or footer. Does that help?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[maruja]

Greetings: If I group by consultant than the patient's data would be all over the report. I want it under the patient's name or patient number so it will group all together. Below is what I am getting. For example: there are 4 consultants on this account. And there are 4 charges on this account. I only want to see 4 consultants and 4 charges. what I am seeing is what is below. The same 4 charges broken out each time the consultant's name changes. So at the end of the patient's group I get a quantity of '16' charges. That is incorrect. Please advise. Thanks.

Patient CONSULTANT
REED ,ROBERTA
Activity Code (Charges) QTY

20431037 - CT HEAD W/WO CONTRAST 1 SKOLNIK, BRUCE J
20431086 - CT ABDOMEN W CONTRAST 1 SKOLNIK, BRUCE J
20431169 - CT PELVIS W/CONTRAST 1 SKOLNIK, BRUCE J
21700067 - BONE-JOINT IMG-WHOLE 1 SKOLNIK, BRUCE J
BODY

20431037 - CT HEAD W/WO CONTRAST 1 MAHAJAN, DEVINDER
0431086 - CT ABDOMEN W CONTRAST 1 MAHAJAN, DEVINDER
20431169 - CT PELVIS W/CONTRAST 1 MAHAJAN, DEVINDER
21700067 - BONE-JOINT IMG-WHOLE 1 MAHAJAN, DEVINDER
BODY

20431037 - CT HEAD W/WO CONTRAST SIGNORI, OSCAR R
20431086 - CT ABDOMEN W CONTRAST SIGNORI, OSCAR R
20431169 - CT PELVIS W/CONTRAST SIGNORI, OSCAR R
21700067 - BONE-JOINT IMG-WHOLE SIGNORI, OSCAR R
BODY
20431037 - CT HEAD W/WO CONTRAST LEE, CHOON
20431086 - CT ABDOMEN W CONTRAST LEE, CHOON
20431169 - CT PELVIS W/CONTRAST LEE, CHOON
21700067 - BONE-JOINT IMG-WHOLE LEE, CHOON
BODY
QTY 16

 

[Madawc]

Crytal works by 'rows' - that's to say, single instances of a record from several tables, treated as a 'detail line'.

Your 'row' is evidently patient-consultant-activity, but you want to show patient-consultant and then patient-activity. That goes against the way Crystal works, and I don't see an easy and efficient answer.

An easy but inefficient answer is to have a subreport for each patient, with the main report showing patient-consultant and the subreport showing patient-activity.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[maruja]

Madawc Williams

Thanks for the reply regarding duplicating rows. I think I just may publish the report with the duplications. I believe you are right that there are no easy answers. But thanks.....
By the way, I'm writing from Detroit right across the border from Canada where they televise 'Coronation Street'. Have been a fan for several years....How's things in the UK?
Maruja