Dynamics 365 FO – New Grid Control in Action

New Grid control in Dynamics 365 Finance & SCM

The new grid control in Dynamics 365 FO comes with some cool new features. Watch the video on my YouTube channel to see it in action.

  • Copy&Paste data from Excel into Dynamics 365 FO
  • Faster data entry due late field validation
  • Easily Drag&Drop reorder columns
  • Pin columns on the left while scrolling
  • Use formulas in numeric fields
  • Calculate totals for numeric fields

How to choose the right BI and reporting strategy for Dynamics 365 Finance

Dynamics 365 for Finance and Supply Chain Management offers a broad range of reporting and business intelligence options. You can utilize the integrated Power BI dashboards, link the Power BI report gallery within Dynamics, use integrated SSRS reports or develop Power BI reports and dashboards that connect to Dynamics 365. Sometimes it can be hard to decided which one to choose. Here is a guideline which one to choose depending on the reporting requirements.

Dynamics 365 Power BI and Reporting strategy

Decision Tree for Power BI / Reporting in Dynamics 365 Finance & SCM

Report Format:

What is the purpose of the report? Is it an interactive report / dashboard or is it a static list or document like artifact? For example, sales analysis is typical an interactive report while a collection letter is a printed document. Power BI is great for interactive data analysis, SQL Server Reporting Services (SSRS) is the right tool for lists and page oriented printable documents.

Real Time:

Do you need to see transactional data as soon as it is generated in Dynamics? For example posting and invoice and immediately printing the document. If so, you need to access the transactional database (AxDB). There are two ways: Use integrated Reporting Services or query entities via OData. However, using entities allows you to access the AxDB but Power BI doesn’t support Direct Query mode for OData, i.e. you have to hit refresh in order to get the latest data.

Multiple data sources:

Is Dynamics 365 Finance the only data source for your report, or do you need to integrated external data sources as well? An example could be to develop a revenue analysis which includes actual sales data from Dynamics 365 as well as demographics and household income. Integrated Power BI dashboards in Dynamics 365 use direct query to access the AxDB and cannot integrate other data sources. It is also not recommended to load external data into Dynamics 365 AxDB because you have a limited cost free database size in your subscription. Additional SQL storage has to be paid.

Additional licenses:

Dynamics 365 Finance and Supply Chain Management includes the rights to view the integrated Power BI dashboards. No additional Power BI license is required. Reports developed using the integrated SQL Server Reporting Services technology are also covered by the Dynamics license. External Power BI reports, dashboards and paginated reports require additional Power BI licenses. At least Power BI Pro for reports and dashboards, Power BI Premium Capacity or Premium per User for paginated Reports.

Examples:

An interactive custom Power BI Report viewed in Dynamics 365 Finance via the users Report Catalog option

Production Performance is part of Dynamics 365 Finance & SCM and directly connects to the entity store (aka. AxDW)

A paginated report in Power BI (Premium)

A static SSRS paginated report in Dynamics 365 Finance and SCM

Conclusion

Before you start working with a certain product, make sure to understand the requirements. Identify the data sources and how to access them. Then choose the right tool for the job. Don’t try to make a printable Power BI or fancy SSRS. By leveraging the full reporting and BI potential you can deliver a great user experience that adds value to Dynamics 365 Finance and SCM.

Use Data Entities in Dynamics 365 Finance & SCM to expose Business Logic

Entities in Dynamics 365 Finance & Supply Chain are not only good to access data. Entities can also be used to execute X++ business logic from 3rd party applications like Power Automate.

For example a table contains shipping carrier records. A Carrier has a Name and Description. The name is the primary key. The combination of Name and DataAreaId makes a record unique at the database.

Carrier Table

There is also an entity to access the data.

Carrier Table Entity

An Action is a piece of X++ logic exposed via entity. This example sets the Carrier as default value for a customer:

