AnsweredAssumed Answered

Totaling related data in summary report

Question asked by Ely_Age on Apr 25, 2012
Latest reply on Apr 26, 2012 by 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?

Outcomes