Actually there are three select statements in that SP. So only fields from the first select statement are shown in the field object. What i have observed is only one select statement is supported in crystal reports. I am attaching my SP also with this. Please go through the stored procedure.
CREATE PROCEDURE [dbo].[Sp_SickleaveReport]
@EmpNo varchar(6),
@Surname varchar(18),
@Fortnightbasehours int,
@Year int,
@CompactorExitEmployee varchar(20)
AS
BEGIN
select PBER.EmployeeNo,PBER.Surname,PBER.DOB,PBER.CommencementDate,PBER.MedicalNo,PBER.EmpStatusCode,Esc.Description,
pc.ClassificationDescription,pc.ClassificationCode,pc.SW,SLD.Resumptiondate,SLD.Paidfortnightend ,
cast(SLTOB.Fullcurrent/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLTOB.Fullcurrent%60 as varchar(2)), 2) as Fullcurrent, --Opening Balance
cast(SLTOB.Halfcurrent/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLTOB.Halfcurrent%60 as varchar(2)), 2) as Halfcurrent,
cast(SLTOB.Fullaccumulated/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLTOB.Fullaccumulated%60 as varchar(2)), 2) as Fullaccumulated,
cast(SLTOB.Halfaccumulated/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLTOB.Halfaccumulated%60 as varchar(2)), 2) as Halfaccumulated,--
cast(SLS.Currentfull/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLS.Currentfull%60 as varchar(2)), 2) as Currentfull, --S U M M A R Y
cast(SLS.Currenthalf/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLS.Currenthalf%60 as varchar(2)), 2) as Currenthalf,
cast(SLS.Accumulatedfull/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLS.Accumulatedfull%60 as varchar(2)), 2) as Accumulatedfull
,cast(SLS.Accumulatedhalf/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLS.Accumulatedhalf%60 as varchar(2)), 2) as Accumulatedhalf
,cast(SLD.Fullsickpaydebitedminutes/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLD.Fullsickpaydebitedminutes%60 as varchar(2)), 2) as Fullsickpaydebitedminutes
,cast(SLD.Halfsickpaydebitedminutes/60 as varchar(5)) + ':' + RIGHT('0' + cast(SLD.Halfsickpaydebitedminutes%60 as varchar(2)), 2) as Halfsickpaydebitedminutes --
,SLS.Medcerts,SLS.Medcertsdays,SLS.Casualabsentunpaiddays,SLS.Casualabsentpaid,SLS.Casualabsentpaiddays,SLS.Casualabsentunpaid--Absences
,(sls.Medcerts+SLS.Casualabsentpaid) as totalOccasions,(SLS.Medcertsdays+SLS.Casualabsentpaiddays) as totalPaidDays,(SLS.Casualabsentpaiddays+SLS.Casualabsentunpaid) as TotalUnpaidDays
from PERSYBasicEmployeeRecords PBER inner join PERSYEmployeemovementshistory PEMH
on PEMH.Positionnumber=PBER.PositionNo inner join PERSYclassifications PC on
pc.ClassificationCode= PEMH.Classificationcode inner join Sickleavedetails SLD on PBER.EmployeeNo=SLD.Employeenumber
inner join SickLeaveTotalOpeningBalance SLTOB on SLTOB.Employeenumber=PBER.EmployeeNo
inner join Sickleavesummaries SLS on SLS.Employeenumber=PBER.EmployeeNo inner join EmpStatusCode ESC on ESC.EmploymentStatusCode=PBER.EmpStatusCode
where PBER.EmployeeNo=@EmpNo and SLD.Year=@Year and SLTOB.Year=@Year and SLS.Year=@Year and sld.Fortnightbasehours=(@Fortnightbasehours*60)
Select 'SICK LEAVE ENTITLEMENT ADJUSTMENT BY NEW REGULATION AT 1.5.'+ CAST(Year as VARCHAR(50)) as SickleaveAjustment,
Fullcurrent as FullcurrentSICK,Halfcurrent as HalfcurrentSICK ,Fullaccumulated as FullaccumulatedSick,Halfaccumulated as HalfaccumulatedSick,Indicators from SickLeaveTotalAdjustment where Year=@Year and Employeenumber=@EmpNo
select Fullsickpaydebiteddays,cast(Fullsickpaydebitedminutes/60 as varchar(5)) + ':' + RIGHT('0' + cast(Fullsickpaydebitedminutes%60 as varchar(2)), 2) as Fullsickpaydebitedminutes,dateadd(DAY,-1,Resumptiondate) As Medicalcertificateto
,cast(Halfsickpaydebitedminutes/60 as varchar(5)) + ':' + RIGHT('0' + cast(Halfsickpaydebitedminutes%60 as varchar(2)), 2) as Halfsickpaydebitedminutes
,Halfsickpaydebiteddays,Resumptiondate as Fromdate,Paidfortnightend as Todate,Halfconverted,NOI.Description from Sickleavedetails SLD inner join NatureofIllness NOI on NOI.IllnessCode=sld.Illness where Year=@Year and Employeenumber=@EmpNo
END
Mudassar says:
While designing, you need to specify all the fields you need to display in the Crystal Report. Now if the fields are changing you need to again add the new fields to Crystal Reports Typed DataSet