public class DMOCarrierTableEntity extends common
{
/// Set as default
/// Customer Account
[SysODataActionAttribute('SetDefault', true)]
public void approve(CustAccount _custAccount)
{    
    ttsbegin;
    CustTable custTable = CustTable::find(_custAccount,true);
    custTable.DMOPreferredCarrier = this.Name;
    custTable.update();
    ttscommit;
}

Such an action can be called via Power Automate:

Call custom action via Power Automate

Mini Webcast Series: Dynamics 365 FO Connector for Power Automate

Power Automate (aka. Flow) is a great solution for process automation. It is very useful to realize cross-application processes. Power Automate features a wide variety of tasks and connectors. It also comes with a connector for Dynamics 365 Finance and Supply Chain Management. It can be used to trigger a flow, interact with data and execute business logic in D365 FO from within a flow. You can find all videos on my YouTube Channel.

Part 1: Trigger a Flow with Business Events

Business Events in Dynamics 365 Finance and Supply Chain Management are use to interact with other systems. For example you can send message to Azure Event Hub but also to Power Automate. There are some specific events but you can also use change-based alerts in Dynamics. This video shows how to trigger a flow using such a business event.

Business Events can trigger Power Automate flows

Part 2: Interact with data in Dynamics 365 Finance and SCM

The Connector for Dynamics 365 FO uses entities to interact with data in the ERP system. You can create new records, read, update and delete records. Here is an example where Power Automate receives data from Forms Pro and create a new customer in Dynamics 365 FO.

Create new records in Dynamics 365 FO from Flow

Part 3: Execute Business Logic in Dynamics 365 Finance and SCM

The Dynamics 365 FO Connector in Power Automate supports to execute business logic by calling actions on entities. Here is a video how to approve a Bill of Materials in Teams and execute the Approval logic via Flow.

Execute Business Logic from Flow

Filter on NoYes field in Dynamics 365 Flow Connector

The Dynamics 365 connector in Power Automate provides the basic operations to interact with your Dynamics 365 FO instance. You can create new records via flow (e.g. a new Customer), read, update and delete records (e.g. Sales Order Lines) and retrieve the list of available entities.

Dynamics 365 Finance and Supply Chain Connector in Power Automate
Dynamics 365 Finance and Supply Chain Connector in Power Automate

The connector uses OData to interact with the entities in Dynamics 365 FO. Therefore, it also supports OData syntax for filter expressions. If you want to filter on a boolean Extended Datatype like NoYes and try to use “Yes” or “1” or true it will not work. Dynamics 365 has its own Datatype that comes from the entity definition Microsoft.Dynamics.DataEntities.NoYes .

One Time Customer in Dynamics 365 Finance
One Time Customer in Dynamics 365 Finance

For example, if you want to get all One-Time Customers from Dynamics 365 FO, use the Dynamics 365 connector and choose the action “List items in present Table”. Use the Customers entity. To filter on a NoYes field like One-Time Customer you have to use the following syntax:

  • IsOneTimeCustomer eq Microsoft.Dynamics.DataEntities.NoYes’No’
  • IsOneTimeCustomer eq Microsoft.Dynamics.DataEntities.NoYes’Yes’
Filter on NoYes field in Dynamics 365 Finance Connector
Filter on NoYes field in Dynamics 365 Finance Connector

Extend Dynamics 365 Dual Write Solution

Dual Write is a Power Platform based solution to synchronize Dynamics 365 Finance and Supply Chain with Dynamics 365 Customer Engagement in real time. I’ve uploaded a Youtube Video that shows Dual Write in Action. The default solution comes with a set of synchronization entities like Products, Customers, Vendors, Quotations, Sales Orders and Invoices. In a Dynamics 365 project you typically need to add additional information in both applications.

In this example a customer shall have a preferred carrier. The carrier (e.g. UPS, DHL) will be stored in a main table. The customer entity will have a reference to set the carrier. The corresponding account entity in Dynamics 365 Sales will have the same lookup field and preferred carrier settings will be synchronized between Finance and Sales.

Carrier in Dynamics 365 Finance and Supply Chain Management
Carrier in Dynamics 365 Finance and Supply Chain Management

Extend Dynamics 365 Finance / SCM Data Model

For Dynamics 365 Finance / SCM create the necessary artifacts in Visual Studio:

