1 Reply Latest reply on Apr 19, 2017 7:51 AM by philmodjunk

    Report that sorts a long list by payment source with subtotals?




      We're making a database of all royalty payments from the last 15 years, and although its going well, we've hit a bump which we can't work out how to sort.


      1 track of music can have multiple payment sources, and at the moment we can search for the track and get a grand total for how much it has earned. What we'd like however is to see how much each payment source has earned towards that grand total. ie:



      Payment source 1 total - £20

      Payment source 2 total - £50


      Payment source n total - £100

      Grand Total = £170


      At the moment we just have the grand total.


      The payment sources are part of a value list which is displayed as a drop down list - we want to be able to create totals for each different payment source on the value list for each track.


      How do we do that? I've taken an example screenshot and can take more to give the relevant info. For this track there are only 2 payment sources, but as you can see they are made up of lots of smaller payments over time.


      Thanks very much!



        • 1. Re: Report that sorts a long list by payment source with subtotals?

          What you are describing is called a summary report.


          Use part set up to add a sub summary part to your layout "when sorted by" your payment source field. You can set this to appear above or below the body layout part. If you then put the summary field that computes your grand total into this sub summary part, it will compute a sub total based on just the records grouped above or below your sub summary part. The records must be sorted by payment source or the sub summary part is not visible.


          As a test, sort your records by track and then by payment source to group your records by track first, then by payment source. You should see the tracks that make up each payment source group and the total computed from them. Once you have this working, you can go back into layout mode and delete the body layout part so that you now only get one row of information for each payment source for a given track.