Hi there, I'm learning to use Crystal and I'm also not great in SQL.

When creating a subreport that connects to a stored procedure I'm not able to expand it to select values during the report wizard or in the subreport.

I'm passing two paramaters, para1 a unique id and para2 a multiple value paramater. Basically I have a group that groups people based on statuses, there are about 13 different statuses. I want the stored procedure to do something based on 3 of those statuses and something else for the rest.

It's my understanding that to do this I have to use the join formula to pass this info to the stored procedure. I have done that, the formula is join({?Instant Status},","). It's linked to para2 in the subreport.

My stored procedure code is below. I'm not sure where I'm going wrong here. Any help would be appreciated and I apologize if I'm missing any info you need, please let me know!

Thanks!

CREATE PROCEDURE test @Para1 uniqueidentifier, @Para2 varchar(Max) as
    BEGIN

SELECT * INTO #MyTempTable  from Split(@Para2,',')

 IF EXISTS (SELECT  ITEM  FROM   #MyTempTable   WHERE  ITEM IN ('09 Candidate','08 TCP Interviewed', '06 Prospect'))

        select tCompany.CompanyName, tExperience.OfficialTitle, tExperience.StartDate, tExperience.Enddate from tPeople
    LEFT JOIN tExperience ON tPeople.GUID = tExperience.PeopleGUID
    LEFT JOIN tCompany ON tExperience.CompanyGUID = tCompany.GUID
    WHERE tPeople.GUID = @Para1

    EXCEPT

    select tCompany.CompanyName, tExperience.OfficialTitle, tExperience.StartDate, tExperience.Enddate from tPeople
    LEFT JOIN tExperience ON tPeople.CurrentExperienceGUID = tExperience.GUID
    LEFT JOIN tCompany ON tExperience.CompanyGUID = tCompany.GUID
    WHERE tPeople.GUID = @Para1

      ELSE
       
Return null

drop table #MyTempTable

END

Thanks for the reply!

I've created a formula but I get the error "A variable name is expected here".

Does your code need any modification or should it work off the bat?