How to build a Quick Report in SalesPad

http://docs.salespad.net/support/desktop-support/entry/reporting/quick-reports-reference-guide



<?xml version="1.0" encoding="utf-16"?>
<report name="Recent Sales Documents" AutoLinks="true" GroupFooterShowMode="Expanded">
  <search name="Sales Person" column="Sales_Person_ID" searchOp="like" />
  <search name="Doc Date" Type="DateTime" column="Doc_Date" searchOp="&gt;=" />
  <query addWhere="true">

<![CDATA[ select * from spv3SalesDocument ]]>

</query>

  <column name="Sales_Person_ID" BestFit="true" />
  <column name="Doc_Date" Sort="Descending" />
  <OnRunScript>
  </OnRunScript>
</report>

report
name
autolinks TRUE FALSE
GroupFooterShowMode Expanded
bestFitAll TRUE FALSE
autoRun TRUE FALSE
 HideUndeclaredColumns TRUE FALSE
query
addwhere TRUE FALSE
search
name “Sales Person”
column “Sales_Person_ID”
searchOp like &gt;= &lt;=
Type DateTime lookup
sql Lookup sql=”SELECT DISTINCT xManufacture FROM spxItemMaster where xManufacture IS NOT NULL”
column
name
BestFit TRUE FALSE
DisplayFormat $#,##0.00###
SummaryType Sum Count Average
Caption “Job Supplies”
Visible TRUE FALSE
sort descending ascending
OnRunScript