  • A carrier table with a mandatory Name and Description field. Also create a primary index based on the Name.
  • An entity to make the carrier table accessible from external applications.
  • An extension of the CustTable with a Carrier field and a table reference from the CustTable to the Carrier table.
Dynamics 365 Finance / SCM Solution for customers preferred carrier
Dynamics 365 Finance / SCM Solution for customers preferred carrier

Save, compile and create a deployable package. Upload the package to your projects LCS asset library. After the package was validated, deploy the package to your target environment. After the package was deployed, go to Data Management Workspace > Framework Parameters > Entity settings and refresh the entity list.

Extend Dataverse (aka. Common Data Service) Entities

Navigate to https://make.powerapps.com and switch to your target environment. Go to Data > Tables and create a new table for the carrier. The table shall match the carrier table in Dynamics 365 FO. Make the Name field as mandatory primary field and add a second optional text field for the Description. Add a lookup field to the existing company table to match the DataAreaId that will come from Dynamics 365 Finance. Note that PowerApps will add a lot of other fields like owner, created, etc. as well.

Create a carrier entity in the Dataverse (CDS)
Create a carrier entity in the Dataverse (CDS)

Find the account entity that was created by the Dual Write solution. Add an addition lookup field called Carrier that references the newly created carrier table.

Carrier lookup in the account entity
Carrier lookup in the account entity

Customize the accounts form in Dynamics 365 sales

In PowerApps > Data go the accounts table and switch to the forms tab. There go to the Main form that is used in Dynamics 365 Sales to work with accounts. Click on the Name of the form, this will open the forms designer.

Dynamics 365 Dataverse Account entity
Dynamics 365 Dataverse Account entity

In the PowerApps designer add the Carrier field to the form. Save the change and publish the customized form.

Add carrier to Dynamics 365 Sales Account Form
Add carrier to Dynamics 365 Sales Account Form

Map the Carrier Entity from Dynamics 365 Finance to the Carrier table in Sales

In Dynamics 365 Finance open the Data Management Workspace and go to Dual Write. Create a new table mapping. Map the Carrier entity from Dynamics 365 FO with the Carrier table form the Dataverse.

Create a new table mapping in Dynamics 365 Dual Write

Next map the Carrier Name fields and the Carrier Description fields. Save and start the initial synchronization.

Map Dynamics 365 FO entity to Dynamics 365 Sales table

Check if the synchronization was successful. Open https://make.powerapps.com in a browser and switch to your target environment. At the left menu go to Data > Tables. Click on the Carrier table and switch to the Data tab. You should see the same carrier like in Dynamics 365 Finance.

Add the Carrier Lookup field to CustomverV3 mapping

Stop the synchronization of the CustomerV3 to account. Go to table mappings and refresh the tables. This will load the table schema from the Dataverse with the new carrier lookup field. Create a new mapping from the Carrier in Finance to the Carrier field in Dataverses entity. Save the mapping definition as a new version. Restart the synchronization and make sure there are no errors.

Mapping carrier fields in Dual Write
Mapping carrier fields in Dual Write

Now you should see and edit the Carrier in Dynamics 365 Finance as well in Dynamics 365 Sales. Changes made in any of the two products will be synchronized.

Preferred Carrier field in Dynamics 365 Finance and Supply Chain Management
Preferred Carrier field in Dynamics 365 Finance and Supply Chain Management
Mapped and synchronized Carrier field in Dynamics 365 Sales
Mapped and synchronized Carrier field in Dynamics 365 Sales

Limitations

Dual Write has some limitations regarding the number of lookup fields. If you come across an error during the synchronization you may need to remove some of the lookup fields from the Dual Write table mapping.

Dynamics 365 Dual Write in Action

Since the early days the integration of Dynamics AX ERP and Dynamics CRM was a painful challenge. Microsoft came up with may not so great working solutions. Dual Write is a Power Platform solution for Dynamics 365 Finance / SCM and Dynamics 365 CE. It supports the synchronization beween these two products almost in real time.

I’ve made a short video to demonstrate the synchronization between Finance and CE. A customer is created in CE and becomes immediately a customer in FO. The quotation from CE is synchronized to FO where it is converted into a sales order. The sales order is visible in CE. The order is invoiced in FO and can be reviewed from within CE.

Dynamics 365 Finance – CE integration via Dual Write

Load context-sensitive Power BI tiles in Dynamics 365 Finance via Power Apps

Power BI goes hand in hand with Dynamics 365 Finance and Supply Chain Management. By default Power BI can be used within workspaces and Dynamics 365 comes with a data warehouse and a large set of reports and dashboards. But wouldn’t it be nice to show Power BI visuals in common forms and filter on the active record? This can be done without coding by using Power Apps:

D365 Parameter to Power BI filter

Dynamics 365 Finance is capable to load Power Apps and pass parameters to the App, while Power Apps can load PowerBI reports and pass a filter to Power BI.

Create a Power BI report

Create a report that can be drilled down to the granularity you want to display in Dynamics 365 Finance. For example if you want to show customer specific information, your report should support filtering on a customer account.

For example I’m using the SalesInvoiceV2Lines and ReleasedProductsV2 entities. The SalesInvoiceV2Lines comes with a table reference to the SalesInvoiceHeaders where the invoice account is stored. The ReleasedProductsV2 can be linked to the lines via the product number.

Dynamics 365 Finance SalesInvoiceV2Lines entity in Power Query editor

Next create the desired visuals. For example a column chart for the revenue by Year and a donut chart for the revenue by product group. Add a filter and inspect the different results you would expect for differnt customers.

Revenue by Year and Product Group

Save and Publish the report. Open the report in Power BI Online and pin the two visuals on a new dashboard. Make sure to give the visuals on the dashboard a useful title and subtitle.

Power BI tiles on a dashboard

Power Apps

Everything comes together in Power Apps. Here the parameter from Dynamics 365 Finance is stored in a variable and passed as filter to Power BI. Open Power Apps via https://make.powerapps.com and create a new canvas app. I’d suggest to use the smart phone layout. According to the documentation add the following code to the OnLoad in Power Apps. This will store the parameter value from Dynamics to a Power Apps variable called FinOpsInput. (Depending on your local settings you may need to replace , with ; in PowerApps)

If(
    !IsBlank(Param("EntityId")), 
    Set(FinOpsInput, Param("EntityId")), 
    Set(FinOpsInput, "")
   )
Read parameter from Dynamics 365 Finance in Power Apps

Next, add the Power BI tiles. From the ribbon go to Insert > Diagram > Power BI. Insert a Power BI tile to the empty screen. Choose your workspace, next the dashboard and finally the tile.

Insert a Power BI tile in Power Apps

The Power BI tile is referenced via an URL. This can be edited by selecting the Power BI tile and switch to Advanced. The syntax is:

&filter=TableName/FieldName eq 'YourValue'

Add a filter on the customer account with the values from the FinOpsInput variable as value. Make sure that the filter matches the field in your Power BI report. For example this would look like the following URL in my example:

"https://app.powerbi.com/embed?dashboardId=d496ce2a-5836-4fc2-a3e9-34a5c1cdd674&tileId=7f1aa48a-e2f8-4eef-a59f-56a0e873e143&config=eyJjbHVzdGVyVXJsIjoiaHR0cHM6Ly9XQUJJLU5PUlRILUVVUk9QRS1yZWRpcmVjdC5hbmFseXNpcy53aW5kb3dzLm5ldCIsImVtYmVkRmVhdHVyZXMiOnsibW9kZXJuRW1iZWQiOmZhbHNlfX0%3d&filter=SalesInvoiceV2Lines/InvoiceAccount eq '"& FinOpsInput &"'"

Save and publish your App. From the list of your Apps, open the details page of your app and copy the App ID.

Power App Details
Copy the App-ID from the details page

Add the Power App in Dynamics 365 Finance and Supply Chain

Logon to Dynamics 365 Finance and navigate to the screen where you want to display the Power BI tiles. In my example I’d choose Module Accounts Receivable > All Customers. In the upper right at the ribbon click on the Power App button and select add an App.

Add a Power App to Dynamics 365 Finance and Supply Chain Management

In the Add an app dialog provide a useful name. Paste the App-ID in the second field. From the context dropdown select the field to pass as parameter to Power Apps. In my case this would be the AccountNum. Finish by clicking on Insert.

Dynamics 365 Finance requires a reload of the page (F5). Test your Power App by selecting a record and the from the Power Apps button open the Power App. It will load the Power App and present the filtered Power BI tiles.

Microsoft-hosted Dynamics 365 Finance Tier1 Sandboxes are dicontinued: Switch to Cloud-Hosted

Dynamics 365 Finance / SCM Tier1 sandbox environments are heavily used by partners for development and building Dynamics 365 Finance / SCM applications. Microsoft-hosted Tier 1 environments were a great deal because we got well sized VMs with 28 GB RAM and 4 Cores plus SQL Server, Visual Studio and Dynamics 365 Finance pre-installed for a very small fixed price per month available 24/7. Now Microsoft recently announced that they will no longer include Microsoft-Hosted Tier1 Sandbox environments with the Dynamics 365 Financen / SCM license and we will no longer be able to purchase additional Tier1 sandbox Addons. The preferred solution is to use Cloud-Hosted environments instead.

No more Microsoft-Hosted Tier1 environments

Microsoft-Hosted vs. Cloud-Hosted

From a technical standpoint there is no difference between a Microsoft-Hosted or a Cloud-Hosted environment. Both solutions deploy a Windows Server VM in Azure. In both cases the deployment is managed via Lifecycle Service (LCS).

LCS management of a Cloud-Hosted environment
Artefacts of a Cloud-Hosted Dynamics 365 FO Tier 1 environment in Azure

However, there are 3 major aspects to consider:

One big difference is the pricing model. Microsoft-Hosted environments (or Addons) come with a fixed (!) price per month while Cloud-Hosted environments deploy on an Azure subscription and therefore are billed like a classic IaaS (aka. virtual Machine in Azure). Make sure to calculate the costs (!) and turn of the environments if not needed.

Another difference is the ability to choose the sizing of the deployed environment. In contrast to Microsoft-Hosted Tier 1 environments, you are now free to choose a sizing that fits your needs e.g. more (or less) RAM, CPU, Premium SSD storage, etc.

Moreover, in contrast to Microsoft-Hosted environments, we now get an Admin account on our machines. It was understandable that Microsoft tried to lock down the cheap VMs to prevent the misusage with anything else then Dynamics 365. Since we own and pay the VM in a Cloud-Hosted environment its more than fair to have Admin access on the machine.

Video: How to deploy a Cloud-Hosted Dynamics 365 Tier1 developer VM

Make sure to visit my Youtube channel and watch how to deploy a Cloud-Hosted Dynamics 365 FO developer VM using an Azure Subscription.

Update: Management Certificates

The use of management certificates is not supported when using a CSP Azure Subscription. Use a user-connection instead.

Update: Provisioning Admin User

Please note that the user deploying the environment is provisioned as the administrator. Microsoft-Hosted environment had to be signed off using a user from the customer tenant. I’d recommend to stick to this process when deploying a Cloud-Hosted environment

Use Power BI dataflow to decouple report design from ETL logic in an ERP upgrade project

A common requirement during an ERP upgrade project (e.g. from AX 2012 to D365 Finance) and transition phase is to include both systems in the BI or reporting environment. Because of its tight integration with Dynamics, in many cases PowerBI is the preferred reporting and BI platform. PowerBI is capable to combine different data sources like OData feeds from D365 and SQL connections via gateway. However, for the person developing reports, it will become complicated to integrate cloud and on-prem datasources. For example, to create a sales report, one would need to include the Customers, SalesInvoiceHeader and SalesInvoiceLine entities as well as the CustTable, DirPartyTable, CustInvoiceJour and CustInvoiceTrans tables.

Different data sources in one PowerBI report

One way to address this issue can be to separate ETL logic from report design. PowerBI supports this approach by using dataflows. By using dataflows you can place PowerQuery logic direct in the Microsoft cloud and offer reuseable data artefacts. People designing reports simply connect to the dataflow but are not concerned with the ETL logic required to combine data from the old AX installation and a new Dynamics 365 ERP cloud environment.

Use PowerBI dataflow to decouple ETL logic from report design

Example

From PowerBI workspace create a new entity using dataflow. Choose the OData feed for Dynamics 365 and provide the URL for the CustomersV3 entity.

OData feed for entities from Dynamcis 365 Finance

Clicking next will open the Power Query editor and load the customers from Dynamics 365 Finance. Remove all the fields you don’t need in your application. In this example I’m using the DataAreaId, Account, Name, Group, Address and Delivery mode + terms.

PowerBI dataflow based on Dynamics 365 Finance OData CustomerV3 entity

For an on-premises AX 2012 installation you need to install a data gateway, so PowerBI can access the local SQL database. If you already have a gateway, create a new dataflow in PowerBI and use the SQL connection. I’d recommend to create a view on the database instead of loading tables in PowerBi.

CREATE VIEW [dbo].[PBIX_Customer] AS
select
DataAreaId, DirPartyTable.NAME, ACCOUNTNUM, CUSTGROUP, TAXGROUP, LogisticsPostaladdress.ADDRESS, DlvTerm, DLVMODE
from CUSTTABLE
join DIRPARTYTABLE
on CUSTTABLE.PARTY = DIRPARTYTABLE.RECID
join DIRPARTYLOCATION on
DIRPARTYTABLE.RECID = DIRPARTYLOCATION.PARTY
join LOGISTICSPOSTALADDRESS
on DIRPARTYLOCATION.LOCATION = LOGISTICSPOSTALADDRESS.LOCATION
where
LOGISTICSPOSTALADDRESS.VALIDFROM <= GETDATE() and LOGISTICSPOSTALADDRESS.VALIDTO >= GETDATE()
GO

Choose SQL Server data source for PowerBI dataflow

Select the data gateway and provide a user to access the database

Connect a PowerBI dataflow to your on-premises AX 2012 database using a gateway

Select the view and load the AX 2012 data to PowerBI. Save the dataflow

Dynamics AX 2012 customer data via data gateway

After you have created both dataflows return to your workspace, go to your dataflows and refresh both to load the data.

Refresh dataflow from Dynamics 365 Finance and Dynamics AX 2012

Next, create a third dataflow to combine the data from the Dynamics 365 Finance and AX dataflow. This time choose to link entities from the other dataflows:

Link PowerBI entities via dataflow

Select both dataflows

Select PowerBI dataflows to merge

In the Power Query Online editor rename the fields in both dataflow entities so you can append both queries. Be aware that Power Query is case sensitive and dataAreaId is not the same as DATAAREAID. When you have done this, append both queries as new one.

Append queries in PowerBI

From the new query make sure to remove duplicate customers

Remove duplicates in Power Query Online

If your have a PowerBI Pro but not a Premium subscription, deactivate load of the underlying queries.

Deable load when using PowerBI Pro

Save and refresh the dataflow. From the settings schedule the refresh and endorse the dataflow as “Promoted” or “Certified”. This is not necessary but it adds a label to dataflow and your report designer users see that they can trust the datasource. In PowerBI Desktop open Get-Data and choose PowerBI dataflow as data source:

Get data from PowerBI dataflow

Select the merged Customer data source.

Promoted and certified PowerBI dataflows

You can use the dataflows in your PowerBI datamodel but dont have to worry about the logic behind

Linked dataflow sources in a PowerBI data model

Conclusion

Using dataflows has some advantages. It helps you to decouple ETL logic from design logic. Especially when working with older versions of Dynamics AX you have to have deeper knowledge about the data structure. Another advantage is the reuse of dataflows. Typically you are not creating 1 single report, but more reports that require the same dimensions e.g. customers. By using dataflows you don’t need to maintain the load and merge in multiple PowerBI files.