SIMATIC BATCH报表开发说明

SIMATIC BATCH报表开发说明

开始前准备

• 在batch项目设置中找到报表服务主机：

• 在报表服务器上打开报表配置工具

默认连接：

• ie打开报表管理地址：

• 找到报表列表，如果找不到点击右上角详细视图：

以“_batch”命名结尾的报表将会在batch打印预览中显示！

• 点击报表右侧小三角，在菜单中选择最后一项，即可在report builder中编辑报表：

在report builder中自定义报表

新建报表

点击左上角按钮，新建空白报表，命名以“_batch”结尾，CTRL+S保存位置如下：

这样我们在batch里打印预览就可以选择我们保存的报表，当然目前为空白；

添加数据源

• 右键数据源文件夹，点击添加数据源，选择batch默认的数据源：

添加参数

右键参数文件夹，可以添加参数：

在batch中每次打印报表会向模板传递一些默认参数，目前我已知如下，这三个参数必须添加：

schemaname:文本类型，传递过来的数据名，针对ph服务器上存储报表时，该参数很关键

objectguid:文本类型，batch打印报表时，传入订单批唯一id

lcid :整数类型，语言id

添加后如下：

添加数据集

右键数据集，添加：

常用数据集：

订单基础信息

DECLARE @stmt nvarchar(max)
              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
             SET              @stmt = '
              SELECT     DISTINCT bat.Name, bat.Description, bat.OBJID, bat.State, bat.ExState,
              cast(cast(bat.Quantity as DECIMAL(15,6)) as float) as Quantity,
              cast(cast(bat.ActQuantity as DECIMAL(15,6)) as float) as ActQuantity,
              bat.FormulaName, bat.FormulaCategoryName, bat.FormulaVersion, bat.MRecipeName,
              bat.MRecipeVersion,units.UnitName,
              (case
              when bat.PlanStart > bat.PlanEnd then null
                else [' + @schemaname + '].[batch_Seconds2Duration](DATEDIFF(second,  bat.PlanStart, bat.PlanEnd), '+STR(@lcid)+')
               end)    as DurationPlanned,              
              CONVERT(varchar(19), bat.ActStart, 20) as [ActStart],
              bat.[ActEnd] as [ActEnd],
              (case (bat.ActEnd)
              when null then null
              else [' + @schemaname + '].[batch_Seconds2Duration](DATEDIFF(second,  bat.ActStart, bat.ActEnd), '+STR(@lcid)+')
              end) as [DurationActual],
              ord.name as order_name, bat.ProductCode as Code,
              ordercat.name as ordercat_name,
              bat.ProductName AS mat_name, uom.Name AS uom_name,
              GETUTCDATE() as PrintTime
              FROM 
              [' + @schemaname + '].vBatch as bat left outer join [' + @schemaname + '].vUoM as uom
              ON (uom.PCellGUID = bat.PCellGUID and uom.OBJID = bat.UOMId and uom.OTID = 27)
              LEFT OUTER JOIN
              [' + @schemaname + '].[vOrder] as ord ON bat.ROOTGUID = ord.OGUID
              left join  [' + @schemaname + '].[vRecipeUnitProcedure] as units on  units.ROOTGUID = bat.OGUID
              LEFT OUTER JOIN  [' + @schemaname + '].[vOrderCategory] as ordercat on (ord.ROOTGUID = ordercat.OGUID)
              WHERE     (bat.OGUID = ''' + @objectguid + ''')'
              EXEC (@stmt)

单元操作的开始和结束统计

DECLARE @stmt nvarchar(max)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 SET  @stmt = ' select t1.[Name],CONVERT(varchar(19), t1.[Start], 20) as [Start],CONVERT(varchar(19), t1.[End], 20) as [End],t1.OTID,t1.Description,t1.DataDescription  from [' + @schemaname + '].vRecipeElement t1  
  where t1.ROOTGUID=''' + @objectguid + ''''

 EXEC (@stmt)

输入输出物料或者参数统计

DECLARE @stmt nvarchar(max)
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET @stmt = ' select  CONVERT(varchar(19), t0.[Start], 20) as [Start],CONVERT(varchar(19), t0.[End], 20) as [End],t0.name as elname,t1.sp_matname,t1.name,t1.sp_float,t1.av_float,t1.UoMName,t1.DataTypeId,t1.POBJID,t1.P2OBJID,
  t1.OBJID from 
  [' + @schemaname + '].vRecipeElementOnline t0 left join [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vOnlineParameterValue t1 
   on t0.OBJID = t1.POBJID and t0.ROOTGUID = t1.ROOTGUID where t0.ROOTGUID=''' + @objectguid + ''''
EXEC (@stmt)

归档数据查询

DECLARE @stmt nvarchar(max) 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
declare @start varchar(100) 
declare @end varchar(100) 
select @start=CONVERT(varchar(100),t1.Start,0),@end=CONVERT(varchar(100),t1.[End],0) from [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vRecipeElementOnline T1 WHERE T1.Name = 'TMC3' 
SET @stmt = ' 
select CONVERT(varchar(19), t1.TagValueTime, 20) as TagValueTime,t1.TagValue as tt_v,t2.TagValue as pt_v from ( select t1.TagName,max(t1.TagValue) as TagValue,t1.TagValueTime from ['+@schemaname+'].vOnlineTagExtended t1 where t1.ROOTGUID='''+@objectguid+''' 
and DateName(minute,t1.TagValueTime)%5=0 and ( t1.TagValueTime between '''+@start+''' and '''+@end+''' ) and t1.TagName=''OT_SP'' group by t1.TagValueTime,t1.TagName) t1 
left join 
(select t1.TagName,max(t1.TagValue) as TagValue,t1.TagValueTime from [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vOnlineTagExtended t1 where t1.ROOTGUID='''+@objectguid+''' 
and ( t1.TagValueTime between '''+@start+''' and '''+@end+''' ) and t1.TagName=''Mat'' group by t1.TagValueTime,t1.TagName) t2 on t1.TagValueTime = t2.TagValueTime' 
EXEC (@stmt)

以上语句基本可以满足常用的需求！

表格绑定数据

右键报表空白处，插入表，然后点击表右键，设置属性：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-anaKboP6-1654667653391)(file://C:\Users\ThinkPad\AppData\Roaming\marktext\images\2022-04-10-15-35-45-image.png?msec=1654667644406)]

设置数据集：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HgbibmOL-1654667653392)(file://C:\Users\ThinkPad\AppData\Roaming\marktext\images\2022-04-10-15-36-39-image.png?msec=1654667644406)]

设置好数据集后，就可以在表格里选择相应字段了；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zEiazEKB-1654667653392)(file://C:\Users\ThinkPad\AppData\Roaming\marktext\images\2022-04-10-15-38-42-image.png?msec=1654667644406)]

常用batch表视图说明：

• batch批订单主表

  select * from [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vBatch t1
  where t1.OGUID='036D6A69-70AC-4F38-85CF-453D2F0521E4';

• 单元或操作的开始结束时间

   select * from [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vRecipeElementOnline; 

• 归档数据查询

    select t1.TagName,t1.TagValue,t1.TagValueTime from [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vOnlineTagExtended t1 where
    t1.ROOTGUID='036D6A69-70AC-4F38-85CF-453D2F0521E4' and t1.TagName='OT_SP'

• 归档点位

  select * from  [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].vOfflineTag

• 单元名称

 select * from  [SIMATIC_BATCH_SB9_2763-9351-49_V9_00_00].[vRecipeUnitProcedure] t 

最后

不明白不清楚的请联系我：

wx：dengchunyi2
tel:15071747425