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

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

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

 

//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

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

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

sum( mailto:%7b@currentmonth - {@currentmonth }, {GfCnBio.GfCnBio_Constit_Code})

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

sum( mailto:%7b@currentmonth - {@currentmonth })

 

If you chaneg these last 2 formulas to include %3700 it will change it from the sum to your percentage display

 

 

 

 

 

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( mailto:%7b@lastmonth - {@lastmonth }) or sum ( mailto:%7b@lastmonth - {@lastmonth }, {CfCnBio.GfCnBio_Constit_Code}) I get this error code: "This field cannot be summarized."

 

?

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

For thismonths data you use

 

 

for last months data you use

for last months data you use

 

Oookay, thanks for clarifying. That was really helpful. Ok based on the formulas above here is what I came up with, and it seems to be working (in the sense that there are no errors according to CR), however the amount is equal to zero, which is definitely not correct. Any idea why that might happen?

 

if {Gf.Gf_Date} in lastfullmonth then Sum ({GfFnds_1.GfFnds_1_Amount}) else 0 % 37500

You cannot conditionally summarize like this. That can be accomplished via Running Totals or shared variables but that is a different approach.

 

Try to reverse your thinking. What ew are doing is converting your row level data to be either the row value or a 0. Now if you sum ALL of these it would be the same total as if you had only summed some of them because you are zeroing out all of the ones you did not want to include.

 

Lets call the first formula "MTD_only"

Place this on your detail section. You will see it 'zero out' all rows that were not part of this month.

now sum this formula field

SUM( mailto:%7b@MTD_only - {@MTD_only })

this will show you the total for the currnt month only

now if you want to see it as a % add that to the summary

SUM( mailto:%7B@MTD_only - {@MTD_only }) % 37500

 

Does that help?

 

Ok. I've got it working now, thank you so much!! Sorry it took a bit for me to grasp what you were saying.

 

Ok. The only other thing I'm unsure about is how do I get the monthly amount and % to be filtered by constituent code? Looks like it's just doing the total, and I need it to be separated out...

Thanks again!