patternMinor

Different where clause based on SSRS parameter

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

ssrswheredifferentbasedclauseparameter

Problem

I have an SSRS report where a parameter can take 3 values. The select query is the same in all cases but the where clause needs to check different date fields based on the parameter value. Is this possible in the same query?

Query:

SELECT OrdNO, OrdType, customer, SalesRep, FundDate, DisbursementDate, Branch, SalesPrice 
FROM Orders

-- Case @ReportType=1, use FundDate
   where (FundDate >= @FromDate and FundDate = @FromDate and DisbursementDate = @FromDate and FundDate = @FromDate and DisbursementDate <= @ToDate)

Solution

Yes, something like the below.

SELECT * 
FROM DataTable
WHERE (@SearchField = 'CreateDate' AND CreateDate BETWEEN @StartDate AND @EndDate)
OR (@SearchField = 'ModifyDate' AND ModifyDate BETWEEN @StartDate AND @EndDate)

Or, to use the example WHERE clauses you specified, how about the below?

SELECT *
FROM DataTable
WHERE (
        @ReportType = 1
        AND FundDatte >= @FromDate
        AND FunDate = @FromDate
        AND DisbursementDate = @FromDate
                AND FunDate = @FromDate
                AND DisbursementDate <= @ToDate
                )
            )
        )

Code Snippets

SELECT * 
FROM DataTable
WHERE (@SearchField = 'CreateDate' AND CreateDate BETWEEN @StartDate AND @EndDate)
OR (@SearchField = 'ModifyDate' AND ModifyDate BETWEEN @StartDate AND @EndDate)

SELECT *
FROM DataTable
WHERE (
        @ReportType = 1
        AND FundDatte >= @FromDate
        AND FunDate <= @ToDate
        )
    OR (
        @ReportType = 2
        AND DisbursementDate >= @FromDate
        AND DisbursementDate <= @ToDate
        )
    OR (
        @ReportType = 3
        AND (
            (
                FundDatte >= @FromDate
                AND FunDate <= @ToDate
                )
            AND (
                DisbursementDate >= @FromDate
                AND DisbursementDate <= @ToDate
                )
            )
        )

Context

StackExchange Database Administrators Q#128825, answer score: 3

Revisions (0)

No revisions yet.