Calculating AR Days by Hospital by Month 1

[mdcson]

thread767-1635183

My goal is to create a report showing the number of AR Days per month per hospital as indicated by the following array:


Hosp1 Hosp2 Hosp3 Total
Month1 x x x x
Month2 x x x x
. x x x x
. x x x x
. x x x x
Month12 x x x x

My AR Days formula is as follows:

if ({PatientSummaryTBMonthly.FYear}="2007" and {PatientSummaryTBMonthly.Period}<12) then 0
else
(evaluateafter ({@3MRAvgCharges});
whileprintingrecords;
numbervar x;
{@UpdatedEndBal}/(x/{@3MRAvgDays}))

{@3MRAvgCharges}, from the previous thread, which indicates the average charges for the current month together with the prior two, is as follows:

whileprintingrecords;
numbervar array rev;
numbervar i;
numbervar j;
numbervar x := 0;


if onfirstrecord then (
redim rev[1];


) else
(
redim preserve rev [ubound(rev)+1];

);
j := ubound(rev);

rev[j] := tonumber(sum({PatientSummaryTBMonthly.TB Period Charges},{@ToDate}));
if j < 4 then (
x := sum(rev);

) Else
(
for i := j-2 to j do (
x := x + rev;

);
x;
);
x


{@3MRAvgDays} indicates the average number of days in the current month together with the prior two, is as follows:

day(dateserial(year({@ToDate}),month({@ToDate})+1,0))+
(day(dateserial(year({@ToDate}),month({@ToDate}),0)))+
(day(dateserial(year({@ToDate}),month({@ToDate})-1,0)))

When I put the AR Days formula in my design view GF1, group being @todate, I do get the proper result for the "total" column (the total amount of AR Days across all hospitals combined). However, the problem lies with the individual hospitals.

I have a hospital field which I have tried adding to the criteria in the AR Days formula, however, that is not giving me the correct results.

I have tried changing the groupers to make hospital the primary, @todate the secondary, and period the tertiary which did give the proper results by hospital, however, each set of 12 months was repeated as many times as there are facilities and, in the process, the total column then was not accurate.

How, then, do I simply get the hospital numbers and the total numbers to be accurate and in one clean array?

 

[lbass]

I think you could substitute formulas like the following for the {PatientSummaryTBMonthly.TB Period Charges} field in the earlier formula:

//{@hospA}:
if {table.hospital} = "Hospital A" then
{PatientSummaryTBMonthly.TB Period Charges}

-LB

 

[mdcson]

Are referring to the {@3MRAvgCharges} formula with this line:
rev[j] := tonumber(sum({PatientSummaryTBMonthly.TB Period Charges},{@ToDate}));
if j < 4 then (
x := sum(rev);?

So, are you saying I could have a different {@3MRAvgCharges} formula for each hospital and, upon creating the hospital formula that you mentioned, use that formula in place of {PatientSummaryTBMonthly.TB Period Charges}?

 

[lbass]

Yes.

-LB

 

[mdcson]

Unfortunately, this did not work. I am getting numbers that are about 3 times larger than they should be.

I created individual formulas for each hospital, for example,

if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
{PatientSummaryTBMonthly.TB Period Charges} and, for simplicity's sake, called it @Hosp1

Then, I created a version of the @3MRAvgChgs for each hosp. For this example, I called it @3MRAvgChgsCV

Finally, I created an AR Days formula for each hospital that, for this example, looks like:

(evaluateafter ({@3MRAvgChgsCV});
whileprintingrecords;
numbervar x;
{@UpdatedEndBal}/(x/{@3MRAvgDays}))

I did this for all the hospitals and am getting larger numbers. Also, when I add these other AR Days formulas to GF1, the original formula that reflects the grand total, which is the only formula that is currently yielding the proper results, changes to numbers that are larger as well.

I am pretty stumped at this stage.

 

[lbass]

So the value of "x" was incorrect in the middle formula? What are the contents of {@updatedEndBal} and {@MRAvgDays}? All of these nested formulas make it very hard to comprehend.

-LB

 

[lbass]

Actually I think the issue is that you have to rename your variables for each hospital formula and also create separate display formulas (and resets) for rev, i, j, and x.

-LB

 

[mdcson]

@UpdatedEndBal =

Sum ({PatientSummaryTBMonthly.TB Ending Balance}, {@ToDate}) -
Sum ({PatientSummaryTBMonthly.TB Ending Balance Credits}, {@ToDate})

{@MRAvgDays}=

day(dateserial(year({@ToDate}),month({@ToDate})+1,0))+
(day(dateserial(year({@ToDate}),month({@ToDate}),0)))+
(day(dateserial(year({@ToDate}),month({@ToDate})-1,0)))

I'm not sure if there is an easier way. If so, I am certainly open to all suggestions. I know that these formulas worked for a report that utilized the same data sources and presented the AR Days information grouped by hospital for the primary, @todate the secondary, and period the tertiary. It was just a long list.

However, this differs since I need to create a report that displays this information in more of a summary where the only data that will appear is AR Days and in the concise format I mentioned above.

 

[lbass]

Did you see my last post?

Also, I'm wondering whether you mean to compare a specific hospital with all data or just with all data for that hospital, in which case you would need to sum balances and credits for a particularn hospital.

-LB

 

[mdcson]

Yes, apparently, we posted at the same time. Basically, I need to show AR Days for each individual hospital in our system of hospitals (which would be a subset of the whole). The database consists of data for every hospital in the system and there is a hospital field to query on. Then, the final column will show for the entire system as a unit, which is what IS working.

I understand the renaming of the variables as I can see now how that is effecting all the totals. So, instead of i and j which I have for the total hosp calculation, I create a separate formula for hosp 1 that uses, say, a and b, for hosp 2, c and d, etc?

I'm not sure what you mean by separate display formulas (and resets) for the variables?

Here again is the @3MRAvgChgs formula for all hosps:

whileprintingrecords;
numbervar array rev;
numbervar i;
numbervar j;
numbervar x := 0;


if onfirstrecord then (
redim rev[1];


) else
(
redim preserve rev [ubound(rev)+1];

);
j := ubound(rev);

rev[j] := tonumber(sum({PatientSummaryTBMonthly.TB Period Charges},{@ToDate}));
if j < 4 then (
x := sum(rev);

) Else
(
for i := j-2 to j do (
x := x + rev;

);
x;
);
x

Can you show me an example of what you mean that would tweak and/or add to this?

 

[lbass]

YOu should change all the variables that i identified in my last post: rev,i,j,x. Your display formula/final calculation is:

evaluateafter ({@3MRAvgChgsCV});
whileprintingrecords;
numbervar x;
{@UpdatedEndBal}/(x/{@3MRAvgDays}))

