Skip to main content

Notifications

Small and medium business | Business Central, N...
Answered

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

Posted on by 10

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

  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 5,051 Super User 2024 Season 1 on at
  • Suggested answer
    YUN ZHU Profile Picture
    YUN ZHU 73,035 Super User 2024 Season 2 on at
    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.

    pastedimage1675386841038v1.png

    Hope this helps.

    Thanks.

    ZHU

  • Lucian C. Profile Picture
    Lucian C. 10 on at
    RE: UNION ALL data of the same table from all companies in the database

    Hi Daniele,

    Thank you for sugestions.

    Lucian

  • Verified answer
    DAnny3211 Profile Picture
    DAnny3211 9,272 Super User 2024 Season 1 on at
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans