Looking up a value in another datatable when creating a report viewer report

Question

I have a local report that I created in my application. It displays the contents of a table in my access database called "History". One of the columns is "ID" and it refers to the primary key in another table "Employees".

My report shows the employee's ID when it is refreshed but of course I want it to display the employee's name instead.

In short, I need to be able to perform a lookup on another table in my rldc file. How can I go about that?

Why don't you just add a join to your query, and return all of the results that you need? — James Johnson, Aug 09 '11 at 19:52
I have my dataset for the Employees table here in my rldc file but how do I replace the EmployeeID field in the History table report with the EmployeeName from the Employee table. Thanks in advance, I hope I'm making sense here :) — ChrisO, Aug 11 '11 at 00:51
It's like @James say.. Add a join in query to fill the dataset so that you have the Name of your employee on all rows — Martin, Aug 11 '11 at 19:31
Thank you, I created a new dataset and now I can drag the Name as a field on the report designer. However, when I generate the report, the name field is blank but all of the history fields are present. The query I have was auto generated by visual studio. Mind taking a look? `SELECT Agents.Name, History.AgentID, History.StatsDate, History.ActivityTime, History.TimeWorking, History.WeightedTotal FROM (Agents INNER JOIN History ON Agents.ID = History.AgentID)` I should also mention that the Employees table is now called Agents. — ChrisO, Aug 12 '11 at 02:31
Figured this out now, thanks! The query was setup fine, I just needed to switch the bindingsource of the report to my new dataset or something. Still a beginner when it comes to this. At least it works. — ChrisO, Aug 12 '11 at 18:00

score 1 · Answer 1 · answered Aug 11 '11 at 19:44

Here is an example of how to join your employee and history tables with SQL. My Access is rusty, so the syntax might not be perfect, but this should demonstrate the concept. Obviously, I made up the column names, so you will have to change the columns to whatever you need.

SELECT EmployeeID,
       EmployeeName,
       TimeIn,
       TimeOut
FROM   Employee Emp
       INNER JOIN History History
          ON Emp.EmployeeID = History.EmployeeID

score 1 · Accepted Answer · answered Aug 11 '11 at 19:45

I assume you fetch data from the database through a dataadapter and a typed dataset. something like this?

        TestDataSetTableAdapters.CategoryTableAdapter ca = new TestDataSetTableAdapters.CategoryTableAdapter();
        this.ds1 = new TestDataSet();
        ca.Fill(this.ds1.Category);

Then you go to the dataset and modify the query in your tableadapter to something like this

select h.*,e.EmployeeName from history h inner join Employees e on e.ID = h.UserID

Then the datset will be modified to include the column EmployeeName on all rows in the History table. Then the column "employeename" is directly available in yyour report

Looking up a value in another datatable when creating a report viewer report

2 Answers2