how to create ad hoc reports in microsoft excel how to edit business data in microsoft excel how to...

40

Upload: osborn-boone

Post on 22-Dec-2015

225 views

Category:

Documents


4 download

TRANSCRIPT

Page 1: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word
Page 2: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Extending the Reach of Microsoft Dynamics AX 2012 R2 with MS Office Interoperability

Chris GartySenior Program ManagerMicrosoft Corporation

BRK317

Page 3: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Disclaimer© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. Other names and brands may be claimed as the property of others.

Microsoft Dynamics AX 2012 R2 is a pre-release product under development. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft. Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. All product release dates and features specified are preliminary based on current expectations, and are subject to change without notice. Microsoft may make changes to specifications and product descriptions at any time, without notice.

Sample code included in this presentation is made available AS IS. THE ENTIRE RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE USER.

MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. NO LICENSE, EXPRESS OR IMPLIED, BY ESTOPPEL OR OTHERWISE, TO ANY INTELLECTUAL PROPERTY RIGHTS IS GRANTED BY THIS PRESENTATION. Microsoft products are not intended for use in medical, life saving, or life sustaining applications.

Page 4: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Session Objectives1. Identify the Office Interop possibilities in Microsoft Dynamics AX 2012 R2.2. Describe how the Office Interop capabilities of Microsoft Dynamics AX 2012 R2 help

it work with other business software.

Key TakeawaysMicrosoft Dynamics AX 2012 R2 can interop with Excel, Word, SharePoint, and Lync:• Read, analyze, summarize, and update Dynamics AX data in Microsoft Excel.• Generate Microsoft Word documents from Dynamics AX.• Store document templates and record attachments in SharePoint.• Communicate with contacts via Lync.

Session Objectives And Takeaways

Page 5: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

New in R2Responding to your feedback…

Document ManagementNew: SharePoint and SharePoint Online support

Excel Add-inNew: Office 365 support via export, filtering APIs, dimension descriptions, refresh on openImproved: Table support (40% to 95%), services support, user experience

Word Add-inNew: Generate from template to Office 365, template storage in SharePoint Online

LyncNew: Lync 2013 and Lync Online support

Page 6: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Session Focus - Scenarios

How to create ad hoc reports in Microsoft

Excel

How to edit business data in Microsoft Excel

How to create a document template in

Microsoft Word

How to import data into Microsoft Dynamics AX using Microsoft Excel

How to communicate with contacts via Lync

Page 7: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Pains and challengesKevin, Sales Manager• Every time I need a change to a report I need to involve the IT department

June, Product Division Manager• I plan my budget using Microsoft Excel, then need to copy it to my budgeting

system

Prakash, Project Manager• I want to personalize the project quotations I send to important prospects

Simon, Systems Implementer• I need to gather data from functional team members and import it into Microsoft

Dynamics AX

Kevin, Sales Manager• I want to initiate communications with contacts without leaving Microsoft

Dynamics AX

Page 8: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

SolutionsMeeting the challenge

Ad hoc reporting in Microsoft ExcelKevin, the sales manager, analyzes his opportunities pipeline using Excel

Editing business data in Microsoft ExcelJune, the budget manager, organizes and edits budget information in Excel

Create a document template in Microsoft WordPrakash, the project manager, builds a project quotation in word that his team can useImport data using Microsoft ExcelSimon, the implementer, collects customer details in Excel and sends them to Dynamics AXCommunicate using Microsoft LyncKevin, the sales manager, can quickly initiate communications with colleagues and contacts

Page 9: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Generic Document Service

Metadata Service

Business Document Services

Architecture: Office Add-insService-based interop with Office 2010 and 2013

Metadata

Services

Tables

QueriesQuery Service

Page 10: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Architecture: SharePointSDK-based interop with SharePoint 2010 and 2013

Export to Excel

Templates

Exported workbooks

Document attachments Document Management

Generate from template

Page 11: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Initiate Communication

Architecture: LyncSDK-based interop with Lync 2010 and 2013

Contacts

Presence information

Contact details

Page 12: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 1 : Ad hoc reporting in Microsoft Excel

Persona Pain Points and Challenges Features Covered Results and Benefits

Business User Self-service reporting

Send to SharePointRead data from QueriesAdd fieldsFilter dataRefresh data

Build reports in a familiar toolNo need to involve IT or DevelopersSecurity and localization built in

• Export the opportunities list page• Extend it with additional fields• Add formatting and pivots• Refresh as conditions change

Page 13: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Highlights for R2: Excel

Fresh data automaticallyAdded a user option and an OpenXML API to allow refresh of a workbook on open to retrieve fresh data.

Send to Office 365Added support for sending exports to SharePoint and SharePoint Online to enable use of Office 365 Excel.

Localize itAdded support for localized templates via column and field label support.

Easier to useImproved the Add-in user experience.

Page 14: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

demoAd hoc reporting in Microsoft Excel

Page 15: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 1 : Ad hoc reporting in Microsoft Excel

Page 16: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 2 : Editing business data in Microsoft Excel

Persona Pain Points and Challenges Features Covered Results and Benefits

Business User

Duplication of data between Microsoft Excel and the budgeting systemFormulas and formatting in budgeting tools

Write data to ServicesLock designDimensions support

Store budget information where it belongs – Microsoft Dynamics AXFlexible and familiar tools for forecasts and budgets

• Build a template• Modify data• Publish changes

Page 17: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Highlights for R2: Excel editing

Filter workbooks from codeAdded an OpenXML API for programmatic filter changes so workbooks can be customized to a user.

