6

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?

Improve this question

6 Answers 6

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Just curious to know whether the static values to the variable make SSRS ignore it as parameters ?
No, the declaration. The mapping could be SELECT @StartDate = periodStart, @EndDate = periodEnd FROM periods p WHERE p.ID = @PeriodID and @PeriodID can be the only SSRS parameter handed in. (But @StartDate and @EndDate must be delcared in the query.)
I have to agree with praveen. My query DECLARE's the variables and SSRS still thinks of them as parameters.
Does the case match between the declaration and all uses, and have you removed the parameters from the dataset after they were created? (Not just removing them from the report parameters list but also from the Parameters tab on the dataset properties)
The case does match. When I remove them from the parameters list and from the dataset, I get an error has occurred during report processing.
|
2

Whenever You add a select statement in SSRS it will by default generally add your parameters for you if you paste in a select statement such as:

select thing 
from table 
where item = @Parm1

It then should display on the report designer screen under 'Report Data' the folder 'Parameters'. If that parameter with the value is not there it needs to be added for your main body to work. Parameters are chosen differently in SSRS then in SQL. You define them in their own section.

If you want it to ignore two parameters why do you have to include them? That seems a little counter intuitive. You have two options:

  1. Where the variables are declared set a 'default value' of the parameter to a static value.

  2. Set the variable to 'allow nulls' and handle a null reference.

EDIT (With CTE below):

In SSRS You don't do this (generally speaking, sometimes you may want a table variable and that is fine or other statics):

Declare @Var int;

select thing
from table
where item = @Var

You just do this:

select thing
from table
where item = @Var

Then you handle the 'Parameter' as it's own property with a type and deterministic outcomes.

I would just do this in SSRS's 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

Ensure that you see parameters listed for 'Q' and 'Year' exist under 'Parameters' folder and set them to integers. When a user runs the report it will ask for those values and they will determine dataset as long as they are legitimate values in the scope. EG: they are not non valid values which will return nulls.

Improve this answer

3 Comments

How do you then refer to StartDate and EndDate in later queries?
You need to ask yourself two things: 1. Do I really need a variable if I can get the information itself 2. Does the variable need to be seen or is it excess. See updated example using a CTE....
I would also add that treating SSRS like a complex SQL Management Studio expression with multiple variables is more trouble than it is worth. The RDL langauge (SSRS's xml structure) by nature is made to think of parameters as inputs. You can make hidden and deterministic ones but a lot you can accomplish without setting too many or else create a procedure on SQL Server that does all the comlex internal logic and just have a parameter or two needed to pass to it.
2

Keep the parameters in the report, but give them a default value and set them as Hidden.

When the query runs, the code will override the @STARTDATE and @ENDDATE values so the defaults won't matter.

Improve this answer

Comments

0

Are you putting your query into a stored proc? (you should if you aren't, it's just good practice). If you using a proc, you can have just two input variables, and then declare as many other internal variables as you need.

Improve this answer

9 Comments

I wasn't planning on creating a stored proc, but will if I need to.
@ajspacemanspiff: it is the best practice. Especially when you want to start searching your database for things (such as does anything use a certain table?) its much easier to hunt these things down if it is on the DB side.
Leaving the query in the report is often preferable: the report can then be independently promoted from test to production, or a test revision of the report can run against a production db. I tend to try to keep the SQL in the report, but I know a case can be made for either.
@JamieF: Yes, you can gain an easier push to a new enivorment, but it is a much better practice in a proc. It is much easier to track issues that my arise on the DB side (try having to go thru a few database cleanups and you will understand what I mean).
I still disagree. In my experience, this makes database cleanups even harder: you now have to track what stored procedures are being used by which reports.
|
0

Wrap the procedure into a nvarchar(max) variable and then execute it:

declare @statement nvarchar(max)
set @statement='....
'

exec sys.execsql(@statement)
Improve this answer

Comments

-1

I don't think that is possible because SSRS creates a dynamic query and converts all the variables to parameters .So AFAIK the only option is to wrap the query in a stored procedure

Improve this answer

3 Comments

Sorry, but I'm sure that you are wrong. I regularly have variables in my SQL queries in SSRS that are not Stored Procs.
I don't think so that is possible when u use @ symbol in your query .Can you please show me one such query ?
@praveen: I'll give you a vote back. Should be no reason why a reports query needs to be buried into the report file. Its such a better practice to put it in a proc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.