formula HELP

[msay]

I have a report designed using the Report designer in VB. In the Details sec. I have 3 fields; ado.salesman, ado.overRide, ado.mgrName. I also sub total on the ado.overRide amount. What I want to do is when ado.salesman has a manager (ado.mgrName) I want to take the sub total of ado.overRide and place it under or next to the manager (who by the way will also be a salesman). It might look something like this:

ado.salesman ado.mgrName ado.overRide
@overRideDuemgr sum of ado.overRide
I hope this isn't too confusing.
Mike Sayler

 

[lbass]

You could try grouping on {ado.mgrName}. Then insert a summary on {ado.overRide}. You can display the results either in the group header or footer, by dragging the group name and summary to the position you want.

-LB

 

[msay]

LB, That really won't accomplish what I'm tring to do. Here's an example of what a report might look like:

Salesman Mgr Name OverRide Commission Due
Joe Salesman Harry Peter $69.00 $500.00
_________________________________________________________

Harry Peter N/A N/A $250.00
OverRide Due $ 69.00
_________________________________________________________
Total Commission: $ 319.00

Does this make sense?

 

[Ngolem]

Salesman Mgr Name OverRide Commission Due
Joe Salesman Harry Peter $69.00 $500.00
_________________________________________________________

Harry Peter N/A N/A $250.00
OverRide Due $ 69.00
_________________________________________________________
Total Commission: $ 319.00

Does this make sense?

*********************************

Not quite...I used to be a sales manager too

What is the relationship between Joe Salesman and Harry Peter under the Salesman column??? And how is the commission in the upper line related to the the commission total?? $500 versus $319??



Jim Broadbent

 

[lbass]

Here's something that might work. First replace {table.MgrName} with a formula {@manager}:

if {table.MgrName} = "N/A" then "{table.Salesman} else {table.MgrName}

Group on {@manager}. The formula now ensures that the sales record for the manager will appear in the same group as the other salespeople that he supervises. Drag the groupname from the header to the footer and insert a summary on {Override}. Create a running total {#mgrsales}:
Select {Commission}, sum, evaluate based on a formula:

{@manager} = {table.Salesman}

Reset on change of group 1 ({@manager})

Place the running total {#mgrsales} in the group footer 1.

Create a formula {@totalmgrearnings}:

{#mgrsales} + sum({OverRide},{@manager})

You could place this in the same group footer section or create a group footer #1b if you want to format it differently.

-LB

 

[msay]

LB, You are on the right track with that but, when I try to create a running total {#mgrsales} using the formula; {@manager} = {table.salesman} I get the wrong results or nothing. But, I wrote the formula this way to test it and it works: {@manager} = "Harry Peter" Unfortunantly, I can't use it that way. Because there are several different manager possiblities.
MS

 

[lbass]

You substituted your own table field names for mine in the formula, right? I should have been using "ado" instead of "table". And you grouped on {@manager}?

Is it possible that {ado.mgrName} can be null or blank instead of saying "N/A"? Try this:

if isnull({ado.mgrName} or
{ado.mgrName} = "" or
{ado.mgrName} = " " or
{ado.mgrName} = "N/A" then {ado.salesman} else
{ado.mgrName}

Please let me know if this doesn't help.

-LB

 

[msay]

LB, I am substituting the correct table names. I already tried {ado.mgrName} = "" ( N/A will never be data.) I just used that as an example. The field will either be blank or contain a managers name. Back to my previous example:

Salesman Mgr Name OverRide Commission Due
Joe Salesman Harry Peter $69.00 $500.00
_________________________________________________________

Harry Peter $250.00
OverRide Due $ 69.00
_________________________________________________________
Total Commission: $ 319.00

Harry Peter is Joe Salesman's MGR. He also is a salesman who makes a commission. When Joe Salesman makes a sale an overRide is paid to his manager Harry Peter.($69.00) What I want to do is put that $69.00 under Harry Peter's commission due column and total the commission due Harry. I don't know if this makes a difference but the manager's name is be pulled from a different table. I do have both tables in my report. This one has me stumped!

 

[lbass]

I bet we're almost there. Did you try my last formula in its entirety, including the clause for isnull?

And did you group on {@manager}?

Do you have the tables linked with a left join from the salesman's table to the manager's table? This is the only way you'll get the salesmen who are also managers included in the report.

-LB

 

[msay]

LB,
I added this formula for {@manager}:
if isnull({ado_1.manager}) or
{ado_1.manager} = "" or
{ado_1.manager} = " " then {ado.salesman}
else
{ado_1.manager}
Works like a charm! Thank you so much for sticking with me!!
Mike Sayler