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

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
SELECT
      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
 FROM
       tblNoticeResponse where ResponseDateTime IS NOT NULL
 GROUP BY
      convert(varchar(30), DATEADD(hh, DATEPART(hh, [ResponseDateTime]),
              CAST(FLOOR(CAST([ResponseDateTime] AS FLOAT)) AS DATETIME)),100)
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
reb73Commented:
Remove the Declare statements and use report parameters to indicate/configure the parameter variables..

See link below -

http://www.sql-server-performance.com/articles/reporting/report_parameters_p1.aspx
0
 
Chris LuttrellSenior Database ArchitectCommented:
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.
HTH
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.

All Courses

From novice to tech pro — start learning today.