+ Start a Discussion
Debbie ChrysochouDebbie Chrysochou 

Tricky Report!! by payment for each account sorted by month and year

Hello,

This is a tricky one. So my problem is (All of this due to my poor knowledge of SF as a newbie). We do not have an accounting function on SF but I manipulated Quotes in a way that when I create the template to be used as a Payment Receipt as well. So I created a section with payments so everytime a payment is made I input the amount and the date of the payment received :
User-added image

The month and the year are produced automatically through formulas.
My problem is that when I want a report for the payments made for each account, I would like to get something like
User-added image

So the year of wedding comes out of the account of each client automatically by formula. The money taken in year is from the first picture the year received field. The booking ref, the hotel, the wedding date, the brand and the names are taken from the account details so no problem implementing them. The amount paid comes from the payments of the first picture. Also balance due comes from the totals of the Quote as a formula.

My problem is when I try to create the report through SF the result is :

User-added image
As you can see for each account it has only a line with all the payments. So an account has 30 payments that will create a really huge line. 
Is there a way to get a result like the 2nd picture of excel look alike?
It might be the whole set up as well that is wrong so any suggestions will be helpfull.

( A suggestion from another SF developer was: 
1. Create a Custom Object called Quote Payment (Name - Autonumber)
2. Create an MD relationship field on Quote Payment with Quote
3. Create Payment Date field on Quote Payment as Mandatory.
4. Month and Year again formulae
5. Additionally you can also have a "Payment Made" field that called be summed up at Quote level as roll up summary.)

If I use this suggestion how do I get the report where the payments will linked with the accounts as it shows on the 2nd picture. And should I create all the payment fields that I created in the 1st picture?