Robert Laughlin 9/12/2012 10:04:40 AM

New to GP10

I am a newbie to GP10, but my company needed a better way to calculate commissions.  Our commission calculations includes different rates by product, salesperson and customer.  We were doing this pretty much manually by extracting data to Excel and working on it there.

 My solution involved using a Python script to fetch Sql Server data from three tables.   Doing the calculations, and then using report Lab to produce PDFs that could be sent to the Salespersons.  I am willing to share my solution if there would be any interest.  For example, here is my SQL select statement:

 sql = "select RM20101.CUSTNMBR, RM20101.DOCNUMBR, RM20101.DOCDATE, RM20101.DINVPDOF, RM20101.SLSAMNT," \
   + " RM00101.CUSTNAME, RM00101.SLPRSNID," \
   + " SOPNUMBE, ITEMNMBR, ITEMDESC, ITEMDESC, XTNDPRCE, INVINDX" \
   + " from dbo.RM20101" \
   + " LEFT OUTER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR" \
   + " LEFT OUTER JOIN SOP30300 ON SOPNUMBE = RM20101.DOCNUMBR" \
   + " WHERE YEAR(DINVPDOF) = %d AND MONTH(DINVPDOF) = %d ORDER BY SLPRSNID, RM20101.DOCNUMBR" % (Year, Month)

 

I wonder if anyone else is using Python to generate custom reports when Smart Lists is not up to the job.  On the other hand, perhaps I will look silly once someone points out a much easier way to do this.

Robert

Version: GP 10
Section: Dynamics GP, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3