How to set Cross-Company in PowerBI when using Odata

(0) Share

Report

Posted on by Mark Schurmann

I am trying to write a PowerBI report against my D365FO Odata feed. This defaults to my D365FO default legal entity. I would like to write my report against all companies and be able to filter by DataareaID. There is a separate post on this site related to doing this within C# code, but I cannot figure out where to set the cross-company=true for the PowerBI report. I've attempted to append it to the url without success. Can someone show me the syntax to use in either the odata url or the advanced data entities configuration?

Categories:

Finance and Operations

I have the same question (0)

All responses (15)

Answers (0)

Suggested answer

Sukrut Parab 71,710 Moderator on at

Like (1)

Report

You can use it like this

"myaos.cloudax.dynamics.com/.../Customers"

customers is a entity Public name deployed at odata endpoint

take a look at this blog from Martin

community.dynamics.com/.../odata-service-across-companies

Was this reply helpful? Yes No
Mark Schurmann 4 on at

Like (1)

Report

I'm still confused about where to set this in PowerBI. If I select the Customers entity from the Odata feed, the only place I see a possible place to set this is by right clicking on the entity and selecting Advanced Editor. The text in the editor looks as follows:

let
Source = OData.Feed("apco-prod.operations.dynamics.com/data"),
Customers_table = Source{[Name="Customers",Signature="table"]}[Data]
in
Customers_table

Is this where I need to set the Cross-company to true? If so, can you show me the syntax? I've tried every obvious combination and it always gives me an invalid identifier error. Also, is there a reason why this can't be set in the Source definition rather than having to do it for each entity?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Adding "?cross-company=true" to the end of the url used work. However, with the new [Implementation="2.0"] it no longer does. I have not yet been able to find a way to pull all or multiple legal entities when using [Implementation="2.0"]. Which is a shame as it otherwise would help speed up queries.

Using D365FO 7.1 (about to roll up to 7.3)

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

[quote user="Mark Schurmann"]
I am trying to write a PowerBI report against my D365FO Odata feed. This defaults to my D365FO default legal entity. I would like to write my report against all companies and be able to filter by DataareaID. There is a separate post on this site related to doing this within C# code, but I cannot figure out where to set the cross-company=true for the PowerBI report. I've attempted to append it to the url without success. Can someone show me the syntax to use in either the odata url or the advanced data entities configuration?
[/quote]

You can try this:

let
Source = OData.Feed("apco-prod.operations.dynamics.com/data/Customers?cross-company=true", null, [Implementation="2.0"]"),
//Customers_table = Source{[Name="Customers",Signature="table"]}[Data]
Customers_table = Source
in
Customers_table

Was this reply helpful? Yes No
Suggested answer

Momochi 1,036 on at

Like (0)

Report

OFeed will always bring your default company, unless you specify a data entity in the URL with added "?cross-company=true"

For example:

OFeed URL: "Name.Dynamics .com/data/customers?cross-company=true"

Unfortunately, "cross-company=true" does not work directly after "/data" (I mean /data?cross-company=true)

You have to do OFeed for each entity you need separately :/

It's a little more work, but anyway it works!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

How do I specify a non-default legal entity in OFeed URL? Let's say the company name is USOP

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Can you share the example of odata url that works for you? I have been wrapping my head around this and have not been to able succeed so far, thanks in advance.

Was this reply helpful? Yes No
Momochi 1,036 on at

Like (0)

Report

You can see my above reply for an example :)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks so much. My instance url is: xxx.xxyys.dynamics.com/

so should I add customers?cross-company=true", is that the solution? Just trying to understand if adding customers is generic thing or specific to the prod instance of Dynamics for a tenant?

Was this reply helpful? Yes No
Momochi 1,036 on at

Like (0)

Report

customers in "customers?cross-company=true" is the entity name you want to import in your Power BI.

So you can change it to whatever entity you want to get :)

For example, if you want to get the Vendors entity, you simply replace customers with vendors like the following: xxx.xxyys.dynamics.com/data/vendors?cross-company=true

Was this reply helpful? Yes No