Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Links in Database Expert section! Post Reply Post New Topic
Author Message
atp2K3
Newbie
Newbie


Joined: 29 Dec 2011
Online Status: Offline
Posts: 10
Quote atp2K3 Replybullet Topic: Links in Database Expert section!
     Posted: 24 May 2012 at 3:24am
Hello all,
 
My complex report consists of a lot of aggregated calculations and data from different source of tables. There are three data tables from dataset that use in my report. I wrote three store procedures in order to manipulate the data for the report.
 
I got an issue with Links option when using join table, for example:
 
DataTable1
 
Make | Type | Model
================ 
BMW | Sport  | X5
BMW | Sedan |  525XI
BMW | Sedan |  745LI
 
DataTable2
Make | Type | Price
================ 
BMW | Sport | 50000
BMW | Sedan |  15000
BMW | Sedan |  30000
 
I try to display the data on report by order when join them as following:
 
Make | Type | Model | Price
======================
BMW | Sport  |  X5 | 50000
BMW | Sedan |  525XI | 15000
BMW | Sedan |  745LI | 30000
 
I would like to obtain three records as shown above. Which options I need to select in Links section? Any help is much appreciated. Thanks in advance.
 


Edited by atp2K3 - 24 May 2012 at 3:53am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 May 2012 at 3:52am
unless there is another field or table that indicates from table 1 to table 2 which price relates to which model there is nothing in the links that would give you this.
Usually there is a primarykey field that would be the link
IP IP Logged
atp2K3
Newbie
Newbie


Joined: 29 Dec 2011
Online Status: Offline
Posts: 10
Quote atp2K3 Replybullet Posted: 24 May 2012 at 4:01am

I only see the options linking by key or name on Crystal Report. So I linked them by name: make and type. Then select either inner join or left outer join but it produces more or less than records what I expect to have the data for my report because I need to loop the ordered records on the details section. Can you explain how to have a primary key that you mention? Thanks.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 May 2012 at 4:12am

it is not really adding a pk as much as relational databases generally use them so you can easily connect to related pieces of information that are store in 2 different tables. so in general, table 1 would have a field called something like

car_id which is just a number, but a unique number in that that table.

in table 2 there is also a field called the same thing.

if you join the two together table 1 would always match to table 2.

Sometimes you have to use a 3rd table. Say you have a table that has your model years. You might have to match your table1 to model years table on car_id. Inside the model year table there is another primary key that is makes a unique number per model per year (e.g. car_year_Id) and that is how you get the price by linking table 3 (year models) to table 2(prices) using the  car_year_Id field.

I am not saying your DB is made this way, just that it is a usual process and if yours is set this way you just need to figure out which PK to link to which tables to get what you want.



Edited by DBlank - 24 May 2012 at 4:13am
IP IP Logged
atp2K3
Newbie
Newbie


Joined: 29 Dec 2011
Online Status: Offline
Posts: 10
Quote atp2K3 Replybullet Posted: 24 May 2012 at 5:01am
Thanks for your hints. I am not designed the database relational model, just used the existed data tables. In fact, I must write store procedures that contain the UNION keywords with complex join tables and group by for aggregated calculation in order to manipulate data for each record to display on report. The issue is occured when having two duplicated rows with two fields in which the same data. I think to add another column in DataTable to distinct the duplicated issue. There are many records in the database, so if I add the flag column with value 1, 0 to link between datatables, my report will not display other record with NULL value. Is it anyway to match NULL data in Crystal Report. I found the solution by using Left Outer join in links to solve my issue. 

Edited by atp2K3 - 30 May 2012 at 4:29am
IP IP Logged
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