CODEWITHSUNDEEP
sqlreporting-services
GabrielVa
GabrielVa

Reputation: 2388

SSRS gives me an error but works in Data view

Working on a query in SSRS 2005. The query works in the Data view but in preview it bombs out. This is what I have: 

SELECT 
      TOP(cast(@N as integer))  
      Date, 
      StartItem, 
      MasterItem, 
      SubItem, 
      SubItemDescription, 
      Quantity, 
      NetQuantity, 
      UnitOfMeasure, 
      BomLevel, 
      Cost, 
      Cost * NetQuantity * (CASE WHEN a.Purch = 1 THEN 1 ELSE 0 END) AS TotalSumCost
 FROM         
      Bom AS a
 GROUP BY 
      Date, StartItem, MasterItem, SubItem, SubItemDescription, Quantity, NetQuantity,       UnitOfMeasure, BomLevel, Cost, 
                        Cost * NetQuantity * (CASE WHEN a.Purch = 1 THEN 1 ELSE 0 END)
 ORDER BY TotalSumCost DESC

The error I get is "the Value Expression for the query parameter '@N' refers to a non-existing report parameter 'N'. I have N setup in my reports parameter. See picture below.

enter image description here

Upvotes: 2

Views: 288

Answers (1)

JonH
JonH

Reputation: 33141

You have to name it N, I see RowsReturned.

Also if you defined that parameter later on in the process there are cases especially in 03 and 05 (Visual Studio) where that parameter is not picked up. Right click your report and select "View Code" in the XML look for the parameters section and ensure it is there. It will look like this:

 <Query>
        <DataSourceName>dsTKExpenseReports</DataSourceName>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>rptExpenseReportReasons</CommandText>
        <QueryParameters>
          <QueryParameter Name="@ExpenseReasonID">
            <Value>=Parameters!ExpenseReasonID.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@Reimbursable">
            <Value>=Parameters!Reimbursable.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@RequiresDesc">
            <Value>=Parameters!RequiresDesc.Value</Value>
          </QueryParameter>
        </QueryParameters>
      </Query>

Notice the query parameters section, make sure yours has both Item and RowsReturned. And change either the parameter name to "N" or change your SQL or Stored Procedure to the value "RowsReturned". They should always match. Your code should look like this:

SELECT 
      TOP(cast(@RowsReturned as integer))  
      Date, 
      StartItem, 
      MasterItem, 
      SubItem, 
      SubItemDescription, 
      Quantity, 
      NetQuantity, 
      UnitOfMeasure, 
      BomLevel, 
      Cost, 
      Cost * NetQuantity * (CASE WHEN a.Purch = 1 THEN 1 ELSE 0 END) AS TotalSumCost
 FROM         
      Bom AS a
 GROUP BY 
      Date, StartItem, MasterItem, SubItem, SubItemDescription, Quantity, NetQuantity,       UnitOfMeasure, BomLevel, Cost, 
                        Cost * NetQuantity * (CASE WHEN a.Purch = 1 THEN 1 ELSE 0 END)
 ORDER BY TotalSumCost DESC

BTW you are improperly using the GROUP BY clause, you are simply listing all of your columns in the group by to get expected results. This might bite you down the road. Proper way would be an aliased query and then the group within the alias...:

SELECT 
      n.Blah,
      a.Blah
FROM
      MyTable
INNER JOIN
 (
  SELECT...GROUP BY...
 ) n

Upvotes: 5

Related Questions