Help Please- Report Calculation

[DCH]

New sales reps at my company are allotted a “draw accountâ€, (effectively a
loan) to supplement their base salary. The reps sell contracts which pay a
monthly residual commission. Until the “draw accountâ€, is paid off our reps
do not get commission. The “draw accountâ€, is in place as it takes about a
year to build up enough monthly recurring commission to meet the living needs
of our reps. That said….

I have a report (grouped monthly) which totals all rep’s commissions. I can
easily get the report to subtract the first month from the “draw account†ex.
June(T= (Payout-Draw)), however, can’t figure out how to get the next month
to calculate July(Payout-T) instead of propagating each months calculation on
the original draw number.

Any help would be greatly appreciated.
Many thanks,
David.

 

[Wayne-I-M]

Hi

In your report create 2 more "groupings" (yearly and all-time commision).
As you already have monthly you will now be able to run the report showing
the full spectrum of payments to each agent.

The yearly group could be YTD or Y since employment started - or you could
conditionaly format this to show both in the same group.

If you used your loan - commision formula in the all-time group footer you
could show the date at which commision payments started. I think this may be
useful for an unfiltered report to show the average time it takes to starts
the payments (loan paid off).

Now after I hve said all this I really think that you would be able to
better report on the payments by doing all the forumulas in a query and
subquery (the sub being the yearly payments). A query would enable you to
produce more meaningful reports.

Hope this helps

 

[Ken Sheridan]

David:

Try this:

1. In the Declarations area of the report's module declare a variable of
currency data type:

Dim curT as Currency

2. In the Print event procedure of the report header initialize the
variable to the value of Draw:

curT = Draw

3. In the Print event procedure of whichever group header or footer you are
doing the calculation in assign curT a value of Payout less curT and assign
this value to the control T. Test for the PrintCount property being 1 to
avoid any double counting:

If PrintCount = 1 Then
curT = Payout – curT
T = curT
End If

Note that if the report is being viewed in Print Preview the Print event for
a section will not fire if the page containing the section is skipped over by
using the navigation bar. Paging down through the pages or sending the whole
report to a printer should be fine however. Using the Format event
procedures rather than the print event procedures gets around this, but is
less easy to predict as the Format event for a section can fire more than
once in the same iteration through the report while still having a PrintCount
of 1. Any redundant calculation can be undone in the Retreat event, but it
can be a little tricky. The Print event procedure on the other hand is easy
to use provided you are aware of the above caveat.

Ken Sheridan
Stafford, England