Calculate total the commission

[Former Member]

Hi all,

I have a CR that populate the DataSet  for sale data with columns as following:

Item | condition | cost

1 | used | 10.00

2 | new | 450.00

3 | refurbished | 200.00

4 | used | 45.00

5 | used | 600.00

6 | new | 65.00

7 | new | 400.00

8 | refurbished | 4500.00

etc...

where sale commission percents such new: 20%; used: 5% and refurbished: 10%.

I like to add a new column name: commission amount is the product of Cost and above percent range, finally count the total of commission amounts in the report. I  am not sure how to code in new formula in order to group by in Condition column, add sub total for it, then grand total. Please help me to get a solution. I really appreciate it. Thanks in advance.

[DellSC]

Here's what I would do:

1.  Group by the Condition column.

2.  Create a formula that will give the commission percent.  It will look like this (use the actual condition field name):

{@CommissionPct}

     switch (

          {condition field} = 'new', 0.2,

          {condition field} = 'used, 0.05,

          {condition field} = 'refurbished', 0.1

          true, 0

     )

3.  Create another formula to calculate the commission:

{@CommissionAmt}

     {cost field} * {@CommissionPct}

4.  In the group footer section, place a summary (Insert>>Summary menu or click on the Summary button) that sums {@CommissionAmt} at the Condition group level.

5.  In the report footer section, place a summary that sums {@CommissionAmt} at the Grand Total level.

-Dell

[former_member11696]

Moved to Report Designer forum