Transcription
4/24/2019Querying DynamicsGP Data Using SQLServerApril 25, 2019TO RECEIVE CPE CREDIT Participate in entire webinar Answer attendance checks & polls when they are provided If you are viewing this webinar in a group, complete group attendance form oAll group attendance forms must be submitted to training@bkd.com within 24hours of live webinaroAnswer polling questions when they are providedIf all eligibility requirements are met, each participant will be emailed theirCPE certificate within 15 business days of live webinar1
4/24/2019INTRODUCTIONSCharles AllenSenior Managing Consultant More than 27 years of experience Microsoft MVPAGENDA How to Determine Tables for Reports &Queries Resources for Finding Tables SQL Objects Querying GP Data Q&A2
4/24/2019SQL ObjectsSQL Objects Tables Views Stored Procedures Functions3
4/24/2019Tables The lowest level storage of data Each module comes with many tables Windows utilize multiple tables Tables utilize keys to quickly find recordsTablesSelect * From RM00101CUSTNMBRCUSTNAMECUSTCLASAARONFIT0001Aaron FitzElectricalUSA-ILMO-T1CNTCPRSNSTMTNAMEAaron FitzElectricalADAMPARK0001Adam Park ResortUSA-INMI-T2Adam Park ResortADVANCED0001Advanced PaperCo.USA-ILMO-T1Advanced PaperCo.ADVANCED0002Advanced TechSatellite SystemCAN-ONMBSK-T6Advanced TechSatellite System4
4/24/2019Views SQL views are a combination of one or moretables They select specific columns They select specific rows Security can be set at the view level instead ofthe table level Can provide simpler ways of viewing dataSQL ViewsSelect * From CustomersCustomerNumberCustomer NameAddress 1Address 2CityAARONFIT0001Aaron FitzElectricalOne MicrosoftWayADAMPARK0001Adam Park ResortSuite 9876ADVANCED0001Advanced PaperCo.456 19th Street S.ChicagoADVANCED0001Advanced TechSatellite System8765 66 Ave.TorontoRedmond321 ChestnutDriveIndianapolis5
4/24/2019Stored Procedures A group of one or more Transact-SQL statements Returns data like a table or view Can accept parameters Security can be set on the stored procedure insteadof tables Used to manipulate data Used for reports like Aged Trial BalanceStored ProceduresUSE AdventureWorks2012;GOCREATE PROCEDURE HumanResources.uspGetEmployeesTest2@LastName nvarchar(50),@FirstName nvarchar(50) ASSET NOCOUNT ON;SELECT FirstName, LastName, DepartmentFROM HumanResources.vEmployeeDepartmentHistoryWHERE FirstName @FirstName AND LastName @LastNameAND EndDate IS NULL;GO6
4/24/2019Functions Table-Valued – Returns a table data type Scalar Returns a single value Good for converting numeric values to spelled out information Example: DYN FUNC Gender – Gets spelled out gender instead of numbero 1 Maleo 2 FemaleFunctions/****** Object: UserDefinedFunction [dbo].[DYN FUNC Gender]12:29:04 AM ******/Script Date: 4/9/2019SET ANSI NULLS ONGOSET QUOTED IDENTIFIER OFFGOcreate function [dbo].[DYN FUNC Gender] (@iIntEnum integer) returns varchar(100) asbegin declare @oVarcharValuestring varchar(100) set @oVarcharValuestring case when@iIntEnum 1 then 'Male' when @iIntEnum 2 then 'Female' when @iIntEnum 3 then'N/A' else '' end RETURN(@oVarcharValuestring) ENDGO7
4/24/2019SQL ObjectsHow to DetermineTables forReports &Queries8
4/24/2019How to Determine Tables GP Table Schema Dictionaries SeriesTable TypesMain Table TypePhysical Name AbbreviationTechnical Name AbbreviationMaster000 – 099MSTRWork100 – 199WORKOpen200 – 299OPENHistory300 – 399HISTSetup400 – 499SETPTemp500 – 599TEMPRelation600 – 699RELReport Options700 – 799ROPT9
4/24/2019Table Names Display – Name that is easiest to understand, like RM Customer MSTR Physical – Name used for the physical table, like RM00101 Technical – Name used in Report Writer & in Alert Messages, likeRM CUST MSTRHow to Understand a Physical Table NameSample abbreviations for moduleGL – General LedgerCM – Bank ReconciliationRM – Receivables ManagementPM – Payables ManagementPOP – Purchase Order ProcessingSOP – Sales Order ProcessingINV – InvoicingIV – InventoryUPR – US PayrollPA – Project AccountingBOM – Bill of MaterialsAAG – Analytical AccountingMOP – MFG Order ProcessingBE – HR Benefits10
4/24/2019How to Understand a Physical Table Name Table numbers Examples RM00101 – Customer Master PM20000 – PM Transaction OPEN File GL30000 – Account Transaction HistoryResources for Finding Data11
4/24/2019Resources for Finding Data Resource Descriptions Dynamics SDK GP Table Reference Query for Finding Tables with Column Existing ReportsResource DescriptionsTablesDisplays lists oftables by product& moduleFieldsDisplays fields &tables usingthemWindowsDisplayswindows &tables used12
4/24/2019Resource DescriptionsDynamics SDK Installed from the GP media Provides a collection of documents fortable information Provides a list of functions & procedures13
4/24/2019GP Table Reference Website that provides tables by modulehttp://dyndeveloper.com/DynModule.aspx Easy to use Can be searched by Table Name & FieldNameQuery for Finding Tables with a ColumnProvides a list of tables containing a specific column nameSELECT SYSOBJECTS.NAME FROM SYSCOLUMNS,SYSOBJECTSWHERE SYSCOLUMNS.NAME 'CUSTNMBR' AND SYSCOLUMNS.ID SYSOBJECTS.ID and SYSOBJECTS.TYPE 'U' ORDER BYSYSOBJECTS.NAME14
4/24/2019Existing Reports GP Report Writer SSRS Report Builder Dynamics GP Integration Guide –Provides information aboutSmartListsUsing GP Reports15
4/24/2019Querying GP DataQuerying GP Data SELECT UPDATE INSERT16
4/24/2019Select Statement SELECT CUSTNMBR, SLSAMNT FROM RM20101 WHERE DOCDATE BETWEEN ‘2017-04-01’ AND ‘2017-04-30’ ANDRMDTYPAL 1 GROUP BY CUSTNMBR HAVING SUM(SLSAMNT) 1000 ORDER BY CUSTNMBRUpdate Statement UPDATE RM00101 SET SHIPMTHD ‘GROUND’ WHERE SALSTERR ‘TERRITORY 1’17
4/24/2019Insert Statement INSERT INTO SY04100 (BANKID, BANKNAME, ADDRESS1, ADDRESS2, ADDRESS3, CITY,STATE, ZIPCODE, COUNTRY, PHNUMBR1, PHNUMBR2, PHONE3,FAXNUMBR, TRNSTNBR, BNKBRNCH, NOTEINDX, DDTRANUM) FROM VALUES (‘BBVA’, ‘BBVA BANK’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘’, ‘044000808’, ‘’, 326,‘044000808’)Querying GP Data18
4/24/2019RESOURCES System Administrator’s Guide Microsoft Docs (https://docs.Microsoft.com/en-us/) Microsoft Dynamics GP Integration Guide19
4/24/2019BKDTECHNOLOGIESSUPPORTCENTER BKD Technologies Support Center forMicrosoft Dynamics GPo877.253.7778 (toll free)oGPsupport@bkd.comoMonday–Friday, 8 a.m.–5 p.m.BKD, LLP is registered with theNational Association of StateBoards of Accountancy (NASBA) asa sponsor of continuingprofessional education on theNational Registry of CPE Sponsors.State boards of accountancy havefinal authority on the acceptance ofindividual courses for CPE credit.Complaints regarding registeredsponsors may be submitted to theNational Registry of CPE Sponsorsthrough its website:www.nasbaregistry.org.The information contained inthese slides is presented byprofessionals for your informationonly & is not to be considered aslegal advice. Applying specificinformation to your situationrequires careful consideration offacts & circumstances. Consultyour BKD advisor or legalcounsel before acting on anymatters covered.20
4/24/2019CPE CREDIT CPE credit may be awarded upon verification of participantattendance For questions, concerns or comments regarding CPE credit,please email the BKD Learning & Development Department attraining@bkd.comThank You!Charles Allen, Senior Managing Consultant713.499.4629 callen@bkd.com21
Microsoft Dynamics GP o 877.253.7778 (toll free) o GPsupport@bkd.com o Monday-Friday, 8 a.m.-5 p.m. BKD,LLPis registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the .
