Microsoft Dynamics 365 Business Management Solution Enthusiast
10. March 2023 Leave a comment
PowerBI supports training machine learning models like classification and regression in PowerBI Online. In preparation for a webcast I’ve recorded the training of regression model to predict mean-time-to-failure. Watch the step-by-step guide in my ERPCoder Youtube Channel
7. February 2023 Leave a comment
Dynamics 365 Finance and Supply Chain Management uses SQL Server Reporting Services for reporting and document generation. In 2022 Microsoft included SSRS (aka. Paginated Reports) in PowerBI Pro license. One may ask if it is possible to use PowerBI Paginated Reports as well for reporting purpose in Dynamics 365 F/SCM.
| X++ SSRS | PowerBI Paginated Reports |
| + Direct access to transaction DB + Business logic in X++ + Integrated with Dynamics 365 FO – Complex development – Embedded in Dynamics 365 FO deployment cycle | + Simple development using Report Builder + Integrated with Business Intelligence + Reuse existing data models – Limited development possibilities – No direct data access |
Paginated reports in PowerBI have only a limited capability to access data. There is no Dynamics 365 FO connector or OData access to entities. You could use different approaches to get Dynamics 365 FO data.
Paginated reports are designed by using the Report Builder. It is an adapted version of the SSRS Report Builder with some more data access features. In theory you could develop an SSRS report using Visual Studio and upload the .RDL file. However, PowerBI Paginated Reports are a limited version of SSRS and you might get errors.
Paginated Reports are a reduced SSRS feature in PowerBI. The Report Builder is very limited. You can define data access, defined parameters and place data on the report sheet. If you want to create printable documents without real-time requirement the Paginated Reports may be an option for you.
16. January 2023 Leave a comment
In some cases you want to filter a table based on a selection which is not related to the table. For example the SalesTable is realted to the CustTable via the Account. The QuotationTable is related to the SalesTable (QuotationID -> Origin). The QuotationTable also has an Account field but is not related to the CustTable.
Such a situation can be avoided by using a better design approach, however you may come across such a problem one day đŸ˜‰
Create a new measure in the CustTable that returns the selected value. Place a table visual showing the CustTable in the report. For debugging reasons you may want to place a card visual with your measure as well.
M_SelectedValue = SELECTEDVALUE(CustTable[Account])
In the not-related table (QuotationTable) create a new measure that returns the account number which will be used to filter the records
M_Account = SELECTEDVALUE(QuotationTable[CustAccount])
Add a second measure to compare the selected CustTable value to the CustAccount value in the Quotation table and returns a numeric value (not a boolean). If there is no CustTable selected return e.g. 2, if the selected values are equal return 1, else return 0.
M_Filter = IF(
ISBLANK([M_SelectedValue]),2,
If([M_SelectedValue] = [M_Account], 1, 0)
)Place a table visual in your report showing the QuotationTable and both measures
Configure a filter on the visual to show only records where the M_Filter value greater than 0.
If no customer is selected all quotations are shown because the M_Filter is 2.
If a customer is selected M_Filter is 1 for all matching records or 0 otherwise.
Now you can hide the M_Filter in the QuotationTable so your users don’t get confused
7. November 2022 1 Comment
The export to data lake feature in Dynamics 365 Finance & SCM allows you to export data from tables to a storage account. You can directly export tables like CustTable, InventTable, etc. and do not need to put it in entities. Power BI can directly access the datalake and load D365 tables like in the good old times. I’ve published a short video on Youtube:
13. October 2022 Leave a comment
Recently we had the requirement to use the product categories from Dynamics 365 Supply Chain Management in a PowerBI report as well. It should be used to filter products by categories and sub-categories. We were using Data Lake export in D365 FSCM.
This is how product category hierarchy looks like in Dynamics 365 SCM:
This is how it should look in PowerBI:
To build a product category model in PowerBI some tables need to be exported:
Start with EcoResCategory table. I’d recommend to remove all unnecessary fields and keep at least RecID, Name and ParentCategory.
The field ParentCategory is a reference to the RecID of the parent line. In theory this would be sufficient to build the hierarchy in PowerBI. In practice we want to create the hierarchy via the name, not the RecID. Create a copy of the table and remove all fields except the Name and RecID. I call the new table EcoResCategoryName:
Create an outer join from the original table to the new EcoResCategoryName table via the ParentCategory to the RecID.
As result the Name of parent is now part of the table. For example the parent of PC&Tablet is Lenovo Sales. and the parent of Server as well as the parent of Storage is Server & Storage.
Safe and load the data.
In PowerBI you can use the DAX formula PATHITEM to identify a certain element within a hierarchy. You have to provide the PATH (i.e. Name and ParentName) how the hierarchy is linked. For example to get the first level hierarchy element use the following code:
Level1 = PATHITEM(PATH(EcoResCategory[Name],EcoResCategory[ParentName]),1)In my example “Lenovo Sales” is a root element for my hierarchy. The other root elements like “Neue Kategorie” and “Elektro Geräte” belong to another hierarchy (see CategoryHierarchy field). My hierarchy has 3 levels, therefore I create 3 additional fields called Level1, Level2, Level3.
Level2 = PATHITEM(PATH(EcoResCategory[Name],EcoResCategory[ParentName]),2)
Level3 = PATHITEM(PATH(EcoResCategory[Name],EcoResCategory[ParentName]),3)
To create the hierarchy in PowerBI right click on the Level1 field and select create hierarchy. Right click on Level 2 and choose “Add to hierarchy”. Do the same with Level 3. Your hierarchy should look like this:
To implement a report like in Dynamics 365 SCM, load 3 additional tables:
Link the EcoResCategoryHierarchy.RecId to EcoResCategory.CategoryHierarchy
Link the EcoResCategory.RecId to EcoResProductCategory.Category
Link the EcoResProductCategory.Product to EcoResProduct.RecId
In my example the relations look like this:
Add visuals to the report
I’ve added 3 visuals to the report. First, a filter element to choose from the EcoResCategoryHierarchy. For example the “Lenovo” hierarchy consisting of Laptops, Servers and Storage products.
Next a filter element for the hierarchy that was created earlier. PowerBI presents the hierarchy in a drop down tree. To avoid “Blank” elements in the hierarchy create a visual level filter and exclude the blanks
My third visual is a table containing the product. The products get filtered based on the selection of the hierarchy elements.
21. August 2022 Leave a comment
A recent requirement at a customer was to compare Sales Quantity and Sales Totals of different time spans in one matrix. The customer is selling saisonal as well as all-time products. It should be possible to compare the sale of saisonal products from last season to the actual sales.
The original data contains 6 rows: The product, the corresponding collection, the sold quantity, the unit price, calculated total price (Qty * Price), date.
In order to make the Quantity and Totals available for a row-like visualization the original data was unpivoted. This created a new column called “Attribute” and another Column called “Value”. The unit (pcs, €) have been added to the attribute to make it more readable. The resulting data looks like the following in PowerBI:
To implement 2 independet filter options based on date and collection 4 independet tables have been added. A date table and a collection table for both filter options. The date tables can easily be generated by using the Calendar() function:
Filter1Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))Filter2Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))The collection tables are generated by using the Distinct() function on the collection column in the sales data table. Each of the tables contain a distinct list of collections available for filtering.
Filter1Collection = DISTINCT(Sales[Collection]) Filter2Collection = DISTINCT(Sales[Collection])
Three measures have been create. Two measures that sum up the Value column in the sales data filtered on the selected values in the Filter1* table or Filter2 table:
M_Sum1 = CALCULATE(
  SUM(Sales[Wert]),
  FILTER(
    Sales,    Â
    Sales[Date] >= FIRSTDATE(Filter1Date[Date]) &&
    Sales[Date] <= LASTDATE(Filter1Date[Date]) &&
    Sales[Collection] in VALUES(Filter1Collection[Collection])
    )
  )M_Sum2 = CALCULATE(
  SUM(Sales[Wert]),
  FILTER(
    Sales,    Â
    Sales[Date] >= FIRSTDATE(Filter2Date[Date]) &&
    Sales[Date] <= LASTDATE(Filter2Date[Date]) &&
    Sales[Collection] in VALUES(Filter2Collection[Collection])
    )
  )The third measure calculates the difference between the other two measures:
