using a dynamic array

[amoroney]

I'm using crystal 8.5 and ODBC.

I'm just learning about arrays so bear with me.

I'm working on a productivity report where I'm trying to list certain statistics such as much billable service each staff provided in a user defined reporting period. How many and which staff appear in the report is variable according to the date parameters selected.

simplified Example, data in Report footer:
MHSMin MHSUnits AverMHSmin/unitperday $MHSAmount
Staff A:
Staff B:
Staff C:
ETC, ETC:

Because of the formulas I have to use to calculate the results I want for some of my columns I am unable to use a crosstab report.

I had to group the following way
Group 1: client id(who was served)
Group 2: date of service

I've been able to get the results I want by creating running totals and variables for each staff but this causes the following issues:
1) I have to continuously maintain the report when ever there is staff turnover, very cumbersome
2) Sometimes staff are displayed in the report when there is no data to report on them (eg-they weren't working here for the selected dates)
3) I have recently been asked to add about 15 more columns of information to display per staff

Example of one of the columns "MHSUnits": We bill in units. Units are calculated by adding the minutes of service provided to that client by all staff and then using a minute to unit conversion scale. However, management wants to see how many units each staff provided.

I am able to do this by the following cumbersome method:
1) Creating a Running Total {#RTotDurDaystafflastname} each staff that sums the minutes provided to each cliet each day when provider ID = Staff A
2)Creating a Sum of all the minutes provided to each consumer by all staff
3) Creating a formula {@UnitsperDay} to convert the total minutes per day by all staff into units per client per day.
4)Creating a formula for each staff{@UnitCreditstafflastname} to get unit credit per client per day per staff.

{#RTotDurDaystafflastname}/ Sum ({@Duration}, {staff_tx_history.date_of_service}, "daily") * {@UnitsperDay}
//{@Duration} converts string value into a number. //Calculates percentage of how many of the total minutes a specific staff provided and then multiples by the total units obtained to give "unit credit"

5) Creating a set of variables Per staff to accumulate, reset, and display Unit credit per staff.

After reading about arrays and trying to figure this out, I am guessing that I could use a dynamic array to simplify this and also save mass amounts of time in creating the addition summary columns requested. Would an array allow me to only have one formula equivalent to{#RTotDurDaystafflastname} and one set of variables to accumate all the unit credit for all staff at once? Or is there some easier way of doing this? Help!

 

[synapsevampire]

Since you're new to Arrays, but seem to be a good coder, why not post example data and expected output and allow for other ideas of how to approach this?

And why not also group by the staff?

You appear to show what you want as the final output as in:

simplified Example, data in Report footer:
MHSMin MHSUnits AverMHSmin/unitperday $MHSAmount
Staff A:
Staff B:
Staff C:
ETC, ETC:

But you neglect to show the actual data that will be used to produce the output.

-k

 

[amoroney]

Thanks for your response.
I am unable to group by staff because of how we need to bill. Basically, we provide services to client and then bill the client's insurance for those services. We must submit our billing to for each client insurance in units. How to calculate units: All the minutes provided by any staff in a given day are added-lets say Staff A provides client X 30 mins of care and Staff B provides 60 minutes of care for a total of 90 minutes for that day. Unit conversion:
if Sum ({@Duration}, {staff_tx_history.date_of_service}, "daily") in (0 to 59) then 0 else
if Sum ({@Duration}, {staff_tx_history.date_of_service}, "daily") in (60 to 179) then 1 else
if Sum ({@Duration}, {staff_tx_history.date_of_service}, "daily") in (180 to 299)then 2 else
if Sum ({@Duration}, {staff_tx_history.date_of_service}, "daily") in (300 to 419) then 3 else
if Sum ({@Duration}, {staff_tx_history.date_of_service}, "daily") in (420 to 1440) then 4
So, 90 minutes = 1 unit
Since units must be calculated per client per day and more than one staff may be responsible for provide mins towards a unit, I don't think grouping by staff would work.

An important part of what the powers that be want to look at is how many units each staff is providing, thus my "cumbersome method" that I descibed above.

Sample data for date parameters 1/1/06 to 1/31/06:
Client X
1/8/06
30minutes Staff A
60minutes Staff B
//"unit credit" calculate as previous posted, would be .33 units for Staff A and .67 units credited to Staff B
1/15/06
90min Staff A
90min Staff B
//180min=2 unit, Staff A gets 1 and Staff b gets 1
Client Y
1/4/06
60min Staff A
1/21/06
60min Staff B

sample output in report footer:
MHSMin MHSUnits AverMHSmin/unit $AmountProduced
Staff A: 180 2.33 77.25 $193.39
Staff B: 210 2.67 78.65 $221.61
//Amount Produced would be {@MHSAmount} where units per staff * $83 per unit