1 2 Previous Next 17 Replies Latest reply on May 16, 2012 12:52 PM by alanfink@comcast.net

    Summary cross tab report help requested

    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

        • 1. Re: Summary cross tab report help requested
          philmodjunk

          I've had to pass on this one for a while, as I know that such cross tab reports take a lot of time to walk someone through the process needed to produce the desired result. it's further complicated by the fact that the poster often discovers that they need to restructure their tables in order to get the desired result.

          Let's see if we can now get the ball rolling...

          On your last screen shot, is every row shown an individual record?

          And you want one row for each down payment and type combination...

          The typical cross tab report in Filemaker combines a list view of your data with one row (often filtered) portals to organize data usually found in different records into columns of the same row. In your case, though, I don't think we need those portals...

          For the layout part for your row you can use a sub summary part, "when sorted by" Down payment. For a sort order for your found set of records, sort by Type FIRST, then also sort by down payment. This will produce one row for every type and downpayment combination.

          Now use regular data fields for columns 1 and 2. For the other fields, use summary fields. As I understand your data from what you have here, that produces sub total groups for the sub summary part that consist of one record with data and the rest empty so that should work to combine your numbers into a single row.

          • 2. Re: Summary cross tab report help requested
            alanfink@comcast.net

            Phil, thanks for your thoughts/reply.  I had already created the sub summary parts to accompish the single row,  but I may have not been as clear as needed here on the problem.

            Summary fields for Monthly Payment work as long as there is not more than one record for a given sub summary part with identical Type/Term/Down Payment AND a different interest rate for that given term.  Since in that case, there would be more than one record for the given term. Depending on how the summary data field is set up (lets say Total), then the data is totaled for those cases and is not desired result.

            Here is sample data and report showing the result when summary field for Monthly Payment 60 is set to MIN.

            There are 2 records for Type=Purchase, Down Payment=0, and Term=60

            Of course it is showing the MIN amount and rate amoungst the 2 records ($510.14 at 1.9). If summary field is set to Total, then obviously will total the 2 results for that 60 month term (583.05+510.14) and show rate of last record.  Both not desired results.

            Can you advise the best way to accomplish showing results under those conditions.

            Thanks again, Alan

            Type Down Payment   24 36 48 60  
            Purchase 0   1313.13 906.82 704.20 583.05  
               Rate----->
            4.49 0 1.9

            7.29

             
                        510.14  
                        1.9  
               500   1290.61 891.27 692.12 501.39  
              Rate----->    7.29  7.29  7.29 1.9   
              1000   1268.09 875.71 680.04 492.64  
              Rate----->   7.29 7.29  7.29  1.9  



             

            Payment

            • 3. Re: Summary cross tab report help requested
              philmodjunk

              ...as long as there is not more than one record for a given sub summary part with identical Type/Term/Down Payment AND a different interest rate for that given term.

              The method still works, but you have to modify your sorting strategy to get groups where all the numerical values are zero save one per group.

              Make your sub summary part when sorted by Sell Rate (that's your interest rate?). Sort by Type, Term, Down Payment AND then by interest rate.

              • 4. Re: Summary cross tab report help requested
                alanfink@comcast.net

                Phil,

                Yes "Sell Rate" is the interest rate. Sorry for the confusion there.  Still can't get the sum summary part to work right.  When I created the sub summary part when sorted by Sell Rate, and sorted as you mentioned ( Type, Term, Down Payment, Sell Rate ) I am getting too many result records that are not grouping in same row.  I will take some screen shots of some trial results once I have tried various tests, and post them.  

                 

                My oringinal report had 2 sub summary parts

                1 when sorted by Type with 1 filed Type (description field)

                2 when sorted by Down Payment with all Payment and Rate fields for each term

                Thanks...Alan

                 

                • 5. Re: Summary cross tab report help requested
                  alanfink@comcast.net

                  Phil,

                   

                  Thanks to your help, I got close to my desired result, but not 100%.  Best I could accomplish was the following (see posts below).  I am now wondering if a portal solution is the best way to acomplish 100% of the layout results desired.

                  • 7. Re: Summary cross tab report help requested
                    alanfink@comcast.net

                    Report Sort

                    Had to move Sell Rate above Term

                    • 9. Re: Summary cross tab report help requested
                      philmodjunk

                      Can you explain what aspects of the last screen shot are not what you want?

                      • 10. Re: Summary cross tab report help requested
                        alanfink@comcast.net

                        Basically want fewer lines for same information.  Having sub summary part for Sell Rate produces to many lines.

                        Here is an example with sub summary parts of Type and Down Payment.  Data in sub summary part for Down Payment are summary fields for each Monthly Payment and Sell Rate set to Maximum.  This produces a single line with higest payments for each Term assoicated with the Sell Rate for that payment and term.  That works fine.

                        Now for any given Type and Down Payment, i want to add any additional matching payments and rates not equal to Maximum values above, and sorted in decending Sell Rate order.  

                        I have been trying to concieve a portal (8 individual portals, 1 for each Term 24 through 84) arrangement for these additional values, but cant get it right yet.

                        Perhaps thats the solution?

                        Heres what I would like the end product to be vs what I currently have:

                        • 11. Re: Summary cross tab report help requested
                          alanfink@comcast.net

                          Here is the table data fields used for the report.  Thanks for looking at this again!

                          • 12. Re: Summary cross tab report help requested
                            philmodjunk

                            I had to flip back and forth and look very carefully to see the issue, which is that you are combinging payments for different rates on the same row--I'd find that confusing myself--and sometimes you have different payment amountss for the same number of payments, down payment and type.

                            In that case a portal is your ownly practical option as it can show multiple rows of data when needed and filter out zero values. I'm not sure that I see what logic determines that 736.33 at 4.09% is listed above $690.66 at 0% in your example, however. (By rate in descending order?)

                            Also, do you need to see this in browse mode or if you could only see it as printed/PDF'd or in preview mode, would that work for you? (May need to use sliding portals...)

                            • 13. Re: Summary cross tab report help requested
                              alanfink@comcast.net

                              I had to flip back and forth and look very carefully to see the issue, which is that you are combinging payments for different rates on the same row--I'd find that confusing myself--and sometimes you have different payment amountss for the same number of payments, down payment and type.

                              The reason for the one line multiple view falls in the "less is more" category when presenting choices to the average customer.

                              Most rates for a given term are the same for 24-60 months.  Rates are always higher for longer terms 72-84 months.

                               I'm not sure that I see what logic determines that 736.33 at 4.09% is listed above $690.66 at 0% in your example, however. (By rate in descending order?)

                              Some times there are promotional incentive programs for 36,48,60 month terms.  Hence the reason for these to be shown independently along with non promoitional options.  A rebate cash incentive may be chosen in lieu of a discounted promotional rate.  So showing payments with non promotional rates grouped together on 1 line is easy to compare with any promotional rate options directly below it.

                              Its all about presenting options to a customer with various forms of cash down and what that means to them for a monthly payment.  In other words.....Choose your down payment, choose your monthly payment.

                              Also, do you need to see this in browse mode or if you could only see it as printed/PDF'd or in preview mode, would that work for you? (May need to use sliding portals...)

                              Main use is a printed/preview report.  It would be nice to see in browse if possible, but not mandatory....

                              Thanks...Alan

                              • 14. Re: Summary cross tab report help requested
                                philmodjunk

                                Some times there are promotional incentive programs...

                                That doesn't answer my question. What data in the data base determines that 736.33 is listed in the first row and that 690.66 should be listed in the second row?

                                This data can be presented in a portal with extra rows, but set to slide up and resize the enclosing part. If needed a sort order can be specified for the portal that controls which value is listed first, second, etc. but that assumes there is some way that a value in a field can be used as the basis of that sort order.

                                1 2 Previous Next