Solved

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

Posted on 2011-02-15
4
691 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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