Multi-Company Reports

Hi All,

Today I want to share with you a way how you can print multi-company (multi-tenancy) reports.
You know that Acumatica tenants are completely separated and there is no way to make a report or generic inquiry for multiple tenants.

So first of all want to share with you the technical details – Acumatica stores all tenants in one database, but separate it by CompanyID column, that is a part of primary key. You can read more about it here.

Acumatica Framework automatically detects CompanyID column in the table and adds “WHERE CompanyID = {Something}” to every query. Unfortunately you cannot change anything here.
But good thing is – if you have table without CompanyID column, it will be also supported and Acumatica will work with that table as a global for all tenants.

So if we create a view without CompanyID column, we can use it for global reporting. The only one thing that lefts is to create a DAC for that view and use it in Acumatica.

However views and DACs are not really protected from updates – if you update Acumatica and there are changes in database, you have to recreate views and DACs.
So here I want to show you few tricks, how you can keep it update protected with minimum changes.

Welcome in the article for more details.

Views
You cannot specify select * from table except one column, so when you create a view it should have list of all columns. That approach is not really stable, but we can design the procedure that will automatically recreate a view in case on any change in database.

To do this I have done following:

1. Stored procedure ( dbo.pp_GenerateView ) that can dynamically create a view based on existing table with excluded Company ID column. However to identify tenant from the report we should add new column – TenantID. It would not be used by Acumatica Framework, but can be used by us. Full procedure code is attached later in thit article.
2. Recreate a view on every customization publication using customization plug-in. Acumatica can execute stored procedures, so that is a way to recreate a view. PXDatabase.Execute(“pp_GenerateNTView”, … ); Full code of customization plug it is attached later in this article.

You can read more about stored procedures in Acumatica here.

DACs
When you have a new table or view, you can generate DAC from Acumatica customization project browser, but it will not contains all special attributes, like PXSelector, PXUIField, PXDBCalced and so on. Do and support it manually is really tough.
Good way is to inherit your new DAC from original, so your DAC would be always up to date, but here we need to be careful with table name. By default Acumatica identifies table name based on IBqlTable interface of top most DAC.

To advise Acumatica that it should select data from yours view, we can use PXTableNameAttribute on your DAC.

[PXTableName]
[PXBreakInheritance]
public class mc_Project : PMProject, IBqlTable
{
}

PXTableNameAttribute – idicates the name of the table that should be selected from database.
PXBreakInheritance – when placed on a derived data access class (DAC), indicates that the cache objects corresponding to the base DACs should not be instantiated.

Than just add there a TenantID field there and it is ready to use.

Report
Finally we can use our view and dac in generic inquiries and and reports:

Full source code of customization:

Have a nice reporting!