Details about this thread
  • State Not Answered
  • Replies 6 replies
  • Subscribers 315 subscribers
  • Views 2207 views
  • Users 0 members are here
Related content from around SugarClub

Report to Show Total Sales per person

Mandi Lytle

I have created a Total Purchases field on the Account cards, which is a calculated value based off of each purchase order amount field that is entered. I am struggling to create a report which shows me the username of who the account is assigned to and the Total purchases for all of their accounts in a certain time frame. I thought I was close, as I made a Matrix report with the display summaries as follows. 

The Total Purchases is way higher than it should be- Its like it is multiplying the total by how many individual purchases were entered. Maybe that is what the SUM is supposed to do in this instance.  I tried using MAX: Total Purchases, but I can't seem to get this right.  I have tried different types of reports as well, but do not seem to get what I am looking for. Any ideas?

Parents
  • 0

    Hi Mandi,

    The issue is you are reporting across two modules, the accounts module and purchases so it will be giving you duplicate data. 

    You are better off just reporting on the purchases module  filtering by your date periods on a run time and grouping by the assigned user and account record related to the actual purchases.

    Thanks

    Joe

  • 0 in reply to Joe Bushnell

    Thanks Joe, I did try that but all purchases are uploaded by the admin not manually added by the assigned to person. So when I put 'assigned to' in the purchases module, it only pulls admin. The total purchases field is only found on the Accounts as it was a custom field that I created to calculate all order amounts from each purchase entered (in the purchase module) into one field to show the total for that account.  I am looking for a report that will just pull the exact amount that is in that field (from the account module), who the account is assigned to and then when the orders were added (from the purchase module). Even when I start with the purchase module and try to pull a sum of all of the order amounts, it is still duplicating. 

  • 0 in reply to Mandi Lytle

    Hi  , are you able to provide a screenshot of how your report is set up please? 

  • 0 in reply to Nord Herida

    Thank you so much for looking at this. I have tried about 5 different ways in both the Accounts and Purchases module with all different types of reports. This one seems to be the closest. 

    Summation Report>Account Module.  

    Then on the last page I have both of these check marked

    Optional Related Modules: 

    Accounts > Assigned to User
    Accounts > Purchases

    Results show: $325.

      I have 2 accounts assigned to me with purchases (1st count shows this). Three purchases total (2nd count shows this)

    • 1 account I added an entry of $100.00 and a second entry of $50.00
    •  My 2nd assigned account I added 1 entry of $25.00

    I would expect to see the Total Purchases on my assigned accounts to be $175.00. 

Reply
  • 0 in reply to Nord Herida

    Thank you so much for looking at this. I have tried about 5 different ways in both the Accounts and Purchases module with all different types of reports. This one seems to be the closest. 

    Summation Report>Account Module.  

    Then on the last page I have both of these check marked

    Optional Related Modules: 

    Accounts > Assigned to User
    Accounts > Purchases

    Results show: $325.

      I have 2 accounts assigned to me with purchases (1st count shows this). Three purchases total (2nd count shows this)

    • 1 account I added an entry of $100.00 and a second entry of $50.00
    •  My 2nd assigned account I added 1 entry of $25.00

    I would expect to see the Total Purchases on my assigned accounts to be $175.00. 

Children
  • 0 in reply to Mandi Lytle

       I received your reply 

    Hi Mandl, from what I'm reading, it sounds like your calculated field does the SUM for you, so you would just need to add the calculated field into the display column section.

    You could do Summation Report with Details and in the Define Group By you can do the assigned to user so it groups all the accounts by assigned to and in the Choose Display Columns you can select your calculated field."

    However, looking at the above screen shots that I provided, I have this set up this way already.  The only option to display my calculated field is to either choose Avg, Sum, min or max- which is duplicating values.