Report Design: Summary on formula field

Print Page | Close Window

Summary on formula field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20324
Printed Date: 29 Apr 2024 at 12:33am

Topic: Summary on formula field

Posted By: drintharamy
Subject: Summary on formula field
Date Posted: 06 Jan 2014 at 8:16am

Hi All,

I have a very simple report which contains detail section and group footer.

In Detail section of the report, I displayed formula field called RevenueByDate.

In side my formula RevenueByDate, I have:

IF onfirstrecord then
   {Revenue.Charge}
ELSE
IF {Revenue.Charge}= previous({Revenue.Charge}) then
    0
ELSE
    {Revenue.Charge}

I now want to sum this formula field (RevenueByDate) on my group footer but this field is not available for me to summarize.

Help please!

Thanks!

-------------
DOUANG RINTHARAMY

Replies:

Posted By: DBlank
Date Posted: 06 Jan 2014 at 9:18am

You can't sumamrize it. You would have to use shared variable formulas.

That said conside this:

I assume you have a (small) risk of missing a revenue charge that is the same on a subsequent row but is not actually a duplicate and this formula excluding it.

I also assume you are using this becasue you have duplicate rows in teh report because of joins.

I also would hazard a guess you have a unique PK field from your charge table.

If these assumptions are corerct a better way to handle this is to use teh PK form charges and eitehr a Running Total (RTs) or shared variable formulas that use the PK as a the key to include or excldeu a row.

I prefer RTs

In this case you would create a runnnig total

name=group_charges (or whatever)

field to summarize =revenue.charge

type=sum

evaluate= on change of field charges.PK_field

reset= on group (select the group you want to display it on)

place in group footer as RTs do not work in Headers

Posted By: drintharamy
Date Posted: 06 Jan 2014 at 10:12am

Hi DBlank,

Thank you for your reply!

I actually have solution for my issue by doing the calculation of the totals mannually.

Ex: Initialize to 0 in GH, Add to total in Detail section and display total in GF.

But, the reason I really want to be able to use the SUMMARY Function is I want to use this same formula field to do another summary in Crosstab section of the report.

-------------
DOUANG RINTHARAMY

Posted By: DBlank
Date Posted: 06 Jan 2014 at 10:19am

i believe you can use RT's in a cross tab (CT) if the CT set up follows the report design with the same groupings.

Neither the manual (shared variable solution you used) nor Running Total will be able to be used in a summary. Summaries are created in a first data pass, running totals and variables are created in a later data pass.

As lockwelle would suggest, you can also get your summary data via Stored Procedures so they are available at the point the data is brought into the report.

Posted By: drintharamy
Date Posted: 06 Jan 2014 at 10:40am

Thank you DBlank for your advice!

I started working with Running Totals (RT) and I was able to get the summary I needed.

Thanks again for your advice.

-------------
DOUANG RINTHARAMY

Print Page | Close Window