Cost Gross Margin Quick Report Custom Procedure


USE [TMCHN]
GO
/****** Object: StoredProcedure [dbo].[cp_CostGrossMarginByRep] Script Date: 02/04/2016 15:22:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[cp_CostGrossMarginByRep]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='1/1/1900',
@enddate datetime='1/1/2099'

as

DECLARE @spCostGrossMargin TABLE(
Sales_Doc_Num char(25),
Sales_Doc_Type char(25),
Return_Cft numeric(19,5),
Source char(25),
Sales_Person_ID char(25),
Doc_Date datetime,
Customer_Num char(30),
Customer_Name char(65),
Cost numeric(19,5),
Total numeric(19,5),
Profit numeric(19,5),
Gross_Margin numeric(19,5),
Cost_Items numeric(19,5),
PerDiem numeric(19,5),
Rivets numeric(19,5),
Mileage numeric(19,5),
FreightOut numeric(19,5),
Labor numeric(19,5),
JobSupplies numeric(19,5),
Installers numeric(19,5),
BidCosts numeric(19,5),
USETAX numeric(19,5) );

insert into @spCostGrossMargin
(Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost,
Total,
Profit,
Gross_Margin,
PerDiem,
Rivets,
Mileage,
FreightOut,
Labor,
JobSupplies,
Installers,
BidCosts,
USETAX)
select
sd.Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
Total=Total-Tax,
Profit = Total - Tax - ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
--Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
ISNULL(case Total
when 0 then 0
else (case (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
when 0 then 1
else (Total - Tax - (select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total - Tax)
end)
end, 0) as GM,
--ISNULL(tot.Cost_Items, 0),
PerDiem = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'PER DIEM'), 0),
Rivets = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIVETS'), 0),
Mileage = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILES'), 0),
FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
Labor = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR'), 0),
JobSupplies = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'SUPPLIES'), 0),
Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
BidCosts = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'BID COSTS'), 0),
USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
spv3SalesDocument as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

insert into @spCostGrossMargin
(Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost,
Total,
Profit,
Gross_Margin,
PerDiem,
Rivets,
Mileage,
FreightOut,
Labor,
JobSupplies,
Installers,
BidCosts,
USETAX)
select
sd.Sales_Doc_Num,
Sales_Doc_Type,
Source,
Sales_Person_ID,
Doc_Date,
Customer_Num,
Customer_Name,
Cost = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
Total=Total-Tax,
Profit = Total - Tax - ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
--Gross_Margin = ISNULL(((Total-(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
ISNULL(case Total
when 0 then 0
else (case (select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
when 0 then 1
else (Total - Tax -(select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total - Tax)
end)
end, 0) as GM,
--ISNULL(tot.Cost_Items, 0),
PerDiem = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'PER DIEM'), 0),
Rivets = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIVETS'), 0),
Mileage = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILES'), 0),
FreightOut = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT OUT'), 0),
Labor = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR'), 0),
JobSupplies = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'SUPPLIES'), 0),
Installers = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'INSTALLERS'), 0),
BidCosts = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'BID COSTS'), 0),
USETAX = ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'USETAX'), 0)
from
spv3SalesDocumentHistory as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

update @spCostGrossMargin set Return_Cft = 1 where Sales_Doc_Type <> 'RETURN'
update @spCostGrossMargin set Return_Cft = -1 where Sales_Doc_Type = 'RETURN'

select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin, PerDiem, Rivets, Mileage, FreightOut, Labor, JobSupplies, Installers, BidCosts, USETAX from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name,
Cost=Cost*Return_Cft,
Total=Total*Return_Cft,
Profit=Profit*Return_Cft,
Gross_Margin=Gross_Margin*Return_Cft,
PerDiem=PerDiem*Return_Cft,
Rivets=Rivets*Return_Cft,
Mileage=Mileage*Return_Cft,
FreightOut=FreightOut*Return_Cft,
Labor=Labor*Return_Cft,
JobSupplies=JobSupplies*Return_Cft,
Installers=Installers*Return_Cft,
BidCosts=BidCosts*Return_Cft,
USETAX=USETAX*Return_Cft
from @spCostGrossMargin) as a

SET ANSI_NULLS OFF

Standard Cost

USE [TMCHN]
GO

/****** Object:  StoredProcedure [dbo].[cp_CostGrossMarginByRep2_stndcost]    Script Date: 04/27/2015 10:50:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER procedure [dbo].[cp_CostGrossMarginByRep2_stndcost]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='1/1/1900',
@enddate datetime='1/1/2099'

as

DECLARE @spCostGrossMargin TABLE(
	Sales_Doc_Num char(25),
	Sales_Doc_Type char(25),
	Return_Cft numeric(19,5),
	Source char(25),
	Sales_Person_ID char(25),
	Doc_Date datetime,
	Customer_Num char(30),
	Customer_Name char(65),
	Cost numeric(19,5),
	Total numeric(19,5),
	Profit numeric(19,5),
	Gross_Margin numeric(19,5),
	Cost_Items numeric(19,5),
	Consignment numeric(19,5),
	Freight_Delivery numeric(19,5),
	Job_Supplies numeric(19,5),
	Labor_Service numeric(19,5),
	Mileage numeric(19,5),
	Rigging_Forklift numeric(19,5),
	CC_Service_Parts numeric(19,5),
	TotalCostDifference numeric(19,5) );

insert into @spCostGrossMargin
	(Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost,
	Total,
	Profit,
	Gross_Margin,
	Consignment,
	Freight_Delivery,
	Job_Supplies,
	Labor_Service,
	Mileage,
	Rigging_Forklift,
	CC_Service_Parts,
	TotalCostDifference)	
select 
	sd.Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost = [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 0),
	Total=ISNULL(sd.Total, 0) - Tax,
	Profit = Total - Tax - ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
	--Gross_Margin = ISNULL(((Total-(select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
	ISNULL(case Total
		when 0 then 0
		else (case (select sum(ISNULL((case sli.stndcost when 0 then sli.Unit_Cost else sli.stndcost end),0)) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
			  when 0 then 0
			  else CAST([dbo].[spfGetGrossMargin](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 0) AS DECIMAL (10,2))/100
		end)
	end, 0) as GM,
	--ISNULL(tot.Cost_Items, 0),
	Consignment = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CONSIGNMENT'), 0),
	Freight_Delivery = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT/DELIVERY'), 0),
	Job_Supplies = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'JOB SUPPLIES'), 0),
	Labor_Service = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR/SERVICE'), 0),
	Mileage = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILEAGE'), 0),
	Rigging_Forklift = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIGGING/FORKLIFT'), 0),
	CC_Service_Parts = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcost as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CC SERVICE PARTS'), 0),
	TotalCostDifference = Total - Tax - [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 0)	
from 
	spv3SalesDocument as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

insert into @spCostGrossMargin
	(Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost,
	Total,
	Profit,
	Gross_Margin,
	Consignment,
	Freight_Delivery,
	Job_Supplies,
	Labor_Service,
	Mileage,
	Rigging_Forklift,
	CC_Service_Parts,
	TotalCostDifference)	
select 
	sd.Sales_Doc_Num,
	Sales_Doc_Type,
	Source,
	Sales_Person_ID,
	Doc_Date,
	Customer_Num,
	Customer_Name,
	Cost = [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 1) + Freight,
	Total=ISNULL(sd.Total, 0) - Tax,
	Profit = Total - Tax - ISNULL((select sum(Extended_Cost) from spv3SalesLineItemHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type), 0),
	--Gross_Margin = ISNULL(((Total-(select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)-Tax)/(Total+.001)), 0),
	ISNULL(case Total
		when 0 then 0
		else (case (select sum(ISNULL((case sli.stndcost when 0 then sli.Unit_Cost else sli.stndcost end),0)) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type)
			  when 0 then 0
			  else CAST([dbo].[spfGetGrossMargin](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 1) AS DECIMAL (10,2))/100
		end)
	end, 0) as GM,
	--ISNULL(tot.Cost_Items, 0),
	Consignment = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CONSIGNMENT'), 0),
	Freight_Delivery = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'FREIGHT/DELIVERY'), 0),
	Job_Supplies = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'JOB SUPPLIES'), 0),
	Labor_Service = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'LABOR/SERVICE'), 0),
	Mileage = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'MILEAGE'), 0),
	Rigging_Forklift = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'RIGGING/FORKLIFT'), 0),
	CC_Service_Parts = ISNULL((select sum(stndcost) from spv3SalesLineItem_stdcostHistory as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type and sli.Item_Number = 'CC SERVICE PARTS'), 0),
	TotalCostDifference = Total - Tax - [dbo].[spfGetConditionalCost](sd.Sales_Doc_Num, (select SDT.SOPTYPE from spvSalesDocType as SDT where sd.Sales_Doc_Type = SDT.Sales_Doc_Type), 1)
from
	spv3SalesDocumentHistory as sd
where (Doc_Date between @startdate and @enddate and Sales_Doc_Type in ('RETURN','INVOICE') and Source = 'History')

update @spCostGrossMargin set Return_Cft = 1 where Sales_Doc_Type <> 'RETURN'
update @spCostGrossMargin set Return_Cft = -1 where Sales_Doc_Type = 'RETURN'

select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin, Consignment, Freight_Delivery, Job_Supplies, Labor_Service, Mileage, Rigging_Forklift, CC_Service_Parts, "TP-TC"=TotalCostDifference
from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name,
	Cost=Cost*Return_Cft,
	Freight_Delivery=Freight_Delivery*Return_Cft,
	Job_Supplies=Job_Supplies*Return_Cft,
	Labor_Service=Labor_Service*Return_Cft,
	Mileage=Mileage*Return_Cft,
	Rigging_Forklift=Rigging_Forklift*Return_Cft,
	CC_Service_Parts=CC_Service_Parts*Return_Cft,
	Total=Total*Return_Cft,
	Profit=Profit*Return_Cft,
	Gross_Margin=Gross_Margin*Return_Cft,
	Consignment=Consignment*Return_Cft,
	TotalCostDifference = (Total-Cost)*Return_Cft
	from @spCostGrossMargin) as a

SET ANSI_NULLS OFF


GO


Quick Report

<report name="Sales By Rep Machine" AutoLinks="true" autoRun="true" HideUndeclaredColumns="true">

	<!--<search name="Sales Person ID" column="Sales_Person_ID" searchOp="like" />-->
	<!--<search name="Sales Document Type" column="Sales_Doc_Type" searchOp="like" />-->
	<search name = "Begin Date" column="startdate" searchOp="&gt;"  />
	<search name = "End Date" column="enddate"  searchOp="&lt;" />

	<query addWhere="false">

		exec cp_CostGrossMarginByRep
		
	</query>

	<column name="Doc_Date" Caption="Doc Date" BestFit="true" Sort="Descending"/>
	<column name="Source" BestFit="true"/>
	<column name="Sales_Person_ID" Caption="Sales Person" BestFit="true"/>
	<column name="Total Price" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Total Cost" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Profit" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Sales_Doc_Num" SummaryType="Count"/>
	<column name="Sales_Doc_Name" Caption="Customer Name"/>
	<column name="Sales_Doc_Type" Caption="Sales Doc Type"/>
	<column name="Gross_Margin" Caption="GM %" DisplayFormat="0.00%" SummaryType="Average" BestFit="true"/>


	<column name="Consignment" Caption="Consignment" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Freight_Delivery" Caption="Freight/Delivery" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Job_Supplies" Caption="Job Supplies" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Labor_Service" Caption="Labor/Service" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Mileage" Caption="Mileage" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="Rigging_Forklift" Caption="Rigging/Forklift" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
	<column name="CC_Service_Parts" Caption="CC Service Parts" DisplayFormat="$#,##0.00###" SummaryType="Sum" BestFit="true"/>
</report>



Leave a Reply