Dynamic Data Source for Power BI reports in case of dynamics 365

[nihal2001]

I am currently working on a Dynamics 365 project, where we intend to utilize Power BI reports as linked reports within Dynamics 365 (online). Our goal is to design a single report that can be reused across multiple clients, with each client accessing data from their respective Dynamics 365 environment. The data source for the report should change dynamically based on the client, ensuring that the same URL can be shared with different clients while displaying data relevant to their specific environment.

We do not wish to publish the reports within the clients' environments. Instead, we aim to have clients access our report, configured to dynamically connect to their respective Dynamics 365 as data sources.

Is there a way to achieve this?

We attempted to create a parameterized report, but encountered an issue. When we set a parameter value (e.g., "OrgName") in Power BI Service, the same value was applied to all clients. For instance, if we set the parameter "OrgName" to "ABC" in Power BI Service, the data source was configured as "ABC" for all clients, which is not the desired behavior.

Our requirement is for the parameter "OrgName" to be dynamically set based on the client accessing the report. Specifically:

• For Client 1, the parameter should be set to "ABC."
• For Client 2, it should be set to "DEF."
• For Client 3, it should be set to "GHI," and so on.

We need this dynamic behavior to ensure each client views data from their respective Dynamics 365 environment without affecting others.

Is there anything missing or incorrect in the approach we have followed so far? Additionally, are there alternative approaches to achieve the desired functionality?

Our primary goal is to ensure that each client dynamically accesses their respective Dynamics 365 data through a single Power BI report, without the need to publish separate reports in their environments. Any guidance or suggestions would be greatly appreciated.

[v-mengmli-msft]

Hi @nihal2001 ,

Thanks to @lbendlin  and @rohit1991  for your replies, @nihal2001 the following are some additions I have made that may be helpful.

In Power BI, published report is usually connected to a fixed data source. Dynamic data source connections for published report are not a feature directly supported by Power BI, but you can consider other alternatives. In addition to parameters, URL filtering and RLS, you can also consider the following approaches.

1.Using Power BI Embedded and Custom Applications
With Power BI Embedded, you can embed reports into your web application and use the Power BI REST API to manage reports and datasets. While this doesn't directly switch data sources, you can do so by creating multiple reports or datasets and switching them in your application as needed.

Embed Power BI report in a Power BI embedded analytics application for your customers - Power BI | M...

2. Web.Contents.(Expose D365 data as a web service through a custom API or middle-tier service and access that data using Web.Contents)
You might consider writing a custom Web service that returns different data depending on the parameters passed in, and then using Web.Contents to get the data from this service.

Web.Contents - PowerQuery M | Microsoft Learn

In Power BI Desktop, you can use the Web.Contents function to access this web service. You need to provide the correct URL and any necessary request headers (such as authentication information).

let
Source = Web.Contents(“Your D365 Data Web Service URL”, [Headers=[Authorization=“Bearer Your Authentication Token”]]),, ...
// Parse the JSON response (assuming the response is in JSON format)
JsonData = Json.Document(Source)
in
JsonData

Best regards,

Mengmeng Li

[nihal2001]

Okay!!, but how do I set the parameter of the report embedded in runtime, dynamically?

[lbendlin]

You do that in the embedding framework of your choice, programmatically.

[rohit1991]

To create dynamic Power BI reports for multiple Dynamics 365 environments:

1. Use Parameters for Data Sources:

   • Create a parameter (e.g., OrgName) in Power BI Desktop for the Dynamics 365 environment URL:
     https://{OrgName}.crm.dynamics.com.

2. Dynamic Filtering with URL Query Strings:

   1. Use query strings in the Power BI Service URL to pass parameters dynamically:
      Example:
      https://app.powerbi.com/reports/{report_id}?filter=Parameter/OrgName eq 'ABC'
      

      3.Add Row-Level Security (RLS):

      • Use RLS to restrict data by client (e.g., ClientID) for added security. Map users to their respective data using USERPRINCIPALNAME().

3. 1. Alternative - API-Based Approach:

      • Use an Azure Function or API Proxy to return connection strings dynamically if query strings aren't viable.

   Key: Use dynamic filtering or RLS to ensure client-specific data access securely.

[nihal2001]

but using query strings in the Power BI Service URL to pass parameters dynamically filters out the existing data based on filter parameters, can that be used to fetch the report dynamically with different data sources as well?

[nihal2001]

Wouldn't the data source be the same for all the clients, if we used Power BI Embedded?

[lbendlin]

It is entirely up to you which data to show to which customer. You need to program that 

[lbendlin]

You are looking for Power BI embedded analytics overview - Power BI | Microsoft Learn