[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


My reporting services data set SQL Query is not prompting me for the input parameter

Posted on 2009-04-28
Medium Priority
Last Modified: 2012-05-06
I have a reporting services report. Associated with that report is a data set, and associated with the data set is the SQL query below. Now if I go into the Reporting Services Business Development Studio and open up my project there are three tabs displayed "Data","Layout","Preview". If I select the "Data Tab" I can view my query below. The problem is that when I run the query it does not prompt me for the value that I want to be passed into the report? In my case "inputID"

Why am I not being prompted to enter in this input parameter? Is something wrong with the way I declared "@inputID" in my query below, or do  I need to do some other step, like open the "Data Set" dialog box by pressin "..." on the toolbar and then setting or creating something?

Please help.

-- Text SQL Statement associated with my data set. This data returned from this query is displayed on the report.
DECLARE @inputID VARCHAR(10) -- This will be passed in from aspx/c#

DECLARE @EmployeeCount int -- Number of Customers we are reporting on

SELECT  @EmployeeCount= COUNT(DISTINCT(intUserID)) from tblNoticeResponse as theTotal where NoticeID IS NOT NULL AND (NoticeID=CAST(@inputID AS INT))
PRINT @EmployeeCount
      convert(varchar(30), DATEADD(hh, DATEPART(hh, [ResponseDateTime]),
              CAST(FLOOR(CAST([ResponseDateTime] AS FLOAT)) AS DATETIME)),100) dates,
      ceiling((CAST(COUNT(intUserID) AS FLOAT)/@EmployeeCount) * 100 ) theCount
       tblNoticeResponse where ResponseDateTime IS NOT NULL
      convert(varchar(30), DATEADD(hh, DATEPART(hh, [ResponseDateTime]),
              CAST(FLOOR(CAST([ResponseDateTime] AS FLOAT)) AS DATETIME)),100)
Question by:brgdotnet
LVL 25

Accepted Solution

reb73 earned 1080 total points
ID: 24252571
Remove the Declare statements and use report parameters to indicate/configure the parameter variables..

See link below -

LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 120 total points
ID: 24253142
as reb said, do not declare the input variables in the text of the sql.   Go to Report in the Menu and Report Parameters.  Add a parameter there and define it.  Leave off the @ so your @inputID would just be inputID, etc.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question