webi笔记

大于值取整=CEILING(E18/POWER(10,LEN(E18)-1),1)*POWER(10,LEN(E18)-1)


excel=TEXT(INT(C8/86400),"0天")&TEXT(TRUNC(MOD(C8,86400)/3600),"0小时")&TEXT(TRUNC(MOD(C8,3600)/60),"0分")&TEXT(TRUNC(MOD(C8,60)),"0秒")


webi=FormatNumber(ToNumber([111]/86400);"0天")+FormatNumber(Truncate(Mod([111];86400)/3600;0);"0小时")+ FormatNumber(Truncate(Mod([111];3600)/60;0);"0分")+FormatNumber(Truncate(Mod([111];60);0);"0秒")


webi跳转传参="<a href=\"../../opendoc/openDocument.jsp?iDocID=Fhq5s1JCUgAAMiEAAEDZQUUAAFBWvSHw&sIDType=CUID&sType=wid&sOutputFormat=H&sRefresh=N&lsSXSDDPZ="+[销售订单凭证]+"&lsSXSDDPZHH="+[销售订单凭证行号]+"&lsSreportTime="+UserResponse("reportTime")+"&sWindow=Same\" title=\"\" target=\"_blank\" nav=\"doc\">"+[销售订单凭证]+"</a>"




上月：sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON"=to_char(add_months(to_date(@Prompt('reportTime','A',,,Free,Not_Persistent),'yyyymm'),-1),'yyyymm') then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKIMG" else 0 end )
累计：sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON">=substr(@Prompt('reportTime','A',,,Free,Not_Persistent),1,4)||'01' and "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON"<=@Prompt('reportTime','A',,,Free,Not_Persistent) then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKIMG" else 0 end )
同期累计：sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON">=to_char(to_number(substr(@Prompt('reportTime','A',,,Free,Not_Persistent),1,4)||'01')-100) and "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON"<=to_char(to_number(@Prompt('reportTime','A',,,Free,Not_Persistent)-100)) then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKIMG" else 0 end )
同期：sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON"=to_char(to_number(@Prompt('reportTime','A',,,Free,Not_Persistent))-100) then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKIMG" else 0 end )
本期：sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON"=@Prompt('reportTime','A',,,Free,Not_Persistent) then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKIMG" else 0 end )


SELECT
  Table__5."ZYWBK_DESC",
  sum(case when Table__5."FKDAT_MON"='201303' then Table__5."FKIMG" else 0 end ),
  sum(case when Table__5."FKDAT_MON"=to_char(to_number('201303')-100) then Table__5."FKIMG" else 0 end ),
  sum(case when Table__5."FKDAT_MON">=substr('201303',1,4)||'01'  and Table__5."FKDAT_MON"<='201303' then Table__5."FKIMG" else 0 end )
,
  sum(case when Table__5."FKDAT_MON">=to_char(to_number(substr('201303',1,4)||'01')-100)  and Table__5."FKDAT_MON"<=to_char(to_number('201303')-100) then Table__5."FKIMG" else 0 end )
FROM
  "_SYS_BIC"."ycsdprd/ZCV_XSTJ_MON"  Table__5
WHERE
  (
   ( Table__5."FKDAT_MON">=substr(to_char(to_number('201303')-100),1,4)||'01' and Table__5."FKDAT_MON"<='201303'  )
   AND
   Table__5."ZYWBK_DESC"  Is Not Null  
  )
GROUP BY
  Table__5."ZYWBK_DESC"




=If(IsError([来货数]/[PO数量]);"";FormatNumber([来货数]/[PO数量]*100;"#,##0.00"))
=If(IsError([来货数]/[PO数量]);"";formatNumber([来货数]/[PO数量]*100;"#,##0.00"))
('PLACEHOLDER' = ('$$KSSJ$$',@Prompt('时间','A',,,free)))


