running total for a date range outside the range of a crystal report
Ok experts this one has me stumped.
As I mentioned in a prior question I have a commission report that runs total commissions paid - actually this one is overrides for managers of salespeople but basically the same thing. It is already built and runs for a 30 day time frame, the 16th of "Last Month" to the 15th of "current month" Each sales manager receives a commission that is a factor generated by different criteria per manager, for example one might get a factor of 1 for some sales people under them and a factor of 2 for others, this factor is then multiplied by the total of all closed loans in that commission period, this is all fine. I handle this with a separate formula per manager in the group 1 header that sets the factor, then in the details section I have a formula per manager that multiplies the total sales amount by the factor, giving me a breakdown of commission paid per sale and a total in the footer that adds them all up.
But now I have a person who's factor is calculated based on the total sales Year to Date. I inserted a sub report to get the total sold to date but now I have a problem, the factor for this manager will adjust downward once 1 billion in sales is hit, and I need to apply that factor from that dollar amount onward, not for the whole commission period. So in other words if 1000 sales were closed during the commission period but the 5th sale is the one that put the YTD total over 1 billion then the first 4 sales would be calculated at a factor of 3, and the rest at a factor of 1. So I need to know the running total of the YTD sales not just the final total right? How do I get that?
As I mentioned in a prior question I have a commission report that runs total commissions paid - actually this one is overrides for managers of salespeople but basically the same thing. It is already built and runs for a 30 day time frame, the 16th of "Last Month" to the 15th of "current month" Each sales manager receives a commission that is a factor generated by different criteria per manager, for example one might get a factor of 1 for some sales people under them and a factor of 2 for others, this factor is then multiplied by the total of all closed loans in that commission period, this is all fine. I handle this with a separate formula per manager in the group 1 header that sets the factor, then in the details section I have a formula per manager that multiplies the total sales amount by the factor, giving me a breakdown of commission paid per sale and a total in the footer that adds them all up.
But now I have a person who's factor is calculated based on the total sales Year to Date. I inserted a sub report to get the total sold to date but now I have a problem, the factor for this manager will adjust downward once 1 billion in sales is hit, and I need to apply that factor from that dollar amount onward, not for the whole commission period. So in other words if 1000 sales were closed during the commission period but the 5th sale is the one that put the YTD total over 1 billion then the first 4 sales would be calculated at a factor of 3, and the rest at a factor of 1. So I need to know the running total of the YTD sales not just the final total right? How do I get that?
vasto
You will need 2 different SQL statements : one to get sales less than 1b and calculate the commission and another one to get the others (and calculate the commission) . Then sum the values. You can create a stored procedure ( or a command ) which will use a temp table to prepare the records and the return the grouped values by manager. Later if there is a new manager with different commission settings you will just add the queries to fill the data without changing the report.
Is the commission based on the full 4 sales or just up to $1Billion?
mlmcc
mlmcc
ASKER
See the attached screen shot. The details section shows the individual sales their detail and the commission paid to the sales person, including any modifiers, then to the right there are columns for the different managers that receive overrides on that salesperson. The names "Kelly" "Brown" etc. are formulas that are pulling in the name of the managers that oversee that salesperson, and the factor is displayed in basis points below the name, then the numbers below the factor are the individual override amounts per transaction, and then I will be totaling these below, I used to have a summery inserted there but had to remove that due to the need to pull data from a subreport to determine the factor. see my last question which you answered mlmcc.The commission is paid individually per sale as the sale amount times a factor, in this manager’s case each sale is paid at 3 basis points, or {salesamount} * .0003, however once the total sales for the year, not the commission period passes 1 billion dollars the factor for all additional sales changes to 1 basis point. So if 1,000,000.00 was sold in this commission period and the year to date sales volume surpassed 1 billion during that period as well, the basis point factor would need to change from 3 to 1 as of the sale that caused the total sold Year to Date to surpass 1 billion. For example if there were 10, 100,000.00 sales for a total of $1million and the fifth sale put the YTD total over 1 billion then the first 5 sales of this period would be paid at 3 basis points ( 100,000.00 * .0003 = $30 each) but the remaining 5 would be paid at 1 basis point ( 100,000.00 * .0001 = $10 each) as would all future overrides on all future commission periods until Year end when the YTD total would refresh.
FWIW, I was having some trouble with this sentence:
> For example if there were 10, 100,000.00 sales for a total of $1million and the fifth sale ...
I was reading that as "10.1 million sales for a total of $1million", which didn't make much sense. Then I figured out that it was "ten 100,000.00 sales for a total of $1million". Just thought I'd mention that, in case anyone else was confused.
You seem to be contradicting yourself. At the end of your first post, you said that the sale that puts them over the limit (the 5th sale) should get the lower factor. But in your last post, you say that the sale that puts them over the limit (also the 5th sale) should get the original (higher) factor. Which is it?
Putting that question aside for the moment, it seems like you could do this using a formula. That formula would check a running total, or a variable with the total in it, and choose a factor based on that. Exactly what total you check would depend on which factor should be used on the sale that puts them over the limit.
James
> For example if there were 10, 100,000.00 sales for a total of $1million and the fifth sale ...
I was reading that as "10.1 million sales for a total of $1million", which didn't make much sense. Then I figured out that it was "ten 100,000.00 sales for a total of $1million". Just thought I'd mention that, in case anyone else was confused.
You seem to be contradicting yourself. At the end of your first post, you said that the sale that puts them over the limit (the 5th sale) should get the lower factor. But in your last post, you say that the sale that puts them over the limit (also the 5th sale) should get the original (higher) factor. Which is it?
Putting that question aside for the moment, it seems like you could do this using a formula. That formula would check a running total, or a variable with the total in it, and choose a factor based on that. Exactly what total you check would depend on which factor should be used on the sale that puts them over the limit.
James
ASKER
You are correct that I mean Ten 100,000.00 sales. The change in factor should happen starting with the first sale after the total sales have exceeded 1 billion. I'm not worried about where to change the factor, that's not a problem, I understand that I can use a formula, the problem I am having is figuring out how to access the running total of all loans closed this year. The record selection for this report only accesses 30 days of data, from the 15th of the prior month to the 16th of the current month. But I need to be able to know what the total sold for the year is, even though it is outside my selection criteria, I can’t just use a sub report and pull in the current total sold volume because I can’t apply the current volume to the entire report , I need to know the running total as each sales happens so that I can switch the factor at the correct time.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.