Cost Per Click Quick Report


  select Sales_Doc_Type,
  Doc_Date,
  Customer_Name,
  Customer_Num,
  Sales_Doc_Num
         Sales_Doc_Num,
         xCampaignMedium,
         Subtotal,
         Prev_Sales_Doc_Num,
         Gross_Margin = (Total-(select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total+.001),
         Profit = Total - (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Cost = (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Total,
         Sales_Person_ID,
         Source
  
  
   from spv3SalesDocument as sd
      WHERE xCampaignMedium = 'CPC' and (Sales_Doc_Type = 'QUOTE' or Sales_Doc_Type = 'ORDER')
   
   UNION 
   
     select Sales_Doc_Type,
  Doc_Date,
  Customer_Name,
  Customer_Num,
  Sales_Doc_Num
         Sales_Doc_Num,
         xCampaignMedium,
         Subtotal,
         Prev_Sales_Doc_Num,
               Gross_Margin = (Total-(select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type))/(Total+.001),
         Profit = Total - (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Cost = (select sum(Standard_Cost) from spv3SalesLineItem as sli where sli.Sales_Doc_Num = sd.Sales_Doc_Num and sli.Sales_Doc_Type = sd.Sales_Doc_Type),
         Total,
         Sales_Person_ID,
         Source
  
  
   from spv3SalesDocumentHistory as sd
   
   
   WHERE xCampaignMedium = 'CPC'  and Sales_Doc_Type = 'ORDER'
   
   

Sales Report


 USE [MTHND]
GO

/****** Object:  StoredProcedure [dbo].[cp_SalesReport]    Script Date: 09/18/2015 15:36:27 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

 
ALTER procedure [dbo].[cp_SalesReport]
--@sp char(15)=NULL,
--@Sales_Doc_Type char(30)=NULL,
@startdate datetime='11/01/2014',
@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),
          ADDRESS1 char (61),
          ADDRESS2 char (61),
          CITY char(35),
          STATE char(29),
          ZIPCODE char(11),
       Email char (131),
          Cost numeric(19,5),
       Total numeric(19,5),
       Profit numeric(19,5),
       Gross_Margin numeric(19,5),
          FreightOut numeric(19,5),
          Installers 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,
          ADDRESS1,
          ADDRESS2,
          CITY,
          STATE,
          ZIPCODE,
       Email,
       Cost,
       Total,
       Profit,
       Gross_Margin,
          FreightOut,
          Installers,
          USETAX)
     
select
       sd.Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address_Line_1,
          Address_Line_2,
          CITY,
          STATE,
          ZIP,
       Email,
     
       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,
       Profit = Total - 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 - (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)
              end)
       end, 0) as GM,
          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),
          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),
          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)
 
insert into @spCostGrossMargin
       (Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address1,
          Address2,
          CITY,
          STATE,
          ZIPCODE,
       Email,
       Cost,
       Total,
       Profit,
       Gross_Margin,
          FreightOut,
          Installers,
          USETAX)
     
select
       sd.Sales_Doc_Num,
       Sales_Doc_Type,
       Source,
       Sales_Person_ID,
       Doc_Date,
       Customer_Num,
       Customer_Name,
          Address_Line_1,
          Address_Line_2,
          CITY,
          STATE,
          ZIP,
       Email,
       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,
       Profit = Total - 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 -(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)
              end)
       end, 0) as GM,
          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),
          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),
          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)
 
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, ADDRESS1, ADDRESS2, STATE, ZIPCODE, Email, FreightOut, Installers, USETAX, "Total Price"=Total, "Total Cost"=Cost, Profit, Gross_Margin
from
(select Sales_Doc_Num, Sales_Doc_Type, Source, Sales_Person_ID, Doc_Date, Customer_Num, Customer_Name, ADDRESS1, ADDRESS2, STATE, ZIPCODE, Email,
Cost=Cost*Return_Cft,
Total=Total*Return_Cft,
Profit=Profit*Return_Cft,
Gross_Margin=Gross_Margin*Return_Cft,
FreightOut=FreightOut*Return_Cft,
Installers=Installers*Return_Cft,
USETAX=USETAX*Return_Cft
from @spCostGrossMargin) as a
 
 
SET ANSI_NULLS OFF

GO


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 &lt;&gt; '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>



WIP Quick Report Salepad


<report name="WIP Report" AutoLinks="true" autorun="true" bestFitAll="true">

<search name="VendorName" column="VENDNAME" searchOp="like"/>
<search name="Total" column="UNITCOST" searchOp="like"/>
<search name="Begin PO Date" Type="DateTime" column="stadate" searchOp="&gt;=" />
<search name="End PO Date" Type="DateTime" column="enddate"  searchOp="&lt;=" />
<search name="SalesDoc#" column="VNDDOCNM" searchOp="like"/>
<search name="CustomerName" column="Customer" searchOp="like"/>
<search name="PO#" column="Customer_PO_Num" searchOp="like"/>
<search name="DocDate" column="DocDate"  Type="DateTime" searchOp="like"/>

<query addWhere="true">

select
VENDNAME, UNITCOST, stadate, enddate, VNDDOCNM, Customer, Customer_PO_Num, DocDate, Item_Number, PO_Number, PO_Type
from(

select vendinv.VENDNAME,    
rcpt.UNITCOST,     
stadate=CONVERT(datetime,receiptdate, 101), enddate=CONVERT(datetime,receiptdate, 101),   
VNDDOCNM,  
soppop.PO_Number,    
PO_Type=dbo.spfpurchaseordertype(pop.potype),
sd.Customer,    
sd.Customer_PO_Num,    
sd.Sales_Doc_Num,    
pop.DOCDATE,    
sd.Item_Number    
from POP30300 as vendinv (NOLOCK)    
left join POP30310 as rcpt (NOLOCK) on vendinv.POPRCTNM = rcpt.POPRCTNM    
left join POP10100 as pop (NOLOCK) on rcpt.PONUMBER = pop.PONUMBER    
join spvSopToPop as soppop on pop.PONUMBER = soppop.PO_Number and Sales_Doc_Type = 'ORDER' and soppop.PO_Line_Num = rcpt.RCPTLNNM  
left join spvSalesLineItemSearch as sd on soppop.Sales_Doc_Num = sd.Sales_Doc_Num and soppop.Line_Num = sd.Line_Num and sd.Sales_Doc_Type = 'ORDER' 
)as a
/*where*/

</query>

<column name="stadate" Caption="PO Date" Visible="true" BestFit="true" />
<column name="enddate" Visible="false" BestFit="true" />
<column name="PO_Type" Visible="false" BestFit="true" />
</report>

WIP Script Item Number N1000-N9999 Salespad Quick Report


SELECT *
from ( Select 

sli.Sales_Doc_Type,
im.xManufacture,
im.xMachinetype,
im.Item_Number,
im.xSeries,
im.Model_Number,
es.Serial_Num,
im.xOptionsOnMachine,
sli.Sales_Person_ID,
iqty.Avail,
iqty.Onhand,
iqty.Onorder,
im.Notes,
im.Port_Date,
im.xfactoryshipdate,
im.Standard_Cost,
im.Current_Cost,
im.List_Price,
sli.Customer_Name,
sli.Customer_Num,
sli.City,
sli.STATE,
Sli.Sales_Doc_Num, 
sli.Fulfillment_Date,
im.Item_Description,
sli.Source


FROM spvitemmaster as im


LEFT JOIN spvInventory as iqty ON im.Item_Number = iqty. Item_Number and im.Item_Number = iqty.Item_Number
LEFT JOIN spvEquipmentSearch as es on im.Item_number = es. Item_number and im.Item_Number = es.Item_Number
LEFT JOIN spv3SalesLineItem as sli on im.Item_number = sli. Item_number and im.Item_Number = sli.Item_Number

where im.Item_Number between 'N1000' and 'N9999' and (sli.Sales_Doc_Type = 'order' or sli.Sales_Doc_Type is null)

union

Select 

sli.Sales_Doc_Type,
im.xManufacture,
im.xMachinetype,
im.Item_Number,
im.xSeries,
im.Model_Number,
es.Serial_Num,
im.xOptionsOnMachine,
sli.Sales_Person_ID,
iqty.Avail,
iqty.Onhand,
iqty.Onorder,
im.Notes,
im.Port_Date,
im.xfactoryshipdate,
im.Standard_Cost,
im.Current_Cost,
im.List_Price,
sli.Customer_Name,
sli.Customer_Num,
sli.City,
sli.STATE,
Sli.Sales_Doc_Num,
sli.Fulfillment_Date,
im.Item_Description,
sli.Source

FROM spvitemmaster as im

LEFT JOIN spvInventory as iqty ON im.Item_Number = iqty. Item_Number and im.Item_Number = iqty.Item_Number
LEFT JOIN spvEquipmentSearch as es on im.Item_number = es. Item_number and im.Item_Number = es.Item_Number
LEFT JOIN spv3SalesLineItemHistory as sli on im.Item_number = sli. Item_Number and im.Item_Number = sli.Item_Number

where im.Item_Number between 'N1000' and 'N9999' and sli.Source <> 'Void' and (sli.Sales_Doc_Type = 'order' or sli.Sales_Doc_Type is null)
) as a