Getting More Out of Excel and Dynamics GP

1. Getting More Out of Excel and Dynamics GP Mark Polino - I.B.I.S., Inc. Principal Consultant, Microsoft MVP

2. Feel free to Tweet, Blog or update Facebook live with things you learn in this session. Official Tag: #GPSummit Share

3. Ways to Get Data Out of Excel What Can You Do with Data Once it’s in Excel? Analyze It Present It Agenda

4. SmartList Export SmartList Export Solution Navigation List Export Report Writer FRx / Management Reporter 10 Ways to get Data From Microsoft Dynamics® GP to Excel

5. SQL Server Reporting Services 10 Ways to get Data From Microsoft Dynamics GP to Excel

6. 10 Ways to get Data From Microsoft Dynamics GP to Excel • Refreshable Excel Reports • MS Query • Analysis Cubes • SQL Query

7. Support Debugging Tool http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx Third Party Tools Leftovers

8. Analyze it What to Analyze How to Analyze Present It Once Microsoft Dynamics GP Data is in Excel

9. Data Transaction Sales by Salesperson Sales by Customer Associated item sales Customer who haven’t bought a particular item Performance Vendor Delivery Performance Sales Shipment Performance Customer Payment Performance What to Analyze

10. Aggregated Data Gross Margin YTD Profit Current Ratio Meta Data Workload Volume Staff Performance What to Analyze

11. Transform Data Subtotal Data Pivot Data Pivot Lots of Data Chart Data Use Statistical Data How to Analyze Data

12. Tables Charts Trend Lines Conditional Formatting Sparklines Slicers Presenting Information

13. Dashboards Online Microsoft SharePoint Excel Services Presenting Information

14. More Tips • Microsoft Dynamics GP 2010 Cookbook • Book, Kindle and PDF options Amazon, Barnes and Noble, Packt Publishing

15. Follow up PowerPoint deck has lots of detail and links for more information Fill out your evaluation! Follow up: mpolino@gmail.com www.DynamicAccounting.net Twitter.com/DynAcctNet Facebook.com/DynAcctNet Follow Up

16. SmartList Export Push the Big Excel Button SmartList Export SolutionExport to Excel with consistent name and Excel Macros before/after exporthttp://msdynamicsgp.blogspot.com/2009/08/weekly-review-excel-ing-with-smartlists.html Navigation List ExportGoTo > Send to Excel Report WriterDex.ini setting ExportOneLineBody=TRUE - eliminate line wrapping when exporting reports to comma or tab delimited filesAlso possible with Support Debugging Tool - http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx FRx / Management Reporter 10 Ways to get Data From Dynamics GP to Excel

17. SQL Server Reporting ServicesExport | Select a Format > Excel Refreshable Excel Reportshttp://msdynamicsgp.blogspot.com/2008/03/weekly-dynamicexcel-reports-in-dynamics.htmlhttp://www.dynamicscare.com/blog/index.php/how-to-deploy-200-excel-reports-with-direct-data-connection-to-dynamics-gp-100/http://msdynamicsgp.blogspot.com/2011/02/frequently-asked-questions-about-excel.html MS Queryhttp://msdynamicsgp.blogspot.com/2010/04/weekly-dynamic-portable-excel-reports.htmlhttp://msdynamicsgp.blogspot.com/2010/05/weekly-dynamic-portable-excel-reports.html Analysis CubesTalk to your partner SQL QuerySet Header to export by default in SQL Server Management Studiohttp://msdynamicsgp.blogspot.com/2009/10/weekly-dynamic-copy-headings-and-data.html 10 Ways to get Data From Dynamics GP to Excel

18. Support Debugging Tool http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx 3rd Party Toolshttp://www.isv-central.com Leftovers

19. Analyze it What to Analyze How to Analyze Present It Once Dynamics GP Data is in Excel

20. Data Transactions Sales by Salesperson Sales by Customer Associated item sales - http://msdynamicsgp.blogspot.com/2010/06/weekly-dynamic-customers-who-bought-x.html Customer who haven’t bought a particular item - http://vstoolsforum.com/blogs/sqlserver/archive/2008/04/30/how-to-get-a-query-of-all-customers-that-have-not-been-shipped-a-product.aspx Performance Vendor Delivery Performance Sales Shipment Performance Customer Payment Performance What to Analyze

21. Aggregated Data Gross Margin YTD Profit Current Ratio Meta Data Workload Volume http://msdynamicsgp.blogspot.com/2010/06/weekly-dynamic-counting-ap-transactions.html http://msdynamicsgp.blogspot.com/2010/07/weekly-dynamics-count-purchase-orders.html http://msdynamicsgp.blogspot.com/2010/07/weekly-dynamic-count-of-fixed-assets.html Staff Performance What to Analyze

22. Transform Data Subtotal Datahttp://msdynamicsgp.blogspot.com/2005/10/weekly-featureexcel-subtotals.html Pivot Data Pivot Lots of Datahttp://www.powerpivot.com Chart Data Use Statistical Data How to Analyze Data

23. Tables Charts Trend Lines Conditional Formatting Sparklines Slicers Presenting Information

24. Dashboards Online Microsoft SharePoint Excel Services Presenting Information

25. More Tips • Microsoft Dynamics GP 2010 Cookbook • Book, Kindle and PDF options Amazon, Barnes and Noble, Packt Publishing

26. Follow up PowerPoint deck has lots of detail and links for more information Fill out your evaluation! Follow up: mpolino@gmail.com www.DynamicAccounting.net Twitter.com/DynAcctNet Facebook.com/DynAcctNet Follow Up