Reporting Service : CIE分佈圖

X1屬性

X1屬性

X軸屬性

Y1屬性

CIEY屬性

數列標籤屬性

SQL :
select cie_rank,pri,x1,y1,null ciey,
       row_number() over(partition by x1,y1 order by cie_rank) xy_rownm
  from (
select distinct
       c.cie_rank,1 pri,
       c.x1,c.y1
  from m1_mch_ft_header b,system.m1_ft_bin_spec c
 where b.test_prog = c.prod_name
   and b.mpcrdno in (:aufnr)
   and x1 is not null
   and c.cie_rank not in ('CIE-H','CIE-L','CIE-LEFT','CIE-RIGHT','Iv-H','Iv-L','Vf-H','Vf-L')
   and x1 != 0
 union all
select distinct
       c.cie_rank,2 pri,
       c.x2,c.y2
  from m1_mch_ft_header b,system.m1_ft_bin_spec c
 where b.test_prog = c.prod_name
   and b.mpcrdno in (:aufnr)
   and x1 is not null
   and c.cie_rank not in ('CIE-H','CIE-L','CIE-LEFT','CIE-RIGHT','Iv-H','Iv-L','Vf-H','Vf-L')
   and x1 != 0
 union all
select distinct
       c.cie_rank,3 pri,
       c.x3,c.y3
  from m1_mch_ft_header b,system.m1_ft_bin_spec c
 where b.test_prog = c.prod_name
   and b.mpcrdno in (:aufnr)
   and x1 is not null
   and c.cie_rank not in ('CIE-H','CIE-L','CIE-LEFT','CIE-RIGHT','Iv-H','Iv-L','Vf-H','Vf-L')
   and x1 != 0
 union all
select distinct
       c.cie_rank,4 pri,
       c.x4,c.y4
  from m1_mch_ft_header b,system.m1_ft_bin_spec c
 where b.test_prog = c.prod_name
   and b.mpcrdno in (:aufnr)
   and x1 is not null
   and c.cie_rank not in ('CIE-H','CIE-L','CIE-LEFT','CIE-RIGHT','Iv-H','Iv-L','Vf-H','Vf-L')
   and x1 != 0
 union all
select distinct
       c.cie_rank,5 pri,
       c.x1+0.00001,c.y1+0.00001
  from m1_mch_ft_header b,system.m1_ft_bin_spec c
 where b.test_prog = c.prod_name
   and b.mpcrdno in (:aufnr)
   and x1 is not null
   and c.cie_rank not in ('CIE-H','CIE-L','CIE-LEFT','CIE-RIGHT','Iv-H','Iv-L','Vf-H','Vf-L')
   and x1 != 0
   )
 union all
select c.cie_rank,null pri,ciex x1,null y1,ciey,null xy_rownm
  from m1_mch_ft_line a,m1_mch_ft_header b,system.m1_ft_bin_spec c
 where a.header = b.header
   and b.test_prog = c.prod_name
   and a.bin = c.bin_zone_id
   and b.mpcrdno in (:aufnr)
   and ciex is not null
   and ciex != 0
   and c.cie_rank not in ('O/S','BIN1','Ir')