BEGIN 
  var_out = 
     SELECT A."MBLNR",--物料凭证编号
            A."BWART", 
            A."EBELN",
            A."SHKZG",
            A."SOBKZ",
            A."MAT_KDAUF",--销售订单号为空的为返修机
            B."BUDAT", 
            SUBSTRING(B."BUDAT",1,6) "BUDAT_MON",
            SUBSTRING(B."BUDAT",1,4) "BUDAT_YEAR",
            A."AUFNR",
            D."DISPO",
            E."DSNAM",
            A."LGORT",
            F."LGOBE",
            A."WERKS",
            H."DAUAT",
            G."NAME1" AS WERKS_NAME1,
            G."NAME1" AS WERKS_NAME2, 
            C."MATNR", 
            C."MAKTX", 
            C."SPART", 
            --REPLACE(C."SPART_DESC",'系列发动机','') SPART_DESC, 
            C."SPART_DESC",
            C."ZCPDH", 
            C."LAEDA", 
            C."ZPTCJ", 
            C."ZNJZS", 
            C."ZDQMS", 
            C."ZGLZS", 
            C."ZYT1_CODE", 
            C."ZYT1_DESC", 
            C."ZYT2_CODE", 
            C."ZYT2_DESC", 
            C."ZYT3_CODE", 
            C."ZYT3_DESC", 
            C."ZCPZXL", 
            C."ZJZPZ", 
            C."ERNAM", 
            C."ZGJ", 
            C."ZJBX", 
            C."ZCPXH", 
            C."ZCPXHJ", 
            C."ZXC", 
            C."ZYWBK_CODE", 
            C."ZYWBK_DESC", 
            C."ZPL", 
            C."ZGL", 
            C."ZDOC", 
            C."ZXPXMH", 
            C."ZXPXM_DESC", 
            C."ZHHDL", 
            C."ERSDA", 
            C."ZCPXL", 
            C."ZPDF", 
            C."ZEGR", 
            C."ZZTJ", 
            C."AENAM", 
            C."ZQGS", 
            C."ZPTCX", 
            C."ZPOC", 
            C."ZJGDH", 
            C."ZJGDH_DESC", 
            C."ZJQFS", 
            C."ZJQFS_DESC",
            C."ZDKDL", 
            C."ZPF_CODE", 
            C."ZPF_DESC", 
            C."ZQTJ", 
            C."ZSCR", 
            C."ZYYXTCODE", 
            C."ZYYXT_DESC", 
            C."ZXPJD", 
            C."ZZCFL_CODE", 
            C."ZZCFL_DESC", 
            C."ZJGFL", 
            C."MTART",
            A."MENGE",
            CASE WHEN A."SHKZG" = 'H' THEN -1 * A."MENGE" ELSE A."MENGE" END AS SCSL,
            C."MATKL"
       FROM "YCERP"."MSEG" A
            INNER JOIN
            "YCERP"."MKPF" B
            ON A."MBLNR" = B."MBLNR"
            AND A."MJAHR" = B."MJAHR"
            INNER JOIN
            "_SYS_BIC"."ycsdprd/ZAT_MARA" C
            ON A.MATNR = C.MATNR
            LEFT JOIN
            "YCERP"."AFKO" D
            ON A."AUFNR" = D."AUFNR"
            left JOIN YCERP.AFPO H
            on a.AUFNR=h.AUFNR             
            LEFT JOIN
            "YCERP"."T024D" E
            ON D.DISPO = E.DISPO
            LEFT JOIN
            (select * from "YCERP"."T001L"  where "WERKS"='4000')F
            ON A."LGORT" = F."LGORT"
            LEFT JOIN
            "YCERP"."T001W" G
            ON A."WERKS" = G."WERKS"
      WHERE
       -- A."BWART" IN ('301','302')
       -- AND A."WERKS" IN ('1000','4000')
        (( A."BWART"='301' and   A."WERKS" ='4000' AND SHKZG='S')
        OR
        (  A."BWART"='302' and   A."WERKS" ='4000' AND SHKZG='H'))
        -- AND A."EBELN" = ''
        AND C."MTART" = 'FERT'
        --AND A."LGORT" NOT IN ('3300','3400')
        ; 


END /********* End Procedure Script ************/








































select '1' AS BJ, '已关帐' AS flag from "YCERP"."T001B"
  where mandt='800'
  and mkoar='S'
  and bukrs='1000'
  and 201305  not between frye1||substr(frpe1,2,2) and toye2||substr(tope2,2,2)






SELECT
  Table__35."MONTH",
  Table__35."GSMC",
  Table__35."BKMC2",
  sum(case when Table__35."YEAR_MONTH">=substr('201212',1,4)||'01' 
and Table__35."YEAR_MONTH"<='201212'  then  Table__35."BQXL"  else 0 end )
FROM
  "YCERP"."SCXSTJAYT"  Table__35
WHERE
  ( Table__35."YEAR_MONTH">=substr('201212',1,4)||'01'   
and  Table__35."YEAR_MONTH"<='201212'  )
GROUP BY
  Table__35."MONTH", 
  Table__35."GSMC", 
  Table__35."BKMC2"
TO_CHAR(ADD_MONTHS(TO_DATE(YEAR_MONTH,'YYYYMM'),1),'YYYYMM')  AS FKDAT_MON_SY,
TO_CHAR(ADD_MONTHS(TO_DATE(YEAR_MONTH,'YYYYMM'),1),'YYYY') AS  FKDAT_YEAR_SY




SUM(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON">=
to_char(to_number(substr(@Prompt('reportTime','A',,,Free),1,4)||'01')-200) and
 "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKDAT_MON"<=@Prompt('reportTime','A',,,Free) 
then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_SCRIPT"."FKIMG" else 0 end )




sum(case when "YCERP"."SCXSTJAYT"."YEAR_MONTH"=
to_char(add_months(to_date(@Prompt('reportTime','A',,,Free)||'01','yyyymmdd'),-1),
'yyyymm') then "YCERP"."SCXSTJAYT"."BQXL" else 0 end )




sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_MON"."FKDAT_MON"=
to_char(add_months(to_date(@Prompt('reportTime','A',,,Free,Not_Persistent),'yyyymm'),-1),'yyyymm') 
then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_MON"."FPJE_HS" else 0 end )


=FormatNumber([同期值];"#,##0.00[%]'%'")




UserResponse("reportTime") 传参数




问题
1:@Prompt('reportTime','A',,,Free,Not_Persistent)和@Prompt('reportTime','A',,,Free)有什么区别。
2: sum(case when "_SYS_BIC"."ycsdprd/ZCV_XSTJ_MON"."FKDAT_MON"=to_char(add_months(to_date(@Prompt('reportTime','A',,,Free,Not_Persistent),'yyyymm'),-1),'yyyymm') then "_SYS_BIC"."ycsdprd/ZCV_XSTJ_MON"."FPJE_HS" else 0 end )和
TO_CHAR(ADD_MONTHS(TO_DATE(YEAR_MONTH,'YYYYMM'),1),'YYYYMM')  AS FKDAT_MON_SY,
TO_CHAR(ADD_MONTHS(TO_DATE(YEAR_MONTH,'YYYYMM'),1),'YYYY') AS  FKDAT_YEAR_SY是什么意思？


('PLACEHOLDER' = ('$$KSSJ$$',@Prompt('开始时间','A',,,free)), 'PLACEHOLDER' = ('$$JSSJ$$',@Prompt('结束时间','A',,,free) ))