Summarise a formula field in crystal reports 1

[reneek1]

hi there

i need to total/summarise a formula field in crystal reports.

i know crystal cannot do this but was hoping someone could help me with a work around.

the formula in the column/field i need to summarise is:

if{@Trade CAP}<0 then {@Trade Capacity per Month} else
Sum ({ps_20MonthSpreadSum.MANHRS}, {ps_20MonthSpreadSum.Resource})

Thanks in advance

regards
Renee

 

[pmax9999]

Hi Renee

If I understand the issue correctly, one approach would be to use variables to achieve the result.

To do this, create the following formula and place it in the Group Footer for the {ps_20MonthSpreadSum.Resource} Group (it can be suppressed if needed):

WhilePrintingRecords;
Global NumberVar SUMM_x;
Local NumberVar x;

If	{@Trade CAP}<0
Then 	x := {@Trade Capacity per Month} 
Else	x := Sum ({ps_20MonthSpreadSum.MANHRS}, {ps_20MonthSpreadSum.Resource});

Summ_x := Summ_x + x;

x;

Then in the Report Footer, place the following formula:

WhilePrintingRecords;
Global NumberVar SUMM_x;

Hope this helps. If it doesn't, please show the code for the 2 formulas referred to, and provide some explanation as to the layout of the report (groups etc).

Cheers
Pete

 

[Madawc]

pmax9999's method could be used without the need for variables, assuming that {@Trade Capacity per Month} is the same for all records in the group.

You can also use automatic totalling to create a summary, use it in a formula and then delete it from the report.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[reneek1]

Hi Pete

Thank you so much for your reply.

The first part of the formula works.
The second part works perfectly for the first month (first group), but subsequent months is adding them to each other.

For example the total for Oct is 6,402 which is correct, but the total for November is 22,925. It should be 16,523 (16,523+6,402 = 22,925) and it is cumulative through the months - so carrying on to Dec total shows 37,992. December should be 15,067 (15,067+16,523+6,402 = 37,992).

So i need the total of the column to be for each group (month). Is this posible?

Thanks
Renee

 

[reneek1]

Sorry - more details:

The report is grouped by calendar month.

@Trade Cap formula is:
{@Trade Capacity per Month}-Sum ({ps_20MonthSpreadSum.MANHRS}, {ps_20MonthSpreadSum.Resource})

@Trade Capacity per Month formula is:
{@Trade Numbers}*1720/12

@Forecast Hours Limited to Trade Capacity formula is: (this is the column I need to total by month(group)
if{@Trade CAP}<0 then {@Trade Capacity per Month} else
Sum ({ps_20MonthSpreadSum.MANHRS}, {ps_20MonthSpreadSum.Resource})

I just need to total the @Forecast Hours Limited to Trade Capacity field for each month (group).

Thanks.
Renee

 

[pmax9999]

Hi Renee

I don't really understand what you mean when you say the first part works but the second part doesn't. Also, the grouping is a little unclear as you say it is grouped by Calendar Month but I assume it is also grouped by {ps_20MonthSpreadSum.Resource}.

The following assumes the report is grouped 1st by Calendar Month and then by {ps_20MonthSpreadSum.Resource}). Add the following formula to the Calendar Month Group Header to reset the variable to zero.:

WhilePrintingRecords;
Global NumberVar SUMM_x := 0;

Cheers
Pete

 

[reneek1]

Thank you!
Works PERFECTLY!!!!