Grouping multiple procedures together 1

[CJP12711]

I have a list of 4 procedures, with a code attached to each one.

Proc 1 = 123
Proc 2 = 456
Proc 3/2 = 789, 456

Proc 1 and 2 each have a single code attached to them. For proc 3, I want to find all cases that have both proc 3 and proc 2 in a case. So, if a case has only proc1, list proc one. If a case only has proc 3, ignore it. If a case has proc 3 and proc 2 in it, then list it.

Is there a simple way to do this? I have no problems listing all the procedures individually. I'm having trouble with the combination of the 2, while also listing the individual proc 1 and proc 2's. I'm using Crystal Reports 10

Thanks!!!
CJ

 

[lbass]

Can you show us how your data looks if you just place it in the detail section (include the case #), and then show us how the same data in the format you would like to see?

-LB

 

[CJP12711]

Sure - no problem:

Case # Proc Name Billing Code
222 Procedure 1 123
333 Procedure 2 456
333 Procedure 3 789

Let me know if that give you what you need....

 

[lbass]

Okay, then group on {table.case} and then create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + totext({table.billingcode},0,"")+","; //assumes billingcode is a number. If a string, remove totext(,0,"")

//{@display} to be placed in the case group footer:
whileprintingrecords;
stringvar x;
if len(x) > 4 then
left(x,len(x)-1)

This assumes that multiple billing codes only occur for Procedure 3. Do you also need the multiple Procedures identified as shown in your first post?

-LB

 

[CJP12711]

Each procedure has a single billing code. But, any case can have multiple procedures. Some procedures will only have a single procedure, but I need to identify cases that have a combination of 2 particular billing codes. There are really 4 entries that I'm looking for:

Procedure 1
Procedure 2
Procedure 1 and procedure 3
Procedure 1 and procedure 4

Each procedure has a corresponding procedure bililng code. I am filtering the report to pull only specific billinb codes. Does that make sense?

 

[lbass]

I am unclear on what procedures you would never show alone. Is it always 3 and 4? Or can this vary?

-LB

 

[CJP12711]

Yes - that is correct. Procedures 3 and 4 I wouldn't show alone... They only want to see procedures 3 and 4 when they also have procedure 1 in the case too.
CJ

 

[lbass]

Change the formulas to:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x;
stringvar proc;
if not inrepeatedgroupheader then (
x := "";
proc := ""
);

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar x;
stringvar proc;
if distinctcount({table.procedure},{table.case}) > 1 then (
x := x + totext({table.billingcode},0,"")+","; //assumes billingcode is a number. If a string, remove totext(,0,"")
proc := proc + {table.procedure}+","
);

//{@displaycodes} to be placed in the case group footer:
whileprintingrecords;
stringvar x;
if distinctcount({table.procedure},{table.case}) > 1 then
left(x,len(x)-1) else
if {table.procedure} in ["Procedure 3", "Procedure 4"] then
"" else
totext(maximum({table.billingcode},{table.case}),0,"")

//{@displayproc}:
whileprintingrecords;
stringvar proc;
if distinctcount({table.procedure},{table.case}) > 1 then
left(proc,len(proc)-1) else
if {table.procedure} in ["Procedure 3", "Procedure 4"] then
"" else
maximum({table.procedure},{table.case})

-LB

 

[CJP12711]

Thanks LB. I'm not sure what this is supposed to do. It is displaying the billing code of the procedures for the single ones, but when it gets to Proc3 and 4, nothing is showing up...

 

[CJP12711]

Hopefully this will clarify... In the end, I want counts... It should look like this:

Procedure 1 34
Procedure 2 56
Procedure 1 and 3 112
Procedure 1 and 4 76

For now, this will work. In the future, I'll want to get a monthly breakdown for the year. But, the main thing is to group the procedures together, and count them.

Thanks again for your help!
CJ

 

[lbass]

There are two separate display formulas that should be placed in the group footer, one for the billing codes and one for the procedure names. Please show me some sample detail level data and then what the formula results look like in the case group footer.

-LB

 

[CJP12711]

Detail Data:
Case # Procedure Name billing code
1 Procedure 1 456
2 Procedure 2 123
3 Procedure 2 123
4 Procedure 1 456
5 Procedure 3 789
5 Procedure 1 456
6 Procedure 4 654
7 Procedure 4 654
7 Procedure 1 456
8 Procedure 3 789

Footer Data with your formulas (grouping of case number):
Billing Code Proc Name
123 Procedure 2
123 Procedure 2
456 Procedure 1
456, 789 Procedure 1, Procedure 3
456, 654 Procedure 1, Procedure 4
Procedure 4
456 Procedure 1

What I want it to look like:
Procedure Name Count
Procedure 1 2
Procedure 2 2
Procedure 1 and 3 1
Procedure 1 and 4 1

Thanks!

 

[CJP12711]

Sorry - 1 correction below:

Detail Data:
Case # Procedure Name billing code
1 Procedure 1 456
2 Procedure 2 123
3 Procedure 2 123
4 Procedure 1 456
5 Procedure 3 789
5 Procedure 1 456
6 Procedure 4 654
7 Procedure 4 654
7 Procedure 1 456
8 Procedure 3 789

Footer Data with your formulas (grouping of case number):
Billing Code Proc Name
123 Procedure 2
123 Procedure 2
456 Procedure 1
456, 789 Procedure 1, Procedure 3
456, 654 Procedure 1, Procedure 4
Procedure 4
456 Procedure 1

What I want it to look like:
Procedure Name Count
Procedure 1 2
Procedure 2 2
Procedure 1 and 3 1
Procedure 2 and 4 1

Thanks!

 

[CJP12711]

Sorry - correction below. I added the change to the wrong part.

Detail Data:
Case # Procedure Name billing code
1 Procedure 1 456
2 Procedure 2 123
3 Procedure 2 123
4 Procedure 1 456
5 Procedure 3 789
5 Procedure 1 456
6 Procedure 4 654
7 Procedure 4 654
7 Procedure 1 456
8 Procedure 3 789

Footer Data with your formulas (grouping of case number):
Billing Code Proc Name
123 Procedure 2
123 Procedure 2
456 Procedure 1
456, 789 Procedure 1, Procedure 3
456, 654 Procedure 2, Procedure 4
Procedure 4
456 Procedure 1

What I want it to look like:
Procedure Name Count
Procedure 1 2
Procedure 2 2
Procedure 1 and 3 1
Procedure 1 and 4 1

Thanks!

 

[lbass]

I think you should just create running totals, a separate one for each condition: Proc 1, Proc 2, Proc 1 and 3, Proc 1 and 4. Do a distinctcount of case, evaluate using a formula, reset never (or on change of group if at a group level). For the first two, use an evaluation formula like this:

{table.procedure} = "Procedure 1"

For the multiple procedure rts, use evaluation formulas like this:

distinctcount({table.billingcode},{table.case}) > 1 and
{table.procedure} in ["Procedure 1","Procedure 3"]

Place these in the footer section, and add text boxes for labels.

-LB

 

[CJP12711]

Hi LB, the running totals aren't giving me exactly what I need... The code you gave me (below) looks pretty good, except that it's at the case level. Can it be tweaked to run at the procedure name level? That way we could do a distinct count on the case ID to get the counts, and the procedure names would just show up once... What do you think?

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x;
stringvar proc;
if not inrepeatedgroupheader then (
x := "";
proc := ""
);

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar x;
stringvar proc;
if distinctcount({table.procedure},{table.case}) > 1 then (
x := x + totext({table.billingcode},0,"")+","; //assumes billingcode is a number. If a string, remove totext(,0,"")
proc := proc + {table.procedure}+","
);

//{@displaycodes} to be placed in the case group footer:
whileprintingrecords;
stringvar x;
if distinctcount({table.procedure},{table.case}) > 1 then
left(x,len(x)-1) else
if {table.procedure} in ["Procedure 3", "Procedure 4"] then
"" else
totext(maximum({table.billingcode},{table.case}),0,"")

//{@displayproc}:
whileprintingrecords;
stringvar proc;
if distinctcount({table.procedure},{table.case}) > 1 then
left(proc,len(proc)-1) else
if {table.procedure} in ["Procedure 3", "Procedure 4"] then
"" else
maximum({table.procedure},{table.case})

 

[lbass]

I meant you should use regular inserted running totals, using the running total expert--simpler than manual running totals.

-LB

 

[CJP12711]

I'm sorry I'm being dense here... But I'm not seeing how the running totals will combine the 2 procedures together and count them only if both procedures are there... It seems to be keeping a running total of all procedures. Not just the ones in each category...

 

[CJP12711]

Procedures 1 and 2 are totaling correctly, but the records where Procedure 1 and 3, or 1 and 4 are together, the running total is 0.

 

[lbass]

I think you must have set up the evaluation formula incorrectly.

But separately from that issue, I think you should change my last suggestion, as I don't think that was quite right either (sorry):

Create a formula {@null} by opening and saving a formula without entering anything. Then create formulas like this (one for each combo):

//{@1and3}:
if {table.procedure} in ["Procedure 1", "Procedure 3"] then
{table.procedure} else
{@null}

Then insert a running total that does a distinctcount of {table.case} with an evaluation formula of:

distinctcount({@1and3},{table.case}) = 2

Reset never.

Repeat for each combo and place in the report footer with text boxes for labels.

-LB