Formulas generate different values on "Refresh" than initial load 1

[pontupo]

So here's the problem I'm having--
I have a fairly complicated report with some 50 or so formula fields that are creating a variety of subtotals/totals of my data using the following technique:
whileprintingrecords;
NumberVar LicensesYearTotalCorporate;

LicensesYearTotalCorporate := LicensesYearTotalCorporate + {Opportunity.oppo_licensetot};

ToNumber(LicensesYearTotalCorporate);

I'm unable to use the standard Crystal Sum() function because many of these subtotals are beyond the scope of that function (such as a subtotal by an area of the country or subtotals of a given area over a variety of date ranges, etc.) The above formula, however, can be done with a Sum() function, so I created another formula using Sum() to confirm my suspicion that something wasn't right.

The issue is that on an initial viewing of the report, say after having made a modification to a formula, my formulas give the -correct values-. However, if I click "Refresh", the values change to incorrect ones!! In the example above, my running total will differ from the Sum() function by some amount x, and it is exactly the same amount of difference every time.

This problem only affects certain subtotals of mine and the particular formulas that are affected seem to indicate that somehow a couple of records are not being included in these formulas. The problem isn't across the board, so this technique seems to generally work.... I'm really at a loss here as to what is going on. Thoughts?? I was thinking that perhaps some sort of group suppression, etc. was interfering... The strangest thing to me is that the Sum() function is not affected while my formulas are. I guess I'm just looking for some ideas on where I might look for the source of the problem... Thanks in advance,

Pont

 

[satinsilhouette]

Where are you placing your formulas, group footers, headers?

Thanks so much!
satinsilhouette

 

[pontupo]

All of the formulas are placed in the Report Footer. The 'grouping' in them is done via selection in the formula itself. For example:
whilereadingrecords;
NumberVar LicensesMonthTotalEast;

if( (Month({Opportunity.oppo_dtprocessed}) = Month(CurrentDate()))
AND ({Users.user_userlocation} = "East")) then
LicensesMonthTotalEast := LicensesMonthTotalEast + {Opportunity.oppo_licensetot};

ToNumber(LicensesMonthTotalEast);

This pulls only those records from the current month in the "East".

 

[pontupo]

I've been wondering if this may in part be due to my continued confusion over whileprintingrecords vs. whilereadingrecords and that incorrect use of these two may be the source of my problem... The first example I posted shows whileprintingrecords, which was what I was experimenting with an hour ago as part of this investigation. The formulas actually use whilereadingrecords because I get definitely wrong values when I use whileprintingrecords. Initially, I left those statements out entirely and left it to Crystal to choose evaluation order, which worked exactly the same as it does now with whilereadingrecords, which makes sense to me since I'm building the totals as I go and Crystal must be smart enough to know the kind of formula I'm using.

 

[lbass]

I think you should be using whileprintingrecords. Also, also you should have a separate display formula if the final result is to be displayed in a section other than the one in which it is being accumulated. If you are doing group subtotals, make sure your reset formulas are written like this, if you have repeating group headers:

whileprintingrecords;
numbervar x;
if not inrepeatedgroupheader then
x := 0;

Finally, since your variable is a number variable, wrapping the result in tonumber() is redundant--it's already a number.

-LB

 

[pontupo]

Wow! thanks so much, it seems to be behaving much better. I created the following, just to experiment with this:

@initialize (in the report header)
whileprintingrecords;
NumberVar LicensesYearTotalCorporate;
LicensesYearTotalCorporate := 0;

@LicensesYearCorporate (in the Details)
whileprintingrecords;
NumberVar LicensesYearTotalCorporate;

LicensesYearTotalCorporate := LicensesYearTotalCorporate + {Opportunity.oppo_licensetot};

@display (in the report footer)
evaluateafter({@LicensesYearCorporate});
NumberVar LicensesYearTotalCorporate;
LicensesYearTotalCorporate;

