SSRS - Ignore SQL Variables in my query

Question

I have a query that works very well in SQL. When I try to bring this into SSRS, the report asks for 4 parameters. Two of the parameters/variables are actually based on the other Two parameters as such:

DECLARE @Q int       --SET @Q = 1 -- Quarter 
DECLARE @Year int    --SET @Year = 2013

DECLARE @STARTDATE varchar(10)
SELECT @STARTDATE = D FROM (
    select case @Q
        when 1 then '1/1/' + convert(varchar(10),@Year)
        when 2 then '4/1/' + convert(varchar(10),@Year)
        when 3 then '7/1/' + convert(varchar(10),@Year)
        when 4 then '10/1/' + convert(varchar(10),@Year)
    end as D
    ) sd

DECLARE @ENDDATE varchar(10)
SELECT @ENDDATE = D FROM (
    select case @Q
        when 1 then '3/31/' + convert(varchar(10),@Year)
        when 2 then '6/30/' + convert(varchar(10),@Year)
        when 3 then '9/30/' + convert(varchar(10),@Year)
        when 4 then '12/31/' + convert(varchar(10),@Year)
    end as D
    ) ed

--(ADDITIONAL SQL CONTINUES USING ALL 4 PARAMETERS) ...

How can I get SSRS to only ask for the first two parameters (@Q, @Year) and ignore the @StartDate and @EndDate as those are calculated within the query?

Answer 1

Parameters are added for the query of an SSRS data set for any variables in the query that aren't also declared in the query.

But once they are added, they aren't always automatically removed, so you may need to manually remove them from your dataset query.

For example, this query will only create parameters for @ParamOne:

DECLARE @StartDate, @EndDate DATETIME

SET @StartDate = 'January 1, 2013'
SET @EndDate = 'February 1, 2013'

SELECT
   UserName,
   Action,
   DateOccurred
FROM
   myTable
WHERE
   DateOccurred BETWEEN @StartDate AND @EndDate
AND UserName = @ParamOne

But SSRS can be picky about capitalization. Make sure they match between your declaration and your uses of the variable.