• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Display Date correctly when Grouping on a Formula in Crystal

I have created a Formula that I am grouping on.. Everything checks out with it expect for when it is trying to group on my date field and it doesn't display the Date correctly. The formula is this:

if {?GroupDate} = "Year"
    and {?Show Details} = "Yes"
    and {?Show Customers} = "Yes"
    and {?Show Project IDs} = "Yes"
    and {?GroupWhichFirst} = "Customer"
        then {BAQReportResult.Customer.CustID}
else if {?GroupDate} = "Year"
    and {?Show Details} = "Yes"
    and {?Show Customers} = "Yes"
    and {?Show Project IDs} = "Yes"
    and {?GroupWhichFirst} = "Project ID"
        then {BAQReportResult.OrderDtl.ProjectID}
else if {?GroupDate} = "Year"
    and {?Show Details} = "No"
    and {?Show Customers} = "Yes"
    and {?Show Project IDs} = "Yes"
    and {?GroupWhichFirst} = "Customer"
        then {BAQReportResult.Customer.CustID}
else if {?GroupDate} = "Year"
    and {?Show Details} = "No"
    and {?Show Customers} = "Yes"
    and {?Show Project IDs} = "Yes"
    and {?GroupWhichFirst} = "Project ID"
        then {BAQReportResult.OrderDtl.ProjectID}
else cstr(DatePart("WW",{BAQReportResult.OrderDtl.RequestDate}))



It is basically looking at the parameter being entered by someone and then displaying the group according to there selections. Whenever I want it to display the RequestDate field though, it displays as ##.## (it is displaying the actual number week of the year 1.00 - 52.00), where I would like it to display as a week ending date like 5/15.

I also have Year, Quarter, and Month as well that I will have almost the exact same formula for only I will need them displayed as 2011, 1st Quarter, 2nd Quarter, etc, and May, June, etc.

Thanks for all your help and let me know if you have any questions!

Maybe there is also an easier way for me to accomplish what I'm after, and I'm open to other suggestions. Basically what I have is Several Parameters setup for the user to select how they want their data viewed. The parameters are;
Group by Customer?
   Yes or No
Group by Project ID?
   Yes or No
Show Details?
   Yes or No
GroupWhichFirst?
   Customer or Project ID
Group Date?
   Year
   Quarter
   Month
   Week
   None

The current setup is:
Group 1: ReqDate by Year
Group 2: ReqDate by Quarter
Group 3: ReqDate by Month
Group 4: ReqDate by Week (The formula for this is displayed above)
Group 5:Customer or Project ID (Formula)
Group 6 Project ID or Customer (Formula)

Originally I didn't have formulas for the ReqDate groups but decided I needed to make some so that I didn't end up with gaps in my G5 Cust or Proj and G6 Proj or Cust.

I originally was just suppressing the Week, Month, and Quarter Groups when Year was selected as a parameter. Unfortunately even though they are suppressed the report still creates the gaps and new headers for Group 5 and 6 during any break in the Quarter, Month or week.

If anyone has ideas on an easier way to set this up I'm open to suggestions but would be just as happy with a solution to just getting my date groups to display correctly.

Thanks again!
0
SeyerIT
Asked:
SeyerIT
  • 2
  • 2
2 Solutions
 
musalmanERP ConsultantCommented:
Hi
I know Epicor is not a piece of cake ...

Your question is too long,,

If you are concerning only Date , First try to show the date without any condition...
Comment out your full code and show only

cstr(DatePart("WW",{BAQReportResult.OrderDtl.RequestDate}))

Increase the width of field , Crystal shows ### when data is more then its width...
0
 
mlmccCommented:
QUestion is not too long.  

Field width is not the issue, need to convert the week number to a date.

Use this as the formula instead of the ww

