Selecting on Destinct Records

[terenceb]

Hi,

I am using Crystal 8.5 pulling data fron MS SQL table. I have not worked much with SQL so I am open to suggestions. My problem is that I am totalling charges and these tables usually have multiple interations of data because of how the charges break out to different payors. However, even though the charge breaks out to different payors there is a field in the table called Unique Service ID so that I know if I see the same Unique Service ID for the same charge that and the same payor that only one of them is correct. I have implemented this formula to get the correct charge.

whileprintingrecords;

shared numbervar totalcharges;

if not onfirstrecord and {SP_Bill_Charges;1.UNIQUE_SERVICE_ID} <> next({SP_Bill_Charges;1.UNIQUE_SERVICE_ID})
then totalcharges:= totalcharges + {SP_Bill_Charges;1.amt_charged};

totalcharges;

However, it appears that the data does not fall in order. So that the next field it checks may not have the same UNIQUE_SERVICE_ID it may appear later in the table.

Please find list an example of the table data

Unique SRv Date of Serv Charge Service Code
59170.01 01/01/2003 -37.00 0.00 BF1
59170.01 01/01/2003 -37.00 BF1
59175.01 01/06/2003 148.50 9500
59175.01 01/06/2003 148.50 9500
59177.01 01/08/2003 150.00 9500
59177.01 01/08/2003 150.00 9500

 

[lbass]

Do you have the option of sorting on unique ID? If not, we need an idea of the overall report structure, including groups, with a sample of data that shows unique IDs that cross groups.

-LB

 

[terenceb]

When you say sort on Unique ID, do mean group on Unique ID. No, I have'nt tried that. But I will.

 

[lbass]

To sort, go to report->sort records and choose the unique ID field.

Or, you can insert a group on the field and drag the fields into the group header or footer. Then you could go to the field explorer and insert a running total (yours is a manual version) that evaluates on change of group.

-LB