running total for a date range outside the range of a crystal report

Posted on 2012-08-30
Last Modified: 2012-09-12
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?
Question by:ISBTECH
    LVL 18

    Expert Comment

    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.
    LVL 100

    Expert Comment

    Is the commission based on the full 4 sales or just up to $1Billion?


    Author Comment

    ScreenshotSee 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.
    LVL 34

    Expert Comment

    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.


    Author Comment

    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.
    LVL 100

    Accepted Solution

    I assume in the report you are grouping by the manager.  Is it $1 Billion for the manager or the company?
    If the company you could have a subreport in the report header that calculates sales for YTD where the date is 15th of last month.

    If it is the manager then the subreport would be in the manager group header.

    You can also include the data for the year and suppress records that aren't inthe selection range.

    Basically you need a formula like
    Global NumberVar TotalSales;
    Global NumberVar CommisionRate;
    Global NumberVar TotalCommision;

    If TotalSales > 1000000000 then
             CommisionRate := 0.01
             CommisionRate := 0.03;
    TotalSales := TotalSales + {Salesfield};
    TotalCommision := TotalCommision +  {Salesfield} * CommisionRate;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now