BAQ breaking dates into days/weeks/months for report

Yahoo Archive
1
You could do this in Crystal with a formula call it 'bucket' as follows:

Sorry - I can't recall the exact syntax and do not have Crystal at the
moment but this is close in VB:
If DueDate < Today Then Bucket = Past Due
If DueDate >= Today and DueDate <= Today + 7 Then
Select Case Weekday(DueDate)
Case 1: Bucket = "Sunday" // 2=Monday, etc.
ElseIf DueDate > Today + 7 and DueDate <= Today + 30 Then
WeekNum = INT(DueDate - Today) /7 //might have to tweak this a bit
Else
// more than a month out
Select Case Month(DueDate)
Case 1 : Bucket = "Jan"
Case 2: Bucket = "Feb"// and so on
End If


Having the 'Bucket' Formula, you can create a pivot table in Crystal with
headings as follows:

Part# Past Due Tue Wed Thu Fri Sat Mon Week-1 Week-2 Week-3 Week-4
Jan Feb

--
Randy Weber
weber.randy@...
(651) 263-1811

http://randallweber.com/


[Non-text portions of this message have been removed]
2
I am trying to create a report in Epicor 9.05 that shows scheduled
shipments by timeframe: by individual day for shipments required this
week, by week for the next month-ish, then monthly after that. Is it
possible to do this? I was able to create a calculated field (ReqDate -
Today) as the number of days in the future, but couldn't see a way to
group this into a week or month for certain ranges. In SQL I'd tackle
this with a CASE in a (probably long & ugly) select statement.



We can't use the standard scheduled shipments report because of some
customer & part groups we want. The desired report (or dashboard or
whatever - I'm open to suggestions for the best tool) would have a row
per customer/part and a several columns for the date ranges, with the
quantity required that date. And I should probably mention I'm new to
9.05 & BAQs (migrating from 6) so hopefully its not something too
obvious I've missed.



Thanks,

Brian.





[Non-text portions of this message have been removed]
3
I had a similar requirement where we wanted to group shipments by month on a dashboard. I used a calculated field to parse out the month value and we grouped on that. I never did tackle a by week function. Im not in the office at the moment let me know if you would like the formula and I can post tomorrow.



-----Original Message-----
From: Brian Roberts <broberts@...>
Sent: Tuesday, December 14, 2010 3:59 PM
To: vantage@yahoogroups.com <vantage@yahoogroups.com>
Subject: [Vantage] BAQ breaking dates into days/weeks/months for report

I am trying to create a report in Epicor 9.05 that shows scheduled
shipments by timeframe: by individual day for shipments required this
week, by week for the next month-ish, then monthly after that. Is it
possible to do this? I was able to create a calculated field (ReqDate -
Today) as the number of days in the future, but couldn't see a way to
group this into a week or month for certain ranges. In SQL I'd tackle
this with a CASE in a (probably long & ugly) select statement.



We can't use the standard scheduled shipments report because of some
customer & part groups we want. The desired report (or dashboard or
whatever - I'm open to suggestions for the best tool) would have a row
per customer/part and a several columns for the date ranges, with the
quantity required that date. And I should probably mention I'm new to
9.05 & BAQs (migrating from 6) so hopefully its not something too
obvious I've missed.



Thanks,

Brian.





[Non-text portions of this message have been removed]
4
If your using MS SQL 2005 and want to put it into a Crsytal report you can use the datediff function to create a timeframe grouping. You can then use the DateName function to return a more user friendly IE Monday, December Etc String if u wish.

Links:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

http://msdn.microsoft.com/en-us/library/ms174395.aspx

--- In vantage@yahoogroups.com, "Brian Roberts" <broberts@...> wrote:
>
> I am trying to create a report in Epicor 9.05 that shows scheduled
> shipments by timeframe: by individual day for shipments required this
> week, by week for the next month-ish, then monthly after that. Is it
> possible to do this? I was able to create a calculated field (ReqDate -
> Today) as the number of days in the future, but couldn't see a way to
> group this into a week or month for certain ranges. In SQL I'd tackle
> this with a CASE in a (probably long & ugly) select statement.
>
>
>
> We can't use the standard scheduled shipments report because of some
> customer & part groups we want. The desired report (or dashboard or
> whatever - I'm open to suggestions for the best tool) would have a row
> per customer/part and a several columns for the date ranges, with the
> quantity required that date. And I should probably mention I'm new to
> 9.05 & BAQs (migrating from 6) so hopefully its not something too
> obvious I've missed.
>
>
>
> Thanks,
>
> Brian.
>
>
>
>
>
> [Non-text portions of this message have been removed]
>