Hi,

I'm trying to use If Then statements to sum data so I can show each quarter in the same row.  My select criteria asks for a date range and returns all records for the range.  The idea being that I could report on Q1, then in the next quarter I can expand the date range to report on Q1 and Q2 and so forth, until I have the whole year.

I have a Formula called TimeworkQ1 containing the following
IF {RecordsOfWork.DateWorked} in Calendar1stQtr Then
Sum({RecordsOfWork.MinsWorked}, {Register.PersonID})
and similar formulas for Q2, Q3, Q4.  So I want to show rows of a PersonID, TimeworkQ1, TimeworkQ2 etc.  I have to group on PersonID to prevent endless rows appearing for each date worked.

Except they don't work.  If I just use the date range for this January I get the correct figures.  If I use December and January most data is zero and I'm only getting a few sums for the most recent dates.  I wonder if it's something to do with the dateworked.  If work spreads over more than a quarter, TimeworkQ1 returns nothing because of dates appearing in Q4, and the TimeworkQ4 formula returns nothing because some work was done in Q1.  It feels like this is the problem but I'm not sure.

Any suggestions?

Thanks for the advice.  My formula is just some 10% of the entire report which is full of demographics which is why I was trying to do it in this fashion. I was hoping I could export to excel and copy and paste it into a monitoring form.  As per your suggestion I've done this as a cross tab and it's working.  I'll just have to have the report split into two and then merge following the export.

Thanks,