Group Data by Month 1

[kapaa]

Crystal 8 - SQL Server/SQL database

I need to group the data on my report by Month, mgmt wants change requests to fall under Jan, Feb, March, etc. based upon need by date on the change ticket.

The need_by date is of course a 10 digit number that I convert to a human readable date and to do that a proprietary (I believe it is) formula is used - PDMTimeToDateTime. At this point the report looks like:

04/17/2007 Upgrade XYZ Server - Scheduled
01/15/2007 Install ABC Software - Still in progress
06/25/2007 Security patcehs 123 - Waiting for apprl

If I try to Group the data off "need_by date" Crystal doesn't see this as a date it still sees need_by as a 10 digit string so the options given are ascending, descending, etc. and the group headers are like 1,166,801,760. Trying to figure out how to setup a formula to accomplish Grouping by Month.

PS - I did try to search the knowledge base for this solutions but it appears this feature is not working currently.

 

[lbass]

Did you just use the conversion for display of the number field? You should actually be inserting your group on the formula that does the conversion.

-LB

 

[mintman]

I had this problem too, When we convert the string format date 1,166,801,760 to number in the formula and then if we group on it date field in the detail section will be messed up. For some reason it dosent show right data. But indtead if we group on the original string format data and use the converted field in the detail section then it works fine.
Thanks
M

 

[kapaa]

At first I tried to group by my converted date field hoping it would allow "group for each month" but it doesn't. I can try inserting the group on the formula but not sure what syntax to use.

 

[lbass]

Do you know whether the PDMTimeToDateTime function returns the number of seconds since 1/1/1970? If so, then you could use a formula {@convert} like:

dateadd("s",{table.yournumber}, date(1970,1,1))

You can then insert a group on {@convert} and choose "on change of month". You should be able to do that with your original conversion formula as well.

-LB

 

[kapaa]

Yes, the PDM function determines the number of seconds since ??? and converts that to a human readable time such a 1/30/2006 ... and within Crystal I can modify that to read January, 30 2006 or 2006/1/30 9:15:20 etc. After running the report I can sort the records by date and this is accurate, however, from here if I try to group the records it will attempt to group by individual dates but I need to somehow figure out how to create buckets (Jan, Feb, March..) and then get things to fall under the right bucket/group.

I suppose I could figure out what the 10 digit numbers are for 1/1/2007, 1/31/2007, 2/1/2007, 2/28/2007, etc. and create some formula that says if need_by data is >= to 156893945 but < 15789456 then January and so on. From here I might be able to Group as needed. I'd simply have to keep on top this or run it out to 2015.

 

[kapaa]

I think I figured something out, using the following formula I'm able to now group by month:

MonthName (Month (PDMTimeToDateTime ({need_by})))

So far so good, the only issue to overcome - multiple tickets created in the same month but different year.

 

[lbass]

I wouldn't use that approach. If you can format the conversion formula with date formatting, then I see no reason why group by month would not be available in the insert group screen. Choose to group on {@convert} and then choose "the section will be printed for each month".

-LB