CODEWITHSUNDEEP
sql-serverreporting-servicesparameters
Dave Swersky
Dave Swersky

Reputation: 34820

SQL Reporting Services Report Parameters

I'm rebuilding an application that allows a user to generate a report based on a little mini-query. Do SSRS report parameters support selection of the comparison predicate? My users need to be able to search where a given attribute equals, does not equal, is greater than, etc.

Upvotes: 0

Views: 167

Answers (3)

Alan Schofield
Alan Schofield

Reputation: 21703

Another option would be dynamic SQL and actually use the operator parameter directly to build the SQL statement.

DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT * FROM [Table] WHERE [Attribute] ' + @operator + @value
EXEC (@SQL)

Upvotes: 0

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107606

Not exactly, but I think you could replicate it, verbose as it may be. To start, you would have a parameter that specifies the comparison operator (explicit list or a query to available options). Then you would have to use an ugly set of OR checks in your query:

SELECT
    *
FROM
    [Table]
WHERE
    (@Operator IS NULL) /* All rows if no operator, or some default condition */
    OR
    (@Operator = '=' AND [Field] = [Value])
    OR
    (@Operator = '<' AND [Field] < [Value])
    OR
    (@Operator = '>' AND [Field] > [Value])

Upvotes: 1

StevenWhite
StevenWhite

Reputation: 6034

Not directly, but you can certainly accommodate that.

First, add a parameter with the comparison options. Let's call it @Operator.

Next, add conditions to your query WHERE clause to handle the situations. Something like:

WHERE ((@Operator = '=' and Col1 = @Parameter) OR (@Operator = '!=' and Col1 != @Parameter) ...)

Upvotes: 0

Related Questions