Solved

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

Posted on 2011-02-15
4
690 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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