Question on running total of subreport data in main report 1

[BigC72]

Hello,

I am using CR 8.5 and querying a DB2 database via ODBC connection. I work for a medical clinic and the manner in which we bill for our services sometimes involves several components known as Global, Technical and Professional. My supervisor has asked to see a particular physicians generated charges for a certain range of procedures. For each of those procedures he wished to see what the breakdown cost wise is for each of those components. In addition he would like to see what the physicians total amount is for the professional component. I have created a main report with the data grouped by service and totaled and a subreport which displays via a formula the type and amount of each component for a particular service. From a display standpoint I have it right where I want it but what I'm struggling with is how to create a total of the professional component for each group on the main report. For instance if I have a procedure code 99999 and over the specified period of time there have been $2000 of charges generated on 10 encounters. The professional component of this procedure is $50 per encounter so of course the total professional component is $500. How can I create the ability within the report to generate those totals? Thanks...

 

[synapsevampire]

Either use the Professional amount from the database if it exists, or code a formula to handle it in Crystal, such as:

sum({table.charges},{table.component}) * 2.5

-k

 

[lbass]

How is the subreport linked to the main report? If each execution of the subreport gives you the three components grouped, then you could use a formula like {@Prof} in the subreport:

if {table.component} = "Professional" then {table.amt}

Then create a shared variable which you must place on the subreport canvas:
whileprintingrecords;
shared numbervar prof := sum({@Prof});

In the main report, insert a section below the one in which the subreport is located and then place the following formula there:

//{@accum}:
whileprintingrecords;
shared numbervar prof;
numbervar sumprof := sumprof + prof;

Then in the report footer, add a display formula:

whileprintingrecords;
numbervar sumprof;

If the shared variable can be null, then also add a reset formula in a section above the one in which the subreport is executing:

whileprintingrecords;
shared numbervar prof := 0;

-LB

 

[BigC72]

Thank you LB....I had to tweak the variables just a bit but it worked like a charm. As always this is the place to go!!!

 

[BigC72]

Well I didn't speak to soon I just didn't ask a detailed enough question. I've got my shared variables set up to give me a group total for each procedure code. Now what I'm trying to to is to get a report total of all of those values. Here are the formulas I'm using based upon lbass's recommendation:

in subreport;
if {SERVICE_CODE.PROCEDURE_MOD1} = "26" then {SERVICE_CODE.STANDARD_AMT}

and

whileprintingrecords;
shared numbervar prof := sum({@Prof});

Here is my main report formula that is being utilized to give me my group totals for this value which is the professional component:

//{@accum}:
whileprintingrecords;
shared numbervar prof;
numbervar sumprof := prof * {#count_subt}

Basically I took the shared value and multiplied it by a running total of the number of encounters since the value does not change until you change procedure code which is how the report is grouped.

All I'm hoping to do is take each of these summed values and generate a total for the report footer so that my supervisor can see how much is professional component charges in relation to the total charges. Hope this makes some sense. Thanks....

 

[lbass]

Please describe your report structure--what your groups are on the main report, where the subreport is located, and how the subreport is linked to the main report. It sounds like you want both a group total and a report total using the subreport values. This is easy to do--but we need to know more about the report in general.

-LB

 

[BigC72]

LB,

To answer your questions:

A) The report is grouped by procedure code (just an easier method for describing what procedure has been performed).

B) The subreport is located in the Details(b) section and then underlayed into the Details(a) section.

C) The subreport is linked via the procedure code only. In other words for each Procedure Code Group it will pull from the Subreport a Professional, Global & Technical Component description and dollar value for each code.

Of course is there are multiple instances of the same procedure code the subreport values remain the same. As I mentioned I have it totalling the professional component for each group I'm struggling with how to sum all those individual group values into that report total.

Thanks for the continued guidance....

 

[lbass]

The subreport should be in a group section, not the details. Please also explain how the encounters fit in--is each procedure one encounter or are there multiple encounters per procedure?

-LB

 

[BigC72]

LB,

Okay I will move it to a group section any particular one since I already have some grouped totals?

Actually it's more like each encounter which is just a medical billing way of saying an instance where a patient has seen a physician will only have one instance of that procedure code. Now a patient may have several procedures done in a day but I'm only focusing on a certain set of procedures and a patient will not have the same procedure more than once in a day. They may have it more than once during the time span I specify but that will just count as another encounter. So in essence I'm just totalling up how many encounters included this certain set of procedure or CPT codes during the time span I specify.

 

[lbass]

Since you are displaying the subreport (I think), that will drive where you put it. If you want it below the procedure group header, then insert a GH#1b and move it there. Then you would use my earlier formulas, and place {@accum} in a GH#1c section (it must be in a section below the one in which the subreport is located). You do not need to use a running total to multiply the result. Just try my formulas. Place the reset formula in GH#1a (suppress the formula), and place {@display} in the report footer.

-LB