Stimulsoft Reports报表组件--关于主表和子表打印设计

1、创建主表和子表

2、建立主表和子表的关系：
3、主表和子表自定义sql语句

主表SQL语句：select B.BillNo,B.WorkerID,B.WorkOrderID,ISNULL(B.Remark+';','')+T.Remark as Remarks,B.CreateDate,
	C.WorkerName,F.WarehouseName
	,B.LogisticsCompany,B.LogisticsBillNo,B.TotalMoney,H.Text AS SalesObject,

 Z.Name ReceiverPerson ,Z.Mobile ReceiverPhone ,ISNULL( B.ReceiverAddress,P.DetailAddress) ReceiverAddress
 from [wsi_salesOut] B 
 left join [sms_worker] C on B.WorkerID=C.WorkerCode 
 left join sms_workorder T on T.WorkOrderID= B.WorkOrderID
 left join sms_Consumer Z on Z.ConsumerID= T.ConsumerID
 left join sys_warehouse F on B.Warehouse= F.WarehouseCode
 left join sms_ConsumerAddress P on P.ID= T.consumeraddressid
 left join sys_code H on B.SalesObject=H.Code
 where B.BillNo in  (
SELECT TempB.Name
	FROM(
select Name=CONVERT(XML,'<root><v>'+replace(RTRIM(LTRIM(@BillNo)),',','</v><v>')+'</v></root>')
) TempA 
OUTER APPLY(
SELECT Name = TempC.v.value('.','NVARCHAR(MAX)') 
	FROM TempA.Name.nodes('/root/v') TempC(v)
) TempB
)

子表SQL语句：

select top 100 ROW_NUMBER() over(order by A.RowId) as RowNum, A.*,
D.MaterialName,D.CompanyCode,E.Text as UnitName,
L.SumNum,L.SumMoney
 from [wsi_salesOutGoods] A 
 left join [sms_material] D on A.GoodNo=D.MaterialCode
 left join [sys_code] E on A.Unit=E.Code
left join (select SUM(A.Num) as SumNum,SUM(A.Num*A.UnitPrice) as SumMoney,BillNo from wsi_salesOutGoods A where
BillNo IN (SELECT TempB.Name
	FROM(
select Name=CONVERT(XML,'<root><v>'+replace(RTRIM(LTRIM(@BillNo)),',','</v><v>')+'</v></root>')
) TempA 
OUTER APPLY(
SELECT Name = TempC.v.value('.','NVARCHAR(MAX)') 
	FROM TempA.Name.nodes('/root/v') TempC(v)
) TempB
)
 group by BillNo ) L
 on A.BillNo=L.BillNo 
 where A.BillNo in  (
SELECT TempB.Name
	FROM(
select Name=CONVERT(XML,'<root><v>'+replace(RTRIM(LTRIM(@BillNo)),',','</v><v>')+'</v></root>')
) TempA 
OUTER APPLY(
SELECT Name = TempC.v.value('.','NVARCHAR(MAX)') 
	FROM TempA.Name.nodes('/root/v') TempC(v)
) TempB
)

4、报表的效果