The notion of placing the @initialize field into a group header is a bit mysterious to me... Could you expand on why you might want to do that? Also, one problem still exists: for some reason, the -last record- gets added to the total twice, making the final result too high by exactly the difference of that last record. Any thoughts on why this is happening? There may be something hidden in my, admittedly, messy list of formulas that is still touching the LicensesYearTotalCorporate variable in addition to the above, I guess...

 

[pontupo]

On second thought, I can see placing the @initialize field into a group header if I wanted a running total that I could reset for each instance of the group. But why would I put it there if I was just going to say:
if not inrepeatedgroupheader
??

 

[lbass]

You don't need the reset if there are no groups. You also don't need evaluate after in the display formula. Whileprintingrecords forces the data to be evaluated by order of the section.

If you are getting a repeating value in the report footer, make sure you have removed all previous formulas that might be accumulating the value.

-LB

 

[pontupo]

I can't thank you enough lbass! It finally works =) I'm still not sure what the deal was with the last record being doubled in the total... but after deleting some extraneous groups that were left over from earlier revisions, the problem went away. *shrug* As they say... don't look a gift horse in the mouth.

I'll be restructuring the report to take advantage of this method tomorrow. One question: Is there any reason why I can't initialize all of the variables in a single formula? Bad practice? Likewise, is there any reason not to increment all of the variables in a single formula? While I'm not keen on having all of this compressed into a single formula, I'm also not pleased about the idea of tripling the number of formulas in the report. Just wondering if there's anything 'mechanical' that would get in the way of this...

 

[lbass]

You don't need the initialize/reset formulas at all, unless you are summarizing at a group level. If you are summarizing multiple values at the same group level, you can do the reset in one formula. The accumulation can be done in one formula, too--if you are accumulating values at the same level (e.g., detail or a group section). The display formulas need to be separate, regardless of whether they are at the same or different levels.

-LB

 

[pontupo]

For the sake of a solid resolution to this thread, here's the changes that I made on lbass' suggestion:

I now have a single accumulation formula that tracks all of the running totals. This formula is a couple hundred lines long and consists in an if-then-else style block for each of the relevant totals. Here's an example of some of its content:
@accumulator (placed in the Details section)
WhilePrintingRecords;
// initialize/declare the vars
NumberVar CDDayTotalEast; //CDs
NumberVar CDDayTotalInHouse;
NumberVar CDDayTotalWest;
....

//date calculations
DateVar dateToday := CurrentDate();
DateVar dateToday := CurrentDate();
NumberVar quarter :=
Switch(
(Month(dateToday) >= 1 AND Month(dateToday) <= 3),
1,
(Month(dateToday) >= 4 AND Month(dateToday) <= 6
...

// accumulations
// Day East
if( (Month({Opportunity.oppo_dtprocessed}) = Month(dateToday))
AND ({RAM.user_userlocation} = "East")
AND (Day({Opportunity.oppo_dtprocessed}) = Day(dateToday)))
then(CDDayTotalEast := CDDayTotalEast + {@CDs};
LicensesDayTotalEast := LicensesDayTotalEast + {Opportunity.oppo_licensetot};
PDDayTotalEast := PDDayTotalEast + {Opportunity.oppo_pd};
SMDayTotalEast := SMDayTotalEast + {Opportunity.oppo_schoolcare};
TextbooksDayTotalEast := TextbooksDayTotalEast + {Opportunity.oppo_textbooks};
...

Previously each of the Then-clauses was in a separate formula (5 total as there are 5 such clauses in each block). I then have a single Display formula for each of the totals. These basically just print out the relevant variable like so:
@DisplayCDDayEast (placed in Report Footer)
WhilePrintingRecords;
NumberVar CDDayTotalEast;

CDDayTotalEast;


The result is very nicely organized and understandable. I was concerned about this approach increasing complexity, but I found that it actually made things much more clear. All programmatic action takes place in a single formula, whereas before I had 60 independent formulas to maintain. What can I say, I'm pleased =) Thanks again to lbass for the help.