SSRS parameter in WHERE

Question

I thought this would be really simple but I can't get a way that works.

Basically I would like to make the report search by the entered parameters, IE in the WHERE clause.

For example, parameters in the below would be @ColumnName and @SearchTerm

SELECT
OITM.[ItemCode] AS [Item Code],
OITM.[ItemName] AS [Item Name],
OITM.[FrgnName] AS [Merchandise Type],
OITM.[OnHand] AS [In Stock],
OITM.[IsCommited] AS [Customer Reserved], 
OITM.[Onorder] AS [On Order],
OITW.[AvgPrice] AS [Cost]

FROM OITW
 INNER JOIN OITM ON OITM.[ItemCode] = OITW.[ItemCode]

WHERE
OITM.(@ColumnName) LIKE '%(@SearchTerm)%'

This must be possible? Many thanks.

score 2 · Accepted Answer · answered Jul 07 '16 at 17:09

I don't always like to encourage the use of dynamic SQL, but the following will do what you are trying to accomplish.

DECLARE @ColumnName varchar(10) = 'ItemName', @SearchTerm varchar(5) = 'pat', @sql varchar(1000) = ''

SELECT @sql = 'SELECT
OITM.[ItemCode] AS [Item Code],
OITM.[ItemName] AS [Item Name],
OITM.[FrgnName] AS [Merchandise Type],
OITM.[OnHand] AS [In Stock],
OITM.[IsCommited] AS [Customer Reserved], 
OITM.[Onorder] AS [On Order],
OITW.[AvgPrice] AS [Cost]

FROM OITW
 INNER JOIN OITM ON OITM.[ItemCode] = OITW.[ItemCode]

WHERE OITM.' + @ColumnName + ' LIKE ''%' + @SearchTerm + '%'''

EXEC (@sql)

The following is how I would really do this, but you may have reasons for not wanting to do this. Up to you.

SELECT
OITM.[ItemCode] AS [Item Code],
OITM.[ItemName] AS [Item Name],
OITM.[FrgnName] AS [Merchandise Type],
OITM.[OnHand] AS [In Stock],
OITM.[IsCommited] AS [Customer Reserved], 
OITM.[Onorder] AS [On Order],
OITW.[AvgPrice] AS [Cost]

FROM OITW
 INNER JOIN OITM ON OITM.[ItemCode] = OITW.[ItemCode]

WHERE (OITM.ItemName LIKE '%' + @SearchTerm + '%'
    OR OITM.FrgnName LIKE '%' + @SearchTerm + '%')...

Good luck!

I feel really silly for not considering an OR. Your 2nd suggestion is just better in every way. — coblenski, Jul 08 '16 at 08:12

score 1 · Answer 2 · answered Jul 07 '16 at 14:52

1

Try this:

SELECT
OITM.[ItemCode] AS [Item Code],
OITM.[ItemName] AS [Item Name],
OITM.[FrgnName] AS [Merchandise Type],
OITM.[OnHand] AS [In Stock],
OITM.[IsCommited] AS [Customer Reserved], 
OITM.[Onorder] AS [On Order],
OITW.[AvgPrice] AS [Cost]

FROM OITW
 INNER JOIN OITM ON OITM.[ItemCode] = OITW.[ItemCode]

WHERE
OITM.(@ColumnName) LIKE '%' + @SearchTerm + '%'

Note your parameter must be text type.

Let me know if this helps.

answered Jul 07 '16 at 14:52

alejandro zuleta

13,962
3
28
48

Query execution failed for dataset 'ItemCodes'. (rsErrorExecutingCommand) ---------------------------- An error has occurred during report processing. (rsProcessingAborted) – coblenski Jul 07 '16 at 15:03
do you have another parameter that holds the name of the column? – alejandro zuleta Jul 07 '16 at 15:06
No I don't think so. My query in the OP wasn't being accepted (obviously), but I copy-pasted yours as-is and it goes in no problem. The error occurs when I try to run it. I have defined the two parameters in the Paramaters folder above the Datasets folder and also in the Parameters tab on the dataset. – coblenski Jul 07 '16 at 15:11
I see you want to dynamically set the column in the WHERE clause. The pattern for this is compose a dynamyc string of the SQL code and then call it using `EXEC sp_executesql`. – alejandro zuleta Jul 07 '16 at 15:24

SSRS parameter in WHERE

2 Answers2