OK I know there are hundreds of posts out there that state how to pass a parameter from a stored procedure to a crystal report. But unfortunately with some research I haven't come across a specific answer to my question with regards to SQL Server variables and crystal report parameters.
Question: If I have a stored procedure and I am declaring a variable called "Location" and I want to reference that variable in my crystal report as a new parameter and end users to be presented a dialog box that allows them to select from various locations and that would in return results only for a specific location chosen from a drop down. How would that occur?
Methods tried so far:
Crystal Report Builder 11
In "parameter fields" under field explorer, select new and then choose "List of Values" to by Dynamic and choose the data source and column that's been used with my variable and it says "Parameters" is @location. But when I run the report I am presented with a dialog box for a location value but there are no values to select from. (To make a note the location field is being supplied by the users drop down selection via the report thus returning results from the stored procedure)
In "parameter fields" select new and then leave list of values as "static" and choose value field as column "location" and enter multiple values with regards to locations for the user to select from. (This does present the user with a dialog box suggesting drop down values for a location but no results returned so I was unsure if this was the proper way?)
ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP] AS BEGIN IF (object_id('tempdb..#tempTruckSummary') IS NOT NULL) BEGIN DROP TABLE #tempTruckSummary END DECLARE @location VARCHAR(10) SELECT O.order_no, L.shipped, L.price, L.[location] FROM orders O WITH (NOLOCK) INNER JOIN Ord_list L ON O.order_no = L.order_no WHERE O.routing NOT LIKE 'FEDX%' AND O.routing NOT IN ('UPS', 'UPS 1', 'UPS 2', 'UPS 3') AND ISNULL(O.void,'') <> 'V' AND L.location = @location GROUP BY O.order_no, L.shipped, L.price, L.[location]