4 Replies Latest reply on Apr 26, 2012 9:47 AM by Ely_Age

    Totaling related data in summary report

    Ely_Age

      I have a report I'm working on and I've hit a snag. I have a table that tracks sales for my organization. In addition to PK, sales rep information, date, and sale amount have a field for the sale closer. If two guys, say Joe and Bob, are both on a sale and Bob closed the deal there will be a record for each in the table with Bob listed on both records as the closer. I've designed this report to show the month/year and dollar amount for their personal "best month".

       

      An example of the fields in play:

       

      tCloser

      nSale

      dSale Date

      sSaleSummary = Total of nSale

      cSales Period = MonthName ( dSale Date ) & "/1/" & Year ( dSale Date ) through field formatting I'll just display it as Month and Year

      tCloser Period = Auto-enter Replace tCloser & cSales Period

      nSaleSummary by Closer Period = Auto-enter Replace GetSummary ( sSaleSummary ; tCloser Period )

      sSaleSummary by Closer Period = Maximum of nSaleSummary by Closer Period

       

      I'm sure there's probably a better way to do this, but this was the best I could come up with to avoid the issues involving unstored calculations and related data. Anyway, I have a self join using the tCloser field in two tables I'll call Sales and SalesSJ with the SalesSJ side of the relationship sorted by SalesSJ::tCloser Period. The report is set up in the context of the Sales table with a Sub Summary part when sorted by Sales::tCloser. In addition to Sales::tCloser I have SalesSJ::cSales Period and SalesSJ::nSaleSummary by Closer Period in the Sub Summary part. To get my final output I sort by Sales::tCloser decending and reorder using the sSaleSummary by Closer Period field.

       

      All of that works beautifully and I even have them ranked using a 1/GetSummary ( Closer_Count ; Closer ) calculation field. Here's where I'm hitting a wall; I need to find a way to show a grand total based on just the amounts being displayed. I'm using all of the related records since their "best Month" is going to be dynamic so any total I try to generate ends up being inclusive of all the related records. I can't seem to come up with a way to isolate those values. I'm not doing any of this with scripting yet; I'm just getting the basic parts of it worked out to make sure I'm getting the correct data and displaying it how I want. It seems like I could use a couple of variables in a looping if/then compare-and-add-to-total kind of script but I'd like to avoid that unless it's my only option.

       

      So, any thoughts?

        • 1. Re: Totaling related data in summary report
          Stephen Huston

          How are you isolating the found set you want to total?

           

          Does it include multiple reps and multiple closers?

           

          If so, you could run subsummaries when sorted on each grouping field, but this will require resorting them to show the correct subsummary amount when you want to see it for each field grouping.

           

          If not,  what does the list contain that you can't use simple sums and subsums?

          • 2. Re: Totaling related data in summary report
            Ely_Age

            Since I have data going back to 2003 but we've only been tracking the closers since 2010 I'm just searching for populated data in the closer field using >@ in my find.

             

            There can be multiple reps on a sale but only one closer. For the purposes of this report though the reps are irrelevant. I'm only after data that relates to the closers. The way I'm achieving this now is using the GetSummary ( sSaleSummary ; tCloser Period ). So if Jim has 5 closes in April 2012 All five of those records in the related table are going to display that his total close was for that sales period, likewise for every other sales sales period since we've been tracking. That GetSummary sort takes place outside of the sort for the SubSummary report in the relationship graph. The only actual sort taking place to generate the report is sorting by closers on the report layout to give me a one line per closer report and reordering using the Maximum summary field just places the highest dollar amount related record at the top. Doing that though I still have every record (1303 to be exact) available behind the scene so it's totaling the whole shebang.

            • 3. Re: Totaling related data in summary report
              Stephen Huston

              It sounds like you need a simple Summary field in the Sale table set to summarize the individual record values in the found set, not  a calc referencing related values.

               

              Then when you do a find for sales by period in the Sales table, sort them closer, and the subSummary part in a Sales table list will show their summaries.

              • 4. Re: Totaling related data in summary report
                Ely_Age

                That was it, thank you. I ended up creating a couple more fields to get to there though. I created one that flags the records in the Sales table that match the corresponding related data in the SalesSJ using the sort and reorder between tables so I'm matching the max value (same formula as the conditional formatting on the two fields highlighted in red). I created another one to calculate out the unique values when sorted by Closer and a final one to total those unique values.

                 

                cSalesSumm Flag = If ( Sales::nGetSaleSummary by Closer Period = SalesSJ::nGetSaleSummary by Closer Period ; 1 ; "" ) flags the max values

                cSalesSumm Unique = Let ( x = GetNthRecord ( tCloser Period ; Get(RecordNumber) - 1 ) ; If ( tCloser Period <> x ; nGetSaleSummary by Closer Period ; "" ) )   //   end let calculates the unique values from the sorted set

                sSalesSumm Unique Total = Total of cSalesSumm Unique

                 

                It's a very simple process now;  find Closer and records flagged in cSalesSumm Flag, go to the Sub Summary report layout, sort by closer and reorder by SalesSJ::sSaleSummary by Closer Period decending.

                 

                Thanks again for the help. After your last post I had one of those "oh, I've done this before" moments. I'm fully convinced that staring at field definitions and relationship maps for an extended length of time melts the brain. Now to get cracking on figuring out a way highlight when a guy bests himself or someone else. That be simple with scripting I think. I have a table that is just rep information (rep code, name etc.) so I can just dump his rank on the sheet and the sales period or dollar amount for future comparison.