3/21/2018 Using Excel with Dynamics GP March 22, 2018 TO 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 ◦ All group attendance forms must be submitted to training@bkd.com within 24 hours of live webinar ◦ Answer attendance checks & polls when they are provided • If all eligibility requirements are met, each participant will be emailed their CPE certificate within 15 business days of live webinar 1
3/21/2018 INTRODUCTIONS Charles Allen Senior Managing Consultant • More than 27 years experience • Microsoft MVP • Using Excel Paste with Journal Entries AGENDA • Using Excel with Integration Manager • SmartLists – Exporting & Macros • Excel Refreshable Reports • OData • Q&A 2
3/21/2018 USING EXCEL Added to GP 2013 R2 PASTE WITH JOURNAL ENTRIES Used for creating GL transactions Single-company Can use with Analytical Accounting • You must create aliases USING EXCEL Description Account Debit Credit AA Alias PASTE WITH JOURNAL Airfare for Charles 200-6520-00 750 ENTRIES 000-2111-00 750 • Create your spreadsheet • Do not select the titles • Enter the top half of the journal entry • Click the Excel button 3
3/21/2018 USING EXCEL PASTE WITH JOURNAL ENTRIES USING EXCEL WITH INTEGRATION • Excel can be used as a data source MANAGER • You must name the range of data • The named range is the table referenced in Integration Manager 4
3/21/2018 • Use the Simple ODBC adapter USING EXCEL WITH INTEGRATION MANAGER SMARTLISTS – EXPORTING & MACROS SmartLists can be exported to Excel Data is now exported without formatting to increase performance Make sure the status is Completed before clicking the Excel button 5
3/21/2018 • Excel macros can be used with SmartLists to perform functions like organizing the data into SMARTLISTS a pivot table – EXPORTING & MACROS • Steps to create an Export Solution SMARTLISTS – EXPORTING Go to the & MACROS SmartList button Select the Select Excel & choose Export SmartList to use Solutions Enter a name for Save your Select the Users the Solution Solution Select the Excel Enter the names file containing of the macros the macro 6
3/21/2018 Hint: Create your SMARTLISTS macros first! – EXPORTING & MACROS • To use the Export Solution SMARTLISTS ◦ Open your SmartList Favorite – EXPORTING ◦ Click Excel >> Name of Export Solution & MACROS 7
3/21/2018 What are they? EXCEL REFRESHABLE REPORTS Excel files with a live data connection to GP data An alternative to exporting data using SmartList EXCEL REFRESHABLE REPORTS How to get them Deploy them from the Administration area page >> Setup >> Reporting Tools Setup 8
3/21/2018 EXCEL How to find REFRESHABLE them REPORTS Access from the Series navigation lists How to use them EXCEL REFRESHABLE REPORTS 9
3/21/2018 How to EXCEL use REFRESHABLE them REPORTS Data is formatted as a table You can add columns to the table Use either the XLSX or ODC file You can create your own files & your own data connections What is it? ODATA A way to access GP data using protocols like HTTP & methodologies like REST Can be used with Excel & other applications like Power BI 10
3/21/2018 BKD TECHNOLOGIES SUPPORT CENTER • BKD Technologies Support Center for Microsoft Dynamics GP ◦ 877.253.7778 (toll free) ◦ GPsupport@bkd.com ◦ Monday–Friday, 8 a.m.–5 p.m. How do you get it? ODATA Install the OData service on the SQL Server using the GP media 11
3/21/2018 How do you get it? ODATA Install the OData service on the SQL Server using the GP media You will need an SSL certificate You will need a domain service account; this account must be assigned to the DYNGRP role in the GP databases How do you deploy it? ODATA Configure the OData service Set security to tables, views & stored procedures 12
3/21/2018 How do you use it? ODATA Open Excel & use the Data tab to connect to the OData feed How ODATA Choose to view it do you Select the object as a table, pivot table or chart use it? 13
3/21/2018 Time for some Q&A BKD, LLP is registered with the National Association of State Boards of Accountancy (NASBA) as The information contained in a sponsor of continuing these slides is presented by professional education on the professionals for your information National Registry of CPE Sponsors. only & is not to be considered as State boards of accountancy have legal advice. Applying specific final authority on the acceptance of information to your situation individual courses for CPE credit. requires careful consideration of Complaints regarding registered facts & circumstances. Consult sponsors may be submitted to the your BKD advisor or legal National Registry of CPE Sponsors counsel before acting on any through its website: matters covered. www.nasbaregistry.org. 14
3/21/2018 CPE CREDIT • CPE credit will be awarded upon verification of participant attendance; however, credits may vary depending on state guidelines • For questions, concerns or comments regarding CPE credit, please email BKD Learning & Development Department at training@bkd.com Thank You! Charles Allen, Senior Managing Consultant 713.499.4629 | callen@bkd.com 15
Recommend
More recommend
