UNION ALL data of the same table from all companies in the database

(0) Share

Report

Posted on by Lucian C.

Hello,

Our company recently migrated our NAV based on an Azure SQL Server to a SaaS - Business Central.

I need to create some Power BI reports with data from all of the companies.

On the SQL Server I had administrative access and could create some views that do UNION ALL for the same table (like Accounts or Customers) for all companies.

Is there any way to achieve this "union all" in Business Central without using other services like Azure Data Lake as I've seen into another forum post (Access data from all companies in one central extension)?

Kind Regards,

Lucian

All responses (4)

Answers (1)

Suggested answer

Steven Renders 5,051 Super User 2024 Season 1 on at

Like (0)

Report

RE: UNION ALL data of the same table from all companies in the database

That is very easy to do in Power BI.

An example:

Business Central Power BI Multi Environment template – think about IT

Create Stunning Power BI Reports Using the Standard BC API’s – think about IT

How Do I: Create a simple API query and page for PowerBI? – think about IT
Suggested answer

YUN ZHU 74,115 Super User 2024 Season 2 on at

Like (0)

Report

RE: UNION ALL data of the same table from all companies in the database

Hi, This needs to be customized if you want to view it in the BC UI.

For example,

Collect data from different companies and Drilldown into the data of another company

https://yzhums.com/21252/

However, if you are using the On-Premises version, you can directly access the BC database.

This part of the table name is the company name. Maybe it will help you.

Hope this helps.

Thanks.

ZHU
Lucian C. 10 on at

Like (0)

Report

RE: UNION ALL data of the same table from all companies in the database

Hi Daniele,

Thank you for sugestions.

Lucian
Verified answer

DAnny3211 9,272 Super User 2024 Season 1 on at

Like (1)

Report

RE: UNION ALL data of the same table from all companies in the database

Hi

not possible, you would have to develop a procedure that would read the data in the various compnays and insert them into a temporary or physical table of type DataPerCompany=yes

otherwise use api to retrieve the data from the various companies

DAniele