asked on

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

Russell Fox

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.

Jimbo99999

ASKER

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?

Russell Fox

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

Jimbo99999

ASKER

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.

ASKER CERTIFIED SOLUTION

Russell Fox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jimbo99999

ASKER

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