欧阳PJ

欧阳丕军
第 642 位会员
注册于 2019-01-13 14:17:17
活跃于 2019-01-15 08:54:00


  • 公司
  • 城市
学习PowerBI使用
专栏文章
没有任何数据~~
最新评论
  • Power BI 高级设置使用存储过程,可以展示数据,加载显示语法错误 at 2019-01-14 08:31:07

    @焦棚子 谢谢,你说的简写是指哪些,比如?

  • Power BI 高级设置使用存储过程,可以展示数据,加载显示语法错误 at 2019-01-13 17:16:00

    最新进展,将directquery改成导入,可以成功,这是什么原因呢?而且我需要用到计划刷新这个功能,因此该选择directquery这个选项的。有知道的吗
    file
    file

  • Power BI 高级设置使用存储过程,可以展示数据,加载显示语法错误 at 2019-01-13 16:00:42

    附上sql server数据库的sql语句,有点长:
    USE [lmgd]
    GO
    /** Object: StoredProcedure [dbo].[lxl_icmo] Script Date: 01/10/2019 08:39:09 **/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[lxl_icmo]
    AS
    BEGIN

    set nocount on

    create table #wip
    (
    fmodel varchar(500),
    fhwip float,
    fmdwip float,
    fpwip float,
    fqwip float,--增加切割wip
    fswip float,
    fmzwip float
    )
    insert into #wip
    select left(mo.产品规格,4),
    SUM(a.转焊线数量)-SUM(hx.转molding数)'焊线WIP',
    SUM(hx.转molding数)-SUM(md.转喷墨数)'Molding_WIP',SUM(md.转喷墨数)-SUM(pm.喷墨生产数)'喷墨_WIP',
    SUM(pm.喷墨生产数)-SUM(pm.切割累计)'切割_WIP',--增加切割wip
    SUM(pm.喷墨生产数)-SUM(pm.SMT转料累计)-(SUM(pm.喷墨生产数)-SUM(pm.切割累计))'SMT_WIP',null
    from COB生产制令信息 mo
    left join cob固晶生产数和报废数汇总 gj on mo.生产制令号= gj.制令
    left join cob焊线生产数和转molding数和报废数 hx on mo.生产制令号=hx.制令
    left join M生产数和转喷墨数和报废数 md on mo.生产制令号 = md.制令号
    left join AOI生产数量和转焊线数报废数汇总 a on mo.生产制令号 = a.制令
    left join cob喷墨数和SMT生产数和报废数 pm on mo.生产制令号 = pm.制令
    left join cob模组数量汇总和报废数 mz on mo.生产制令号 = mz.制令
    left join cob箱体数量汇总 xt on mo.生产制令号 = xt.制令
    where mo.结案日期 is null
    group by left(mo.产品规格,4)
    --SELECT left(gj.型号,4) ,sum(gj.转焊线数量)-sum(hx.焊线生产数量)'焊线WIP',sum(hx.转Molding良品累计)-sum(md.当班产量)'molding-WIP',
    --sum(md.当日转喷墨数量)-sum(dyb.喷墨片)'喷墨WIP',sum(dyb.切割片)-sum(dyb.SMT生产数)'SMT-WIP',
    --case WHEN left(gj.型号,4) = 'M1.5' then sum(dyb.转料SMT转料累计)/12-sum(mz.模组生产数)
    --else sum(dyb.转料SMT转料累计)/10-sum(mz.模组生产数) end '模组WIP'
    --FROM 生产日报AOI_明细 gj
    --inner join 生产日报焊线_明细 hx on gj.日期 = hx.日期 and gj.型号 = hx.型号
    --inner join 生产日报Molding_明细 md on gj.日期 = md.日期 and gj.型号 = md.产品型号
    --inner join 生产日报后站单元板_明细 dyb on gj.日期 = dyb.日期 and gj.型号 = dyb.型号
    --left join 生产日报后站模组_明细 mz on gj.日期 = mz.日期 and gj.型号 = mz.型号
    --left join COB生产制令信息 zl on zl.生产制令号 = gj.制令
    --where zl.结案日期 is null
    --group by left(gj.型号,4)
    ----总
    --select gj.日期,left(gj.型号,4) ,sum(gj.转焊线数量)'固晶产量',sum(gj.直通率)'固晶直通率',sum(gj.不良点数)/sum(gj.总点数)'固晶维修率PPM',
    --sum(hx.转Molding良品累计)'焊线产量',sum(hx.直通率)'焊线直通率',SUM(hx.不良点数)/SUM(hx.总点数)'焊线维修率PPM',
    --sum(md.当日转喷墨数量)'molding产量',SUM(md.[直通率≥90])'molding直通率',SUM(md.[待维修点数≤3颗板])/SUM(md.当班产量)/6480*1000000'molding维修率',
    --sum(dyb.喷墨片)'喷墨产量',null'喷墨直通率',null'喷墨维修率PPM',SUM(dyb.良品)'SMT产量',SUM(dyb.直通率)'SMT直通率',null'SMT维修率PPM',
    --sum(mz.模组生产数)'模组产量',null'模组直通率',null'模组维修率PPM'
    --from 生产日报AOI_明细 gj
    --left join 生产日报焊线_明细 hx on gj.日期 = hx.日期 and gj.型号 = hx.型号
    --left join 生产日报Molding_明细 md on gj.日期 = md.日期 and gj.型号 = md.产品型号
    --left join 生产日报后站单元板_明细 dyb on gj.日期 = dyb.日期 and gj.型号 = dyb.型号
    --left join 生产日报后站模组_明细 mz on gj.日期 = mz.日期 and gj.型号 = mz.型号
    --inner join COB生产制令信息 zl on zl.生产制令号 = gj.制令
    --where zl.结案日期 is null
    --group by gj.日期,left(gj.型号,4)
    --ORDER BY gj.日期 desc

    --固晶产量
    create table #gjcl
    (
    fdate datetime,
    fmodel varchar(500),
    fqty float,
    )
    insert into #gjcl
    select gjcl.日期,left(gjcl.型号,4) ,sum(gjcl.生产完成数量)'固晶产量'
    from 生产日报固晶_明细 gjcl
    inner join COB生产制令信息 zl on zl.生产制令号 = gjcl.制令
    where zl.结案日期 is null
    group by gjcl.日期,left(gjcl.型号,4)
    ORDER BY gjcl.日期 desc

    --固晶直通与维修

    create table #gjzt
    (
    fdate datetime,
    fmodel varchar(500),
    fztl float,
    fwxl float
    )
    insert into #gjzt
    select gj.日期,left(gj.型号,4),
    case when sum(gj.生产数量) is null then 0
    when sum(gj.生产数量) = 0 then 0
    else sum(gj.无坏板数)/sum(gj.生产数量) end '固晶直通率',

    case when left(gj.型号,4)= 'M1.2'
    then

    case when sum(gj.生产数量) IS null then 0
    when sum(gj.生产数量) = 0 then 0
    else sum(gj.不良点数)*1000000/sum(gj.生产数量)/6480 end

    when left(gj.型号,4)= 'M1.5'
    then

    case when sum(gj.生产数量) IS null then 0
    when sum(gj.生产数量) = 0 then 0
    else sum(gj.不良点数)*1000000/sum(gj.生产数量)/3456 end

    else

    case when sum(gj.生产数量) IS null then 0
    when sum(gj.生产数量) = 0 then 0
    else sum(gj.不良点数)*1000000/sum(gj.生产数量)/2880 end

    end
    '固晶维修率'

    from 生产日报AOI_明细 gj
    inner join COB生产制令信息 zl on zl.生产制令号 = gj.制令
    where zl.结案日期 is null
    group by gj.日期,left(gj.型号,4)
    ORDER BY gj.日期 desc
    create table #gj
    (
    fdate datetime,
    fmodel varchar(500),
    fqty float,
    fztl float,
    fwxl float
    )
    insert into #gj
    select ISNULL(#gjcl.fdate,#gjzt.fdate),ISNULL(#gjcl.fmodel,#gjzt.fmodel),#gjcl.fqty,#gjzt.fztl,#gjzt.fwxl from #gjcl
    full outer join #gjzt on #gjcl.fdate = #gjzt.fdate and #gjcl.fmodel = #gjzt.fmodel

    --焊线
    create table #hx
    (
    fdate datetime,
    fmodel varchar(500),
    fqty float,
    fztl float,
    fwxl float
    )
    insert into #hx
    select hx.日期,left(hx.型号,4)'型号',
    sum(hx.焊线生产数量)'焊线产量',
    case when sum(hx.焊线生产数量) is null then 0
    when sum(hx.焊线生产数量) = 0 then 0
    else sum(hx.一次点亮)/sum(hx.焊线生产数量) end'焊线直通率',

    --case when sum(hx.焊线生产数量) is null then 0
    -- when sum(hx.焊线生产数量) = 0 then 0
    --else SUM(hx.不良点数)/SUM(hx.焊线生产数量) end'焊线维修率PPM'

    case when left(hx.型号,4)= 'M1.2'
    then

    case when sum(hx.焊线生产数量) IS null then 0
    when sum(hx.焊线生产数量) = 0 then 0
    else SUM(hx.不良点数)*1000000/SUM(hx.焊线生产数量)/6480 end

    when left(hx.型号,4)= 'M1.5'
    then

    case when sum(hx.焊线生产数量) IS null then 0
    when sum(hx.焊线生产数量) = 0 then 0
    else SUM(hx.不良点数)*1000000/SUM(hx.焊线生产数量)/3456 end

    else

    case when sum(hx.焊线生产数量) IS null then 0
    when sum(hx.焊线生产数量) = 0 then 0
    else SUM(hx.不良点数)*1000000/SUM(hx.焊线生产数量)/2880 end

    end
    '焊线维修率'

    from 生产日报焊线_明细 hx
    inner join COB生产制令信息 zl on zl.生产制令号 = hx.制令
    where zl.结案日期 is null
    group by hx.日期,left(hx.型号,4)
    ORDER BY hx.日期 desc

    --molding
    create table #md
    (
    fdate datetime,
    fmodel varchar(500),
    fqty float,
    fztl float,
    fwxl float
    )
    insert into #md
    select md.日期,left(md.产品型号,4)'型号',
    sum(md.当班产量)'molding产量',
    case when sum(md.当班产量) is null then 0
    when sum(md.当班产量) = 0 then 0
    else 1-sum(md.报废板数无法维修)/sum(md.当班产量) end'molding直通率',

    case when left(md.产品型号,4)= 'M1.2'
    then

    case when SUM(md.当班产量) IS null then 0
    when SUM(md.当班产量) = 0 then 0
    else SUM(md.[待维修点数≤3颗板])*1000000/SUM(md.当班产量)/6480 end

    when left(md.产品型号,4)= 'M1.5'
    then

    case when SUM(md.当班产量) IS null then 0
    when SUM(md.当班产量) = 0 then 0
    else SUM(md.[待维修点数≤3颗板])*1000000/SUM(md.当班产量)/3456 end

    else

    case when SUM(md.当班产量) IS null then 0
    when SUM(md.当班产量) = 0 then 0
    else SUM(md.[待维修点数≤3颗板])*1000000/SUM(md.当班产量)/2880 end

    end
    'molding维修率'
    from 生产日报Molding_明细 md
    inner join COB生产制令信息 zl on zl.生产制令号 = md.制令号
    where zl.结案日期 is null
    group by md.日期,left(md.产品型号,4)
    ORDER BY md.日期 desc

    --喷墨/smt
    create table #dyb
    (
    fdate datetime,
    fmodel varchar(500),
    fqty float,
    fsqty float,
    fqgqty float,--增加切割产量定义
    fsztl float
    )
    insert into #dyb
    select dyb.日期,left(dyb.型号,4) ,
    sum(dyb.喷墨片)'喷墨产量',
    SUM(dyb.SMT生产数)'SMT产量',
    --增加切割产量 2018-12-27
    SUM(dyb.切割片)'切割产量',
    case when SUM(dyb.SMT生产数) is null then 0
    when SUM(dyb.SMT生产数) = 0 then 0
    else sum(dyb.良品)/SUM(dyb.SMT生产数) end'SMT直通率'

    from 生产日报后站单元板_明细 dyb
    inner join COB生产制令信息 zl on zl.生产制令号 = dyb.制令
    where zl.结案日期 is null
    group by dyb.日期,left(dyb.型号,4)
    ORDER BY dyb.日期 desc

    --模组
    create table #mz
    (
    fdate datetime,
    fmodel varchar(500),
    fqty float,
    )
    insert into #mz
    select mz.日期,left(mz.型号,4)'型号',
    sum(mz.模组生产数)'模组产量'
    from 生产日报后站模组_明细 mz
    inner join COB生产制令信息 zl on zl.生产制令号 = mz.制令
    where zl.结案日期 is null
    group by mz.日期,left(mz.型号,4)
    ORDER BY mz.日期 desc

    select
    ISNULL(ISNULL(ISNULL(ISNULL(#gj.fdate,#hx.fdate),#md.fdate),#dyb.fdate),#mz.fdate)'日期',
    ISNULL(ISNULL(ISNULL(ISNULL(#gj.fmodel,#hx.fmodel),#md.fmodel),#dyb.fmodel),#mz.fmodel)'型号',

    gj.fqty'固晶产量',null'固晶wip',#gj.fztl'固晶直通率',#gj.fwxl'固晶维修率', hx.fqty'焊线产量',#wip.fhwip'焊线wip',#hx.fztl'焊线直通率',#hx.fwxl'焊线维修率', md.fqty'molding产量',#wip.fmdwip'molding_wip',#md.fztl'molding直通率',#md.fwxl'molding维修率', dyb.fqty '喷墨产量',#wip.fpwip '喷墨wip',null'喷墨直通率',null'喷墨维修率', dyb.fsqty 'SMT产量',#wip.fswip 'SMT_wip',#dyb.fsztl 'SMT直通率',null'SMT维修率', mz.fqty'模组产量',#wip.fmzwip'模组wip',null'模组直通率',null'模组维修率', dyb.fqgqty '切割产量',#wip.fqwip '切割wip',null'切割直通率',null'切割维修率'

    from #gj
    full outer join #hx on #gj.fdate = #hx.fdate and #gj.fmodel = #hx.fmodel
    full outer join #md on #md.fdate = ISNULL(#gj.fdate,#hx.fdate) and #md.fmodel = ISNULL(#gj.fmodel,#hx.fmodel)
    full outer join #dyb on #dyb.fdate = ISNULL(ISNULL(#gj.fdate,#hx.fdate),#md.fdate)
    and #dyb.fmodel = ISNULL(ISNULL(#gj.fmodel,#hx.fmodel),#md.fmodel)
    full outer join #mz on #mz.fdate = ISNULL(ISNULL(ISNULL(#gj.fdate,#hx.fdate),#md.fdate),#dyb.fdate)
    and #mz.fmodel = ISNULL(ISNULL(ISNULL(#gj.fmodel,#hx.fmodel),#md.fmodel),#dyb.fmodel)
    left join #wip on #wip.fmodel = ISNULL(ISNULL(ISNULL(#gj.fmodel,#hx.fmodel),#md.fmodel),#dyb.fmodel)
    order by ISNULL(ISNULL(ISNULL(ISNULL(#gj.fdate,#hx.fdate),#md.fdate),#dyb.fdate),#mz.fdate) desc

    drop table #gjcl
    drop table #gjzt
    drop table #gj
    drop table #hx
    drop table #md
    drop table #dyb
    drop table #mz
    drop table #wip
    end
    GO

  • Power BI 高级设置使用存储过程,可以展示数据,加载显示语法错误 at 2019-01-13 15:38:00

    是不是描述的不够清楚,有大佬来答一波吗