finereport分组汇总求和后，动态排序不准，如何在报表里修改。

1.对销售单号分组，金额和折扣金额求和，设置了多列动态排序，发现不准，如果改成列表就排序是对的，但是这个表需要分组求和。在sql里面分组求和我试过了，因为数据集的sql非常复杂，还要很多参数，我改后数据都错了，所以希望在报表中调整。有什么办法吗？2.让金额列降序排序，但是仅仅是降序我也无法实现，怎么解决，谁能教教我。

2483c7bb95febcb3ea4d277b11ac36a.png58e6b4b4ed4534c3164934f35216024.pngimage.png

数据集sql放附件里

求助.rar

image.pngimage.png
FineReport GJUhCwxC 发布于 2024-7-2 16:38 （编辑于 2024-7-2 17:05）
1min目标场景问卷 立即参与
回答问题
悬赏：3 F币 + 添加悬赏
举报
提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知
共3回答
最佳回答
0
snrtuemcLv8专家互助
发布于2024-7-2 16:40

这个动态排序，需要列表的

分组本身就不支持会有问题

分组只有组内排序，没法动态排序。。。
最佳回答
0
ID1208Lv6高级互助
发布于2024-7-2 16:43

试试增加一列辅助列，设置为列表，值等于G2，然后动态排序的时候，设置指向辅助列
最佳回答
0
很困想睡觉Lv5初级互助
发布于2024-7-2 16:55（编辑于 2024-7-3 09:42）

