@GroupId int,
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@RegionCode varchar(30) = NULL
AS
BEGIN
DECLARE @AgencyTable TABLE (GpoId int, OfcId int, Total_Bills BIGINT, Bill_Value BIGINT, Commission BIGINT, Total_Amount BIGINT)
SET @EndDate = convert(datetime, convert(Varchar(12), @EndDate, 106) + ' 23:59:59PM')
INSERT @AgencyTable (GpoId, OfcId, Total_Bills, Bill_Value, Commission, Total_Amount)
SELECT bil.GroupId, Bil.SubOfficeId , isnull(COUNT(Bil.ConsumerNumber),0)
,ISNULL(SUM(Bil.C_Amount),0)
,ISNULL(SUM(Bil.Commission),0)
,ISNULL(SUM(Bil.C_Amount),0) - ISNULL(SUM(Bil.Commission),0)
FROM BillTxnSO as Bil inner join pp_offices ofc On bil.GroupId = ofc.Group_Id and bil.SubOfficeId = ofc.OfficeCode
Where bil.GroupId = @GroupId AND TransDate BETWEEN @StartDate AND @EndDate
Group by bil.GroupId, Bil.SubOfficeId
SELECT ofc.OfficeName as SubOffice_Name, isnull(gpo.Total_Bills,0)as Total_Bills , isnull(gpo.Bill_Value,0)as Bill_Value , isnull(gpo.Commission,0) as Commission, isnull(gpo.Total_Amount,0) as Total_Amount
FROM @AgencyTable gpo
INNER JOIN pp_offices ofc On ofc.Group_Id = gpo.GpoId and gpo.OfcId = ofc.OfficeCode
ORDER BY ofc.OfficeName
END
..................................................................................
This Query working fine in report generation but in report viewer i got the SubOffice_Name as it is i.e "SubOffice_Name" in stead of ofc.officeName.Can anyone help me in this regard