Excel Report Builder – Calculation to compare document date in saleslineitems data connection to previous work day
Donna Grice asked 7 years ago
I am trying to build an excel report builder report that will display all of the sales documents from the previous work day. I have this calculation bringing up the previous work day (I have not tested on a Monday yet, so I will have to check that).
DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())
WHEN ‘Sunday’ THEN -2
WHEN ‘Monday’ THEN -3
ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))
But I do not know how to set the restrictions, or what calculation I need, to compare the document date to the above date. I would appreciate any help. I have tried looking it up online and I am not sure how to word what I need.
DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE())
WHEN ‘Sunday’ THEN -2
WHEN ‘Monday’ THEN -3
ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))
But I do not know how to set the restrictions, or what calculation I need, to compare the document date to the above date. I would appreciate any help. I have tried looking it up online and I am not sure how to word what I need.
Answers
Nicole Albertson Staff answered 7 years ago
Donna,
This isn’t pretty, but seems to be working on my install. The Calculated Field below would be setup as a String as it returns a Yes or No. Then you can setup a restriction on the calculated field to be equal to Yes. That should show only the transactions that are the previous business day. Give it a try and let us know how it goes.
CASE CAST(DATENAME (DW, GETDATE()) as char)
WHEN ‘Sunday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 2) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Monday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 3) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Tuesday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Wednesday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Thursday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Friday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Saturday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
ELSE ‘Invalid’
END
This isn’t pretty, but seems to be working on my install. The Calculated Field below would be setup as a String as it returns a Yes or No. Then you can setup a restriction on the calculated field to be equal to Yes. That should show only the transactions that are the previous business day. Give it a try and let us know how it goes.
CASE CAST(DATENAME (DW, GETDATE()) as char)
WHEN ‘Sunday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 2) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Monday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 3) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Tuesday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Wednesday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Thursday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Friday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
WHEN ‘Saturday’ THEN
CASE
WHEN CAST({Sales Transaction Work:Document Date} as DATE) =
CAST((GETDATE() – 1) as DATE) THEN ‘Yes’
ELSE ‘No’
END
ELSE ‘Invalid’
END
Donna Grice replied 7 years ago I have GP 2010 and sql 2005. I am getting the error message that it does not recognize DATE. This looks good and I really appreciate your help. Now I just need to figure out what to use instead of DATE.