A quick to solve problem came up on last Friday. One of our customers needs as part of their contract with us, a list of all the sales order lines we have created for them for the previous month.This report is needed on the first of each month.
To solve this I chose my tool, reporting services and set to work.
As always everyone uses Dynamics GP differently, for this company the back order document type is not used. Thus all items remain on the same order throughout the lifecycle of the order. This makes reporting a relative doddle.
Customers are grouped using the Territory ID field by us so we can report on groups of customers. That results in the following SQL to drive the report, this was pasted into the query of a new report, and the body of the report created to show the results. We wanted to show voided orders, you can exclude them with a VOIDSTTS=0 in the where for each half.
WITH SOPWorkHist
AS
(SELECT SOP10100.SOPTYPE, SOP10100.SOPNUMBE, SOP10100.DOCDATE,
SOP10100.CUSTNMBR, SOP10100.CSTPONBR, SOP10100.CUSTNAME, SOP10200.ITEMNMBR,
SOP10200.ITEMDESC, SOP10200.OXTNDPRC, SOP10200.QUANTITY,
SOP10200.QTYCANCE, SOP10200.QTYTBAOR, IV00101.USCATVLS_6,
IV00101.USCATVLS_1
FROM IV00101 WITH (NOLOCK) RIGHT OUTER JOIN
SOP10100 WITH (NOLOCK) INNER JOIN
SOP10200 WITH (NOLOCK) ON SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
AND SOP10100.SOPTYPE = SOP10200.SOPTYPE ON
IV00101.ITEMNMBR = SOP10200.ITEMNMBR
WHERE (SOP10100.DOCDATE > @StartDate) AND (SOP10100.DOCDATE < @EndDate)
AND (SOP10100.SOPTYPE = 2)
AND SOP10100.CUSTNMBR IN(
SELECT CUSTNMBR FROM RM00101 WHERE (RM00101.SALSTERR = @CustomerIdent))
UNION
SELECT SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.DOCDATE,
SOP30200.CUSTNMBR, SOP30200.CSTPONBR, SOP30200.CUSTNAME, SOP30300.ITEMNMBR,
SOP30300.ITEMDESC, SOP30300.OXTNDPRC, SOP30300.QUANTITY, SOP30300.QTYCANCE,
SOP30300.QTYTBAOR,
IV00101.USCATVLS_6, IV00101.USCATVLS_1
FROM IV00101 WITH (NOLOCK) RIGHT OUTER JOIN
SOP30200 WITH (NOLOCK) INNER JOIN
SOP30300 WITH (NOLOCK) ON SOP30200.SOPTYPE = SOP30300.SOPTYPE
AND SOP30200.SOPNUMBE = SOP30300.SOPNUMBE ON
IV00101.ITEMNMBR = SOP30300.ITEMNMBR
WHERE (SOP30200.DOCDATE > @StartDate) AND (SOP30200.DOCDATE < @ENDDate)
AND (SOP30200.SOPTYPE = 2)
AND SOP30200.CUSTNMBR IN(
SELECT CUSTNMBR FROM RM00101 WHERE (RM00101.SALSTERR = @CustomerIdent)))
-- Now select what we require from above
select * from SOPWorkHist order by 3,2
This was fine. The next challenge was to automatically email this every month. To do this I simply set up a default parameter that calculated the dates for the previous month. If you live in the UK don’t fall into the trap of copy and paste. Many of the examples to get the first and last day of the month for reporting services expressions on the web are assuming US format dates.
For us I ensured the language setting property of the report was set to UK English.
For the parameters of the report, StartDate and EndDate had formulas entered as non-queried default values (you might like to check midnight boundary conditions here, not a worry for my requirement as orders only get entered during office hours):
StartDate:
= DateSerial(datevalue(Now().AddMonths(-1)).Year, datevalue(Now().AddMonths(-1)).Month ,1 )
and
EndDate:
=dateserial(datevalue(Now()).Year, datevalue(Now()).Month,1).AddMilliseconds(-1)
A default code was also set up for the CustomerIdent Parameter for this particular customer group.
This defaults the values using the reporting services expresssion to the first and last day of the previous month.
Finally the report had a schedule created for it that emails it every month on the first of that month to the recipients. The default values populate with the previous month’s first of the month and last day of the month, Job done.