June 24, 2014 at 9:18 am
Hello All,
I have looked on a few dozen web pages and cannot find the answer to this one.
Here is what I am trying to do:
My company would like a report stating how many shipments were "On-Time" in the last week.
This report will show if a shipment is early (w -1), On-Time (w 0), 1 week late (w +1), 2 weeks late (w +2), 3+ weeks late (w +3...)
They anchor these numbers to the calendar week.
For instance, if the due date was 06/18/14 they consider it "On-Time" if it is shipped any time between 06/15/14 - 06/21/14. The week starts on Sundays.
Now, on to what I have tried.
I created a column named "DateDiff" (Not very original) and used this code:
=DateDiff("w",Fields!duedate.Value,Fields!shipdate.Value)
I am running into an issue because it seems that SSRS calculates a week as 7 days from the duedate.
For instance, a shipment with a due date of 06/17/14 which was shipped on 06/11/14 is showing as "On-Time" ( w 0) instead of "Early" (w -1) even though it is in a previous calendar week.
Can someone direct me on how to resolve this?
P.S. I cannot alter the query code at the source because I am connecting to a very old PROGRESS db that doesn't allow logic like this.
June 30, 2014 at 8:33 am
All,
I was able to resolve this issue using this code:
= DatePart("ww",Fields!duedate.Value,IIF(year(Fields!duedate.Value) > 2010,vbSunday,vbSaturday))
This provided the week number and I was able to go from there.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply