Solved

Parameter in Query (SQL Server Report Builder) not working?

Posted on 2011-02-15
4
688 Views
Last Modified: 2012-05-11
I'm running a rather simple query with a parameter, so that in a report, the user can enter the year and a chart will display data from said year.
If I hard-code in the year, like this:

SELECT 
"All Parts" AS `Product Part Number`, 
Month(`DateReworked`) AS Mo, 
Year(`Datereworked`) AS Yr,
Avg(tblReworkRecords.DaysToRework) AS AvgOfElapsedDays

FROM 
(`Product part number List` 
INNER JOIN `Shop Order List` 
ON `Product part number List`.`Product Part Number` = `Shop Order List`.`Product Part Number`) 
INNER JOIN tblReworkRecords 
ON `Shop Order List`.`Shop Order Number` = tblReworkRecords.ShopOrderNumber

WHERE 
((Year(tblReworkRecords.Datereworked) = 2010)) 
AND 
(((tblReworkRecords.DateReworked)>'1/1/2004'))

GROUP BY Month(`DateReworked`)

HAVING (((Avg(tblReworkRecords.DaysToRework)) Is Not Null))

Order BY `Product Part Number`, Mo, AvgOfElapsedDays

Open in new window


I get exactly what I am looking for for 2010 (12 rows, 1 for each month)

However if I used the EXACT same code, only where it says ((Year(tblReworkRecords.Datereworked) = 2010)), I change it to ((Year(tblReworkRecords.Datereworked) = @Year)) , and run the query (it prompts me to enter a year for the parameter, and I type in 2010), I get absolutely no rows of data, none at all.

Does anyone know what I am doing wrong or why I am getting no data?

Thank you in advance.
0
Comment
Question by:AAI_Development
  • 3
4 Comments
 
LVL 5

Expert Comment

by:robertg34
ID: 34898962
Double check your parameters and make sure @Year is mapped correctly to that input box.  Also make sure the parameter is data-typed correctly.  I'm assuming its a number field.  

It might be helpful to put a default value in the parameter as well.  
0
 

Author Comment

by:AAI_Development
ID: 34899440
Tried every data-type, tried a default value....nothing...
0
 

Accepted Solution

by:
AAI_Development earned 0 total points
ID: 34899769
Figured it out...apparently you cannot do this if you are using ODBC, which I am.
0
 

Author Closing Comment

by:AAI_Development
ID: 34936437
Was correct, but I have non-compatable data source.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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