Check my previous post here about Power BI configuration, prerequisites and components needed for report.
In this post, We will see how to create a Power BI report, Upload report to LCS and deploy report. We will use Microsoft cloud hosted environment to build report(tier 1 environment) with continuous updates. We have to use tier 2 environment to view the report in Dynamics 365 F&O workspace.
Note : There are some limitations to view report in tier 1 environment. Always use Tier 2 environment to deploy report and see results. Mostly it can be done by LCS admin. You can develop, test and verify using tier 1 environment before moving to tier 2 environment.
1. Develop a report
- Create a new data entity. Here I took an example of HcmBICurrentPositionEntity.
You can use table/View/Entity. It is based on your requirement
- Create new dimension.
- Create new measurement
- After completing above steps, We have to compile and sync model to see the changes.
- If there are no issues with step 4 then we can see DemoPositionMeasurement is created in Entity Store( System Admin> Setup > Entity Store)
Here we can make periodic refresh for Power BI report.
- Deploy the measurement to see definition and data in AxDW database.
- Lets open Power BI desktop and login with your credentials
Click on Home > Get data > SQL Server
Select the SQL server name, AxDW database and select Direct Query mode
- Select the measurement
- Drag and Drop fields to Visuals and create some filters based on your requirement
- Save the Report
- Add this PBIX file to Your model folder
For example : K:\AosService\PackagesLocalDirectory\YourModelName\ YourModelName \AxResource\ResourceContent\PowerBIReport
Note : Change to your directory path.
- Select the workspace where you want to display Power BI report
Make PoweBIReportGroup – Auto declaration to Yes.
- Write below piece of code to display report when form is initialized.
Here we have to provide Power BI report name(.PBIX) and Menu item name(Form menu item).
2. Upload PBIX file to LCS Environment
3. Deploy report
Now go to System Admin > Setup > Deploy Power BI files
Note : Make Sure, you are logged in with Admin account otherwise It will give an error below.
After you deploy the report successfully then go to Workspace and see the report is embedded within Dynamics 365 F&O workspace.
Note : Sometimes, It ask you to connect LCS again.
In this way, We can embed Power BI report in Dynamics 365 F&O. If you have any questions related to above steps then please contact me.
5 Comments
Hi Parag, great work on this documentation. Just a short question: Did you ever realize some filtering on embedded PBIReports in D365 F&O that is depending on the logged in user? Something like a drillthrough on data that is linked to a certain team, so that this data is prefiltered on the team when a member of the team is opening the report?
Hi Parag, I have some questions.
1. Can we create a Power BI Embedded report using data entity published in AxDW DB or Odata and use it in custom workspace?
2. If yes, do we require a power BI Pro license?
3. In which scenarios we require a Power BI Pro license for power BI embedded report?
Hi Mukul,
You can do both way. If you are using embedded report then you will need power bi pro license.
How can we write the custom code in a Tier 2 environment to add the report to a workspace. To my understanding, we will not be having access to the RDP.
You need to write the code in Dev environment and deploy the code to respective environment where you want to view the report.