Using Excel with Dynamics GP March 22, 2018 TO RECEIVE CPE CREDIT - - PDF document

▶
using excel with dynamics gp
SMART_READER_LITE
LIVE PREVIEW

Using Excel with Dynamics GP March 22, 2018 TO RECEIVE CPE CREDIT - - PDF document

Aug 02, 2023 209 likes •372 views

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

slide-1
SLIDE 1

3/21/2018 1

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

slide-2
SLIDE 2

3/21/2018 2

INTRODUCTIONS

Charles Allen

Senior Managing Consultant

  • More than 27 years experience
  • Microsoft MVP

AGENDA

  • Using Excel Paste with Journal Entries
  • Using Excel with Integration Manager
  • SmartLists – Exporting & Macros
  • Excel Refreshable Reports
  • OData
  • Q&A
slide-3
SLIDE 3

3/21/2018 3

USING EXCEL PASTE WITH JOURNAL ENTRIES

Added to GP 2013 R2 Used for creating GL transactions Single-company Can use with Analytical Accounting

  • You must create aliases

Description Account Debit Credit AA Alias Airfare for Charles 200-6520-00 750 000-2111-00 750

  • Create your spreadsheet
  • Do not select the titles
  • Enter the top half of the journal entry
  • Click the Excel button

USING EXCEL PASTE WITH JOURNAL ENTRIES

slide-4
SLIDE 4

3/21/2018 4

USING EXCEL PASTE WITH JOURNAL ENTRIES USING EXCEL WITH INTEGRATION MANAGER

  • Excel can be used as a data source
  • You must name the range of data
  • The named range is the table referenced in

Integration Manager

slide-5
SLIDE 5

3/21/2018 5

  • 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

slide-6
SLIDE 6

3/21/2018 6

  • Excel macros can be used with SmartLists to

perform functions like organizing the data into a pivot table

SMARTLISTS – EXPORTING & MACROS

Go to the SmartList button & choose Export Solutions Enter a name for the Solution Select the Excel file containing the macro Enter the names

  • f the macros

Select the Users Select Excel Select the SmartList to use Save your Solution

  • Steps to create an Export Solution

SMARTLISTS – EXPORTING & MACROS

slide-7
SLIDE 7

3/21/2018 7

Hint: Create your macros first!

SMARTLISTS – EXPORTING & MACROS

  • To use the Export Solution
  • Open your SmartList Favorite
  • Click Excel >> Name of Export Solution

SMARTLISTS – EXPORTING & MACROS

slide-8
SLIDE 8

3/21/2018 8

EXCEL REFRESHABLE REPORTS

What are they?

Excel files with a live data connection to GP data An alternative to exporting data using SmartList

Deploy them from the Administration area page >> Setup >> Reporting Tools Setup

How to get them

EXCEL REFRESHABLE REPORTS

slide-9
SLIDE 9

3/21/2018 9

How to find them

Access from the Series navigation lists

EXCEL REFRESHABLE REPORTS

How to use them

EXCEL REFRESHABLE REPORTS

slide-10
SLIDE 10

3/21/2018 10

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

How to use them

EXCEL REFRESHABLE REPORTS

What is it?

A way to access GP data using protocols like HTTP & methodologies like REST Can be used with Excel & other applications like Power BI ODATA

slide-11
SLIDE 11

3/21/2018 11

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?

Install the OData service on the SQL Server using the GP media ODATA

slide-12
SLIDE 12

3/21/2018 12

How do you get it?

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 ODATA

How do you deploy it?

Configure the OData service Set security to tables, views & stored procedures ODATA

slide-13
SLIDE 13

3/21/2018 13

How do you use it?

Open Excel & use the Data tab to connect to the OData feed ODATA

Select the object Choose to view it as a table, pivot table or chart

How do you use it? ODATA

slide-14
SLIDE 14

3/21/2018 14

Time for some Q&A

BKD, LLP is 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 acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org. The information contained in these slides is presented by professionals for your information

  • nly & is not to be considered as

legal advice. Applying specific information to your situation requires careful consideration of facts & circumstances. Consult your BKD advisor or legal counsel before acting on any matters covered.

slide-15
SLIDE 15

3/21/2018 15

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