False Summary / Running Total field could not be created error 1

[reporting]

In a complex call report for a pharma client, I am receiving a false "Summary / Running Total field could not be created" error. The report is being created using CR 8.5.

The formula {@R6TargetCallsAvg - GF2} is designed to calculate the average number of calls a sales rep makes to R6 rated physicians in his/her territory. It is the sum of Actual R6 Calls made in a month divided by the monthly R6 Calls Objective ( Sum of {R6TargetCallsAct - GF2} / {R6TargetCallsObj - GF2})

The formula I used is as follows:

[tt]WhilePrintingRecords;

if {@R6TargetCallsObj - GF2} = 0 then 0
else Sum ({@R6TargetCallsAct - GF2}, {Physician.Territory}) / {@R6TargetCallsObj - GF2}[/tt]

{@R6TargetCallsObj - GF2} is actually DistinctCount ({@DaysOnTerrAct - D}, {Physician.Territory})

So both fields are grouped on {Physician.Territory}, and the formula is placed in GF2, grouped on {Physician.Territory}!

To further confuse the issue, this formula works (for calls to non-target physicians):

[tt]Sum ({@NonTargetCalls - D}, {Physician.Territory}) / DistinctCount ({@DaysOnTerrAct - D}, {Physician.Territory})[/tt]


But this one doesn't!!! (I get a "the Summary / Running Total field could not be created" error):

[tt]Sum ({@R6TargetCallsAct - GF2}, {Physician.Territory}) / DistinctCount ({@DaysOnTerrAct - D}, {Physician.Territory})[/tt]

I then broke it down into components. This formula works:

[tt]DistinctCount ({@DaysOnTerrAct - D}, {Physician.Territory})[/tt]

I then discovered that it is this component that gives me the "Summary / Running Total field could not be created" error message:

[tt]Sum ({@R6TargetCallsAct - GF2}, {Physician.Territory})[/tt]

The contents of {@R6TargetCallsAct - GF2} are real simple:

[tt]WhilePrintingRecords;
local numbervar TargetCall := 0;
// local because I intend to re-use the same formula for R5 and R4 physicans

if {Physician.status} = "Z" then TargetCall := 1;
TargetCall[/tt]

It then gets summarized by CR to calculate the number of calls that were made. I have used this kind of logic dozens of times and have never had this problem before. Probably something real dumb...

Any ideas? This is driving me wacky. I took a look on the CR KBase and couldn't find any hints there... Also checked the SR PDFs' to see if this was a known error that had been fixed. And did a search here to see if anyone else has been driven to distraction by this!

Thanks very much,



John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[FVTrainer]

On the surface, it looks like you're trying to use a 2nd pass formula in a first pass calculation...but the original formula is declared as a 2nd pass formula, too, so I'm not sure about that.

But why does {@R6TargetCallsAct - GF2} need to be 2nd pass; why not 1st pass (i.e., WhileReadingRecords)? That may change the results...

 

[reporting]

I always try formulas as 1st pass to see if the results can be validated. If they can't (like this one), I make them 2nd pass...

TNX,

John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca

 

[FVTrainer]

I'll take your word for it, but the following formula has nothing in it that requires waiting for the second pass:

WhilePrintingRecords;
local numbervar TargetCall := 0;
// local because I intend to re-use the same formula for R5 and R4 physicans

if {Physician.status} = "Z" then TargetCall := 1;
TargetCall


You're using a local variable, and you're using a field, {Physician.status}, that is available during the first pass. In fact, what is the difference between the above formula and this one:

if {Physician.status} = "Z" then 1 else 0

They'll both return the same result, except the first formula occurs in the 2nd pass while the second formula occurs in the 1st pass.

To test out the WhilePrintingRecords issue, I created the following formula, using the sample Xtreme database, and placed it in the details section of a Customer report grouped by region:

Formula SalesOver50K
-------------------
WhilePrintingRecords;
local numbervar counter := 0;
If {Customer.Last Year's Sales} > 50000 then
counter := 1;
counter

Obviously, I can't insert a summary on this formula field, which rules out manually creating a formula such as:

Sum({@SalesOver50K},{Customer.Region})

But when I entered the following formula, which models your evaluation time logic, I got the same error message you did:

WhilePrintingRecords;
Sum({@SalesOver50K},{Customer.Region})


I don't think it is a "false" error message. I believe the Sum itself has to make use of first pass formulas, based on when the Sum function is available in the reporting engine.

So, why can't you use the simple if statement shown above as the basis for {@R6TargetCallsAct - GF2}?

 

[reporting]

Hmmm... You may be right on that...

This is a report that worked fine for the past year. Then my client changed how the sales reps worked and asked me to change the reports...

When I did it as first pass, the values did not compute properly. So I tried it 2nd pass and got that error message.

All of this is now moot: I spoke to my client this morning and told her that I was unable to modify the reports as she had requested. Had they asked me for these reports to begin with, I would have done them totally differently. So I am starting over and will be using array variables instead...

Thanks very much for your help!



John Marrett
Crystal Reports Trainer & Consultant

http://www.e-reporting.ca