Author |
Message |
bholt
Newbie
Joined: 30 Oct 2012
Online Status: Offline
Posts: 16
|
Topic: Totals of YTD and Monthly in the same report? Posted: 31 Oct 2012 at 10:49am |
Ok, so I've been using Crystal Reports for about a week now so I'm very new to it and have only taken the intro. class via Blackbaud (signed up for the intermediate in a week or so). I've got enough knowledge to get a report for my boss going, though, and I've got 95% of it completed. However, I'm stuck on this last bit. I have a summary of the gifts in the report, and that's working fine. I have it filtered so that it only shows the sum of gifts given in this fiscal year, and then I have the per cent of our yearly goal using this formula: sum({GfFnds_1.GfFnds_1_Amount},{GfCnBio.GfCnBio_Constit_Code}) % 525000. And that is also working fine. However, I also need to show the sum of giving for the current month (or whatever month) and the % of the goal we have for that corresponding month. I tried to do the % with the formula nelow, and although I'm told there are no errors the result is 0 which is definitely not correct. So clearly I'm not writing the right formula, but I'm not sure what to do to get the total for the month on the same report as the total for the year to date (and then get the % of that monthly total). Any help? Thanks!! if {Gf.Gf_Date} in DateTime (2012, 10, 01, 00, 00, 00) to DateTime (2012, 10, 31, 00, 00, 00)then sum({GfFnds_1.GfFnds_1_Amount},{GfCnBio.GfCnBio_Constit_Code}) % 37500
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Nov 2012 at 3:58am |
there ar eseveral ways to approach this but here is one
write a formula to give you the value of the amount when the date is in the current month and zero all other row s out. then sum this at the report footer level
//currentmonthonly
if {Gf.Gf_Date} in monthtodate then {GfFnds_1.GfFnds_1_Amount} else 0
use this formula in your sum % formula
sum(@currentmonthonly) % 37500
|
IP Logged |
|
bholt
Newbie
Joined: 30 Oct 2012
Online Status: Offline
Posts: 16
|
Posted: 01 Nov 2012 at 9:10am |
Hi DBlank, Thanks so much for your help. I've tried your suggestion and it got me part of the way there...I think I'm formatting it incorrectly. I am unsure as to how to incorporate the monthtodate formula into the sum/% formula? I came up with the formula below, which does give me the % based on the 37500, buuut doesn't seem to filter out gifts that weren't made last month (I did last month since it's Nov. 1st and nothing will have been recorded yet in the current month). What am I missing? :-/ Also, can I use the same //lastmonth only if formula to also create a line that shows the total amount of giving in the last month (or the current month depending on when I run the report)?
Thanks so much for bearing with me!!! I'm trying to get the hang of this! //lastmonthonly if {Gf.Gf_Date} in monthtodate then {GfFnds_1.GfFnds_1_Amount) else 0 sum({GfFnds_1.GfFnds_1_Amount}, {GfCnBio.GfCnBio_Constit_Code}) % 37500
|
IP Logged |
|
bholt
Newbie
Joined: 30 Oct 2012
Online Status: Offline
Posts: 16
|
Posted: 01 Nov 2012 at 9:41am |
Also, if I try to insert the current month only information into the sum formula I get an error message telling me I'm missing a ). But no matter where I add one, I get the same message. I tried it like this: //lastmonthonly if {Gf.Gf_Date} in lastmonthtodate then {GfFnds_1.GfFnds_1_Amount) else 0 sum(@lastmonthonly {GfFnds_1.GfFnds_1_Amount}, {GfCnBio.GfCnBio_Constit_Code}) % 37500 And like this: sum(//lastmonthonly if {Gf.Gf_Date} in lastmonthtodate then {GfFnds_1.GfFnds_1_Amount) else 0 {GfFnds_1.GfFnds_1_Amount}, {GfCnBio.GfCnBio_Constit_Code}) % 37500 Same error for both
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Nov 2012 at 9:47am |
I need a little clarification (but just so you know you cannot put where conditions on summaries the way you are trying to do)
do you need the month to date per constituant or for all constituants together (or both)?
either way make one formula field, as the exmple call it currentmonth as if {Gf.Gf_Date} in monthtodate then {GfFnds_1.GfFnds_1_Amount) else 0
YOu can place this on the detail section to see how it works.
for any row that was in this month you should see the gift amount, for all the other rows not in this month you should see a 0.
now you can sum this new formula field at the group level
to see the total gifts for the month only.
Or you can sum it at the report level to see the month total across all consituants
If you chaneg these last 2 formulas to include %3700 it will change it from the sum to your percentage display
|
IP Logged |
|
bholt
Newbie
Joined: 30 Oct 2012
Online Status: Offline
Posts: 16
|
Posted: 01 Nov 2012 at 10:16am |
Ah, I need to see the month to date for all constituents together. Oooh ok, so I think I understand now. But I'm having 2 issues trying to implement this. First: I made the formula field like you said: //lastmonth if {Gf.Gf_Date} in lastmonth then {GfFnds_1.GfFnds_1_Amount) else 0 and named the formula lastmonth. There aren't any errors in the formula according to CR, and the formula above is exactly what I have in CR. But when I put it into the detail section of my report, nothing shows up in the field - and there should be #s. Second: When I try to make another formula to sum at either the group or report level: sum( {@lastmonth}) or sum ( {@lastmonth}, {CfCnBio.GfCnBio_Constit_Code}) I get this error code: "This field cannot be summarized." ?
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Nov 2012 at 10:20am |
the
//lastmonth
part is just a notation to to tell you what to call this formula. you will see it turns the text green in the formula editor because the line starts with the // which indicates a notation, not actual formula code
in the formula editor just use
if {Gf.Gf_Date} in lastmonth then {GfFnds_1.GfFnds_1_Amount) else 0
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Nov 2012 at 10:23am |
the reason the other formulas got the error
"This field cannot be summarized."
is because your "currentmonth" formula was broken and it was being interpretted as a text field which cannot be summed.
|
IP Logged |
|
bholt
Newbie
Joined: 30 Oct 2012
Online Status: Offline
Posts: 16
|
Posted: 01 Nov 2012 at 10:25am |
Ok, that's what I thought but when I tried to remove it I got this error message: "A number, currency amount, boolean, date, time, time-date, or string is expected here." and it won't let me save it. So I added the // back in 'cause then I didn't get an error. Obviously that's wrong too though! :-/
|
IP Logged |
|
bholt
Newbie
Joined: 30 Oct 2012
Online Status: Offline
Posts: 16
|
Posted: 01 Nov 2012 at 10:27am |
Is it broken because of the //lastmonth? Or is it something else?
|
IP Logged |
|
|