Print Page | Close Window

Update: Can see but not select stored proc?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=16173
Printed Date: 21 Apr 2025 at 1:32pm


Topic: Update: Can see but not select stored proc?
Posted By: dlg_az
Subject: Update: Can see but not select stored proc?
Date Posted: 13 Apr 2012 at 9:27am
PREFACE: Relatively new to Crystal; new to stored procedures.

ISSUE: Stored proc with parameters (runs fine in SQL Server Mgt Studio - returns records). Can't connect it to Crystal.

From Crystal when I go to select the SP I can see it, I select it in the left pane of SSMS, right arrow becomes active to move it to the right pane, I select it ... nothing happens and no error message. I've double-checked my query, googled quite a bit, can't seem to come up with a reasonable explanation. Any assistance would be greatly appreciated.

Connections, etc:
-----------------
SQL Server 2008 R2
Crystal Reports 2008
OLE DB (ADO) - MS OLE DB Provider for SQL Server

First time I've tried an SP (very simple):

ALTER procedure [dbo].[EmployeesNotTakenTool]
     @location nvarchar(40),
     @tool nvarchar (100)
     as
     
BEGIN

select distinct dbo.vw_EmployeeCertificationRecordDetail.EmployeeBadge,
dbo.vw_EmployeeCertificationRecordDetail.FullName,
dbo.vw_EmployeeCertificationRecordDetail.Shift,
dbo.vw_EmployeeCertificationRecordDetail.SuperFull,
dbo.vw_EmployeeCertificationRecordDetail.Location

from     dbo.vw_EmployeeCertificationRecordDetail

where dbo.vw_EmployeeCertificationRecordDetail.EmployeeBadge NOT IN (

select dbo.vw_EmployeeCertificationRecordDetail.EmployeeBadge
from dbo.vw_EmployeeCertificationRecordDetail
where dbo.vw_EmployeeCertificationRecordDetail.Location = @location and dbo.vw_EmployeeCertificationRecordDetail.CertificationName = @tool and dbo.vw_EmployeeCertificationRecordDetail.Status = 'Certified' and dbo.vw_EmployeeCertificationRecordDetail.Shift is not null)

and dbo.vw_EmployeeCertificationRecordDetail.Location = @location
and dbo.vw_EmployeeCertificationRecordDetail.Status = 'Certified'
and dbo.vw_EmployeeCertificationRecordDetail.Shift is not null
and dbo.vw_EmployeeCertificationRecordDetail.SuperFull is not null;

END


Replies:
Posted By: dlg_az
Date Posted: 13 Apr 2012 at 2:53pm
I couldn't get it to work by choosing the SP, so I resorted to using the ADD COMMAND feature. I was able to EXEC my SP that way with some tweaking on syntax and got the data into my report. Was absolutely thrilled about it (pathetic, I know).

My next question:
-------------------
In the add command window when you create a parameter it has a choice of "allows mulitple parameters" - I want to allow the user to input several values of the same parameter - how do I do this? What is the syntax on the EXEC statement?

Posted By: hilfy
Date Posted: 15 Apr 2012 at 2:28pm
You can't pass multiple values in a param to a stored proc.
 
What I have done in the past is put the part of the report that calls the stored proc into a subreport.  In the main report I then parse out the array of parameter values and concatenate them into a single comma-delimited string that has a comma on the end.  That string gets passed as a link into the subreport and thus into the stored proc.
 
The SP then uses InStr(param, field||',') > 0 to determine whether the data is in the list of params.  I use the commas because there are situations where one of the values in the parameter string may be a "piece" of the data in a field and I want to make sure that I'm comparing to ALL of the data in the field.  For example, if the user selects "ABC" and I have records with "ABC", "1ABC", "ABC2", etc., I want the record that has only "ABC" in it.  Make sense?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: dlg_az
Date Posted: 15 Apr 2012 at 2:57pm
Thanks for the pointer; I will try that for at least one of the parameters I offer (4 shift names). However, it sounds like it might be a bit unwieldy for choosing multiple values of a rather long list of mfg. certification courses I need to use.  At present, the user will have to type the cert name in - not elegant but gets the job done.  I don't really need to offer params for certs, however, it does make human typos less likely; I'd like to offer them if I can.

What, then, is the purpose of "allow multiple values" in ADD COMMAND when you create new params? I've yet to find some decent documentation/forum posts on how to use it.

Posted By: hilfy
Date Posted: 16 Apr 2012 at 4:50pm
When I write a query for a command (not using a SP) and I want to filter on a list of items, I'll add to the where clause something like the following:
 
and table.field in {?my_list_param}
 
where the parameter is multi-valued.
 
This is how the multi-valued params work.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: dlg_az
Date Posted: 16 Apr 2012 at 4:54pm
I'm not sure I follow you.  I understand the part about adding it to the query, however, do you then also checkmark the "allow multiple parameters" indicator in the command box? It's that checkbox that's got me confused. 


Print Page | Close Window