The following is my stored procedure for taking case report.
ALTER procedure [dbo].[sp_casedetailsreport](@casenumber varchar(50)) as begin declare @layweid varchar(20) declare @lawyername varchar(50) declare @clienttype varchar(50) select @layweid=Current_Lawyer_Id from CMS.dbo.Client_Lawyer_Modification_details where Case_Number=@casenumber select @lawyername=Current_Lawyer_Name from CMS.dbo.OpponentLawyer_Modification_details where Case_Number=@casenumber select cad.Date AS RegistrationDate,cad.Case_Number as CaseNumber,cad.Court_Hall as CourtHall,cad.Case_Type as CaseType,cad.Client_Type as ClientType,cld.Client_Name as ClientName,cld.Client_PhoneNumber as ClientPhoneNumber,cop.Opp_Name as ClientOpponentName,cop.Opp_PhoneNumber as ClientOpponentPhoneNumber ,ld.Lawyer_Name as LawyerName,olmd.Current_Lawyer_Name as OpponentLawyerName,fud.Current_Details as CurrentDetails,fud.Current_Status as CurrentStage,fud.Next_Hearing_Date as NextHearingDate from CMS.dbo.Case_details cad inner join CMS.dbo.Client_details cld on cld.Case_Number=cad.Case_Number and cld.Client_Number='P1' inner join cms.dbo.Client_Opponent_details cop on cop.Case_Number=cad.Case_Number and cop.Opp_Number='O1' inner join CMS.dbo.Client_Lawyer_Modification_details clmd on clmd.Case_Number=cad.Case_Number and clmd.Current_Lawyer_Id=@layweid inner join CMS.dbo.OpponentLawyer_Modification_details olmd on olmd.Case_Number=cad.Case_Number and olmd.Current_Lawyer_Name=@lawyername inner join CMS.dbo.Lawyer_Details ld on ld.Lawyer_Id=clmd.Current_Lawyer_Id inner join CMS.dbo.Follow_up_details fud on fud.Case_Number=cad.Case_Number and fud.Date=(select MAX(Date) from CMS.dbo.Follow_up_details) where cad.Case_Number=@casenumber end
This actually give the output in the normal way. but i choose this stored procedure for getting report in rdlc report.I could not get this data in reportviewer but i can able to get the design what i designed in the rdlc form. i checked the dataset field expression in rdlc desgin,that is
first(fieldname.value,'dateset'). In the bottom of the filedname.value place the red line is there. what is the problem?
Ravi KumarPosted Aug 2, 2014, 12:39 AM
check the below procedure.
ALTER procedure [dbo].[sp_casedetailsreport]
(@casenumber varchar(50))
as
begin
declare @layweid varchar(20)
declare @lawyername varchar(50)
declare @clienttype varchar(50)
set @layweid=Current_Lawyer_Id from CMS.dbo.Client_Lawyer_Modification_details
where Case_Number=@casenumber
set @lawyername=Current_Lawyer_Name from CMS.dbo.OpponentLawyer_Modification_details
where Case_Number=@casenumber
select cad.Date AS RegistrationDate,cad.Case_Number as CaseNumber,
cad.Court_Hall as CourtHall,cad.Case_Type as CaseType,cad.Client_Type as ClientType,
cld.Client_Name as ClientName,cld.Client_PhoneNumber as ClientPhoneNumber,cop.Opp_Name as ClientOpponentName,
cop.Opp_PhoneNumber as ClientOpponentPhoneNumber ,ld.Lawyer_Name as LawyerName,
olmd.Current_Lawyer_Name as OpponentLawyerName,fud.Current_Details as CurrentDetails,fud.Current_Status as CurrentStage,
fud.Next_Hearing_Date as NextHearingDate
from CMS.dbo.Case_details cad
inner join CMS.dbo.Client_details cld on cld.Case_Number=cad.Case_Number and cld.Client_Number='P1'
inner join cms.dbo.Client_Opponent_details cop on cop.Case_Number=cad.Case_Number and cop.Opp_Number='O1'
inner join CMS.dbo.Client_Lawyer_Modification_details clmd on clmd.Case_Number=cad.Case_Number and clmd.Current_Lawyer_Id=@layweid
inner join CMS.dbo.OpponentLawyer_Modification_details olmd on olmd.Case_Number=cad.Case_Number and olmd.Current_Lawyer_Name=@lawyername
inner join CMS.dbo.Lawyer_Details ld on ld.Lawyer_Id=clmd.Current_Lawyer_Id inner join CMS.dbo.Follow_up_details fud on fud.Case_Number=cad.Case_Number
and fud.Date=(select MAX(Date) from CMS.dbo.Follow_up_details) where cad.Case_Number=@casenumber
end