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

Posted on 2009-04-28
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

    Remove the Declare statements and use report parameters to indicate/configure the parameter variables..

    See link below -
    LVL 26

    Assisted Solution

    by:Chris Luttrell
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
    Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now