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

x
Solved

# summarize a formula field.

Posted on 2005-04-19
Medium Priority
916 Views
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.
0
Question by:brokeMyLegBiking
• 5
• 4
• 3
• +1

LVL 4

Author Comment

ID: 13819548
Is it possible to access the previous record in a formula?

3
4
5   formula= 9

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

0

LVL 28

Expert Comment

ID: 13819695
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

LVL 28

Expert Comment

ID: 13819697
Should have been:

{table.field1} + previous({table.field1})
0

LVL 4

Author Comment

ID: 13819925
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

LVL 28

Expert Comment

ID: 13820141
Can you post your rowSum formula?
0

LVL 4

Author Comment

ID: 13820147
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

LVL 4

Author Comment

ID: 13820157
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

LVL 9

Accepted Solution

Outin earned 2000 total points
ID: 13821905
Get rid of the 'WhilePrintingRecords' statement.

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

hth,

Outin

0

LVL 9

Expert Comment

ID: 13821940
With the above, I mean 'WhilePrintingRecords' in your YtdAve function.

After that, you should be able to create a &#931; summary for @YtdAve.

--
Outin
0

LVL 19

Expert Comment

ID: 13822278
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

LVL 9

Expert Comment

ID: 13822571
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

LVL 4

Author Comment

ID: 13829426
that worked, thanks!
0

LVL 9

Expert Comment

ID: 13831276
Glad I could be of assistance...

Outin
0

## Featured Post

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
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â€¦
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signalâ€¦
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. â€¦
###### Suggested Courses
Course of the Month20 days, 5 hours left to enroll