Cant Summize a forumula field

[mabis]

Premise of the report (CR 10.0) is simple comparing prior year’s sales to current year sales. Groups are by salesperson (useridmaster.lastname) and then by account (account.account_name) sorted by largest 06 sales on down.

Requested by VP of sales is a summary of sales from accounts that ordered in 06 but not 05. For the individual accounts I was able to filter the accounts and totals requested as follows:

tonumber(if Sum ({@yeartodatelinetotal}, {account.account_name})>0
and Sum ({@priorytdlinetotal}, {account.account_name})<=0
then Sum ({@yeartodatelinetotal}, {account.account_name})
else 0)

where @yeartodatelinetotal= sales for the current year and
@priorytdlinetotal=prior years sales

This does provide any sales greater than zero for new accounts in 06 and zero for any account that ordered in 05 and 06.

PROBLEM: I now need to summarize all of the accounts that ordered in 06 but not 05. When trying to create a summary from field explorer I am told that the field can not be summarized. It is not an available field for summary in Insert Summary.

 

[RobbieB]

you need to create a running total

place this formula in the header of the group you want to summarize. If it is a report total than put it in the report header

@ytdstart
whileprintingrecords;
numbervar X;
X := 0

place this formula on the line where your 06not05 formula is (detail section?)

@ytdcalc
whileprintingrecords;
numbervar X;
X := X + @06not05

place this in the group footer of the group you are summarizing. In my example it would go in the report footer.

whileprintingrecords;
numbervar X;
X := X

 

[BobSuruncle]

One caveat about this method.
If the group has the property "Repeat Group Header on Each Page" turned on, and the group spans pages, the formula @ytdstart will be executed twice. This will mess up the running total.
Change @ytdstart as follows:

whileprintingrecords;
numbervar X;
If NOT InRepeatedGroupHeader then
(
X := 0;
)
;

Bob Suruncle

 

[RobbieB]

Not if you in the @ytdstart formula you say

whileprintingrecords;
numbervar X;
if inrepeatedgroupheader
then X := X
else
X := 0

 

[mabis]

Robbie, Bob:

I am most appreciative of your help. Unfortunately, I do not understand and need some clarification.

Robbie, you reference three formulas @ytdstart, @ytdcalc and @06not05. These are not in my current forumulas.

Do I create these as separate formulas first for use with the example Robbie offers above? If so, what is the purpose of each of the three formulas?

Sorry to bother. Thank you again.

mabis

 

[RobbieB]

Hey Mabis, no bother. Basically you are creating a manual running total. You do have to create these formulas manually. You can change the names of the formulas to whaterver you want, I just named them with these name for demonstration purposes.

The first formula creates a variable called "X". It is setting the value of "X" to be 0 (zero). ( "X" could be replaced with any other letter or combination of letters.) Each time this formula is encountered it will reset the value of "X" to zero. That is why it is important to determine which group you are summarizing. If the summary is for group2 you would place this formula in the Header for Group2.

@Formula 1

whileprintingrecords;
numbervar X;
X := 0

Formula 2 is also claiming the variable "X" and adding it to the field you want to summarize. So, if the field you want to summarize is a formula for last years sales and it is called @Lastyearssales (for example) and it is in the detail section of the report, this formula would look like this and would be placed in the detail section of the report. As Bob mentioned, the one thing to keep in mind is whether or not you have "Repeat Group Header on Each Page" checked. If you do look at the way I wrote the formula in the previous post.

@formula2

whilerintingrecords;
numbervar X;
X := X + @Lastyearssales

Formula 3 would go in he group footer for the group level you are summarizing. In my example it would be group footer2. Formula 3 is just showing the value of our variable "X".

@Formula3

whileprintingrecords;
numbervar X;
X := X

Good luck and let us know if you have any more questions or experience any problems.