Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Using Data from multiple Tables Post Reply Post New Topic
Author Message
JamesS
Newbie
Newbie
Avatar

Joined: 16 Jul 2009
Location: United States
Online Status: Offline
Posts: 16
Quote JamesS Replybullet Topic: Using Data from multiple Tables
    Posted: 16 Jul 2009 at 9:12am
Alright I'm a still a newbie when it comes to CR with multiple tables. I don't really have an issue when it is one as it is fairly straight forward. BUT the last few reports I've worked on or am working on require data from multiple tables in the database or data from two separate databases.

Either way I end up with either re-occurring data or running totals which go hay wire. Here is an SS of the bottom portion of my report. the two boxed in portions on the bottom are sub reports (the upper portion is the main report. I just have it boxed off for easier legibility). For this report almost all the data except for those three fields come from one table.



Now if say I try and include a running total to tabulate the # of Sec Messages from table 3 instead of pulling the data in a sub report like I have done my running totals go nuts. For instance in the SS the top portion has # of Sec Calls at 59. If I included my running total on the report to pull the # of sec messages for that same period all of my running totals go way up. that 59 could become 30,000.

Let me explain the report structure a bit.

I have three tables I have tried both linking and not linking them. But currently they have the billing ID of each table linked. I also have the report grouped by the Billing ID of table 1 (where most of the data is pulled from). The subreports use that grouping for determining what data to display instead of just pulling it all everytime.

Now my question is How do I..
A. Pull the data from Table 2 and 3 without it fubaring the data pulled from table 1.
OR
B. Pull the data out of the sub reports so that I can use them in calculations.

If I can pull the data out of the subreport to use in formulas that will suit me in this case just fine. Though it would be very helpful to know how to properly utilize multiple tables so that I can pull data from one without it effecting data pulled from another. So really I only need A or B. but knowing how to do it both ways would extremely benefitial.


Edited by JamesS - 16 Jul 2009 at 9:13am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 20 Jul 2009 at 8:13pm
A lot of it depends on how your data is structured.  If you don't, you'll get a cartesian join where for each record in each table, you'll also get all of the other records in the other two tables.
 
Are you linking from Table 1 to Table 2 and then from Table 2 to Table 3?  Or are you linking from Table 1 to Table 2 and also to Table 3?  For each Billing ID, which of the three tables can have multiple records?  For each table with multiple records, is there another field that identifies a unique record?
 
-Dell
 
IP IP Logged
JamesS
Newbie
Newbie
Avatar

Joined: 16 Jul 2009
Location: United States
Online Status: Offline
Posts: 16
Quote JamesS Replybullet Posted: 21 Jul 2009 at 7:02am
I did have a Cartesian join then it was pulling data from all three tables into one giant pool of data.

Each table has multiple fields that are in the other, but they are not the same data. the only field that stays the same across is billling ID. I figured out what I did wrong. Also found the answer for sharing variables which was more what I needed. since the master report I'm currently working on pulls from 5-6 other rpts who pull from a wide range of databases.

Make a formula field with the below in the data containing the data you want to pass.
Shared NumberVar x := 1000;

and declare it in a formula in the report you want to receive the data as the following example:

Shared NumberVar x;

The report which is sharing the data needs to come before the shared field in the recieving report to work right.
Though this can be occamplish by splitting say a group footer field into A/B and putting the report in A the recieving field in B or however you wish to accomplish it.

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.031 seconds.