Reporting on data for 6 previous months

[carla]

I am creating a report by pulling data from a Customer Support database. I need to report on how many cases had a status of "open" on the last day of each month for the past 6 months not including the current month.

For example, a case is opened in January and closed in March. That case will need to be included in the count of open cases for January and February because it had a status of "open" in both of those months.

I'm stumped. Can anyone help point me in the right direction on how to do this?

 

[kenhamady]

I assume that you have a close date and an open date in the same record. If so you first you have to select the records. That means selecting all records where the close date is null (still open) or where the close date is after the EOM of the first month.

IsNull(Close) or Close >= Date(2000,9,30)
and Open <= (2001,2,28)

The last line ensures that it was open before the end of your target period.


This get all of the records that count in the report. Now you need a conditional formula for each month, something like this would be for January 2001:


If (IsNull(close) or close > (2001,1,31))
and Open <= (2001,1,31)

then 1
else 0


This will print a 1 on every record that counts open end of January. Sum this column to get the Jan number. Do the same thing for the other 5 months.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)