Getting Value From Another Table with a Foreign Key

Question

I've built a report with the report wizard in C#, and I can see my report in ReportViewer. My problem is that I have 2 tables that are related. The value of a column in the first table is a foreign key on the other table. All I see in that column is just the foreign key. I'd like to see the corresponding value from the other table.

How can I see value of the column from the second table?

Answer 1

Drag the item from the table where it's the Foreign Key, not it's native location.

Answer 2

sounds like you probably need a drill down report Create Basic Drilldown Report

Answer 3

Regarding the control that renders the result... Does it have its columns bound manually / is it set to auto-bind?

Answer 4

Change your query to one that involves the fields from the appropriate tables.

SELECT t1.*, t2.value
FROM table1 t1 
   JOIN table2 t2 ON t1.t2id = t2.id

But if you can't, because the data sources are separate, then you'll be wanting another option.

Assuming you have two datasets, from separate data sources.

Put a table in your report to show information from DataSet1 (or whatever it's called). Then put a rectangle in place of one of your textboxes, and then put a table in there, which you attach to DataSet2. Then put a filter on this table, so that it only shows records from DataSet2 which correspond to the appropriate value in DataSet1.

Or, wait for SQL Server 2008 R2 (currently in CTP), which provides a Lookup function for exactly this purpose.

Rob

Answer 5

You should just join on the foreign key columns:

SELECT
    a.*,b.YourNeededColumnHere
    FROM TableA            a
        INNER JOIN TableB  b ON a.columnX=b.columnX

However, if you are having problems doing this in reporting services, just create a view:

CREATE VIEW CombinedAB
AS 

SELECT
    a.*,b.YourNeededColumnHere
    FROM TableA            a
        INNER JOIN TableB  b ON a.columnX=b.columnX

GO

you should now be able to run your report off the CombinedAB view as:

SELECT
    * 
    FROM CombinedAB
    WHERE ...your conditions here...