Personalization, Customization and Productivity Tools

[dhalpin]

What solution can we use to run reports that combine multiple companies in MAS200? We need not only GL Reports, but reports related to Sales History, Vendor History, Customer And Item Sales History. We have tried creating Crystal Reports but they take forever to run.  We've invested so much time in these crw reports that we don't want to lose. Will SQL accomplish this or is there some other report writer besides Crystal we can use? SMI is an option but we want to retain the work we've done in CRW somehow.

[connex]

I do this with MS Access, multiple ODBC DSN's and append queries and refresh it nightly. Then one needs only to redirect their Crystal Report to the Access .mdb file with the Set Location command. This preserves your investment in the Report.  I have done this with over 250 companies at once while adding a column to each table for the company name. If you don't have many companies to combine then you could do this manually.

[DFeller]

Another way to do this is via SQL server.  You can combine multiple tables into one with a column called "Company Code" to distinguish the data between them.  Reporting off SQL is faster than ProvideX and SQL can hold more data than Microsoft Access.

Dawn

[ASI]

We offer an add-on product that does this - it's called company consolidation.  You can review information about it on our website here: http://www.asifocus.com/?page_id=132.

[rmikolainis]

---

ASI wrote:

We offer an add-on product that does this - it's called company consolidation.  You can review information about it on our website here: http://www.asifocus.com/?page_id=132.

---

I use this product for one of my clients, works great.

[dhalpin]

Ended up upgrading to Mas200 SQL 4.50 to be able to combine 2 compaines into one crw report. But how to I combine tables into one to get a column for company code? Is there a writeup somewhere out there on this? Also, once you've placed a report on the Mas200 SQL custom reports menu, how can you get one report to run on whatever company you are in? Thanks.

[DFeller]

I would use a UNION select statement with an extra column to denote the company code.

e.g.

SELECT *, 'XXX' AS companyCode from AR_Customer

UNION ALL

SELECT *, 'YYY' as companyCode from AR_Customer

Note that each AR_Customer table will be deliniated with the database it's coming from.  This would be a view that you could place in each database so you could run it from any company.

Dawn