with as base (select a.saleno

       ,sum(case when b.type=0 then a.disc else -a.disc end) sum_disc 

       ,sum(case when b.type=0 then a.amt else -a.amt end) sum_amt 

from td_sale_detail a  inner join td_sale b

  on a.companyno=b.companyno and a.shop=b.shop and a.saleno=b.saleno

left join tb_goods_lang C on A.companyno=C.companyno and A.pluno=C.pluno and C.lang_type='${para_lang_type}'

left join tb_goods e on A.companyno=e.companyno and A.pluno=e.pluno

inner join ta_org o on o.companyno=a.companyno and o.organizationno=a.shop

left join TD_SALE_DETAIL_AGIO f on f.saleno=a.saleno and a.ITEM=f.Mitem and a.COMPANYNO=f.COMPANYNO and a.shop=f.shop

left join ta_Reason_Lang g on f.bsno=g.bsno and g.companyno=f.companyno and g.lang_type='${para_lang_type}'

left join TB_CATEGORY h on e.sno=h.category and h.companyno=e.companyno

${if(rechargeGIFT==0,"left join tg_recharge f on f.rechargeno=b.ofno and f.companyno=b.companyno","")}

where a.companyno='${para_companyno}'  and

     -- o.BELFIRM='${para_organizationno}' and

       b.bdate between '${format(para_bdate1,"yyyyMMdd")}' 

       and '${format(para_bdate2,"yyyyMMdd")}'

      ${if(len(para_organizationno_shop)==0,""," and a.shop in ('" + para_organizationno_shop + "')")} 

      ${if(len(para_type)==0,""," and b.type in ('" + para_type + "')")}   

      ${if(len(para_pluno)==0,""," and a.pluno in ('" + para_pluno+ "')")} 

       ${if(len(para_sno)==0,""," and e.sno in ('" + JOINARRAY(para_sno,"','")+ "')")} 

        ${if(len(para_plu_name)==0,""," and c.plu_name like  '%%" + para_plu_name+ "%%'")}  

       ${if(len(para_saleno)==0,""," and a.saleno in ('" + para_saleno+ "')")}  

${switch(para_TCType,0,"and (a.IsPackage ='N' or a.IsPackage is null)"

,1,"and a.PackageMaster='N' and a.IsPackage='Y'"

,2,"and a.PackageMaster='Y'"

,3,"and (a.PackageMaster='N' or a.PackageMaster is null)"

,4,"and (a.PackageMaster='Y' or a.IsPackage='N')")} 

       and b.type!=16

             ${if(len(ORDERCHANNEL)==0,"","and ORDERCHANNEL in ('"+ORDERCHANNEL+"')")}

       ${if(dzx==0,"and b.order_id is not null","")}

              ${if(len(gift)==0,"","and ISGIFT = 'Y'")}

       and (a.dishesstatus not in ('3','4') or a.dishesstatus is null)

       ${if(len(para_ofno)==0,""," and b.ofno = '" + para_ofno+ "'")}

${if(rechargeGIFT==0,"and f.rechargeno is not null","")}

group by a.saleno

 order by b.bdate,b.Machine,b.SquadNO,a.saleno,a.item),

 as base1 (

select a.saleno,b.bdate,b.Machine,b.CardNO,b.SquadNO,b.opno, case when b.otype =3 then 3 when b.otype=4 then 4 else b.type end type,

       a.companyno,a.shop, a.item,  

       a.clerkno,a.accno, 

       a.pluno, a.featureno, a.plubarcode, a.scanno, a.unit,a.oldprice, a.price,

       case when b.type=0 then a.qty else -a.qty end qty 

       ,case when b.type=0 then a.uamt else -a.uamt end uamt  

       ,case when b.type=0 then a.qty else -a.qty end packqty 

       ,case when b.type=0 then a.disc else -a.disc  end packdisc 

       ,case when b.type=0 then a.amt else -a.amt end  packamt 

       ,case when b.type=0 then a.uamt else -a.uamt end packuamt ,

       a.counteramt, a.point_qty, b.ofno,a.oitem, 

       a.sdate, a.stime, a.cnfflg,C.plu_name 

       ,case when a.ISGIFT='Y' then '是' else '否' end isgift

       ,g.REASON_NAME GIFTREASON

       ,b.ORDERCHANNEL

       ,e.sno

       ,h.up_category

from td_sale_detail a  inner join td_sale b

  on a.companyno=b.companyno and a.shop=b.shop and a.saleno=b.saleno

left join tb_goods_lang C on A.companyno=C.companyno and A.pluno=C.pluno and C.lang_type='${para_lang_type}'

left join tb_goods e on A.companyno=e.companyno and A.pluno=e.pluno

inner join ta_org o on o.companyno=a.companyno and o.organizationno=a.shop

left join TD_SALE_DETAIL_AGIO f on f.saleno=a.saleno and a.ITEM=f.Mitem and a.COMPANYNO=f.COMPANYNO and a.shop=f.shop

left join ta_Reason_Lang g on f.bsno=g.bsno and g.companyno=f.companyno and g.lang_type='${para_lang_type}'

left join TB_CATEGORY h on e.sno=h.category and h.companyno=e.companyno

${if(rechargeGIFT==0,"left join tg_recharge f on f.rechargeno=b.ofno and f.companyno=b.companyno","")}

where a.companyno='${para_companyno}'  and

     -- o.BELFIRM='${para_organizationno}' and

       b.bdate between '${format(para_bdate1,"yyyyMMdd")}' 

       and '${format(para_bdate2,"yyyyMMdd")}'

      ${if(len(para_organizationno_shop)==0,""," and a.shop in ('" + para_organizationno_shop + "')")} 

      ${if(len(para_type)==0,""," and b.type in ('" + para_type + "')")}   

      ${if(len(para_pluno)==0,""," and a.pluno in ('" + para_pluno+ "')")} 

       ${if(len(para_sno)==0,""," and e.sno in ('" + JOINARRAY(para_sno,"','")+ "')")} 

        ${if(len(para_plu_name)==0,""," and c.plu_name like  '%%" + para_plu_name+ "%%'")}  

       ${if(len(para_saleno)==0,""," and a.saleno in ('" + para_saleno+ "')")}  

${switch(para_TCType,0,"and (a.IsPackage ='N' or a.IsPackage is null)"

,1,"and a.PackageMaster='N' and a.IsPackage='Y'"

,2,"and a.PackageMaster='Y'"

,3,"and (a.PackageMaster='N' or a.PackageMaster is null)"

,4,"and (a.PackageMaster='Y' or a.IsPackage='N')")} 

       and b.type!=16

             ${if(len(ORDERCHANNEL)==0,"","and ORDERCHANNEL in ('"+ORDERCHANNEL+"')")}

       ${if(dzx==0,"and b.order_id is not null","")}

              ${if(len(gift)==0,"","and ISGIFT = 'Y'")}

       and (a.dishesstatus not in ('3','4') or a.dishesstatus is null)

       ${if(len(para_ofno)==0,""," and b.ofno = '" + para_ofno+ "'")}

${if(rechargeGIFT==0,"and f.rechargeno is not null","")}

 order by b.bdate,b.Machine,b.SquadNO,a.saleno,a.item

)

select * from 

base t1

left join 

base1 t2

on t1.saleno=t2.saleno

试一下能成功吗,我的思路是第一步根据销售订单分组求和得出每个销售订单对应的累计金额,分组后销售订单是唯一的,第二步根据你的where条件查出其他展示列,通过唯一的销售订单进行关联形成汇总表,这个思路要求base和base1一一对应开窗函数写法:(可能有重复数据)

select b.bdate,b.Machine,b.CardNO,b.SquadNO,b.opno, case when b.otype =3 then 3 when b.otype=4 then 4 else b.type end type,

       a.companyno,a.shop, a.saleno, a.item,  

       a.clerkno,a.accno, 

       a.pluno, a.featureno, a.plubarcode, a.scanno, a.unit,a.oldprice, a.price,

       case when b.type=0 then a.qty else -a.qty end qty 

       ,sum(case when b.type=0 then a.disc else -a.disc end) over (partition by a.saleno)  as sum_disc

