Currently, it not possible to modify a Power BI report embedded inside Dynamics 365 Human Resources. There is however, a workaround to create such a report. In this article, we will show you how you can create a Leave and Absence report in Dynamics 365 Human Resources. But before we start, we have to understand the functionality of Leave and Absence and the architecture behind it. Below, you can see a diagram to better understand the Leave and Absence architecture in Dynamics 365 Human Resources.
Creating a Leave and Absence report in Dynamics 365 Human Resources
In order to create a Power BI report, we need the following data entities as pictured below:
If you look at the report below, you can see that the data is coming from the entities Worker, Leave type, Leave plan, and Leave bank transactions. These entities are already available in the CDS.
Dynamics 365 Human Resources – Leave and Absence (Current Balance Analysis)
Creating a Power BI report
In order to make a Leave and Absence report, we need to work with Power BI. These are the steps you need to follow:
- Open the Power BI desktop
- Click on Home > Get Data > Common data service > Select the entities mentioned in the screenshot below
- Add a relation between the entities
- Add the Matrix Visual to the report. This is how you do it:
- Add name and Personnel number to rows
- Add leave type to columns
- Add amount to values
Once this is done, your visual should look like this:
5. We can add more visuals to the report if we like. This is how you do it:
Axis: Leave type, Values: current balance.
Once you have followed the steps above, your report should be ready. It should look like this:
You can even add more filters to your report. This way, you can create many visuals, making your report even better. If you compare the report in Dynamics 365 HR and the report created based on CDS, then there is not much difference in the data.
Embed report in Dynamics 365 HR Workspace
- Publish report to Power BI workspace
- Go to report, click on Share > Copy the Secure Embed code
- Go to Dynamics 365 HR Home > Add new page > Website > Add name and use Secure Embed code from Power BI
This is how you can create a Power BI report in Dynamics 365 Human Resources. If you like the article, feel free to share it work others who might find it helpful. If you have any questions, feel free to get in contact with me.
Hi, this is a great report thanks – do you know if it is possible to create a report showing leave enrollment end date and position end date? I can report on both end dates separately, but can’t get them both showing on the same report because Power BI can’t detect the relationships between the fields. many thanks, Tom