[Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2012-08-29
Medium Priority
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
  • 2
  • 2
LVL 101

Expert Comment

ID: 38346618
What is the @Kelly$ formula?


Author Comment

ID: 38346673
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 101

Accepted Solution

mlmcc earned 2000 total points
ID: 38346978
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

ID: 38347023
That is what I was beginning to think.  Thanks!

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

865 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