Lock it down when neededAdded an OpenXML API to optionally lock down the design of a workbook.

Dimensions explainedAdded the dimension descriptions as a field binding option and as a helper in lookups.

Matrix fields as editable aggregatesImproved the support for data entry in a matrix table via new “Add Row” dialog.

Page 18: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 2: Editing business dataFind a budget plan in Dynamics AX

Page 19: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 2: Editing business dataEdit that budget plan in Microsoft Excel

Page 20: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 2: Editing business dataTemplates are designed using the Add-in

Page 21: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

demoEditing business data in Microsoft Excel

Page 22: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 3 : Create a template in Microsoft Word

Persona Pain Points and Challenges Features Covered Results and Benefits

Business User

Manual work and copy/paste to author documentsConsistent yet easily customized documents

Document template libraryDocument data sourcesMicrosoft Word Add-inGenerate from template

Quick and easy generation of documentsEasy-to-build templates

• Define template in Microsoft Word• Place the template in Microsoft SharePoint• Activate the template for use• Generate a document

Page 23: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Highlights for R2: Word

Store templates anywhereAdded SharePoint Online support for template storage

Generate to SharePointAdded support for SharePoint as a document storage location

Dimensions descriptionsAdded the option of including dimension descriptions in documents

Page 24: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

demoCreate a template in Microsoft Word

Page 25: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 3 : Create a template in Microsoft Word

Page 26: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 4 : Import data using Microsoft Excel

Persona Pain Points and Challenges Features Covered Results and Benefits

Business User

Data often needs transformation before importData import process can often require multiple steps

Table selectorWrite data to tablesValidation

Users can massage via data bulk edit, find/replace, calculations, etc. before importingFlexible tool for data import

• Select tables• Enter data• Import data

Page 27: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Highlights for R2: Excel import

Tables galoreImproved our support for surrogate foreign keys (SFK) to improve the percentage of tables supported for import/export from 40% to 95%

Use services when neededAdded support for key services such as LedgerGeneralJournalService and VendVendTableService

Page 28: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

demoImport data using Microsoft Excel

Page 29: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 4 : Import data using Microsoft Excel

Page 30: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 5 : Communicate via Lync

Persona Pain Points and Challenges Features Covered Results and Benefits

Business User

Communicating with contacts often means switching to another application

Lync presence and contact informationInitiating IM and email communications

Quick communication with contacts and colleagues

• Set contact address• View contact presence info• Initiate communications

Page 31: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Highlights for R2: Lync

Lync 2013Added support for Lync 2013

To the cloud!Added support for the latest version of Lync Online

Page 32: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

demoCommunicate via Lync

Page 33: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Demo 5 : Communicate via Lync

Page 34: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Office Interop Capability Summary• Excel read-only

• Query• Excel read-write

• Services• Tables

• Word read-only• Query

Office Add-ins

• Export to Excel• Static• Refreshabl

e• Send to

SharePoint• Generate from

template• Word doc• Excel

workbook

Doc Generation

• Presence• Initiate

• IM• Email

Lync• Document

storage• Database• File folder• SharePoint

Doc Management

Others• Project collaboration sites in SharePoint• OData feeds consumable by Excel/SharePoint

Page 35: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Office 365: Key scenariosSend exports to SharePoint OnlineSend static and refreshable workbooks to SharePoint Online for storage and collaboration.Store document attachments in SharePoint OnlineSharePoint Online can be used as a document storage location for record attachments.

Place templates in SharePoint OnlineStore templates in SharePoint Online for use with “Generate from template”.

Collaborate through Lync OnlineYour local Lync client can use Lync Online as the server.View documents in the Excel and Word web applicationsDocuments stored in SharePoint Online will open in the web applications by default. The Add-ins won’t run, but you can view documents and workbooks.

Page 36: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Top tips from this session

Take advantage of support for SharePointDocument management, content management, collaboration

Use Excel whenever neededAdhoc reporting, data analysis, editing data, data import

Use Word as a lightweight alternative to reportsStore templates in SharePoint, create good looking templates, and add the “Generate from template” button wherever needed.

Take advantage of Lync for collaborationLive contact information and flexible communication options

PerformanceJudicious filtering conserves system resources and saves time. Use the data migration toolkit for large data migration efforts.

Page 37: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Session Objectives1. Identify the Office Interop possibilities in Microsoft Dynamics AX 2012 R2.2. Describe how the Office Interop capabilities of Microsoft Dynamics AX 2012 R2 help

it work with other business software.

Key TakeawaysMicrosoft Dynamics AX 2012 R2 can interop with Excel, Word, SharePoint, and Lync:• Read, analyze, summarize, and update Dynamics AX data in Microsoft Excel.• Generate Microsoft Word documents from Dynamics AX.• Store document templates and record attachments in SharePoint.• Communicate with contacts via Lync.

In Review: Session Objectives And Takeaways

Page 38: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Related Sessions

A few still remaining… 1.15pm - BRK308

Developing cool new visualizations and powerful reports with Dynamics AX 2012 R2

1.15pm - BRK314 Integrating Dynamics AX with other line of Business Applications

2.45pm - CHK306 Business Intelligence in Dynamics AX 2012 R2 Q&A

Page 39: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

Key Resources

Information Sourcehttp://informationsource.dynamics.com

My Blog – Questions and commentshttp://blogs.msdn.com/chrisgarty

Page 40: How to create ad hoc reports in Microsoft Excel How to edit business data in Microsoft Excel How to create a document template in Microsoft Word

© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.