Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

Multiple Data Sources for report

Sort:
You are not authorized to post a reply.
Author
Messages
mil0n023
Veteran Member
Posts: 63
Veteran Member
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
    M. C. Miller
    Analyst
    Salem, Oregon
    John Henley
    Senior Member
    Posts: 3348
    Senior Member
    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.
      Thanks for using the LawsonGuru.com forums!
      John
      You are not authorized to post a reply.