PB 数据窗口采用存储过程的步骤和PB报表开发步骤

1.首先定义存储过程

USE [EBSDB]
GO

/****** Object:  StoredProcedure [dbo].[SP_INV_MaterialReceiveReport2]    Script Date: 10/17/2018 17:00:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO










ALTER PROCEDURE [dbo].[SP_INV_MaterialReceiveReport2] 
    @db_center	 nvarchar(100),
    @Receive_sn  nvarchar(100)
AS

DECLARE @receivereport2 TABLE
  ( 
    DB_Center  nvarchar(100),
    Receiving_SN nvarchar(100),
    Warehouse nvarchar(500) ,
    Receive_Employee nvarchar(500) ,
    Receive_Date datetime ,
    PO_Code nvarchar(100),
    Currency_Code nvarchar(100),
    Total_Price decimal(30, 2),
    Vendor  nvarchar(500) ,
    Material_Code nvarchar(100),
    Material_Desc  nvarchar(500),
    Quantity1  decimal(30, 2),
    Quantity2  decimal(30, 2),
    Quantity3  decimal(30, 2),
    Quantity4  decimal(30, 2),
    UOM nvarchar(100) ,
    Unit_Price  decimal(30, 2),
	Total_Price2 decimal(30, 2)
  );
  
BEGIN		
	
	INSERT INTO @receivereport2(DB_Center,Receiving_SN,Warehouse,Receive_Employee,Receive_Date,PO_Code,Currency_Code,Total_Price,
				Vendor,Material_Code,Material_Desc,Quantity1,Quantity2,Quantity3,Quantity4,UOM,Unit_Price,Total_Price2)
	
	
	
	select a.DB_Center,c.Receiving_SN, b.Warehouse_Name,a.Receiving_By,a.Receiving_Date,c.PO_Code,c.Currency_Code,isnull(c.Total_Amount,0),
			(SELECT vendor.Vendor_Name FROM BAS_Vendor vendor,PUR_PO po where po.PO_SN=c.PO_SN and po.Supplier_Code=vendor.Vendor_Code) Vendor,c.Material_Code,c.Material_Name,c.Quantity Quantity1,			
		isnull((select sum(Quantity) from INV_IssueRequireDet INV_IssueRequireDet 
         where INV_IssueRequireDet.DB_Center ='EBS'
         AND c.PO_Code=INV_IssueRequireDet.PO_SN
         and c.Material_Code=INV_IssueRequireDet.Material_Code),0) Quantity2,
         isnull(c.Quantity -(select sum(Quantity) from INV_IssueRequireDet INV_IssueRequireDet 
         where INV_IssueRequireDet.DB_Center ='EBS'
         AND c.PO_Code=INV_IssueRequireDet.PO_SN
         and c.Material_Code=INV_IssueRequireDet.Material_Code),0) Quantity3,
         isnull(c.Quantity -(select sum(Quantity) from INV_IssueRequireDet INV_IssueRequireDet 
         where INV_IssueRequireDet.DB_Center ='EBS'
         AND c.PO_Code=INV_IssueRequireDet.PO_SN
         and c.Material_Code=INV_IssueRequireDet.Material_Code),0) Quantity4c,
         (SELECT UOM FROM DBO.BAS_UOM UOM WHERE UOM.UOM_SN=C.UOM)UOM,isnull(c.Unit_Price,0)Unit_Price,isnull(c.Total_Amount,0) Total_Price2			
	FROM INV_Receive a,BAS_Warehouse b,INV_ReceiveDet c
	where a.Warehouse_SN=b.Warehouse_SN and a.Receiving_SN=c.Receiving_SN

    
	SELECT DISTINCT DB_Center,Receiving_SN,Warehouse,Receive_Employee,Receive_Date,PO_Code,Currency_Code,Total_Price,
				Vendor,Material_Code,Material_Desc,Quantity1,Quantity2,Quantity3,Quantity4,UOM,Unit_Price,Total_Price2
     FROM @receivereport2  
	WHERE [email protected]_center
	and [email protected]_sn
	
END

为了快速开发就写的并不美观，将就看下。

PB界面 file>>new>datawindow>>freeform 选*格式便于调整报表格式。

数据源选择 stored procedure即可

PB 数据窗口采用存储过程的步骤和PB报表开发步骤