• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45
  • Last Modified:

SSRS 2014 - How to use parameter to set parameter from SQL Query?

Good Day Experts!

I am working on a new assignment that involves SSRS.  I am new to SSRS so just trying to make my way though a training manual and online tutorials.

I have been creating queries to provide data for User needs.  For the future, the queries needed to take in a parameter.  For "proof of concept" data extraction I would just set the parameter to a hard-coded value.  Works great and I get desired results to the Management Studio output window.

Now, I have been tasked with using that query and making it into a report.  I did not have much trouble with that.  However,  I am having difficulty with the parameter coding instead of using that hard-coded value.  I have added a parameter in the report.  It works fine and prompts me for a value but does not do anything at this point.  

Next, I need to take that parameter value and pass it to the parameter at the top of my query instead of using that hard-coded value.  Unfortunately, I cannot figure out how to do that.

Here is the top of my code. Can I set @calendar to the parameter result that I added to the report?

DECLARE @calendar int;
DECLARE @term varchar(3);
DECLARE @termArray varchar(25);
DECLARE @startDate as date;
DECLARE @endDate as date;
SET @calendar = '16';
SET @termArray = 'Q1,Q2,Q3,Q4';

Thanks,
jimbo99999
0
Jimbo99999
Asked:
Jimbo99999
  • 3
  • 3
1 Solution
 
Russell FoxDatabase DeveloperCommented:
Welcome to SSRS! You don't need the DECLARE statements at the top of your query in the report: SSRS knows how to pass them from the report prompter to the query. Generally, create your query like you did in Management Studio, by declaring your parameters and passing in test values, but once it looks good you can copy the query below the DECLARE section and paste it into your SSRS report. SSRS will auto-generate report parameters for you.

Note that the multi-valued parameter, @termArray, will need some special love. Your query can just say...
SELECT 'foo'
FROM bar
WHERE [Term] IN (@termArray)

Open in new window

...but in SSRS you'll need to double-click that param and set it to allow multiple values.
0
 
Jimbo99999Author Commented:
Hello there.  Thank you for our response.  Some of those are variables to hold results of queries(built from term selection by the User) to build a string to use in the query to get data.  Is it not possible to do that?
0
 
Russell FoxDatabase DeveloperCommented:
It's just not necessary. Say I have a [Comments] table and the user wants to get a report of comments from a specific user on a specific day. The report in SSRS will have boxes where the user can enter a user's name and a date. My query in the report definition just looks like this:
SELECT *
FROM dbo.Comment
WHERE EnteredBy = @EnteredBy
	AND DateCreated = @DateCreated

Open in new window

NOT like this:
DECLARE @EnteredBy VARCHAR(25)
	, @DateCreated DATE

SELECT *
FROM dbo.Comment
WHERE EnteredBy = @EnteredBy
	AND DateCreated = @DateCreated

Open in new window

Because the parameters are defined in the report definition, you don't need to re-define them in your query. For something like the @termArray parameter, you would have a query that pulls a list of possible values, set the source value of that parameter to that query, and change the parameter to a multi-select box. Then when the user runs the report they'll be presented with a dropdown with a list, "Q1", "Q2", etc., and a box next to each so they can select one or more values. Now your query would look something like...
SELECT *
FROM dbo.Comment
WHERE EnteredBy = @EnteredBy
	AND DateCreated = @DateCreated
	AND [ReportQuarter] IN(@termArray)

Open in new window

Hopefully that makes sense. Are you using ReportBuilder or Visual Studio/BIDS? I haven't used ReportBuilder, so you may not have the same flexibility. Unfortunately, I'm going to be afk for a few days, but post any other questions and either I'll look at it when I get back or another expert will chime in. In the meantime, here's a tutorial from MS, in case it helps!

add-a-parameter-to-your-report
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Jimbo99999Author Commented:
Hello.  Back again at the contract position working with SSRS.  

I am able to get reports working using a parameter like you have notated.  However, the variables that are causing me troubles are not for User input.  They are to hold and do work within my query.  I am trying to avoid converting this to a StoredProcedure.  

For the variables that are NOT user input, I put a second @ so @@ in front of the name.  This works great in QueryDesigner and I get the right output! But, unfortunately, when I try to Preview it I get an error...The definition  of the report is invalid.  The number of defined parameters is not equal to the number of cell definitions in the parameter panel.
0
 
Russell FoxDatabase DeveloperCommented:
Okay, got it. You're doing some preliminary work that you want embedded in your report query. Not best practices, but I've been there! Here's a short example:

--	Delcare internally-used parameter:
DECLARE @ClientStartDate DATETIME;

--	Set a value for that parameter:
SELECT @ClientStartDate = MAX(StartDate)
FROM tblClients;

--	Run the actual query:
SELECT *
FROM tblClients
WHERE StartDate = @ClientStartDate;

Open in new window


SSRS doesn't really like this because when it sees a parameter, it wants to create one for user input. But there are a couple things you can do. First, you can move the parameter calculations into the body of the query, so in the example above you could instead have...
SELECT *
FROM tblClients
WHERE StartDate = (SELECT MAX(StartDate) FROM tblClients)

Open in new window

A more common approach would be to create another report data set and set your parameter values to the query output. For example, I have a lot of financial reports that run daily for yesterday's numbers, but on Monday I need to include all Friday/Saturday/Sunday activity. For this I create one data set with a StartDate and an EndDate, where the EndDate is always midnight last night, but the StartDate is the beginning of yesterday unless today is Monday, then make it the beginning of Friday. My main report query looks for an InvoiceDate BETWEEN @StartDate AND @EndDate. Now SSRS creates two parameters, @StartDate and @EndDate, but instead of making them user parameters I set their default values to the original date query:
Set default valuesThis way your main query stays clean and you can run any necessary calculations in another data set. You can also perform your extra calculations in a CTE at the top of your main query:
; WITH TopStartDate
AS (
	SELECT MaxStart = MAX(StartDate)
	FROM tblClients
	)
SELECT *
FROM tblClients cl
	JOIN TopStartDate ts
		ON cl.StartDate = ts.MaxStart

Open in new window

0
 
Jimbo99999Author Commented:
Thank you for the help.  I was on my into the office and I was wondering if doing that was possible. Perfect, I have an example to work from.  

Thanks again,
jimbo99999
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now