Dynamics GP Reporting Series: Customer Statement

It seems I totally forgot to publish this article and thought that I finalized the series!

Customer statements are normally dependant on the each client process, end user will normally request the customer statement to be in the way that fulfills their needs, some users will require the statement to integrate the payment document management module and some requires the aging.

However, normally the customer statement option in the utilities covers almost all needed statements, below statement is a standard statement could generate the customer statement in “Payment – Invoice” columns which make easier to the accountant the understanding of customer transactions.

SQL Command:

SELECT      CUSTNMBR,    
DOCNUMBR AS DOCNUM,     DOCDATE,    
CASE RMDTYPAL    
WHEN 1 THEN 'SLS'       WHEN 2 THEN 'SCP'     
WHEN 3 THEN 'DR'       WHEN 4 THEN 'FIN'     
WHEN 5 THEN 'SVC'       WHEN 6 THEN 'WRN'     
WHEN 7 THEN 'CR'       WHEN 8 THEN 'RTN'     
WHEN 9 THEN 'PMT'      END AS CODE,    
ISNULL(CASE RMDTYPAL    
WHEN 1 THEN ORTRXAMT WHEN 3 THEN ORTRXAMT    
WHEN 4 THEN ORTRXAMT WHEN 5 THEN ORTRXAMT    
WHEN 6 THEN ORTRXAMT ELSE 0    
END,0) AS INVOICE,    

ISNULL(CASE RMDTYPAL    
WHEN 7 THEN -(CURTRXAM)     WHEN 8 THEN -(CURTRXAM)    
WHEN 9 THEN -(CURTRXAM)     ELSE 0    
END,0) AS PAYMENT,    DOCNUMBR AS APPLIEDTO    
FROM RM20101
WHERE  (
(RMDTYPAL = 7 AND CURTRXAM <> 0)
OR (RMDTYPAL = 8 AND CURTRXAM <> 0) OR (RMDTYPAL = 9 AND CURTRXAM <> 0)
OR RMDTYPAL = 1     OR RMDTYPAL = 2 OR RMDTYPAL = 3
OR RMDTYPAL = 4 OR RMDTYPAL = 5 OR RMDTYPAL = 6) 
AND VOIDSTTS <> 1     
UNION     
SELECT      CUSTNMBR,    
APFRDCNM AS DOCNUM,     DATE1 AS DOCDATE ,    
CASE APFRDCTY
WHEN 7 THEN 'CR' WHEN 8 THEN 'RTN'       
WHEN 9 THEN 'PMT' END AS CODE,         
0 AS INVOICE,    
ISNULL(CASE APFRDCTY     WHEN 7 THEN APPTOAMT    
WHEN 8 THEN APPTOAMT     WHEN 9 THEN APPTOAMT    
ELSE 0     END,0) AS PAYMENT     ,    
APTODCNM AS APPLIEDTO    
FROM RM20201
WHERE POSTED <> 0     
ORDER BY DOCDATE    

As all other reports, to avoid any issues when creating our crystal report, we’ll need to add the above statement as a “Command” instead of direct tables as the command does not store the database name along with the statement, the report will need to be designed to look like the below:

Crystal Report Design:

 

Few optimizations are still required to include calculations and formulas to get the report in the needed format.

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com