Sum values from a formula field 1

[cordelia]

Help! I need to determine the sum of the values from a formula field. My report has one group and in the Details section there is a column with a formula, @DateDiff, which calculates the difference in time on a DateTime field.

I would like to get the value of the total time for all the rows returned in the Details section, and place in the Report Footer.

I tried creating a secondary formula using the SUM() function but I receive an error saying "This field cannot be sumamrized". If I hilight the field with the formula in the report and right-click, there is no 'Sum' option under the 'Insert' pop-up menu. As a side note, I'm guessing Crystal won't let me use the sum functionality as it's calculating the value of the original formula when generating the results.

So I created a formula called @SumTime (placed in the Details section)...

Shared NumberVar TotalTime;
TotalTime := TotalTime + {@DateDiff};

The formula sums up the time differences correctly, but now I'm stumped on how to get this value into the Report Footer. I created a third formula to display the value of 'TotalTime' and it displays the correct value when placed in the Group Footer, but not when placed in the Report Footer.

I think I'm close, but what am I missing or not understanding? Thank you!!

 

[lbass]

whileprintingrecords;
Shared NumberVar TotalTime;

You don't have to use "shared" for a variable unless you are sharing between a main and subreport--but you do have to use it consistently in all references to the variable.

I wonder what the content of your {@datediff} formula is, as there might be a way to adjust it so that you could insert a summary.

-LB

 

[cordelia]

It's actually a formula you previously helped me with.

@DateDiff...

if onfirstrecord or
{ae_i_pre_e.pr_num} <> previous({ae_i_pre_e.pr_num}) then
0 else
{ae_i_sta_e.status_date} - previous({ae_i_sta_e.status_date})

An additional challenge I'm running into is I'm suppressing some of the results (from the Group Header/Footer and Details sections). It appears those values are being included in the totals. I didn't realize this initially due to the data set I was working with.

Thank you for taking a look at this!!

 

[lbass]

Okay, so the variable is necessary. I just realized what you probably have is a reset formula in the group header. You can't use the same variable for both a group footer and the reort footer. Change the display formula in the group footer to:

whileprintingrecords;
Shared NumberVar TotalTime;
numbervar grtot := grtot + TotalTime;
TotalTime

Then in the report footer, use:

whileprintingrecords;
numbervar grtot;

Suppressing something doesn't eliminate it from calculations. What exactly are you suppressing? What criteria are you using? You either have to remove these records in the selection formula or you have to build them into the variable or datediff formula.

-LB

 

[cordelia]

Here's a sample of the details from my report:

STATUS: TRANSACTION DATE: LOGIN:
------- ----------------- ------
REQ MATL 1/11/2010 7:25:49AM JDOE
BUYER 2 1/11/2010 11:27:37AM BMSITH
MATLS ORDERED 1/11/2010 1:46:03PM BSMITH
CLOSED/RECVD 1/15/2010 3:16:37PM BSMITH

STATUS: TRANSACTION DATE: LOGIN:
------- ----------------- ------
APPRL REQD 1/12/2010 8:25:49AM JDOE
BUYER 2 1/13/2010 8:27:37AM BMSITH
MATLS ORDERED 1/13/2010 3:46:03PM BSMITH
CLOSED/RECVD 1/16/2010 1:16:37PM BSMITH

I'm suppressing the Details (and Group Header/Footer) where the first row in the Details section does not have a status of 'REQ MATL'.

I'm using formulas to do the suppression as I couldn't figure out a way to do this with the Select Expert.

To be honest, I'm not quite sure how to build that logic into the variable and DateDiff formula. I'll have to give all of this some more thought.

Thank you as always for your help!

 

[lbass]

Please post your current suppression formula. I think I helped you with that, too.

-LB

 

[cordelia]

Sorry for the delay in responding!

The formula in the Details and Group Footer section is...

whileprintingrecords;
numbervar x;
numbervar y;

if (x = 1
and {ae_i_sta_e.status_code} = "REQ MATL")
then y := y + 1 else y;
x := x + 1;
y

There are two formulas in the Group Header, @HeaderFormula...

whileprintingrecords;
numbervar x := 1;
numbervar y := 0;

@Header2Formula...

whileprintingrecords;
{ae_i_sta_e.status_code};

In the Section Expert I added the following under the Details and Group Footer sections for the 'Suppress (No drill-down)...

{@DetailFormula} = 0

And in the Section Expert under the Header section...

{@Header2Formula} <> "REQ MATL"

 

[cordelia]

I think I got it! hee, hee

I added logic to the @DetailFormula to set a boolean variable 'suppressed' based on the value of the variable 'y'.

And then in my two formulas, called @SumTime and @TotalTime, which sum up the time for the details in each group and the total time I added logic to perform the sum based on the 'suppressed' variable.

I'm still testing the results to be sure it's working as expected, but so far so good.

Thank you LB for all your help!!!