Skip to Content
avatar image
Former Member

Calculate total the commission

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 26, 2015 at 01:56 PM

    Moved to Report Designer forum

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 26, 2015 at 02:00 PM

    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

    Add comment
    10|10000 characters needed characters exceeded