Hello!
I'm new to Crystal Reports 2011 and I'm facing a challenge trying to add up a particular field in my report based on a date condition. My source data looks something like this:
MTTK MTPER MATTER HRSWORK
00098 0112 1052358 22.8
00098 0212 1558645 8.6
00098 0312 1058745 16.9
00098 0412 1065845 15.8
00526 0212 2468950 6.9
00526 0412 2235675 5.8
00098 0412 2254622 3.2
00098 0512 7833152 23.8
MTTK is an employee number
MTPER is the accounting period (0112 is January 2012, 0212 is February 2012, etc.)
MATTER is a case number
HRSWORK is the number of hours the employee has worked on that particular matter in that particular time period.
What I would like to do, based on the MTPER parameter the User selects when they run the report, is to add up all the amounts in the HRSWORK column for the MTPER selected and all of the earlier MTPERs within the same year (xx12).
For example, assume it is currently May 2012 and when the user runs the report, they would select 0412 as the reporting period. I need a formula to add up all the HRSWORK amounts for period 0412 as well as 0312, 0212, and 0112 (a year-to-date calculation).
Caveats:
*Each employee can have multiple rows within the table with multiple MTPER entries, as the MATTER field is the unique piece of data in the row.
*Depending on when the report is run, it's possible the MTTK may have entries for future MTPERs in the database. If so, the formula needs to ignore the HRSWORK amount for periods that come AFTER the period selected (ie. If running for 0412, only add up 0412, 0312, 0212, 0112 and ignore any entries for 0512, 0612, etc.)
I've tried all sorts of summary formulas and running total fields, but with no luck. Any help or suggestions others can provide will be greatly appreciated. Thanks so much!
-Tim
Submit an article or tip