| Author |
Message |
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
   Topic: Summarise a conditional sum
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.
Edited by rkrowland - 21 Dec 2011 at 1:41am
|
 IP Logged |
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 21 Dec 2011 at 5:41am |
you could try to create a formula like @TimeTif {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})
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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.
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 21 Dec 2011 at 6:00am |
|
I have no idea why my post is formatted like that....
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 21 Dec 2011 at 10:20pm |
That's brilliant, thanks!
Much appreciated!
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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;
in the group header it returns a null value. Can running totals only be placed in a group footer?
Thanks,
Ryan.
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
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.
Edited by rkrowland - 22 Dec 2011 at 3:43am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
Edited by DBlank - 22 Dec 2011 at 3:49am
|
|
IP Logged |
|
|
|
