Who's On?

	Membership:
	Latest: Raju
	Past 24 Hours: 1
	Prev. 24 Hours: 2
	Overall: 5205

	People Online:
	Visitors: 581
	Members: 0
	Total: 581

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1369
Roger French	1315
mark.cook	1244

Multiple Data Sources for report

1 Replies

0 Subscribed to this topic

22 Subscribed to this forum

Sort:

Author

Messages

mil0n023

Veteran Member

Posts: 63

7/13/2006 11:53 PM

Hello there!
Currently we use PMM for our MM/Purchasing Suites, and Lawson for GL. As it stands we are generating 5 monthly reports that show (1)Receipt Accruals - PMM, (2)Invoice Distribution - PMM, (3)Issues Summary(stock items) - PMM, (4)GL Transactions - Lawson, & (5)Reconciliation.

These five go the acct user as a .CSV which is then uploaded to Access, and finally displayed in Excel using embedded Macros and Formula. There are 5 tabs, and each sheet represents the above mentioned reports.

We would like to automate this process. I am thinking of using Crystal to link the PMM dbases with Lawson in one giant report with the parameters needed. Has anyone had experience in using multiple data sources to produce one output? Data Warehouse'ing? I am interested to hear the struggles, or concerns...

I would like to publish this on Crystal Enterprise. We are currently running Lawson / UNIX / v8.03

John Henley

Posts: 3353

7/17/2006 12:27 AM

You can certainly report from multiple data sources in Crystal. What happens is that Crystal creates the SQL for each of the sources, and then joins/combines the data to produce the report.

The issues you will have are that you can't use "server-side group by" option (which means that grouping is done by Crystal not by the underlying database), which can slow things down. The other feature you can't use is "SQL expressions", which are equivalent to formulas, but are performed on the server.

If these are large datasets, you'll want to make sure you do as much selection in the database query itself rather than having Crystal do it after doing the joins. For example, if COMPANY is a key in both data sources, make sure you select on it from both sources as well as join on it--that will (hopefully) force Crystal to include it in the individual SQL queries that get sent to the databases.

You will want to use the "Database | Show SQL Query" menu command in Crystal to verify that the proper SQL queries are being generated.

Depending on your data, if it's what you need, it may be the answer.