else
(
   Local DateVar target := Date ({?Year},1,1);
   Local NumberVar targetWeek;
   targetWeek := DatePart("WW",{BAQReportResult.OrderDtl.RequestDate});
    if DayOfWeek (target) in 2 to 5     then  
       target:= target + 7 * (targetWeek -1)  
    else
      target:= target + 7 * (targetWeek ) ;
    "dd-MMM-yyyy);
    CStr(target - DayOfWeek (target) + 2,"dd-MMM-yyyy)
)


mlmcc


0
 
James0628Commented:
The basic idea is to replace the week number (the last line in your formula) with the values that you want to see instead, and in a form that will still sort/group properly.

 There are a few problems with mlmcc's suggestion.

 He uses a ?Year parameter, which I don't think you have, for the year.

 I don't think this line near the end should be there:
    "dd-MMM-yyyy);

 You'd need a closing quote at the end of "dd-MMM-yyyy" on the line below that.  That also wouldn't sort properly (it would be sorted by the day first, not the month).  If you want something like 5/15, the format should be "MM/dd".  That would include leading 0's on the month and day (eg. 05/08), which you need to get the values to sort properly.

 Having said all of that, I don't think his formula quite works anyway.  For today (05/14), I get 16-May-2011, which doesn't seem right.


 When does your week end?  You're using DatePart, which should default to the week ending on Saturday, which would be today - 5/14, but in your first post, you used 5/15 as an example week ending date.  So, does your week end on Saturday (5/14) or Sunday (5/15)?

 Assuming that your week ends on Saturday, you can get the week ending date using:

{BAQReportResult.OrderDtl.RequestDate} +
 (7 - DayOfWeek ({BAQReportResult.OrderDtl.RequestDate}))

 If your week ends on Sunday, I think you just need to tell DayOfWeek to start the week on Monday, instead of Sunday (the default):

{BAQReportResult.OrderDtl.RequestDate} +
 (7 - DayOfWeek ({BAQReportResult.OrderDtl.RequestDate}, crMonday))

 You'd need to convert the date to a string for your formula, so the last line of your formula would be replaced with:

else
CStr ({BAQReportResult.OrderDtl.RequestDate} +
 (7 - DayOfWeek ({BAQReportResult.OrderDtl.RequestDate})), "MM/dd")

 or, if your week ends on Sunday,

else
CStr ({BAQReportResult.OrderDtl.RequestDate} +
 (7 - DayOfWeek ({BAQReportResult.OrderDtl.RequestDate}, crMonday)), "MM/dd")


 The only problem I see with that is the end of the year.  For example, if your week ends on Saturday and 12/31 is on a Wednesday, the week ending date (the next Saturday) would be 01/03 of the next year, which may not be what you want to show on the report, and it would not be sorted properly if you don't include the year (that week would be included with the weeks in January of this year).  How would you want to handle that?  Just always use 12/31 as the last week ending date in the year?


 There is an assumption that your data will only include one year.  If it could include multiple years, you'd presumably need to include the year in the group string in order to get the weeks grouped/sorted properly.

 You can handle the year, month and quarter in a similar way.

 James
0
 
mlmccCommented:
James is correct ?Year should be the YEAR of the date field you are using
     Year({BAQReportResult.OrderDtl.RequestDate})

Corrected formula
else
(
   Local DateVar target := Date (Year({BAQReportResult.OrderDtl.RequestDate}),1,1);
   Local NumberVar targetWeek;
   targetWeek := DatePart("WW",{BAQReportResult.OrderDtl.RequestDate});
    if DayOfWeek (target) in 2 to 5     then  
       target:= target + 7 * (targetWeek -1)  
    else
      target:= target + 7 * (targetWeek ) ;
      CStr(target - DayOfWeek (target) + 2,"dd-MMM-yyyy")
)


I know what is causing the issue with the date being the end of the week.  It has to do with the day of the week Jan 1 falls.  Not sure what to do about it.  The formula was from Hamady's list and may have worked for the year he built it.

mlmcc
0
 
James0628Commented:
As I mentioned (near the beginning of that long post :-), the format is also presumably wrong.  "dd-MMM-yyyy" would sort by the day first.  And MMM-dd-yyyy would sort by the month name, alphabetically.  He said "I would like it to display as a week ending date like 5/15", so I'd suggest "MM/dd" (to get the leading zeros, for sorting purposes).

 James
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now