Group by Status on montly basis

[Jeremy1216]

I need to create a report on a form to show the number of entries on different status. Each status come with a date time field to track the change like create time, resolved time. What I have to do is to create a chart for the calendar year to show the entries for different status on a monthly basis. Entries were pulled according to the date time field seems ok. However, I don't how to show it on the chart. Chart showing ok with only one status but not with all.

 

[hilfy]

Since you didn't mention which version of Crystal you're using, I'm going to assume XI.

I've done something like this in the past, but it sounds like your report may be more complex as I had only one date field that I was working off of, not different ones based on status.

I would use a couple of formulas to get what you need. The primary one will get you the number of the month that you need to work from. Assuming you're using a rolling time period and not a straight annual, means we have to account for the year as well as the month. It will look something like this:

{@MonthToUse}
Select {table.statusField}
  'Created': ToText({table.Create_Date}), 'yyyy-MM')
  'Resolved': ToText({table.Resolved_Date, 'yyyy-MM')
  Default: '0-0'

Use one line with the appropriate date field for each status. The format string ('yyyy-MM') IS CASE SENSITIVE!

This formula will be your group field. By putting the year first, in ensures that, for example, all months in 2006 appear before those in 2007.

If you need to display the month name in the group header instead of the formula you're actually grouping on, you can always create a similar formula to translate the date using a format string like 'MMM-yyyy' (3-character month abbreviation) or 'MMMM-yyyy' (full month name). DO NOT sort or group by this formula as you'll get your months in alphabetical order!

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Jeremy1216]

Dell,
Thanks for the idea and I will try it out. FYI I am using the CR 10.

 

[Jeremy1216]

I am still having problem to present those data in the chart . Data were extracted correctly from the table with checking whether the time entries were null or not. I couldn't even create the formula as suggested to work. The main hurdle is how to present it on a chart on a monthly basis (I tried the cross-tab with no luck...)