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?)
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]
@location
, and then using it in a query. Since no value has been set for it, it will be NULL, which is probably why you're not getting a result set. You probably want this as a parameter to the proc, not as a variable. Not an answer, since I don't know how you pass the selection back from CR to SQL. – RDFozz May 16 '17 at 19:39