Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Summarize from two data sources Post Reply Post New Topic
Author Message
ahz0
Newbie
Newbie


Joined: 31 Jul 2008
Location: United States
Online Status: Offline
Posts: 2
Quote ahz0 Replybullet Topic: Summarize from two data sources
    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 CIC 2.3.1.
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet 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 Amazon.com or reading the Crystal Reports eBooks online.


Edited by BrianBischof - 31 Jul 2008 at 5:30pm
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>
IP IP Logged
ahz0
Newbie
Newbie


Joined: 31 Jul 2008
Location: United States
Online Status: Offline
Posts: 2
Quote ahz0 Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.032 seconds.