AnsweredAssumed Answered

Summary cross tab report help requested

Question asked by alanfink@comcast.net on May 6, 2012
Latest reply on May 16, 2012 by alanfink@comcast.net

Title

Summary cross tab report help requested

Post

I have the need to present loan payment data with payments horizontally by term, and grouped by type of loan and downpayment.

My data is structured in a table called "Proposal Scenarios", as one record for each combination of Type/Down Payment/Term/Monthly Payment/and Rate. 

 

I took a stab at using summary fields to consolidate, and was partially successful. Problem I have is when there is more than 1 record for same Type of purchase, Down Payment, term, AND the rate is different.

I set a summary field for Pmt 24, Pmt 36, Pmt 48, Pmt 60. Of course when there are duplicate records for a Pmt field (i.e. see Pmt 60 below) and summary field is set to "Total" it will sum the Pmt total for those records and thats not desired. Not a problem if there are not duplicate records.

I need to show rates for each grouping as well.


Table Record Data:

Type Down Payment Term Pmt 24 Pmt 36 Pmt 48 Pmt 60 Rate
Purchase 3500 24 1495.92       4.49
Purchase 3500 36   950.34     0
Purchase 3500 48     741.33   1.9
Purchase 3500 60       613.97 2.9
Purchase 3500 60       623.13 3.49



SUMMARY REPORT DESIRED RESULT(sorted by Type and Down Payment)

Type Down Payment   24 36 48 60  
Purchase 3500   1495.92 950.34 741.33 613.97  
   
4.49
Rate
0
Rate
1.9
Rate
2.9
Rate
 
            623.13  
            3.49
Rate
 
               



I cant figure out a way to do the consolidation, and hope someone might have some ideas for me!



Thanks in advance!!!


Alan

Report.png

Outcomes