How to Report across all companies using dataareaid field from Dynamics365

[Anonymous]

Hi All,

I have a report developed that fetches data from D365 and looks like based on the behavior, its only getting data for default company (dataareaid field). However, I am looking to report across all companies, I have been reading many threads that says adding cross-company=true makes it work but it does not work unfortunately. Basically, the dataareaid field only shows one value for which rows of info is getting pulled in. Can someone help me modify the odata feed so that I can filter on datareaid to pull in data for all dataareaid values. Thanks in advance. 

[hasanhilmi]

I really struggled with this as well! It actually does work but you need to make sure the table is specifically called out in the URL and remove the Navigation step.

Check your Advanced Editor.

Here's an example of a working cross company table for me. 

let
Source = OData.Feed("https://xxx.dynamics.com/data/InventTableBiEntities?cross-company=true", null, [Implementation="2.0"]),
#"Added Custom" = Table.AddColumn(Source, "CompanyItemKey", each [dataAreaId] & "-"&[ItemId])
in
#"Added Custom"

Before my fixes, it looked something like this and did not work. 

let
Source = OData.Feed("https://xxx.dynamics.com/data/?cross-company=true", null, [Implementation="2.0"]),
InventTableBiEntities_table = Source{[Name="InventTableBiEntities",Signature="table"]}[Data],
#"Added Custom" = Table.AddColumn(Source, "CompanyItemKey", each [dataAreaId] & "-"&[ItemId])
in
#"Added Custom"

[v-rzhou-msft]

Hi @Anonymous 

Do you use Dynamic 365(online) connector to get data from D365? The default OData behavior is that for any entity the data return is based on the users set default company. 

I think you can add ?cross-company=true in the end of your Web API URL.

For reference: Dynamics 365 for Operations: Access All Company Entity Data from Power BI

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for your response and sharing the soluton, however the suggested soltion does not work for implementation=2.0, it used to work before but no longer does now so adding cross-company unfortunately is not  a solution. MS should add support for this asap. Do you recommend any other solution?