Technical Questions: Summarize from two data sources

Summarize from two data sources

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3864
Printed Date: 28 Apr 2024 at 1:56am

Topic: Summarize from two data sources

Posted By: ahz0
Subject: Summarize from two data sources
Date Posted: 31 Jul 2008 at 2:12pm

I am not sure how to summarize data from two data sources (SQL Server and Access). I know how to do it from each data source one at a time (by using grouping and hiding the details), but my internal customer wants it all in one report linked by username. My trick does not extend to two data sources because there is not a one-to-one relationship between the records.

Here's a simplification:

Data source 1: SQL Server
Username, Time Interval, Quantity, Time
Bob, 7/1/2008 12:30pm, 5, 10
Bob, 7/1/2008 1:00pm, 1, 2

Data source 2: Access
Username, Date, Quantity, Time
Bob, 7/1/2008, 500, 5
Bob, 7/1/2008, 400, 4
Bob, 7/2/2008, 100, 1

Output
Username, AVG Time/Unit 1, AVG Time/Unit 2
Bob, 2, 1

I use Crystal Reports Advanced 10.0 to embed reports in http://inin.com - CIC 2.3.1.

Replies:

Posted By: BrianBischof
Date Posted: 31 Jul 2008 at 5:29pm

Since you don't have a link between the two fields, then there isn't an easy answer. What you MIGHT be able to do, is use SQL Server to link to the Access database and reference as if it were a field within SQL Server (it has tools to let you do that). Then create a stored procedure which merges the two tables together using a UNION operator. Somthing like this:

SELECT UserName, Qty FROM SQLTable
UNION
SELECT USerName, Qty FROM AccessTable

If that doesn't make sense, I cover the UNION keyword in the SQL Optimization chapter of my Encyclopedia book and Google will give you tons of links for it as well. The solution is a bit convoluted, but I think it will work for you as long as you can get SQL to reference the Access table seamlessly.

You can find out more about my books at http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - Amazon.com or reading the http://members.crystalreportsbook.com - Crystal Reports eBooks online.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: ahz0
Date Posted: 01 Aug 2008 at 8:25am

It is not feasible to modify the SQL Server. I would have to go through too much red tape.

On the SAP forums, I got an interesting answer about subreports, but I am trying to figure out how to pass complex variables

https://forums.sdn.sap.com/thread.jspa?threadID=988084&tstart=0