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]
  • Maybe you need an OUTPUT parameter? technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx – sp_BlitzErik May 16 '17 at 19:15
  • While I haven't used Crystal Reports, I can say that I think the problem is moving things in the opposite direction than what you've asked - providing the user-selected location as a parameter to your stored procedure, and then selecting based on it. In the procedure above, you're declaring @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
up vote 0 down vote accepted

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.