Crystal 2008 Stored Procedure with parameter

[allyne]

I have a stored procedure that looks simular to the below.

Create Procedure sp_Customer

(@SalesPerson Nvarchar (Max))

Select c.customerID,c.LastName, c.FirstName,c.CompanyName,c.SalesPerson
from SalesLT.Customer c
where c.SalesPerson in (@SalesPerson)

I'm new to Crystal reports and I am unable to get the parameter in crystal reports to work as a multi select parameter. Is this possible? If so how can I get this to work?

Thanks for all your help!

 

[hilfy]

You can't use multi-select parameters with stored procedures - they don't work. Crystal stores the data as an array, which the stored proc cannot read.

However, you could possibly do this with a subreport (only if you don't already need a subreport!) Assuming that you want to group the data by salesperson and that there is a separate "SalesPerson" table, try this:

1. Modify your stored proc to just expect a single value from the parameter.

2. Create a main report that uses just the SalesPerson table. Use the Select Expert to filter the data, including using the multi-select parameter that you create to show just specific sales people.

3. Group by whatever field matches the SalesPerson field in the table in your stored proc. Suppress the details section.

4. Put a subreport in the group footer section. This subreport will use the stored procedure and provide data for a single sales person. Link to the stored procedure parameter in the subreport on the field that you're grouping by in the main report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com