I'm using SSRS 2008 and I have a report with a subquery that contains a parameter, @LocationNo. what I'm trying to do is allow a user to enter a Location Number, which is a five digit integer, and have the sub query send a value called CustomerKey back to the outer query. If I hard code a value into the subquery this works fine. When I put this into a report and try to create a parameter the report is blank. Example code is below: SELECT X , Y , Z FROM SomeTable ST JOIN SomeOtherTable SOT . . . WHERE SOT.Customer_KEY IN SELECT c.Customer_Key FROM Customers c WHERE c.Number IN( SELECT * FROM OPENQUERY(DATABASE2, 'SELECT TOP 1 cu.Number FROM CUSTOMER CU INNER JOIN ATable A on A.Number = cu.Number WHERE SUBSTRING (cu.CustomerID, 3,5) = ''@LocationNo''))) What am I doing wrong? There has to be a way to create a parameter for LocationNo.Lee Markum

Hi, Did you try to define the parameter in your dataset? If you put the code above in your dataset's query designer, when you run your report you have to see a blank page with a textbox above the query which is waiting for you to type a LocationNo and run the report with the value you entered. Cheers Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

SoheilBakhshi, Yes. I can create the parameter and I get an empty field into which I can type data. However, the report comes back blank when I know there is data to return because when I hard code a value then data is returned in SSMS.Lee Markum

Hmm, It may occur because of your groupings. Please create a new report, just for test, and place your query including the parameter and see the results. Please share the output with us. CheersPlease Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

HI Lee ! You may get the desired output using below query; SELECT X , Y , Z FROM SomeTable ST JOIN SomeOtherTable SOT . . . WHERE SOT.Customer_KEY IN SELECT c.Customer_Key FROM Customers c WHERE c.Number IN( SELECT * FROM OPENQUERY(DATABASE2, 'SELECT TOP 1 cu.Number FROM CUSTOMER CU INNER JOIN ATable A on A.Number = cu.Number WHERE SUBSTRING (cu.CustomerID, 3,5) = ''Parameters!LocationNo.Value''))) The issue is due to openquery which you are using. Please let me know if this doesnt work for you. Hope I have answered you correctly. Thanks, Hasham Niaz

Hasham, When I do that I get a drop down for LocationNo that is empty. I don't even want a drop down at all. I just want the end user to be able to enter a location number value and if it matches something in the database then the Customer_Key value is passed to the outer query. Lee Markum

Hi Lee ! You need to make sure parameter "LocationNo" Available Value and Default Value is set to "None", and DataType is set to Text and these 3 checkboxes are un-selected in parameter properties page; 1) Allow Blank Value 2) Allow Null Value 3) Allow Multiple Value Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

The report runs but it still comes back blank.Lee Markum

Did you check the effect of grouping in your report (create a new simple test report without grouping but including your parameter)? Please share the results.Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

Shoei, There isn't any grouping in my initial query. Lee Markum

Hi Lee ! Can you try to test one more thing; Try to set below expression in some textbox to see what value is passing to the query; =Parameters!LocationNo.Value And see if it brings the correct value, or its not populating with correct value. You need to figure it out what value is being passed to the query. Please let me know if this helps. Hopefully i have answered you correctly. Thanks, Hasham Niaz

Hmm, In this case I think it is better to do what Hasham suggested. Share the results with us. ThanksPlease Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

Hi There Thanks for your posting. I think if you are using OPENQUERY you cannot pass the parameter like we do normally. To work around this issue you have to use dynamic SQL. Please have a look on these threads that might help you http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/4d22b6eb-c755-426d-abdc-73a55267fca6 http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/310d11bb-ce49-4da1-9253-2df49d6c6027 http://support.microsoft.com/kb/314520 If you have any query please let me know Many thanks Syed Qazafi Anjum Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Based on Syed's reply below containing the KB article on passing parameters to a linked server query, I am going to need to rewrite a good part of my query first before continuing with SSRS work. Lee Markum

Syed, I think your answer is what I need to do. I have discovered some gaps in the activity table I'm trying to query and those gaps are making it difficult to determine the success of this different approach to passing a parameter to a linked server query. I have some other issues as well now, which are not related to SSRS and another developer at my business has been trying to figure out those issues. Lee Markum

I've done away with the OPENQUERY because I was trying to eliminate a source of my problem with passing a parameter. I'm now getting everything from one database. However, I still can't get this to work. When I do what Hasham suggested I can see that it is passing @StoreNo (LocationNo.Value). My reworked query is below. When I put this into SSRS, minus the Declare statements, of course, then I get this after manually supplying the parameters in the Execute window. Declare @DateFrom INT set @DateFrom = 20120409 Declare @DateTo INT set @DateTo = 20120411 DECLARE @StoreNo NVARCHAR(5) SET @StoreNo = '00013' Declare @DealerID NVARCHAR(12) Set @DealerID = 'XYZ' Declare @AddWhere NVARCHAR(4000) Declare @SQL NVARCHAR(4000) IF @StoreNo = '' BEGIN SET @AddWhere = '' END ELSE BEGIN SET @AddWhere = 'AND (c.Serial_Number IN (SELECT MAX(Serial_Number) AS Serial_Number FROM ( SELECT Serial_Number, SUBSTRING(Customer_ID, 3, 5) AS Customer_ID FROM dbo.Customers AS c WHERE (Class_Code = N''XYZ'')) AS customer WHERE (Customer_ID = ''' +@StoreNo+''')))' END SET @SQL = ' SELECT TOP (100) PERCENT CASE dt.Dealer_ID WHEN ''12345678'' THEN SUBSTRING(dt.DEALER_ID, 4, 2) ELSE SUBSTRING(dt.DEALER_ID, 5, 1) END AS DIV, SUBSTRING(dt.Dealer_ID, 7, 2) AS REG, SUBSTRING(dt.Dealer_ID, 10, 3) AS DIST, SUBSTRING(c.Customer_ID, 3, 5) AS StoreNo, c.PostCode, c.Time_Zone, d.Day_Text, d.Date_Text, t.Time_Text_12_Hour, a.Event_Name, a.Area, a.User_Name, a.User_Number FROM dbo.CustomerActivity AS a INNER JOIN dbo.Customers AS c ON a.Customer_Key = c.Customer_Key INNER JOIN (SELECT Dealer_Key, Parent_Dealer_Key, Dealer_ID, Parent_Dealer_ID, [Level], Has_Subdealers FROM dbo.DealerTree(''' + @DealerID + ''', 1, 0) AS DealerTree_1) AS dt ON c.Dealer_ID = dt.Dealer_ID INNER JOIN dbo.Dates AS d ON a.Event_Date_Key = d.Date_Key INNER JOIN dbo.Times AS t ON a.Event_Time_Key = t.Time_Key WHERE (a.Open_Close_Signal = 1) AND (a.Event_Name NOT IN (''LATE-TO-CLOSE'', ''CANCEL'', ''LATE-TO-OPEN'')) AND (d.Date_Key BETWEEN ''' + CONVERT(NVARCHAR(8),@DateFrom) + ''' AND ''' + CONVERT(NVARCHAR(8),@DateTo) + ''') AND (c.Class_Code = ''XYZ'') ' + @AddWhere+' ORDER BY DIV, REG, DIST, c.Customer_ID, t.Time' --PRINT @SQL exec(@SQL) Lee Markum