Technical Questions: Summarise a conditional sum

Print Page | Close Window

Summarise a conditional sum

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15205
Printed Date: 06 May 2024 at 9:30pm

Topic: Summarise a conditional sum

Posted By: rkrowland
Subject: Summarise a conditional sum
Date Posted: 20 Dec 2011 at 11:47pm

Hi,

I have a conditional sum formula in my report, this formula checks if the target of an individual is 0. If it is 0 the formula sums their actual performance since they don't have a target and if it's not 0 it sums their target.

This is working fine, now I want to sum the formula above at the next group level (the team of the individual) but I'm having a few problems, I can't use the insert summary wizard as the above formula isn't in the list to summarise and I can't reference the above formula in another formula as I get an error saying "This field cannot be summarised". Any help would be much appreciated!

Working formula:

@TimeT YTD

if sum({Command.Time Target},{Command.Fee Earner}) = 0
then sum({Command.Billable Time},{Command.Fee Earner})
else sum({Command.Time Target},{Command.Fee Earner})

I've then tried the following formula to summarise the above by team:

@TimeT YTD Team

sum(@TimeT YTD},{Command.Section Code})

If someone could help me fix this I would be most grateful.

Regards,

Ryan.

Replies:

Posted By: kostya1122
Date Posted: 21 Dec 2011 at 5:41am

you could try to create a formula like
@TimeT

if {Command.Time Target} = 0
then {Command.Billable Time}
else {Command.Time Target}

then off of that formula create your summaries.

@TimeT YTD Team

sum(@TimeT},{Command.Section Code})

@TimeT YTD

sum(@TimeT},{Command.Fee Earner})

Posted By: rkrowland
Date Posted: 21 Dec 2011 at 5:58am

Thanks for the suggestion, if that were possible for my purposes that would work.

However, at record level the data is broken down monthly, occasionally (maternity/starters/leavers) a fee earner will have a target for selected months in the year, I need my YTD Time Target field to only use there actual time recording if they haven't had a target at any point in the year - otherwise their YTD target should be used.

Using this method would create a mixture of actual/target information in the YTD summary for people who only have a monthly target some of the year.

Regards,

Ryan.

Posted By: rkrowland
Date Posted: 21 Dec 2011 at 6:00am

I have no idea why my post is formatted like that....

Posted By: DBlank
Date Posted: 21 Dec 2011 at 6:27am

you can use a variable formula or Running Total to accomplish this.

I use RTs so here is a verison of that.

name=billable (or whatever)

field to summarize=billable time

type =sum

evaluate=use a formula

sum({Command.Time Target},{Command.Fee Earner}) = 0
reset=on group you want to see the summary

place in group footer

name=Time (or whatever)

field to summarize=time target

type =sum

evaluate=use a formula

sum({Command.Time Target},{Command.Fee Earner}) <> 0
reset=on group you want to see the summary

place in group footer

Posted By: rkrowland
Date Posted: 21 Dec 2011 at 10:20pm

That's brilliant, thanks!

Much appreciated!

Posted By: rkrowland
Date Posted: 21 Dec 2011 at 10:36pm

Another quick question, this works fine for my team summary of individuals - I now want to use the team totals in a department summary of teams (a group level above the team summary) - when I try placing the new formula;

{#Time}+{#Billable}

in the group header it returns a null value. Can running totals only be placed in a group footer?

Thanks,

Ryan.

Posted By: rkrowland
Date Posted: 22 Dec 2011 at 2:09am

I'd still like to know how I could've got round this if anyone has any suggestions... However, I got too frustrated trying to do this with Crystal and went back to my SQL command and just amended that to calculate the YTD target and then adjust the monthly targets accordingly within the query so now I can just do plain and simple sums in Crystal.

Thanks again.

Posted By: DBlank
Date Posted: 22 Dec 2011 at 3:46am

RT's don't work in headers as they function more akin to whileprintingrecords. basically they happen in a later data pass than the summary functions. That is why you were able to use the summary as a condition in the RT evaluation formula.

I think you can use an RT in a header if it is in a crosstab (created in a pass after the the RT pass) but there are rules about how that works and can't recall them at the moment....(I think that grouping architectur ein both the report and te CT have to be the same).

You also could have used subreports to show values in headers although that forces multiple data calls.

You also can write a command object to do some of this for you and use that as your source.

Or you can write a stored proc or sql view an duse that as the source.

Print Page | Close Window