Grouping on similar codes

[lindagoodwin]

I’m using Crystal 10.
I’ve been working on a report that pulls information from our charge master. I use the following fields.

{CHARGE.CODE} {CHARGE.BILLING_DESCRIPTION} {CHARGE_PRICE.PRICE}
{CHARGE_PRICE.EFFECTIVE_DATE}

What the Controller wants is all of the Xray dept’s charges with the prices and most recent effective dates. I can get the most updated effective dates by grouping. However, there’s more info that is needed that I’m having difficulty with.

As you can see by my example below, for each charge code there is another charge code for a professional fee. The charge codes are similar except the professional fee starts with “PR”. The regular charge codes start with “R”. The have the same numbers after the letters.

Code Description Price effective date dept
R10022 Fine Needle asp 684.80 07/11/09 Radiology
PR10022 Fine Needle asp w/ 79.67 05/22/09 US Pro Fee


What she wants is what % of the Professional Fee from the original Charge code. My idea was grouping the similar numbers, and then figuring the percentage but I can’t seem to figure out a formula for that.

Can anyone help?? Please

 

[Turkbear]

Hi,
Try this to isolate the number from the type code ( R or PR)

@SortbyCode

If LEFT({CHARGE.CODE},1) = 'R' then MID({CHARGE.CODE},2) 
Else
If LEFT({CHARGE.CODE},2) = 'PR' then MID({CHARGE.CODE},3)[COLOR=green]//in case code has other starting letters, ignore them[/color]

Group on this to get the records for that code having either R or PR .






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lindagoodwin]

That worked. But I think I need to rethink the entire report.
Each charge code has a price increase once a year. Not always the same date. So I want the max effective date of the price. Which I got. But when I go to calculate the precentage of professional fee of the related charge code, I first tried to sum the group. Well, it sums all the previous prices. It's weird. It shows the max date effective price per charge code but the sum shows the total of all prices.
So I'm thinking I need to start over from the beginning. Not sure where but your post did help me to isolate just the codes I wanted out of that department. Thanks so much. very helpful.

 

[lbass]

If you are willing to show your results in the code group footer, you could insert two running totals:

//{#sumprice}: sum of {charge_price.price}, evaluate using a formula:

left({charge.code},1) = "R" and
{charge_price.effective_date} = maximum({charge_price.effective_date})

Reset on change of group: {@code} (if you are planning to show the percentage per code group, or reset: never, if at the grand total level).

//{#sumprof}:
sum of {charge_price.price}, evaluate using a formula:

left({charge.code},2) = "PR" and
{charge_price.effective_date} = maximum({charge_price.effective_date})

Reset on change of group: {@code} (if you are planning to show the percentage per code group, or reset: never, if at the grand total level).

Then create a formula {@percent-codelevel}:
{#sumprof}%{#sumprice}

For a percentage at the grand total level, you would need two additional running totals with reset: never, and a similar formula, all placed in footer sections.

-LB