Duplicate Problem 1

[jdaily]

Hi all!

Running CR8.0 and accessing MS Access DB through ODBC.

I have a duplicate problem here is the data and then I will explain the issue.

Grouped by: Denial Code

denial_code
T3000

Detail Section:
Acct_Num Total_Charges Acct_Bal
D1234567890 $5,000.00 $500.00
D0789456123 $3,000.00 $100.00

T5000

D1234567890 $5,000.00 $500.00
D9999999990 $2,500.00 $50.00
D5555555555 $1,250.00 $300.00

I have a group footer that I want to keep because it totals for each denial code and I would like to look at those numbers.

My problem is with the Report Footer and the totals:
Report Footer:
count of Acct_Num: 5
This should be 4. I also have sum totals for the total_charges and acct_bal which will add the same as the count, basicaly incorrectly.

Is there something that I can do to the report footer that can do a distinct count?

Thanks in advance!

John

 

[Cretin]

On the tool bar at top insert then summary. A dialogue box will come up. Choose acct number as the field and in the drop down below it choose distinct count then place that field in the report footer.

Cretin

 

[jdaily]

But how do I get it to do the same with the sums for total charges and acct balance?

 

[lbass]

There is a way of doing this using variables, where you only add amounts if the account number has not appeared before, but I think you would run into the 254-character limit in 8.0.

So, I think the easiest method would be to create an unlinked subreport in the report footer that eliminates the denial code group so that you don't have duplicates. Then you can insert grand totals on the total charges and balance fields--unless you have some other duplicates. If you have other duplicates, you could order the records in the subreport by account number and whatever other field is causing duplicates and then use running totals that sum on change of field.

-LB

 

[jdaily]

Is there a way that I can link it so that I can use the same date criteria? I have the reports so they print with the previous months data and looking at specific denial_codes only.

I guess I could recreate the main report as a subreport and do a distinct count on the database? Would that work?

John

 

[jdaily]

I think I got it. I went into the field explorer and did a distinct count on the acct_num.

Under evaluate I did "For each record"

Under Reset I checked "Never"

-------------------

When I did the same thing on total_charges and the other sum fields I did a sum.

Under evaluate I checked "On Change of Field" and selected the acct_num field.

Under Reset I checked "Never".

This actually gave me the totals I was supposed to have.

Thank you everyone for your help!

John

 

[lbass]

If you used your running total on your example above, the amounts for D1234567890 would be added twice--you can see this by placing the running total field in the details section so you can see how it is incrementing. It will eliminate double counts of account numbers that are sequential though.

The running total method will only work if your fields are sorted by account number without your group on denial code. So you could use the running total method within a subreport for the group footer. Instead of linking on the fields you are using in the main report, just copy the records selection formula from the main report into the subreport.

-LB

 

[jdaily]

What about using a subreport in the Report Footer for the grand totals? The grand totals are what I really need as distinct instead of the group footers.

I have not used the subreport for Crystal yet but will give it a try. Do I set it up just like my original report? I don't understand what you mean by "linking the fields I am using in the main report"? I do understand what you mean about copying the records selection formula to the subreport.

Thanks,

John

 

[lbass]

Sorry, I meant report footer. Set up the subreport the same way EXCEPT do not use the denial code group. Instead, group by acct number and then insert grand totals on the fields you want to summarize in your report footer. You would suppress in the subreport all sections except the report footer and then place the subreport in the report footer of the main report. Copy the record selection statement from the main report to the subreport record selection formula area, and do not link the reports.

-LB

 

[jdaily]

Worked like a charm!

Thanks lbass!