Suppress Duplicates

[sorchard2000]

I am trying to make a simple report in Crystal 8.5 (SQL Server) which currently shows :

Charge charge # Is Credit
Charge Description 1 4761111 0
Charge Description 2 4762222 0
Charge Description 3 4762222 1

I need the report to remove the credit and the original charge description that the credit references to and then do a count. So the final report would look like:

Charge charge # Is Credit
Charge Description 1 4761111 0
-----------------------------------------------
(count of 4761111) 1
This report would be grouped by month (Jan, Feb...)

thanks in advance for your help!

sorchard2000

 

[synapsevampire]

Rather than show what the unwanted report looks like, show example data, and the expected result.

Your final shows the credit and the descrioption, yet your text says that it must remove it...

How are you grouping the data? To create a group use Insert->Group and select the date, and then change the options to each month.

The output doesn't show any dates yet you state it should be grouped by months...

In general, a post should include technical information such as:

Crystal version
Database/connectivity used
Example data
Expected output

Then if you're compelled, add in whatever text descriptions you feel required.

-k

 

[sorchard2000]

OK, sorry, I'll try to explain better:

Crystal version: 8.5
Database/connectivity used: SQL Server
This is a report dealing with patient charges in a hospital.
Example data:

Charge charge # Is Credit

Charge Description 1 4761111 0
Charge Description 2 4762222 0
Charge Description 3 4762222 1
Charge Description 4 4765555 0

Grouped by 1) patient case ID number and 2) date (month)
Note that Charge Description 3 with charge # 4762222 is a credit for Charge Description 2 with charge # 4762222.

Expected output:
I need the report to remove the credit and the original charge description that the credit references to and then do a final count the total number of charge numbers. So the final report would look like:

Charge charge # Is Credit
Charge Description 1 4761111 0
Charge Description 4 4765555 0
-----------------------------------------------
(count of charge #) 2

thanks in advance for your help!

sorchard2000

 

[synapsevampire]

OK, so you want to limit the output to those that only have 1 item per charge. Would this be one item per month, or throughout the charge?

If you reference a date, please show how the date is applicable.

In the Report->Select Formula->Group use:

Count({table.charge},{table.charge}) = 1

This will now only display those with 1 occurence.

To count them, try a formula in the charge group footer such as:

whileprintingrecords;
numbervar TotCharges;
If previous({table.charge}) <> {table.charge} then
TotCharges:=TotCharges+1

Then in the report footer use:

numbervar TotCharges;

Looks about right.

-k

 

[lbass]

If you group on {table.chargeno}, then you could go to report->edit selection formula->GROUP and enter:

sum({table.IsCredit},{table.chargeno}) = 0

This should display the correct records. Then you would have to use a running total to get the count. Using the running total expert, you would select {table.charge}, count, evaluate for each record, reset on change of group. Use the date group to reset the running total at the date group level, the patient group for the running total at the patient level, and reset never for the third running total at the report level.

-LB

 

[sorchard2000]

Thanks lbass! That's exactly what I did and it works!

thanks again!