0

I'm having trouble creating a dataset in Reporting Services 2008. Here's the query I want to use (generic-ized for public consumption):

Declare @PersonID as int
set @PersonID = (select top 1 personID from People where name = @PersonName)

select (some columns)
from [otherTable]
where personID = @PersonID

I only have one input parameter: @PersonName. However, Studio 2008 seems to think @PersonID also needs a value in the "Define Query Parameters" window. So when I try to only enter a value for @PersonName, it gives me the "This variable has already been defined".

I'm passing @PersonName because I need to display a human-readable list of the people to select from a dropdown as the only user-facing parameter to the report. I realize I could do a subquery and forget the variable in the above example, but my real query is much larger/uglier, and I can't exactly add indices to the data tables, so I'd like to limit the table scans.

minameismud
  • 91
  • 1
  • 9

3 Answers3

2

You can create a new dataset to use with your parameter.

First, a new dataset with a query of 

SELECT PersonID, PersonName FROM People

Then, on your report parameter (I'm changing the name to Person for clarity), in the Available Values section, switch it to "Get values from a query". Select your new dataset, and set the Value field to PersonID and the Label field to PersonName.

Finally, set your main dataset's @PersonID parameter to be the report parameter [@Person].

This way, you can show the list, but still work off of the ID. No need for the PersonName report parameter.

Dave Simione
  • 1,441
  • 2
  • 21
  • 31
0
Declare @PersonName char(20) 

select (some columns)
from  [otherTable] 
join  [People]
  on  [otherTable].[personID] = [People].[personID]
where [People].[name] = @PersonName

Give it a try before you assume it is going have performance issues.

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

Why not turn the query into a stored procedure? From my experience, stored procs provide better performance (watch out for parameter sniffing though) as well as reusability.

edit:

And it would def eliminate your issue.

Josh Jay
  • 1,240
  • 2
  • 14
  • 26