...I guess, althought you haven't explained in what sections you are placing these formulas. All I'm saying is after changing the variables in the accumulation formula, you need to reference the new variables in related formulas.

-LB

 

[mdcson]

Thank you very much LBass, after working through my data and changing all variables for each formula representing each hospital, I was able to get exactly report I needed. As with all my reports, I have been asked to place a graph above which I always place in the report header to provide a visual of the matrix of data presented below.

In graphing this particular data, however, I am given this error: "A print time formula that modifies variables is used in a chart or map Details3MRAvgChgsCV" I am graphing, for example, the @ARDaysCV formula below.

Here are my formulas for one hospital:

1)@ARDaysCV = if {PatientSummaryTBMonthly.FYear}= "2010" and {PatientSummaryTBMonthly.Period}= 12 then
{@UpdatedEndBalCV}/({@3MRAvgChgsCV}/{@3MRAvgDays}) else

if {PatientSummaryTBMonthly.FYear}= "2010" and {PatientSummaryTBMonthly.Period}< 12 then
0 else

{@UpdatedEndBalCV}/({@3MRAvgChgsCV}/{@3MRAvgDays})

2)@UpdatedEndBalCV =
if {PatientSummaryTBMonthly.FYear}= "2010" and {PatientSummaryTBMonthly.Period}= 12 then

Sum ({@CVEndBal}, {PatientSummaryTBMonthly.Period}) -
Sum ({@CVCredits},{PatientSummaryTBMonthly.Period}) else

if {PatientSummaryTBMonthly.FYear}= "2010" and {PatientSummaryTBMonthly.Period}< 12 then

Sum ({@CVEndBal}, {PatientSummaryTBMonthly.Period}) else

Sum ({@CVEndBal}, {PatientSummaryTBMonthly.Period}) -
Sum ({@CVCredits},{PatientSummaryTBMonthly.Period})

3)@3MRAvgChgsCV =
whileprintingrecords;
numbervar array cv;
numbervar a;
numbervar b;
numbervar y := 0;


if onfirstrecord then (
redim cv[1];


) else
(
redim preserve cv [ubound(cv)+1];

);
b := ubound(cv);

cv := tonumber(sum({@Rev_CV},{@ToDate}));
if b < 4 then (
y := sum(cv);

) Else
(
for a := b-2 to b do (
y := y + cv[a];

);
y;
);
y

4)@CVEndBal =
if {PatientSummaryTBMonthly.Hospital Abbreviation}="CV" then
{PatientSummaryTBMonthly.TB Ending Balance}

5)@CVCredits =
if {PatientSummaryTBMonthly.Hospital Abbreviation}= "CV" then
{PatientSummaryTBMonthly.TB Ending Balance Credits}

In my graph, I am using OnChangeOf @ToDate and, under value, am placing each AR Days calculation to create a line graph for each, showing the AR Days amount per month for each hospital. The formulas I am placing in the Show value section include, for example, the @ARDaysCV as well as those for the other hospitals.

Thank you again for all your help!

 

[lbass]

You can't simply chart on these formulas because of the print-time variables. You will have to follow the steps in the following article:

http://www.sdn.sap.com/irj/boc/go/p...2a54ff4cd?QuickLink=index&overridelayout=true

-LB

 

[mdcson]

Thanks for the report. However, it uses such a simplistic example and, after examining and working with it for two days, I still do not know how to get it to work with what I am trying to do. As you can see from my formulas above, there are three embedded formulas in the final calculation for AR Days.

Moreover, there are five hospitals in this system. Thus, I have six different sets of formulas (the other being the system total)that will be graphed.

My graph needs to show an "OnChangeOf" of @ToDate and a "Showval" of all six AR Day calculations, something I have no trouble doing when not dealing with a print-time situation. The goal is a line graph with months on the x axis and different color lines depicted the AR Days per month for each hospital.

From what I have read, there needs to be a full calculation on what is being graphed, laid out in a formula on the main page and, then, a subreport that contains a formula each for the "OnChangeOf" and "@showval" pieces of the graph with the graph also displayed on the subreport.

 

[mdcson]

I have tried on my own but to no avail to get this graph to work. Does somebody have an idea?