Problem using a shared variable in a formula that is summerised later.

Posted on 2012-08-29
Last Modified: 2012-08-29
Good Afternoon Experts,

I have a report that uses Paramater Fields to determine a range of dates to run for closed loans and then calculates total commissions paid to sales people for those loans.  I have set up several formulas to determine the commission per loan, per loan officer as many have different deals than others.  I then use a summery in the report footer to give a grand total for each salesman.  This has all been working fine for some time now, but recently a new salesman has come in who's deal involves the total of all loans closed Year to Date rather than to total closed within the commission period as all others are.  Because the selection criteria of the report will allways be a 30 day period of time I inserted a sub report that runs the sales Year to Date and use a shared variable to access the total year to date, that works fine as well.  The problem comes when I try to use that information.  I calculate the amount paid to each individual using a formula that multiplys the loan amount by a factor that changes based on certain criteria for each sales man, for example one salesmans factor is calculated as follows:

if {custom_lo_commission_.OverRidePlan} = "A" then 10.00
Else if {custom_lo_commission_.OverRidePlan} in ["B", "C", "D", "E", "F"] then 5.00
Else if {custom_lo_commission_.OverRidePlan} = "G" then 3.50
Else if {custom_lo_commission_.OverRidePlan} in ["H", "I"] then 3.00
Else if {custom_lo_commission_.OverRidePlan} = "J" then 2.50
Else if {custom_lo_commission_.OverRidePlan} in ["K", "L", "M", "N", "O"] then 2.00
Else 1.00

This works fine, then I use this formula to determin the actual dollar figure per loan:

{@LoanAmount} * ({@Brown} / 10000)

This also works fine, then I insert a sum of the @Brown formula in the report footer and all is well.

However on this new salesman I built a formula to determine the factor that depends on the value of the shared variable from the sub report, it follows:
first I declaire the shared variable in the header.
Shared Numbervar TotalLoans;

Then in the details I have a formula to pull the value of the shared variable;

Shared NumberVar TotalLoans;

then I use that formula in my formula to determine the factor for the salesman:

if {@YTDClosedLoans} < 100000000 Then 3.00
else 1.00

This works as well, I get the correct factor but then when I preview the report the summery of the formula that uses the factor to calculate a dollar figure throws this error:

"A summary has been specified on a non-recurring field.
Details: @Kelly$"

Any ideas?
Question by:ISBTECH
    LVL 100

    Expert Comment

    What is the @Kelly$ formula?


    Author Comment

    if {@YTDClosedLoans} < 100000000 Then 3.00
    else 1.00

    {@LoanAmount} * ({@Kelly} / 10000)

    Then I insert a summery of @Kelly$ in the report footer, it the summery that throws the error.
    LVL 100

    Accepted Solution

    The problem is Crystal uses a multiple pass method to build the report.

    Summary functions are evaluated in the first pass through the report but shared variables and subreports are evaluated in the last pass through the report thus the values aren't available when the summary is evaluated.

    You will need to do a manual summary of that formula.


    Author Closing Comment

    That is what I was beginning to think.  Thanks!

    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

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Need help with a query 3 35
    How to enforce inte 8 27
    Incremental load example 2 30
    SQL Server Degrading on Write 13 51
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    729 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

    15 Experts available now in Live!

    Get 1:1 Help Now