summarize a formula field.

I would like to summarize a formula field by group. But when I go to add a summary field, it only allows me to summarize database fields, not forumlas.

A simple example, I wouldl ike to calculate the sum of the formula, the formula sums up the values on the row (rowSum), and the groupFormula sums up the rowSum for that group.

------group 1------
1 2 3   = 6
2 3 4   = 9
------------------
           = 15
------group 2------
1 2 1   = 4
2 3 2   = 7
------------------
           = 11


Does anyone know how to do this? I would be greatly appreciative of any assistance in this matter.
LVL 4
brokeMyLegBikingAsked:
Who is Participating?
 
OutinConnect With a Mentor Commented:
Get rid of the 'WhilePrintingRecords' statement.

Imho it is not needed, en it's the reason you cannot summarize the formula.

hth,

Outin

0
 
brokeMyLegBikingAuthor Commented:
Is it possible to access the previous record in a formula?

3
4
5   formula= 9


Formula = (current record) + (previous record._


0
 
bdreed35Commented:
Can you post your formula that is not letting you insert a summary on it?

I think rowSum should be something like this:

//@rowSum
{table.field1} + {table.field2} + {table.field3}

You can then insert a summary on this formula and then make a copy of the summary and paste it in the report footer to get the grand total.

There is a previous function to get the previous field value:

{table.field1} + previous{table.field1})
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
bdreed35Commented:
Should have been:

{table.field1} + previous({table.field1})
0
 
brokeMyLegBikingAuthor Commented:
So noone knows an easy way to summarize a formula field by group?


When I use the formula: (VB syntax) it says "this field has no previous or next value.

previousValue({@YtdAve})
0
 
bdreed35Commented:
Can you post your rowSum formula?
0
 
brokeMyLegBikingAuthor Commented:
The "rowSum" formula is called "YtdAve", here it is:


----------------
WhilePrintingRecords
Dim totalmonths as Number

totalmonths = IIF(isnull({pri_pmpm_byspecialty.01}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.02}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.03}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.04}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.05}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.06}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.07}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.08}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.09}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.10}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.11}),0,1) + _
IIF(isnull({pri_pmpm_byspecialty.12}),0,1)

Dim s as Double
s = 0
if(not isnull({pri_pmpm_byspecialty.01})) then s = s + {pri_pmpm_byspecialty.01}
if(not isnull({pri_pmpm_byspecialty.02})) then s = s + {pri_pmpm_byspecialty.02}
if(not isnull({pri_pmpm_byspecialty.03})) then s = s + {pri_pmpm_byspecialty.03}
if(not isnull({pri_pmpm_byspecialty.04})) then s = s + {pri_pmpm_byspecialty.04}
if(not isnull({pri_pmpm_byspecialty.05})) then s = s + {pri_pmpm_byspecialty.05}
if(not isnull({pri_pmpm_byspecialty.06})) then s = s + {pri_pmpm_byspecialty.06}
if(not isnull({pri_pmpm_byspecialty.07})) then s = s + {pri_pmpm_byspecialty.07}
if(not isnull({pri_pmpm_byspecialty.08})) then s = s + {pri_pmpm_byspecialty.08}
if(not isnull({pri_pmpm_byspecialty.09})) then s = s + {pri_pmpm_byspecialty.09}
if(not isnull({pri_pmpm_byspecialty.10})) then s = s + {pri_pmpm_byspecialty.10}
if(not isnull({pri_pmpm_byspecialty.11})) then s = s + {pri_pmpm_byspecialty.11}
if(not isnull({pri_pmpm_byspecialty.12})) then s = s + {pri_pmpm_byspecialty.12}

IF totalmonths > 0 THEN
   formula = s / totalmonths
ELSE
   formula = 0
END IF





0
 
brokeMyLegBikingAuthor Commented:
and here is my attempt (this doesn't work) at sumning the YtdAve formula (i just have to sum the previous record when the center name for the previous record is the same.



if previousValue({pri_pmpm_byspecialty.CenterName}) = {pri_pmpm_byspecialty.CenterName} then
  formula = {@YtdAve} + previousValue({@YtdAve})
else
  formula = "" 
end if
0
 
OutinCommented:
With the above, I mean 'WhilePrintingRecords' in your YtdAve function.

After that, you should be able to create a Σ summary for @YtdAve.

--
Outin
0
 
GJParkerCommented:
If I were you I would get rid of the variables completely and simplify these formulas (these are all Crystal syntax)

@CountMonths
IIF(isnull({pri_pmpm_byspecialty.01}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.02}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.03}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.04}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.05}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.06}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.07}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.08}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.09}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.10}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.11}),0,1) +
IIF(isnull({pri_pmpm_byspecialty.12}),0,1)

@SumMonths
{pri_pmpm_byspecialty.01} +
{pri_pmpm_byspecialty.02} +
{pri_pmpm_byspecialty.03} +
{pri_pmpm_byspecialty.04} +
{pri_pmpm_byspecialty.05} +
{pri_pmpm_byspecialty.06} +
{pri_pmpm_byspecialty.07} +
{pri_pmpm_byspecialty.08} +
{pri_pmpm_byspecialty.09} +
{pri_pmpm_byspecialty.10} +
{pri_pmpm_byspecialty.11} +
{pri_pmpm_byspecialty.12}

@YTDAve
If {@CountMonths} <> 0 Then
    {@SumMonths}/{@CountMonths}
Else
    0


This should then allow you to use the Previous () function

if Previous({pri_pmpm_byspecialty.CenterName}) = {pri_pmpm_byspecialty.CenterName} then
  {@YtdAve} + Previous({@YtdAve})
else
  0

Hope this helps

Gary
0
 
OutinCommented:
Gary,

Why use the previous function?

If the @YtdAve function is written correctly it is possible to use a normal summary which will show what's needed?


Greetz,

Outin
0
 
brokeMyLegBikingAuthor Commented:
that worked, thanks!
0
 
OutinCommented:
Glad I could be of assistance...

Outin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.