Reporting Services - How to define the WHERE clause dynamically ?

[davebarkshire]

We are about to start using Reporting Services which will be accessed from a C# ASP.net application. Many reports will have a different where clause for the same report depending upon who the user is and the circumstances. Therefore, I need to be able to send the report a WHERE clause ideally as a string. I can't use hard defined parameters because they will be on different fields depending on how I want to call the report. Can anyone tell me how this is done?

[Lost User]

You'd get more responses from the database-forum. I haven't used the Reporting Services yet, but I guess that they can use a function or sproc as a datasource. I usually use a construction that's comparable to this;

DECLARE @TEST AS TABLE
(FieldOne VARCHAR(10))
INSERT INTO @TEST VALUES('Hello')
INSERT INTO @TEST VALUES('World')

DECLARE @ArgumentForFieldOne AS VARCHAR(10)
SET @ArgumentForFieldOne = 'He%'

SELECT *
FROM @TEST
WHERE (
(@ArgumentForFieldOne IS NOT NULL AND FieldOne LIKE(@ArgumentForFieldOne))
OR (@ArgumentForFieldOne IS NULL)
)

You'd set @ArgumentForFieldOne to NULL if you don't want to search that particular field. This has the disadvantage that you can't pass NULL as a parameter to search for though. You can bypass this problem by introducing a separate bit that indicates whether you want to filter the field or not;

DECLARE @TEST AS TABLE
(FieldOne VARCHAR(10))
INSERT INTO @TEST VALUES('Hello')
INSERT INTO @TEST VALUES('World')

DECLARE @DoFilterFieldOne AS BIT
SET @DoFilterFieldOne = 1
DECLARE @ArgumentForFieldOne AS VARCHAR(10)
SET @ArgumentForFieldOne = 'He%'

SELECT *
FROM @TEST
WHERE (
(@DoFilterFieldOne = 1 AND FieldOne LIKE(@ArgumentForFieldOne))
OR (@DoFilterFieldOne = 0)
)

You can wrap these constructions in a table-valued function, but a sproc where you pass @DoFilterFieldOne and @ArgumentForFieldOne is also an option.

SQL lingua venusta :)