To begin with, let us first create a Dynamics 365 Trial.
After our CRM trial is setup up successfully, we will install the OOB Sample Data which we would be using for creating the Power BI report.
Next, we need to create a free trial for the above user to use Power BI Service.
Go to Power BI site, and click on Start Free to get started.
Use the same user account (trial) that was used to set up the Dynamics 365 trial.
Note: If we already have an organization account with Power BI License we can use it to log in to Power BI else can associate a free trial to the same organization account instead of creating a new one. The gmail, hotmail, yahoo etc personal account are not allowed. https://app.powerbi.com/.
Download the Power BI Desktop as well as click on Try Free.
Power BI Desktop is basically a feature rich windows application that allows us to get data from various services online or on-premise and develop Dashboard and Reports efficiently.
The alternate way to download is through Microsoft Downloads siteà
After installing it and signing in and click on Get Data ribbon button to connect to the Dynamics CRM Online.
Select Dynamics 365 Online as the Online Services to connect to.
Specify the Web API URL of the instance.
Copy it from Customization à Developer Resources
Select the Organization Account and Sign in to the CRM Instance.
In the Navigator window, we have searched Lead entity and clicked on Edit to select the columns that we need for our sample report.
In the Query Editor window, click on “Choose Columns“, we have selected the Revenue, Created On, Subject and Owning
User field to be used for the report.
Now to get the Full Name of the system user for Owning User, expand that column, and select Full Name as the value to be displayed as shown below.
Click on Close and Apply to apply the changes to the query and to go back to the main editor.
Back in the main editor, we can see the fields added.
Now from the Visualizations section pick any of chart type.
Here we have picked a Stacked Bar Chart to start with. We can drag the fields and specify Area, Legend, and Value and also use the Filters section to generate the report as shown below.
The report after adding the fields à
Similarly, we can add our filter in the FILTER section to filter the records further as shown below
We can also add multiple charts in the report by selecting it from the Visualizations section.
For e.g. here we have used a Clustered Column chart to see all the leads based on Owner and Created On Date.
This completes the creation of simple reports which uses Dynamics 365 Online as Data Source.
In the next post, we’d publish this report to Power BI Service and display it within Dynamics 365 CE.
Hope it helps..
7 thoughts on “Power BI with Dynamics 365 CE – Creating Power BI Report”