To Receive CPE Credit Participate in entire webinar Answer - PDF document

Excel Refreshable Reports in Dynamics GP Charles Allen Senior Managing Consultant BKD Technologies June 22, 2017 callen@bkd.com 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 o Complete group attendance form with  Title & date of live webinar  Your company name  Your printed name, signature & email address o All group attendance sheets must be submitted to training@bkd.com within 24 hours of live webinar o Answer attendance checks & polls when they are provided • If all eligibility requirements are met, each participant will be emailed their CPE certificates within 15 business days of live webinar 1

Introduction Charles Allen • Senior managing consultant More than 27 years of experience • • Microsoft MVP Agenda • Introduction • What are these reports? • How do they work? • How do you use them? • How do you create them? • Security • Q&A 2

What are these reports? What Are These Reports? Reports that use Excel to display the information Based on existing SmartLists Meant to provide tabular reports – not a replacement for financial reporting tools like Management Reporter Use Office Data Connections (ODC) to create live connections to GP data Can be stored & accessed from a shared folder or SharePoint library 3

How do they work? Office Data Connections • Office Data Connections enable Excel files to connect to data • Files can be stored locally or in SharePoint • Data Connections can be created & modified Check out this article from our blog on modifying ODC files that ship with GP: http://dynamicsgpinsights.com/2012/05/29/how-to-modify-an- office-data-connection-in-dynamics-gp/ 4

How do you use them? Deploying Excel Reports • Part of the GP installation • Change or redeploy reports from Reporting Tools Setup 5

Accessing the Reports • Use the Excel Reports shortcut from the Area Pages Accessing the Reports • Three ways to view a report o Double-click it o Click on it & then click the hyperlink o Mark the check box by it & click the View button in the Action Pane 6

Viewing & Working with Reports • Initial view upon launching a report Viewing & Working with Reports • Click Enable Content to view the data • You can change the warnings in the Excel Trust Center options 7

Viewing & Working with Reports • Data is displayed using a Table format Viewing & Working with Reports • You can use the Table Tools to modify the look, feel & other properties of the table o Insert Slicers o Create Pivot Tables o Change available columns o Change colors & borders 8

Viewing & Working with Reports • Use the Refresh button to bring in new data while the Excel file is open Viewing & Working with Reports Adding a Pivot Table • Use the Summarize with PivotTable option on the Table Tools ribbon 9

Viewing & Working with Reports Adding a Slicer to filter data • Use the Insert Slicer option Viewing & Working with Reports Add Total Rows • Select Total Row in the Table Style Options section • Select the type of total in the cell 10

Creating Excel Reports Creating Excel Reports Three options • Create your own ODC file • Use SmartList Designer • Use SmartList Builder 11

Create Your Own ODC File • Start with a blank Excel spreadsheet or new empty file • Click the Data tab • Select a data source from the Get External Data section • Select the table(s) or create a SQL Query Use SmartList Designer • Launch SmartList • Click New • Create a SmartList, but base it on a SQL view • Use the Publish button 12

Use SmartList Designer Use SmartList Designer • With GP 2016 you can use Workflow to create an approval for creating views used in Excel Reports 13

Use SmartList Builder • SmartList Builder is from eOne Solutions • Includes four functions o SmartList Builder o Excel Report Builder o Navigation List Builder o Drill Down Builder Use SmartList Builder 14

Use SmartList Builder • Similar process to creating a SmartList • You can create Lists or Pivot Tables Security 15

Security • Excel reports require SQL Security be set in SQL Server Management Studio, just like SSRS reports • Use the existing RPT database roles or create your own Questions 16

BKD Technologies Support Center • Microsoft Dynamics GP – 877.253.7778 GPsupport@bkd.com • Monday–Friday, 8 a.m.–5 p.m. Call Support Center toll-free Stay Connected Follow dynamicsGPinsights.com us on Twitter Join us on LinkedIn At Dynamics GP Insights you can read articles, watch webinars & join View conversations videos on YouTube 17

Continuing Professional Education (CPE) Credits 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 only. Applying specific information to your situation requires careful consideration of facts & circumstances. Consult your BKD advisor before acting on any matters covered herein or in these seminars CPE Credit • CPE credit may be awarded upon verification of participant attendance • For questions, concerns or comments regarding CPE credit, please email the BKD Learning & Development Department at training@bkd.com 18

Charles Allen | Senior Managing Consultant | 713.499.4629 | callen@bkd.com 19