260 likes | 380 Views
Getting More Out of Excel and Dynamics GP. Mark Polino - I.B.I.S., Inc. Principal Consultant, Microsoft MVP. Feel free to Tweet , Blog or update Facebook live with things you learn in this session. Official Tag : # GPSummit. Share. Ways to Get Data Out of Excel
E N D
Getting More Out of Excel and Dynamics GP Mark Polino - I.B.I.S., Inc. Principal Consultant, Microsoft MVP
Feel free to Tweet, Blog or update Facebook live with things you learn in this session. Official Tag: #GPSummit Share
Ways to Get Data Out of Excel What Can You Do with Data Once it’s in Excel? Analyze It Present It Agenda
SmartList Export SmartList Export Solution Navigation List Export Report Writer FRx / Management Reporter 10 Ways to get Data From Microsoft Dynamics® GP to Excel
SQL Server Reporting Services 10 Ways to get Data From Microsoft Dynamics GP to Excel
10 Ways to get Data From Microsoft Dynamics GP to Excel • Refreshable Excel Reports • MS Query • Analysis Cubes • SQL Query
Support Debugging Tool http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx Third Party Tools Leftovers
Analyze it What to Analyze How to Analyze Present It Once Microsoft Dynamics GP Data is in Excel
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
Aggregated Data Gross Margin YTD Profit Current Ratio Meta Data Workload Volume Staff Performance What to Analyze
Transform Data Subtotal Data Pivot Data Pivot Lots of Data Chart Data Use Statistical Data How to Analyze Data
Tables Charts Trend Lines Conditional Formatting Sparklines Slicers Presenting Information
Dashboards Online Microsoft SharePoint Excel Services Presenting Information
More Tips • Microsoft Dynamics GP 2010 Cookbook • Book, Kindle and PDF options Amazon, Barnes and Noble, Packt Publishing
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
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
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
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
Analyze it What to Analyze How to Analyze Present It Once Dynamics GP Data is in Excel
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
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
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
Tables Charts Trend Lines Conditional Formatting Sparklines Slicers Presenting Information
Dashboards Online Microsoft SharePoint Excel Services Presenting Information
More Tips • Microsoft Dynamics GP 2010 Cookbook • Book, Kindle and PDF options Amazon, Barnes and Noble, Packt Publishing
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