Skip to content
+1-888-319-3663

COMMUNITY FORUM

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.
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
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.


If you would like to submit an answer or comment, please sign in to the eOne portal.