SQL - Passing a Stored Procedure Variable as a Parameter in a crystal report for end users to select

Question

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

1. 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)

2. 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?)

Code:

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]

Answer 1

Ok here are the steps I took to reference parameters from my crystal report to the stored procedure.

1. Add parameter declarations in stored procedure like so:

   ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP]

   @location VARCHAR(10),

   @dateshipped DATETIME

2. Then navigate to crystal report builder and select "Database>>>>Database Verify". This will refresh the database connection and present your parameters as a dialog box. From there you will enter your values to test the parameters

3. If they test ok the dialog box will be closed and you can then navigate to "field explorer" and under "parameter fields" you will now see your new parameters.

4. Then you can simply drag them over to the report.