CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2012
Scotch
Scotch

Reputation: 3226

Report Parameter not working when passing column name

The purpose of my report is to check for nulls with given column name being the parameter.

The issue is that it's not passing my parameter in from the report to the dataset. I have a report parameter called @employeeField. In the Parameters section of my Dataset, I have ParameterName set to @employeeField and value set to [@employeeField].

Example query for my dataset:

SELECT top 100 employeePK from Employees WHERE @employeeField is null

This is also what it evaluates to at runtime (when I set a textbox to =DataSets!dsDetails.CommandText ) Also, if I enter an invalid column name as a parameter, the report executes as normal. I would have thought it would throw an error, but it does not.

Thanks for any input -- I can edit/comment if more clarification is necessary

Upvotes: 0

Views: 521

Answers (2)

Scotch
Scotch

Reputation: 3226

Figured out a solution -- Not positive if it's the correct one though.

I think it's necessary to use an expression if one is trying to dynamically build their query.

Regular parameter passing will work when you are passing filters such as WHERE Age > @ageParam

If doing something like I was trying (in which the value is the same, but the column changes), you should build an expression as shown in screenshot below.

The value of my expression ended up being

="Select * from Employee WHERE " & Parameters!employeeField.Value & "= 'foo' "

Expression Button

Upvotes: 0

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

It is not possible to dynamically change the query using a fieldname as parameter. To achive what you want, you will have to build a WHERE clause with multiple alternative (OR) criteria:

SELECT top 100 employeePK from Employees 
WHERE (@employeeField = 'Field1' AND Field1 IS NULL)
   OR (@employeeField = 'Field2' AND Field2 IS NULL)
   OR (@employeeField = 'Field3' AND Field3 IS NULL)
   -- ... and so on...

Upvotes: 1

Related Questions