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

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

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({@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

 

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

 

 

 

 

 

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