Crystal Reports for Microsoft Great Plains – overview for developer

Looks like Microsoft Great Plains becomes more and more popular, partly because of Microsoft muscles behind it. Now it is targeted to whole spectrum of horizontal and vertical market clientele. Small companies use Small Business Manager (which is based on same technology – Great Plains Dexterity dictionary and runtime), Great Plains Standard on MSDE is for small to midsize clients, and then Great Plains serves rest of market up to big corporations. Crystal Reports on other hand is leader in reporting software industry. If you are developer who is asked: how do we extend Great Plains ReportWriter with somewhat more sophisticated – read this and you will have clues on where to look further. 1.Great Plains Tables Structure – first of all you need to know tables to link. Launch Great Plains and go to Tools->Resource Description->Tables. Find table in proper series. If you are looking for customers – it should be RM00101 – customer master file. If you need historical Sales Order Processing documents – they are in SOP30200 – Sales History Header file, etc. 2.Create ODBC connection to GP Company database. Use same technique as when you create standard ODBC connection for GP workstation – but change default database to targeted company database.

EDI: Electronic Document Interchange for Microsoft Great Plains – overview for Software Developer/Programmer/DBA

Microsoft Great Plains - Microsoft Business Solutions accounting and ERP system, originally targeted to mid-size - now, with advancements and increasing reliability of its database - Microsoft SQL Server, Great Plains is attractive solution for large corporation. Big companies usually have purchasing and order processing automation via so-called Electronic Document Interchange or EDI. EDI was introduced long time ago for UNIX systems and in most of cases appears in form of Header, Lines and Trailer of predefined fixed position fields. We would like to give you - programmer, software developer, database administrator primary clues on producing EDI formatted text from Microsoft Great Plains database. Please, note however that Great Plains here is taken as example, similar approach will work for other SQL based systems: Navision (SQL Database or C/SIDE database), Microsoft RMS, Solomon as well as Oracle and other non-Microsoft products. In case of non-SQL system, such as old Great Plains Dynamics, ACCPAC on Pervasive SQL - IDE interface will involve ADO/ODBC or Microsoft Access programming. 1. Sample Query – The query below uses CAST construction to make result fixed length and meet positioning. Plus unionizing allows to produce Header and Detail in one select statement. Here we are dealing with work Sales Documents select case when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CAST('Header' as char(10)) else CAST('Detail' as char(10)) end as FIELD0,

case when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then cast(a.CUSTNAME as char(65)) else cast(cast(b.QTYDMGED as decimal(19,5)) as char(65)) end as FIELD01,

case when b.LNITMSEQ=c.LNITMSEQ+1 and b.CMPNTSEQ=c.CMPNTSEQ+1 then CONVERT(char(51), a.DOCDATE, 101) else cast(b.ITEMDESC as char(51)) end as FIELD03