Many to One

[kthacher]

Using Crystal 8.5

I'm attempting to get rid of some running totals (per a previous thread). I have two tables, Revenue and Budget. The report is to show total Revenue $, total Budget $, and variance for each account/sub-account combination. The problem is that there are many revenue records per account but only one budget record. The budget field is being repeated for each revenue record so my total budget for each account is way too high.

Revenue Table:
Acct Sub Rev
123 500 1,000
123 000 3,000
123 000 2,000
123 000 4,000
123 500 6,000
456 000 1,000
456 000 2,000
456 500 4,000

Budget Table:
Acct Sub Budget
123 000 8,000
123 500 5,000
456 000 6,000
456 500 4,000

The report should show:
Acct Sub Rev Budget Var
123 000 9,000 8,000 1,000
123 500 7,000 5,000 2,000
GF for 123 16,000 13,000 3,000

456 000 3,000 6,000 -3,000
456 500 4,000 4,000 0
GF for 456 7,000 10,000 -3,000

The report is actually showing a budget of 24,000 instead of 8,000 for Acct 123 Sub 000 because it has three records in the Revenue table. All other budgets are similarly duplicated.

How do I stop Crystal from retrieving a Budget from the budget table for each Revenue record? Or else how to I stop it from adding the budget more than once to the GF totals?

Thanks in advance for any help.

 

[lbass]

The solution has already been presented to you in a different thread. You need to group on Acct and Sub and then insert a maximum on the budget figure at the sub level. If the issue is that you don't know how then to summarize the maximums, you would do it using a variable:

//{@reset} for the acct group header:
whileprintingrecords;
numbervar sumbudg := 0;

//{@accum} to be placed in the group header or footer for sub:
whileprintingrecords;
numbervar sumbudg := sumbudg + maximum({budget.budget},{table.sub});

//{@display} for the acct group footer:
whileprintingrecords;
numbervar sumbudg;

-LB

 

[kthacher]

You are correct, I don't know how to sum the maximums. And I don't know how to use variables very well either.

So I'm guessing I'd make the three new formulas you show (creating them in the regular formula window?) And then just place them on the report as new fields?

Will try that tomorrow. Thanks.

 

[lbass]

Yes, create the three formulas (field explorer->formula->new) and then place them in the report in the identified sections. You can suppress the first two. The last one will display the sum of the budget.

-LB

 

[kthacher]

Thanks. So far so good.

Now I've discovered a new problem with the Maximum formulas. Some of the Account/sub-account combinations have a negative budget (credits to the customer). So the Maximum returns 0 instead of (21) for example.

I think I can create a new formula for the group footers instead of using the (Insert->Summary) menu. I'd do an If Then formula to test if the budget is greater than zero.

But I don't know what to do with the @accum formula you gave me. Can I imbed an If/Then in there somehow?

whileprintingrecords;
numbervar sumbudg := IF maximum ({budget.budget},{table.sub})>0, then sumbudg + maximum({budget.budget},{table.sub}) ELSE sumbudg + minimum({budget.budget},{table.sub})

 

[kthacher]

Sorry, I answered my own question through trial and error. The above did work after some tweaking.

 

[kthacher]

I am resurrecting this thread because I have a new problem. Thank you llbass for your previous help. It worked perfectly for my monthly report.

New problem is that I need a year-to-date version. Also there is an additional grouping: Sub-account, account, and now Customer Type which would be the total of the two group footers in the example at the top (the total of accounts 123 + 456). Then there would be more accounts and another GF for that Customer Type, then finally a Grand Total.

In the monthly version, I was able to sum the budget values (which are repeated due to miltiple revenue records) by using your @reset, @accum, and @display formulas and getting the maximum or minimum value to accumulate in the variable. Max or min is needed because budget may be positive or negative.

But with YTD data selected, there are now multiple budgets per sub-account, one for each month. I need to accumulate the budget value for each month. So instead of the max/min for each sub-account, I need to accumulate the max/min for each sub-account for each month. And a total for sub-account, account, customer type and grand total.

Do I need four sets of @reset, @accum, and @display in order to do this? Or is there a different way?