CR11 - Need help with sum for complex scenario

[ReportDr00]

Hello

I would like to do the sum of units for each service based in which category they fall in

I have service table which stores all the services that client received.
I have billing table which stores all billing info

There are three categories:

first: 3rd Party
Is services billed to 3rd party then units of those service records are sumed

second: Medicaid
If services are billed to Medicaid then sum the units of those service records along with those service records that are not billed to anyone and excluding those one that are billed to 3rd party

third: client pay
If none of the services are billed to anyone on a day then sum the units of these services

For example if client has 5 services on a given day and two of which are billed to 3rd party then this should fall into 3rd party category and one service is billed to Medicaid and other two are not billed then all three fall into Medicaid category

If client has 3 services and none of them are billed then these fall into client pay category

If client has 4 services and 2 are billed to 3rd party and 2 are not billed, then 2 that are billed to 3rd party fall in 3rd party category and other 2 fall in client pay category

user will input service start date and service end date and report will return only those service records that fall with in the range.

sum of units for each service
service 3rd Party Medicaid ClientPay
date
01/01/05 2 3
05/03/05 2
07/01/05 2
09/10/05 2


Really appreciate your help

Armani

 

[Madawc]

I think you need to group by date and then do running totals within those groups. Try it and ask for more help if you still have trouble.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[BobHartung]

What are the columns of the Service and Billing tables?

 

[ReportDr00]

Here is what i have in the report so far

11/1/2006 (service date)
John, doe (client name)

Service Rec Service Units Billing
Id
1,854 1234 16.00
1,855 4560 1.00 Medicaid

11/2/2006
Joe, smith
1,856 2222 1.00
1,857 4422 1.00

11/3/2006
Baby, joe
1,859 2221 1.00 3rd party
1,860 2220 1.00 3rd party
11/12/2006
tough, one
1,885 3322 1.00 3rd party
1,886 6789 1.00 3rd party
Green, john
1,887 9990 1.00
1,888 1122 1.00

I need like this below

Service 3rd party Medicaid Client

1234 16
4560 1
2222 1
4422 1
2221 1
2220 1
3322 1

 

[ReportDr00]

If i could tag the rows with appropriate billing based on the rules then i can have running total in footer to do the calculation based on billing with each record

below is what i have now


11/1/2006 (service date)
John, doe (client name)

Service Rec Service Units Billing
Id
1,854 1234 16.00
1,855 4560 1.00 Medicaid

11/2/2006
Joe, smith
1,856 2222 1.00
1,857 4422 1.00

11/3/2006
Baby, joe
1,859 2221 1.00 3rd party
1,860 2220 1.00 3rd party
11/12/2006
tough, one
1,885 3322 1.00 3rd party
1,886 6789 1.00 3rd party
Green, john
1,887 9990 1.00
1,888 1122 1.00


If i know how to tag the records with app billing then i could take care of the rest
This is what i want


11/1/2006 (service date)
John, doe (client name)

Service Rec Service Units Billing
Id
1,854 1234 16.00 Medicaid
1,855 4560 1.00 Medicaid

11/2/2006
Joe, smith
1,856 2222 1.00 Client
1,857 4422 1.00 Client
11/3/2006
Baby, joe
1,859 2221 1.00 3rd party
1,860 2220 1.00 3rd party
11/12/2006
tough, one
1,885 3322 1.00 3rd party
1,886 6789 1.00 3rd party
Green, john
1,887 9990 1.00 Client
1,888 1122 1.00 Client


Really appreciate any suggestions

 

[BobHartung]

Create the following formulas to return the units for each billing category:
{@3rd_Party_Units}:
IF {Billing} = "3rd Party" THEN {Units} ELSE 0

{@Medicaid_Units}:
IF {Billing} = "Medicaid" OR {Billing} = "" THEN {Units} ELSE 0

{@Client_Units}:
IF {Billing} = "" THEN {Units} ELSE 0


Place this formula in the group header for each date. This is to initialize a variable to determine if blank records are medicaid or client.
{@Initialize_IsMedicaid}:
BooleanVar IsMedicaid;
IsMedicaid := FALSE

Create this formula to determine if blank records are medicaid or client.
{@IsMedicaid}:
BooleanVar IsMedicaid;
IF {Billing} = "Medicaid" THEN IsMedicaid := TRUE


Simply sum {@3rd_Party_Units} for the 3rd Party total.

For Medicaid and Client totals create the following formula:
{@Sum_Medicaid_Client}:
BooleanVar IsMedicaid;
IF IsMedicaid THEN SUM({@Medicaid_Units}, {Date_Field}) ELSE SUM({@Client_Units}, {Date_Field})


I hope this atleast points you in the right direction.