SSRS - ignore SQL variables in my query

Question

I have a query that works very well in SQL. When I try to cast this to 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 make SSRS request only the first two parameters ( @Q , @Year ) and ignore @StartDate and @EndDate , how are they calculated in the request?

Answer 1

Whenever you add a select statement to SSRS, it by default usually adds your options for you if you insert a select statement, for example:

 select thing from table where item = @Parm1 

Then it should display the “Parameters” folder on the report designer screen in the “Report Data” section. If this parameter with a value does not exist, it must be added for your main body to work. Parameters are selected differently in SSRS, then in SQL. You define them in your section.

If you want it to ignore two parameters, why do you need to enable them? It seems a bit counter-intuitive. You have two options:

• If variables are declared, set the default value for the static value.

• Set the variable for 'allow nulls' and handle the null reference.

EDIT (with CTE below):

In SSRS, you do not do this (generally speaking, sometimes you may need a table variable, and this is normal or other static):

 Declare @Var int; select thing from table where item = @Var 

You just do this:

 select thing from table where item = @Var 

Then you process the Parameter parameter, because it has a property with a type and deterministic results.

I would just do it in SSRS Dataset:

 with dates as ( 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 StartDate , 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 EndDate ) select things from mainbodytable, dates -- CTE reference where date between StartDate and EndDate -- referenced from CTE above 

Make sure that you see the parameters listed for “Q” and “Year” in the “Parameters” folder and set them to integers. When the user runs the report, he requests these values, and they will determine the data set if they are valid values ​​in the field. EG: they are not invalid values ​​that return null values.

Answer 2

Added parameters for querying the SSRS dataset for any variables in the query that are also not declared in the query.

But after adding them, they are not always automatically deleted, so you may need to manually remove them from the dataset request.

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 use of the variable.

Answer 3

Save the parameters in the report, but give them a default value and set them as hidden.

When the query is executed, the code will override the @STARTDATE and @ENDDATE , so the default values ​​will not matter.

Answer 4

Do you put your request in a saved process? (you should, if you do not, this is just good practice). If you use proc, you can only have two input variables, and then declare as many internal internal variables as you need.

Answer 5

Wrap the procedure in the variable nvarchar(max) , and then execute it:

 declare @statement nvarchar(max) set @statement='.... ' exec sys.execsql(@statement) 

Answer 6

I do not think this is possible because SSRS creates a dynamic request and converts all the variables into parameters. So AFAIK, the only option is to wrap the request in a stored procedure