0

I'm using SQL Server Report Builder 3.0 to create a report.

I have two tables and I want to look up a value in one table and then use that to select a value from the second table. Example as follows:

Table : Staff

| staff_id | Surname |

| -------- | ------- |

| 1 | Smith |

| 2 | Jones |

Table: Courses

| Teacher | Course |

| -------- | ------- |

| 1 | French |

| 2 | German |

Dataset1: SELECT staff_id FROM staff WHERE surname='Smith'

Dataset2: SELECT course FROM courses WHERE teacher = {staff_id from Dataset1}

But I can't figure out how to pass the value from Dataset1 to the query in Dataset2

I've tried getting the value into a Variable called teacher_id using a lookup against the other dataset and then the query for Dataset2 becomes

SELECT course FROM courses WHERE teacher = @teacher_id

but I get the error

The expression used for the calculated field 'teacher_id' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Is it possible to do what I need to?

Many thanks

Edit: Apologies - I can't seem to format the tables correctly, but they are just 2 cols by 2 rows so should be easily understood

Potatan
  • 13
  • 8

2 Answers2

0

Can't you simply do both of these things in the database and then return the resulting dataset in a single view?

Luke Briner
  • 23
  • 3
  • Thanks but it's not my database - as in, it's a bought product and we cannot add tables or views. The above example is just to simplify the issue – Potatan Jul 08 '22 at 14:28
0

I've figured it out. First I turned on Remote Errors in the SSMS console for the SSRS reporting server, via Properties -> Advanced -> Security -> EnableRemoteErrors

This allowed me to see the error message details mentioning "Must declare the scalar variable ..."

So instead of trying to reference a variable from another dataset, I was able to declare that variable within the actual second dataset SQL query. Then I could pull the value I needed by setting it in a nested Select prior to the rest of the query.

My example is a little off now, but if I had set @staff_id in report parameters, then the second query would be something like

=========

Declare @CourseTeacher as INT

Set @CourseTeacher = (select teacher from courses where teacher_id = @staff_id)

SELECT course FROM courses WHERE teacher = @CourseTeacher

=========

Potatan
  • 13
  • 8