Merge Reports of Multiple Accounts

[Former Member]

Hi Experts,

I need to create a merge crystal report from 3 companies(Accounts from SAP B1), please give me some advice on how to do it. Some told me to create a Merge Database on SQL and create a report from this database but I am a newbie on SQL....

Please give me some procedure or anything than can solve my problem...

Thanks a lot and god bless,

John

[Former Member]

Hi,

You can use View concept to do this task.

What Kind of Reports you need?

Are all the companies are having same accounting structure?

[Former Member]

Hi Manikandan K,

Thanks for the message, yes all accounts have the same structure...(Charts of Accounts, UDF,UDT,Document Numbering ..etc)

Thanks,

jonathan

[Former Member]

Hi,

Assume that your three company Database is Name like this.

Eg: To build a consolidated chart of accounts table.

First Company:

Company Name: ABC

Database Name: DB1

Second Company:

Company Name: DEF

Database Name: DB2

Third Company:

Company Name: XYZ

Database Name: DB3

Now we have to choose any one company, to view the report or else create a seperate SQL database to store the consolidated data.

CREATE VIEW ChartOfAccounts
AS 
Select A.AcctCode as 'Account Code',A.AcctName as 'Account Name',
A.CurrTotal as 'ABC Company Balance', B.CurrTotal as 'DEF Company Balance', C.CurrTotal as 'XYZ Company Balance',
(A.CurrTotal + B.CurrTotal + C.CurrTotal) as 'Consolidated Account Balance'  
from [DB1].[dbo].[OACT] A Inner Join [DB2].[dbo].[OACT] B On A.[AcctCode] = B.[AcctCode] Inner Join [DB3].[dbo].[OACT] C On B.AcctCode = C.AcctCode

After Executing the Above Code. The View ChartOfAccounts Will be created in your database.

It is a virtual table.

Then you can write a simple select statement to select the data from ChartOfAccounts View.

For Eg.

Select * from ChartOfAccount

[Former Member]

Thanks a lot sir...

You've solve my prob...

god bless,

John