M_Diff = [M_Sum2]-[M_Sum1]Finally the report contains 5 visuals. Two slicers for date and collection on the left, another two slicers for date and collection on the right, and a matrix in the middle. The matrix shows the Attribute column as row and shows M_Sum1, M_Sum2 and M_Diff as values.
For example comparing collection “Summer 2021” from 1. Jan. 2021 till 30. Sep. 2021 to the aggregated values of “Collection 2022” + “Basic Wear” from 1. Jan 2022 till 30. Sep. 2022.
The slices on the left only affect the M_Sum1 measure which shows quantity and totals in the Filter1 column while the slicers on the right effect only M_Sum2 measure which shows the results in Filter2 column. The difference is calculate dynamically when adjusting the slicers.
7. June 2022 Leave a comment
PowerBI goals are used to keep track of targets like sales quantity, quality, etc. Goals can be linked to values from PowerBI reports. This video shows how to create a scorecard with goals based on Dynamics 365 Finance data.
12. January 2022 Leave a comment
Dataverse is the name of the Dynamics 365 Apps database (e.g. Sales, Service, Field Services, etc.). PowerBI desktop comes with a builtin Dataverse connector. However, if you have multiple environments not all of them may show up in the connector.
In order to make the environment visible in PowerBI Desktop Dataverse Connector you have to enable PowerBI Embedded in your environment.
Now when you open PowerBI Desktop and use the Dataverse Connector you should see the missing environment.
9. June 2021 2 Comments
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.
Decision Tree for Power BI / Reporting in Dynamics 365 Finance & SCM
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.
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.
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.
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.
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
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.
9. December 2020 1 Comment
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:
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 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.
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.
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.
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, "")
)
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.
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.
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.
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.
ErpCoder 