Download Microsoft Dynamics Office Add-in

[Karisa Freije]

I hope you were able to see the power of the new Excel Add-In and how it can be used to not open manipulate data but also serve as a powerful reporting tool that can directly connect to the Dynamics 365 Finance and Operations ERP system. " context":"http:\/\/schema.org\/"," type":"BlogPosting","name":"How to Use Excel Add-In: Dynamics 365 Finance and Operations","url":"https:\/\/dynamics-tips.com\/how-to-use-excel-add-in-dynamics-365-finance-and-operations\/","articleBody":"The Dynamics 365 Finance and Operations Excel Add-In has greatly improved since earlier AX 2012 versions. The Excel Add-In in AX 2012 was very fickle and buggy to say the least. In Dynamics 365 Finance and Operations (D365) Microsoft has built the Excel Add-In on top of the data management framework, utilizing data entities to retrieve, update, and add data to D365. \n\n\n\nThe improved Excel Add-In has some new features that I have not seen deep coverage on and in this article, I aim to show you some of the coolest features and tips I have come across while working with it. \n\n\n\nLater in the article you will see how, using the tools that we discuss in this article, you can create amazing visual dashboards connected directly to Dynamics 365 Finance and Operations without paying for a Power BI license or writing a single line of code!\n\n\n\nHow To Open Excel Add-In\n\n\n\nOn various forms, list pages, and grids you will find a Microsoft office icon shown below.\n\n\n\n\n\n\n\nOn some list pages and forms when you click this icon you will see drop down that shows two options. One option header will be \u201cOpen in Excel\u201d and the other option header will be \u201cExport to Excel\u201d. \n\n\n\n\u201cOpen in Excel\u201d means that there is a data entity which you can connect to using the Microsoft Excel Add-In. This creates a dynamic connection to the data entity which can be used to update, create, and refresh data to and from the system. \n\n\n\n\u201cExport to Excel\u201d will launch a static export of the selected data to Microsoft Excel and does not contain any of the functionality that will be described in this article. For the purposes of this article I will focus my attention on the \u201cOpen in Excel\u201d functionality.\n\n\n\n\n\n\n\nIn this first example I will use the \u201cSales Order Lines V2\u201d found in Sales and Marketing > Sales orders > Open orders > Backorder lines. \n\n\n\nTo use the Excel Add-In navigate to your desired data set and click the Microsoft Office icon. Click the data entity you want to connect to using the Excel Add-In and make sure you select the option under the header \"Open in Excel\".Click the \u201cDownload\u201d button on the dialog to download the excel workbook.\n\n\n\n\n\n\n\nWhen the workbook finishes downloading click the Excel document to open the document. I am using Chrome so yours may look a little different depending on the browser you are using.\n\n\n\n\n\n\n\nTo enable the Add-In click \u201cEnable content\u201d.\n\n\n\n\n\n\n\nIf this is your first time using Excel Add-In you may need to enable the add-in by clicking the \u201cInsert\u201d tab in the Excel ribbon and click \u201cMy Add-ins\u201d. Then select the \u201cMicrosoft Dynamics\u201d Add-In and click \u201cAdd\u201d.\n\n\n\n\n\n\n\n\n\n\n\nThe Excel Add-In may also ask you to sign in if this is the first time you are using it. Just sign in using the same credentials you used to log into D365.\n\n\n\nOnce you do this you should see all of you data populated in the spreadsheet. Getting the data in the spreadsheet is great but there is so much more you can do read on to find out.\n\n\n\nUpdate Data\n\n\n\nThe Excel Add-In functionality allows you to update data and since the updates occur through the data entity all CRUD (Create, Read, Update, and Delete) operations go through the standard D365 security framework and respect the user\u2019s security role and security restrictions.\n\n\n\nIn the example below I want to update the prices of multiple sales order lines. So all I need to do is make the updates directly in excel and click the \u201cPublish\u201d button. You can see the number of records published and if you go back to the data in D365 you will see the data has been updated.\n\n\n\n\n\n\n\nIf I go into the system I can see the records I am going to be updating.\n\n\n\n\n\n\n\nAfter I publish the data I can go back into D365 and refresh the form and I will see the published changes to the data.\n\n\n\n\n\n\n\nCreate Data\n\n\n\nThe Excel Add-In also enables you to create data. In the same Excel sheet, you can add a record and fill in the relevant and required fields and click the \u201cPublish\u201d button and publish records to D365. For more details on how to do this check out my article How to Load Data.\n\n\n\nOne tip I have picked up along the way is that if you add a record make sure you extend the table to include the new record. What I see sometimes is users add a record below the last row and try to publish it and they will get an error because the Excel Add-In does not see the new data. Drag the table edge as shown below to encompass the new record(s).\n\n\n\n\n\n\n\nFilter\n\n\n\nWhen you open the Excel Add-In and the data first populates in the Excel sheet the entity may be pre-filtered depending on which entity you connected to and the context where you clicked \u201cOpen in Excel\u201d. For example, my \u201cSales Order Lines V2\u201d entity is pre-filtered to just show sales order lines with status \u201cOpen order\u201d which means the line has not been fully shipped. If I wanted to see all sales order lines or only shipped sales order lines, I can modify the filter to show me only the records I want to see. In this example I want to just remove the filter so I can see all the sales lines.\n\n\n\nTo do this click the \u201cFilter\u201d icon in the data connector pane.\n\n\n\n\n\n\n\nClick the trashcan icon to remove the filter and click \"Done\".\n\n\n\n\n\n\n\nClick the \u201cRefresh\u201d icon to refresh the data and pull in the unfiltered data.\n\n\n\nModify Design\n\n\n\nThere are cases where you may want to add, remove, or rearrange the fields that are shown in the Excel sheet. You can do this by using the \u201cDesign\u201d button in the data connector pane. \n\n\n\nClick the \u201cDesign\u201d button in the connector pane.\n\n\n\n\n\n\n\nClick the \u201cEdit\u201d button in the connector pane.\n\n\n\n\n\n\n\nIn the connector pane you will now see two sections. The upper section is called \u201cAvailable fields\u201d the lower section is called \u201cSelected fields\u201d. The selected fields are the fields you currently see in the Excel sheet. \n\n\n\nYou can add a new field to the sheet but double clicking any field in the \u201cAvailable fields\u201d section. You will then see it appear in the selected fields section. You can order the fields in the way you want to see the fields in the Excel sheet by clicking on the field you want to move and click the \u201cUp\u201d or \u201cDown\u201d arrows. The fields at the top are displayed first in the Excel sheet. The first field will have the A1 position the second field will have the B1 position and so on. If you want to remove a field from the Excel sheet, simply double click a field from the \u201cSelected fields\u201d section or click the \u201cRemove\u201d button. You will then see the field move up to the \u201cAvailable fields\u201d section.When you\u2019re done with your changes you can click the \u201cUpdate\u201d button and you will see the changes you have made reflected in the Excel sheet.\n\n\n\n\n\n\n\nCreate A Calculated Field\n\n\n\nThere may be cases where you want to use the power of Excel to create a calculated field. If you just create the manually create the calculated field in the Excel sheet you will lose the field when the data entity is refreshed. \n\n\n\nTo create a calculated field that will bind to the data entity and remain intact after a data refresh, go to the design form as shown in the section above. In the design form click the \u201cFormula\u201d button.\n\n\n\n\n\n\n\nYou will see a new column get created called \u201cColumn 1\u201d you can rename this field by typing the label in the \u201cColumn label\u201d field (with the tag icon).\n\n\n\nClick the \u201cUpdate\u201d button and you will now see your new field appear in the Excel sheet. Now what you can do is enter your formula in the Excel sheet and copy that formula into the \u201cFormula\u201d field (Sigma icon) in the connector pane.Then you just click the \u201cUpdate\u201d button and there you have it. You have a calculated field that will update along with the data you refresh directly from Dynamics 365 Finance and Operations.\n\n\n\n\n\n\n\nDynamic Reporting\n\n\n\nNow that you\u2019ve got you\u2019re data, the fields you want, in the order you want them, and calculated fields you can save this file and save all the work you\u2019ve done to your local computer or network drive. When you re-open the file it will automatically refresh the data in the file. How cool is that!? This enables what I call \u201cDynamic Reporting\u201d. \n\n\n\nThis means you could use the power of Excel to build sophisticated reports, KPI\u2019s, and analytics to meet your business reporting needs. When you create your pivot or pivot chart make sure you use the select all triangle in the top left corner next to the A1 cell this will ensure that all new data is seen by your pivot or chart and all you need to do is refresh the pivot table to see the new data.\n\n\n\n\n\n\n\nIf you do not want the data to automatically be refreshed, you can change this in the data connector settings in the connector pane by clicking the \u201cSettings\u201d button.\n\n\n\n\n\n\n\nOpen the \u201cData connector\u201d tab and un-check the \u201cRefresh on open\u201d option. Click \u201cOK\u201d.\n\n\n\n\n\n\n\nNow the next time you open the spreadsheet, the data will not automatically refresh you will need to click the \u201cRefresh\u201d button in the data connector.\n\n\n\nBuild Dynamic Dashboards\n\n\n\nAs you have seen, with Excel add-in you can bring in and build a dataset and then build multiple reporting visuals from that dataset to produce amazing interactive dashboards connected directly with the ERP. Below is an example of a sales dashboard that I built using all the tools we have discussed in this article. The dashboard contains 7 visuals and 5 summary totals all connected to slicers that control all visuals on the dashboard. These are all connected to pivot tables derived from a main dataset which is made up of a few standard data entities.\n\n\n\nThis was all done without a single line of code being written and without purchasing a Power BI license.\n\n\n\nD365 Finance and Operations Sales Dashboard\n\n\n\nIf you would like to purchase this dashboard, as a starting point or to use it as is, you can connect it directly to your environment, refresh it, and see what your data looks like in this dashboard. The workbook is completely yours after purchase. You are free to add, remove, and modify it as you wish. You can add custom fields, change report types or add new slicers. You can add your own company logo and brand it however you like. If you are not able to see the check out form please try using Edge or Chrome browsers.\n\n\n\n\n\n\n\nDashboard Instructions And Setup\n\n\n\nAfter purchase the Excel file containing the dashboard will be emailed to the email account you enter during purchase. When you receive the email download the Excel file and if you receive a warning about content click \u201cEnable content\u201d. I have built in a macro that will automatically refresh all the filters with the new data you bring in.\n\n\n\n\n\n\n\nYou will receive an error message at first saying \u201cFailed to retrieve the server config\u201d this is because you have not entered your environment \u201cServer URL\u201d. You can just click \u201cOk\u201d, because we will enter that in subsequent steps.\n\n\n\n\n\n\n\nNext, we need to connect the dashboard to your D365 Finance and Operations environment. To do this use the Microsoft Dynamics Data Connector pane on the right hand side and click the gear icon.\n\n\n\n\n\n\n\nThe data connector pane should open automatically but if it does not, you can use what you have learned in this article and click the \u201cInsert\u201d tab in the Excel ribbon, click \u201cMy Add-ins\u201d.\n\n\n\n\n\n\n\nNext find the \u201cMicrosoft Dynamics\u201d add-in and click \u201cAdd\u201d.\n\n\n\n\n\n\n\nNow you should see the connector pane. Click the gear icon. In the \u201cServer URL\u201d enter the URL for your environment and click \u201cOk\u201d.\n\n\n\n\n\n\n\nThis will connect to your Dynamics 365 Finance and Operations environment and the spreadsheet will begin to populate with data. If you receive an error message saying \u201cThe request timed out waiting for a response\u201d just click ok and press the \u201cRefresh\u201d button in the data connector again.\n\n\n\n\n\n\n\nBy default, I have pre-filtered the data coming into the dashboard to include only data coming from company \u201cUSMF\u201d since this is Contoso demo data. You will need to change the filter for each data entity to be whatever company data you want to bring into the report. If you want data from all companies, you can just clear the filter entirely. \n\n\n\nGo to the filter pane as you learned in this article and in the \u201cData source\u201d field select each data entity and ensure that each one is filtering on the company you want to filter on. Again by default it will be set to \u201cUSMF\u201d.\n\n\n\n\n\n\n\nOnce you have set your company filter you can click the \u201cDone\u201d button and then click \u201cRefresh\u201d. This will bring in your data into the dashboard.\n\n\n\n\n\n\n\nNow that you have your data in the dashboard the last step is to refresh all your chart filters. I built in an automatic refresh macro which refreshes all filters in the workbook. Click the \u201cRefresh Filter Cache\u201d button.\n\n\n\n\n\n\n\nNow all of your data should be in the workbook. By default, all the pivot table tabs used for these pivot charts are hidden. If you want to modify any of them, just unhide the tabs and customize anything you like.\n\n\n\nTroubleshooting\n\n\n\nOne Million Record Limit\n\n\n\nIf you have over a million records you will receive an error saying \u201cThe data retrieved has been limited to the cell refresh of 1000000 cells. Apply additional filters as needed\u201d. If you get this you need to go back to the filters pane again and set some additional filters (maybe on \u201crequested ship date\u201d in the sales order lines v2 data entity) to reduce the number of records being brought into the workbook.\n\n\n\n\n\n\n\nSlicer Disaggregation\n\n\n\nIn some cases, after refresh I have noticed that the date slicer disaggregates the dates into individual dates instead of by month. If you want to reaggregate them simply click on one chart, add the \u201cRequested ship date\u201d to the \u201cAxis\u201d of the pivot chart and then remove it. Excel will reaggregate it automatically.\n\n\n\nContact Us\n\n\n\nIf you are not able to get the dashboard to work with your environment please contact us and provide a detailed description of the problem you are experiencing and we will respond back with the appropriate steps to get it working. Support will only be provided to get the dashboard connected and working. Other inquiries will be deprioritized. \n\n\n\nWe cannot provide support if you have modified the dashboard in any way from its original form.\n\n\n\nLoading\u2026\n\n\n\nTakeaways\n\n\n\nI hope you were able to see the power of the new Excel Add-In and how it can be used to not open manipulate data but also serve as a powerful reporting tool that can directly connect to the Dynamics 365 Finance and Operations ERP system.","headline":"How to Use Excel Add-In: Dynamics 365 Finance and Operations","author":" type":"Person","name":"Tim Woods","url":"","datePublished":"2020-10-03","mainEntityOfPage":"True","dateModified":"November 16, 2021","image":" type":"ImageObject","url":"https:\/\/dynamics-tips.com\/wp-content\/uploads\/2020\/10\/HowtoUseExcelAddInPostImageSized.jpg","height":320,"width":640,"publisher":" context":"http:\/\/schema.org\/"," type":"Organization","name":"Dynamics-Tips","logo":" type":"ImageObject","url":"https:\/\/dynamics-tips.com\/wp-content\/uploads\/2020\/10\/LogoWithNameSized-300x300.png","height":600,"width":60Tim WoodsTim has been an AX/D365 Finance and Operations Consultant for over a decade and loves to share insights and knowledge from multiple full cycle implementations.



download microsoft dynamics office add-in

DOWNLOAD https://t.co/xRhL1boS4e





35fe9a5643