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

x
?
Solved

summarize a formula field.

Posted on 2005-04-19
13
Medium Priority
?
916 Views
Last Modified: 2008-01-09
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
Comment
Question by:brokeMyLegBiking
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 4

Author Comment

by:brokeMyLegBiking
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

by:bdreed35
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

by:bdreed35
ID: 13819697
Should have been:

{table.field1} + previous({table.field1})
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 4

Author Comment

by:brokeMyLegBiking
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

by:bdreed35
ID: 13820141
Can you post your rowSum formula?
0
 
LVL 4

Author Comment

by:brokeMyLegBiking
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

by:brokeMyLegBiking
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

by:
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

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

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

--
Outin
0
 
LVL 19

Expert Comment

by:GJParker
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

by:Outin
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

by:brokeMyLegBiking
ID: 13829426
that worked, thanks!
0
 
LVL 9

Expert Comment

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

Outin
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question