,sum(case when b.type=0 then a.amt else -a.amt end ) over (partition by a.saleno)   as sum_amt

       ,case when b.type=0 then a.uamt else -a.uamt end uamt    

       ,case when b.type=0 then a.qty else -a.qty end packqty 

       ,case when b.type=0 then a.disc else -a.disc  end packdisc 

       ,case when b.type=0 then a.amt else -a.amt end  packamt 

       ,case when b.type=0 then a.uamt else -a.uamt end packuamt ,

       a.counteramt, a.point_qty, b.ofno,a.oitem, 

       a.sdate, a.stime, a.cnfflg,C.plu_name 

       ,case when a.ISGIFT='Y' then '是' else '否' end isgift

       ,g.REASON_NAME GIFTREASON

       ,b.ORDERCHANNEL

       ,e.sno

       ,h.up_category

from td_sale_detail a  inner join td_sale b

  on a.companyno=b.companyno and a.shop=b.shop and a.saleno=b.saleno

left join tb_goods_lang C on A.companyno=C.companyno and A.pluno=C.pluno and C.lang_type='${para_lang_type}'

left join tb_goods e on A.companyno=e.companyno and A.pluno=e.pluno

inner join ta_org o on o.companyno=a.companyno and o.organizationno=a.shop

left join TD_SALE_DETAIL_AGIO f on f.saleno=a.saleno and a.ITEM=f.Mitem and a.COMPANYNO=f.COMPANYNO and a.shop=f.shop

left join ta_Reason_Lang g on f.bsno=g.bsno and g.companyno=f.companyno and g.lang_type='${para_lang_type}'

left join TB_CATEGORY h on e.sno=h.category and h.companyno=e.companyno

${if(rechargeGIFT==0,"left join tg_recharge f on f.rechargeno=b.ofno and f.companyno=b.companyno","")}

where a.companyno='${para_companyno}'  and

     -- o.BELFIRM='${para_organizationno}' and

       b.bdate between '${format(para_bdate1,"yyyyMMdd")}' 

       and '${format(para_bdate2,"yyyyMMdd")}'

      ${if(len(para_organizationno_shop)==0,""," and a.shop in ('" + para_organizationno_shop + "')")} 

      ${if(len(para_type)==0,""," and b.type in ('" + para_type + "')")}   

      ${if(len(para_pluno)==0,""," and a.pluno in ('" + para_pluno+ "')")} 

       ${if(len(para_sno)==0,""," and e.sno in ('" + JOINARRAY(para_sno,"','")+ "')")} 

        ${if(len(para_plu_name)==0,""," and c.plu_name like  '%%" + para_plu_name+ "%%'")}  

       ${if(len(para_saleno)==0,""," and a.saleno in ('" + para_saleno+ "')")}  

${switch(para_TCType,0,"and (a.IsPackage ='N' or a.IsPackage is null)"

,1,"and a.PackageMaster='N' and a.IsPackage='Y'"

,2,"and a.PackageMaster='Y'"

,3,"and (a.PackageMaster='N' or a.PackageMaster is null)"

,4,"and (a.PackageMaster='Y' or a.IsPackage='N')")} 

       and b.type!=16

             ${if(len(ORDERCHANNEL)==0,"","and ORDERCHANNEL in ('"+ORDERCHANNEL+"')")}

       ${if(dzx==0,"and b.order_id is not null","")}

              ${if(len(gift)==0,"","and ISGIFT = 'Y'")}

       and (a.dishesstatus not in ('3','4') or a.dishesstatus is null)

       ${if(len(para_ofno)==0,""," and b.ofno = '" + para_ofno+ "'")}

${if(rechargeGIFT==0,"and f.rechargeno is not null","")}

 order by b.bdate,b.Machine,b.SquadNO,a.saleno,a.item
举报 收起评论(8)
  • GJUhCwxC GJUhCwxC(提问者) sql放问题里了
    2024-07-02 17:05 
  • 很困想睡觉 很困想睡觉 回复 GJUhCwxC(提问者) 你要求和的字段和要分组的字段名是什么
    2024-07-02 17:21 
  • 很困想睡觉 很困想睡觉 回复 GJUhCwxC(提问者) 金额和折扣金额求字段名是什么，根据那几个字段分组，字段名是什么
    2024-07-02 17:22 
  • GJUhCwxC GJUhCwxC(提问者) 回复 很困想睡觉 对销售订单（saleno）分组，对金额（amt）求和，对折扣金额（disc）求和
    2024-07-03 08:47 
  • 很困想睡觉 很困想睡觉 回复 GJUhCwxC(提问者) 试一下吧 没有表结构和测试数据写起来有点困难,主要是看思路吧
    2024-07-03 09:39 
  • 4关注人数
  • 296浏览人数
    • 最后回答于：2024-7-3 09:42
    活动推荐 更多
    热门课程 更多
    请选择关闭问题的原因
    确定 取消
    隐私政策
    帆软用户服务协议
    联系管理员
    联系帆软
    Copyright© 帆软软件有限公司 版权所有
    苏ICP备18065767号
    返回顶部