Crystal XI - Need help with a formula

[Lightbug3]

Hi All!

I am brand new to Crystal and I am having trouble with the report I need to create. The report should show Member's total claim cost and threshold, and the total claim cost and threshold per month.

MemberID PersonCode Month TotalPaid Threshold(250.00)
123456 1 1/2006 100.00 100.00
123456 1 2/2006 200.00 150.00
123456 1 3/2006 50.00 0
123456 2 1/2006 500.00 250.00
123456 2 3/2006 150.00 0
456123 1 1/2006 30.00 30.00
456123 1 2/2006 200.00 200.00

Total 1/2006 630.00 380.00
2/2006 400.00 350.00
3/2006 200.00 0

The problem I am having is on the total threshold per month. The only way I could find to get my Threshold value was to make a running total variable to sum up each member's TotalPaid -- TotalCost. I use this variable in my threshold formula.

currencyVar PreviousMonths := {#TotalCost} - {viewRDSCostReporting.TotalPaid};

if PreviousMonths >= 250 then
0
else
if {#TotalCost} >= 250 then
250 - PreviousMonths
else
{viewRDSCostReporting.TotalPaid}

However, now I am unable to sum the Threshold variable.

My question is, can anyone see of a better way to get my threshold numbers... so I can sum them?

Thanks.
Danielle

 

[IdoMillet]

Looks like you are already using views, so one option is to create a view that shows totals by member id and month. Join that view to the detail table on MemberID and month.

Now each detail record can provide the total for the month for that member.

An even easier approach would be to include in the view a computed column that return the next month. Now, if you join the detail's month to the "Next_Month" in the view, you get the total from the previous month...

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

You could do this with a variable. Change {@threshold} to:

currencyVar PreviousMonths := {#TotalCost} - {viewRDSCostReporting.TotalPaid};
currencyvar thresh;
currencyvar sumthreshmo1;
currencyvar sumthreshmo2;
currencyvar sumthreshmo3;

if PreviousMonths >= 250 then
thresh := 0
else
if {#TotalCost} >= 250 then
thresh := 250 - PreviousMonths
else
thresh := {viewRDSCostReporting.TotalPaid};
if month({table.date}) = 1 then
sumthreshmo1 := sumthreshmo1 + thresh;
if month({table.date}) = 2 then
sumthreshmo2 := sumthreshmo2 + thresh;
if month({table.date}) = 3 then
sumthreshmo3 := sumthreshmo3 + thresh;
thresh

Then in the report footer, use display formulas like:

whileprintingrecords;
currencyvar sumthreshmo1; //or sumthreshmo2, etc.

If you are doing this at a group level, you would need a reset formula for the group header:

whileprintingrecords;
currencyvar thresh := 0;
currencyvar sumthreshmo1 := 0;
currencyvar sumthreshmo2 := 0;
currencyvar sumthreshmo3 := 0;

-LB

 

[Lightbug3]

Thank you very much LB! I am still trying to understand all that crystal can do with formulas. This has been very helpful!

Thanks.
Danielle