Display Detail Data in Group Footer

[bkron]

I need to create a report that will list patients seen at our clinic, along with an indication of whether they have had certain charges billed to them. So, we are essentially talking about looking at multiple charges per patient and reading through those records for certain codes.

If, in any of the charges for that patient, if a certain code is found, I would like to display an "X" on the report next to the patient's name.

I imagine the output to look something like below, assuming the patient had multiple charges billed to them, with at least one of them being "Charge B". Example:

Patient Name | Charge A | Charge B | Charge C
John Doe | | X |

I was trying to accomplish this with two formulas, like so:

(Formula One)
If {charges.cpt4_code_id} = "45380" then {charges.person_id} else {@Null}

(Formula Two) - this is the one that would be placed on the report:
if {person.person_id} in {@Biopsy Check Patient} then "X" else ""

I was thinking that I could put this in the group footer, but I'm finding that the X only displays at the detail level. My assumption is that if the code is found, it will pop the person_id into the formula. Then, in the second formula, looks to see if the current person_id is in the first formula.

Hopefully, that makes sense. Can anyone tell me how I could go about this in a better way?

 

[kray4660]

Unfortunately if I believe is correct it will take a bit more work. We have a similiar system and each patient record will have a different code, thus when you get to the group level, it only picks up the last code in the group.

What I would do is set up three different shared variables and set them at the detail level (based on the conditions you have) then you can display them at the GF level. Be sure to reset the values at the GH section.

I hope this helps.

 

[bkron]

That's the direction I was headed. Thanks for the confirmation.

I'll try it that way, unless anyone else has a better idea.

Thanks for your reply!

 

[lbass]

Insert a crosstab in the group section (group is on patient) and then add a column {table.charge}, and add no row (or use the patient name/ID as the row), and a summary (maximum) on a formula:

if not isnull({table.charge}) then "X"

-LB

 

[bkron]

Hey, LB. Thanks for your response--we've all benefited from your expertise on this forum. Thank you!

I thought about a crosstab, but wasn't sure that would work.

The way you suggest, will the formula analyze multiple charge records and then (if the correct charge code is found in at least one record) return an "X" because that would be considered the maximum value of all records analyzed (even if the code was found more than once)?

So, if I have a formula like this:

if not isnull({charges.cpt4_code_id} and {charges.cpt4_code_id} = "123") then "X" else ""

then, due to the nature of a max summary, will return an "X"?

 

[lbass]

You should just use my formula. You don't need to identify the code--the column will take care of that. Did you try it in the crosstab?

-LB

 

[bkron]

I didn't try it yet, but I certainly will. One thing, though: the field could hold any number of different codes (and again, there are potentially multiple charge records per patient).

So, if there are three charge records where the charges.cpt4_code_id field contains the following three codes: "123", "1234" and "12345" and I am looking for at least one record to have the value "123" then your approach will work, even though it doesn't test for a value?

Sorry to bother you--I just want to understand it before I try it. Perhaps it would be better for me to just try it and then see what happens.

Thanks again for your help!

 

[lbass]

If the code field is used as the column, then there would be three columns based on the values you mention, one for "123", one for "1234", and one for "12345". If you want to cluster them, then you'd have to use a formula for the column, as in:

left(code,3)

-LB