Difference between revisions of "ZH/Case-Study-01-Journal-22"
From ADempiere
This Wiki is read-only for reference purposes to avoid broken links.
(→讨论栏) |
(→讨论栏) |
||
(13 intermediate revisions by 3 users not shown) | |||
Line 48: | Line 48: | ||
* [http://jasperforge.org/ Jasper Forge官方网站] | * [http://jasperforge.org/ Jasper Forge官方网站] | ||
===讨论栏=== | ===讨论栏=== | ||
+ | |||
+ | ==2010-11-30== | ||
+ | ===销售订单查询语句=== | ||
+ | ====说明==== | ||
+ | * 为了用JasperReport创建销售订单,借助电子表格终于把SQL查询语句写好了,大概有140行。 | ||
+ | * 难点在于引用Reference和Table的地方比较绕,需要写JOIN ... ON ...。 | ||
+ | * 而且要费点功夫才能搞清楚ad_field表里的xxxx_id是指向哪个表的。 同时还有ad_column表里的ad_reference_id和ad_reference_value_id,也是有点费神的。 | ||
+ | ====OOo Base存在问题==== | ||
+ | * 另外发现OOo Base存在一些问题: | ||
+ | ** 首先是不稳定,主要是执行数据查询时有时会出现重复列,有时会出现记录显示不全。 | ||
+ | ** 其次是Queries的Design View设计不完善。在SQL View界面下写稍微复杂一点的SQL语句,一旦转到Design View界面,会就把SQL语句改换掉,接着就是报错。 | ||
+ | * 于是我还是老老实实的用pgAdmin吧。:-) | ||
+ | ====SQL语句==== | ||
+ | <pre> | ||
+ | SELECT | ||
+ | c_order.c_order_id, | ||
+ | C_Order.IsActive AS "有效", | ||
+ | AD_Client_74.name AS "实体", | ||
+ | AD_Org_82.name AS "组织", | ||
+ | C_Order.contractno AS "合同编号", | ||
+ | C_Order.DocumentNo AS "单据号", | ||
+ | ref49_trl.name AS "业务类型", | ||
+ | C_DocType_57_trl.name AS "目标单据类型", | ||
+ | C_Order.IsSelfService AS "自助服务", | ||
+ | C_Order.issueddate AS "合同签订日期", | ||
+ | C_Order.signedplace AS "签订地点", | ||
+ | C_Order.DateOrdered AS "下单日期", | ||
+ | C_Order.DatePromised AS "承诺日期", | ||
+ | C_BPartner_110.name AS "业务伙伴 ", | ||
+ | C_BPartner_56.name AS "发票伙伴", | ||
+ | C_BPartner_Location_73.name AS "伙伴位置", | ||
+ | C_BPartner_Location_62.name AS "发票地址", | ||
+ | AD_User_75.name AS "用户/联系人", | ||
+ | ref120_trl.name AS "合同份数", | ||
+ | AD_User_61.name AS "发票联系人", | ||
+ | ref50_trl.name AS "配送规则", | ||
+ | ref43_trl.name AS "优先级", | ||
+ | M_Warehouse_81.name AS "仓库", | ||
+ | C_Order.IsDropShip AS "直接供货", | ||
+ | C_BPartner_111.name AS "直送伙伴", | ||
+ | C_BPartner_Location_66.name AS "直送地址", | ||
+ | AD_User_60.name AS "直送联系人", | ||
+ | M_Shipper_80.name AS "承运人", | ||
+ | ref42_trl.name AS "运输方式", | ||
+ | ref51_trl.name AS "运费成本规则", | ||
+ | M_FreightCategory_77.name AS "运费类别", | ||
+ | C_Order.paymenttermdetail AS "付款条件详情", | ||
+ | C_Order.FreightAmt AS "运费金额", | ||
+ | ref37_trl.name AS "包装物的供应", | ||
+ | ref36_trl.name AS "包装物回收", | ||
+ | ref54_trl.name AS "包装标准", | ||
+ | ref38_trl.name AS "是否薰蒸", | ||
+ | ref32_trl.name AS "运输方式", | ||
+ | ref33_trl.name AS "交货地点", | ||
+ | C_Order.portofdestination AS "目的港口", | ||
+ | C_Order.portofshipment AS "装运港口", | ||
+ | ref48_trl.name AS "交货期起算方法", | ||
+ | ref47_trl.name AS "交货期天数计算方法", | ||
+ | C_Order.timeofdelivery AS "交货期(天数)", | ||
+ | ref41_trl.name AS "I发票规则", | ||
+ | M_PriceList_84.name AS "价格表", | ||
+ | C_Currency_78_trl.description AS "币种", | ||
+ | C_ConversionType_83.name AS "货币汇率类型", | ||
+ | AD_User_58.name AS "销售代表", | ||
+ | C_Order.IsDiscountPrinted AS "打印折扣", | ||
+ | C_Charge_59.name AS "费用", | ||
+ | C_Order.ChargeAmt AS "费用金额", | ||
+ | ref103_trl.name AS "付款规则", | ||
+ | C_PaymentTerm_76_trl.name AS "付款条件", | ||
+ | ref39_trl.name AS "发票类型", | ||
+ | C_Order.paymentremitted AS "预付款到账日期", | ||
+ | ref45_trl.name AS "付款方式", | ||
+ | C_Activity_71.name AS "活动", | ||
+ | C_Campaign_69.name AS "营销战役", | ||
+ | C_Order.TotalLines AS "行总计", | ||
+ | C_Order.GrandTotal AS "总额", | ||
+ | ref46_trl.name AS "单据状态", | ||
+ | C_DocType_70_trl.name AS "单据类型", | ||
+ | ref105_trl.name AS "处理订单", | ||
+ | ref107_trl.name AS "已过账", | ||
+ | C_Order.OrderType AS "订单类型", | ||
+ | C_OrderSource_79.name AS "订单来源", | ||
+ | ref55_trl.name AS "验收方式", | ||
+ | C_Order.advancepayment AS "预付款期限", | ||
+ | ref40_trl.name AS "是否要求预付货款", | ||
+ | ref53_trl.name AS "合同生效方式", | ||
+ | C_Order.priceisvaliddays AS "报价有效期限", | ||
+ | ref31_trl.name AS "合同失效方式", | ||
+ | ref34_trl.name AS "仲裁", | ||
+ | ref52_trl.name AS "安装调试人员食宿及来回交通费用承担", | ||
+ | C_Order.installationfeedetail AS "安装调试备注", | ||
+ | ref44_trl.name AS "质保期", | ||
+ | ref35_trl.name AS "售后服务", | ||
+ | C_Order.warrantystartdate AS "质保期起始日期", | ||
+ | C_Order.warrantyenddate AS "质保期停止日期" | ||
+ | FROM C_Order | ||
+ | LEFT OUTER JOIN AD_Client AS AD_Client_74 ON (AD_Client_74.AD_Client_ID=C_Order.AD_Client_ID) | ||
+ | LEFT OUTER JOIN AD_Org AS AD_Org_82 ON (AD_Org_82.AD_Org_ID=C_Order.AD_Org_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref49_trl ON ((ref49_trl.ad_reference_id=1001000) AND (C_Order.businesstype=ref49_trl.value ) ) | ||
+ | LEFT OUTER JOIN C_DocType_trl AS C_DocType_57_trl ON ((C_DocType_57_trl.C_DocType_ID=C_Order.C_DocTypeTarget_ID) AND (C_DocType_57_trl.ad_language='zh_CN')) | ||
+ | LEFT OUTER JOIN C_BPartner AS C_BPartner_110 ON (C_BPartner_110.C_BPartner_ID=C_Order.C_BPartner_ID) | ||
+ | LEFT OUTER JOIN C_BPartner AS C_BPartner_56 ON (C_BPartner_56.C_BPartner_ID=C_Order.Bill_BPartner_ID) | ||
+ | LEFT OUTER JOIN C_BPartner_Location AS C_BPartner_Location_73 ON (C_BPartner_Location_73.C_BPartner_Location_ID=C_Order.C_BPartner_Location_ID) | ||
+ | LEFT OUTER JOIN C_BPartner_Location AS C_BPartner_Location_62 ON (C_BPartner_Location_62.C_BPartner_Location_ID=C_Order.Bill_Location_ID) | ||
+ | LEFT OUTER JOIN AD_User AS AD_User_75 ON (AD_User_75.AD_User_ID=C_Order.AD_User_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref120_trl ON ((ref120_trl.ad_reference_id=1000001) AND (C_Order.numberofcopies=ref120_trl.value ) ) | ||
+ | LEFT OUTER JOIN AD_User AS AD_User_61 ON (AD_User_61.AD_User_ID=C_Order.Bill_User_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref50_trl ON ((ref50_trl.ad_reference_id=151) AND (C_Order.DeliveryRule=ref50_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref43_trl ON ((ref43_trl.ad_reference_id=154) AND (C_Order.PriorityRule=ref43_trl.value ) ) | ||
+ | LEFT OUTER JOIN M_Warehouse AS M_Warehouse_81 ON (M_Warehouse_81.M_Warehouse_ID=C_Order.M_Warehouse_ID) | ||
+ | LEFT OUTER JOIN C_BPartner AS C_BPartner_111 ON (C_BPartner_111.C_BPartner_ID=C_Order.DropShip_BPartner_ID) | ||
+ | LEFT OUTER JOIN C_BPartner_Location AS C_BPartner_Location_66 ON (C_BPartner_Location_66.C_BPartner_Location_ID=C_Order.DropShip_Location_ID) | ||
+ | LEFT OUTER JOIN AD_User AS AD_User_60 ON (AD_User_60.AD_User_ID=C_Order.DropShip_User_ID) | ||
+ | LEFT OUTER JOIN M_Shipper AS M_Shipper_80 ON (M_Shipper_80.M_Shipper_ID=C_Order.M_Shipper_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref42_trl ON ((ref42_trl.ad_reference_id=152) AND (C_Order.DeliveryViaRule=ref42_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref51_trl ON ((ref51_trl.ad_reference_id=153) AND (C_Order.FreightCostRule=ref51_trl.value ) ) | ||
+ | LEFT OUTER JOIN M_FreightCategory AS M_FreightCategory_77 ON (M_FreightCategory_77.M_FreightCategory_ID=C_Order.M_FreightCategory_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref37_trl ON ((ref37_trl.ad_reference_id=1001010) AND (C_Order.packagingprovide=ref37_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref36_trl ON ((ref36_trl.ad_reference_id=1001011) AND (C_Order.packagingrecycle=ref36_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref54_trl ON ((ref54_trl.ad_reference_id=1001001) AND (C_Order.packingstandard=ref54_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref38_trl ON ((ref38_trl.ad_reference_id=319) AND (C_Order.fumigation=ref38_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref32_trl ON ((ref32_trl.ad_reference_id=1001008) AND (C_Order.modeofshipment=ref32_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref33_trl ON ((ref33_trl.ad_reference_id=1001009) AND (C_Order.placeofdelivery=ref33_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref48_trl ON ((ref48_trl.ad_reference_id=1001002) AND (C_Order.timeofdeliverystarttype=ref48_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref47_trl ON ((ref47_trl.ad_reference_id=1001003) AND (C_Order.timeofdeliverydaytype=ref47_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref41_trl ON ((ref41_trl.ad_reference_id=150) AND (C_Order.InvoiceRule=ref41_trl.value ) ) | ||
+ | LEFT OUTER JOIN M_PriceList AS M_PriceList_84 ON (M_PriceList_84.M_PriceList_ID=C_Order.M_PriceList_ID) | ||
+ | LEFT OUTER JOIN C_Currency_trl AS C_Currency_78_trl ON ((C_Currency_78_trl.C_Currency_ID=C_Order.C_Currency_ID) AND (C_Currency_78_trl.ad_language='zh_CN')) | ||
+ | LEFT OUTER JOIN C_ConversionType AS C_ConversionType_83 ON (C_ConversionType_83.C_ConversionType_ID=C_Order.C_ConversionType_ID) | ||
+ | LEFT OUTER JOIN AD_User AS AD_User_58 ON (AD_User_58.AD_User_ID=C_Order.SalesRep_ID) | ||
+ | LEFT OUTER JOIN C_Charge AS C_Charge_59 ON (C_Charge_59.C_Charge_ID=C_Order.C_Charge_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref103_trl ON ((ref103_trl.ad_reference_id=195) AND (C_Order.PaymentRule=ref103_trl.value ) ) | ||
+ | LEFT OUTER JOIN C_PaymentTerm_trl AS C_PaymentTerm_76_trl ON ((C_PaymentTerm_76_trl.C_PaymentTerm_ID=C_Order.C_PaymentTerm_ID) AND (C_PaymentTerm_76_trl.ad_language='zh_CN')) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref39_trl ON ((ref39_trl.ad_reference_id=1001015) AND (C_Order.invoicetype=ref39_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref45_trl ON ((ref45_trl.ad_reference_id=1001007) AND (C_Order.paymentmethod=ref45_trl.value ) ) | ||
+ | LEFT OUTER JOIN C_Activity AS C_Activity_71 ON (C_Activity_71.C_Activity_ID=C_Order.C_Activity_ID) | ||
+ | LEFT OUTER JOIN C_Campaign AS C_Campaign_69 ON (C_Campaign_69.C_Campaign_ID=C_Order.C_Campaign_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref46_trl ON ((ref46_trl.ad_reference_id=131) AND (C_Order.DocStatus=ref46_trl.value ) ) | ||
+ | LEFT OUTER JOIN C_DocType_trl AS C_DocType_70_trl ON ((C_DocType_70_trl.C_DocType_ID=C_Order.C_DocType_ID) AND (C_DocType_70_trl.ad_language='zh_CN')) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref105_trl ON ((ref105_trl.ad_reference_id=135) AND (C_Order.DocAction=ref105_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref107_trl ON ((ref107_trl.ad_reference_id=234) AND (C_Order.Posted=ref107_trl.value ) ) | ||
+ | LEFT OUTER JOIN C_OrderSource AS C_OrderSource_79 ON (C_OrderSource_79.C_OrderSource_ID=C_Order.C_OrderSource_ID) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref55_trl ON ((ref55_trl.ad_reference_id=1001006) AND (C_Order.acceptancemethod=ref55_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref40_trl ON ((ref40_trl.ad_reference_id=319) AND (C_Order.advancepaymentrequired=ref40_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref53_trl ON ((ref53_trl.ad_reference_id=1001004) AND (C_Order.contracteffectiveway=ref53_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref31_trl ON ((ref31_trl.ad_reference_id=1001005) AND (C_Order.conditionofcontactinvalidation=ref31_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref34_trl ON ((ref34_trl.ad_reference_id=1001014) AND (C_Order.arbitration=ref34_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref52_trl ON ((ref52_trl.ad_reference_id=1001016) AND (C_Order.installationfeepaymentside=ref52_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref44_trl ON ((ref44_trl.ad_reference_id=1001012) AND (C_Order.warrantyperiod=ref44_trl.value ) ) | ||
+ | LEFT OUTER JOIN ad_ref_list_trl_v AS ref35_trl ON ((ref35_trl.ad_reference_id=1001013) AND (C_Order.aftersaleservice=ref35_trl.value ) ) | ||
+ | WHERE (C_Order.c_order_id =$P{C_Order_ID}) | ||
+ | AND (C_Order.IsSOTrx='Y'); | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | ===讨论栏=== | ||
+ | * 我觉得这样写报表还是比较繁琐的,不知道大家有没有更好的方法? --Peanut | ||
+ | |||
+ | * 建议:使用java程序或者存储过程将报表所需的原始数据统计汇总到几个报表数据表中,然后在通过报表控件从这些数据表中格式化输出到报表。--siantii | ||
+ | |||
+ | * 谢谢Siantii的建议!--Peanut | ||
==2010-12-02== | ==2010-12-02== | ||
Line 67: | Line 228: | ||
* 父子表的问题解决了之后,今天用iReport来制作报表还算顺利。只是遇到中文大写金额的问题。 | * 父子表的问题解决了之后,今天用iReport来制作报表还算顺利。只是遇到中文大写金额的问题。 | ||
* 找到一篇文章[http://www.blogjava.net/hispark/archive/2008/11/20/241321.html 在JasperReport报表中加入大写金额],不知道能否在ADempiere下实现。有空再试试。 | * 找到一篇文章[http://www.blogjava.net/hispark/archive/2008/11/20/241321.html 在JasperReport报表中加入大写金额],不知道能否在ADempiere下实现。有空再试试。 | ||
+ | |||
+ | ==2010-12-09== | ||
+ | ===销售合同的报表格式调整=== | ||
+ | * 已经把销售合同的报表格式调整到位。 | ||
+ | * 主要解决了以下问题: | ||
+ | ** 页码问题。需要设置好Page_Number的有效范围。 | ||
+ | ** 文本框随内容自动调整高度问题; | ||
+ | ** sub-report相对路径问题; | ||
+ | ** 金额合计问题。需要新建变量,并设置好生效范围; | ||
+ | ** ADempiere与JasperReports参数传递问题。 | ||
+ | ** 避免文本框跨页分断问题。将Band: Detail 1的属性Split Allowed设置为False。 | ||
+ | ** 文本框NULL值问题。将文本框属性Blank when Null设置为True。 | ||
==2010-12-22== | ==2010-12-22== | ||
Line 178: | Line 351: | ||
===讨论栏=== | ===讨论栏=== | ||
+ | |||
+ | ==2011-03-05== | ||
+ | |||
+ | [[Image:Case-Study-01_JasperReports_Sales_Statitistics.png|400px|thumb|报表示例]] | ||
+ | ===销售统计分析报表=== | ||
+ | * 销售模块从12月运行到现在已经有三个月了,已经积累了一批数据,接下来进行销售统计分析报表的制作。 | ||
+ | * 报表制作大概历时5天,共计7张报表。[[Media:JasperReports_SalesStatistics_OpenSource.pdf|下载:销售统计分析表.PDF]] | ||
+ | ====几点体会==== | ||
+ | # 汇总报表可以为企业管理层提供分析数据和决策依据。报表做得直观、实用,成为管理层的有力工具,这样ERP实施才能获得管理层更多的信任和支持。 | ||
+ | # 通过报表的汇总数据,可以很直观地发现数据录入的错误或缺漏,为ERP数据准确性提供了一种检查手段。 | ||
+ | |||
+ | '''主要进行了以下工作:''' | ||
+ | |||
+ | ====数据库结构及数据的调整==== | ||
+ | # 原ERP没有上年遗留订单、本年销售指标等数据,于是新增了两个表—— c_order_lastyear、c_salestarget。 | ||
+ | # 新增了一些字段,如在c_order表新增出货日期、产品类别等等。 | ||
+ | # 根据新的市场区域划分调整了市场区域设置。 | ||
+ | ====JasperReport==== | ||
+ | # SQL查询语句比较复杂,要综合使用UNION, GROUP BY, CASE WHEN等语句。曾经考虑过使用中间表,但是不便于操作和实时更新,于是未采用。 | ||
+ | # CrossTab和Chart的学习和应用。主要难点有两个: | ||
+ | #* 一是搞清楚CrossTab和Chart能做什么,不能做什么; | ||
+ | #* 二是排序,尤其是中文字符串的排序。后来采用的方法是在中文前加数字前缀,如 01-国内、02-国外,同时SQL查询语句中用Order By先行排序,这样来解决问题。 | ||
+ | ====附:用于JasperReport报表的SQL语句==== | ||
+ | <pre> | ||
+ | --2011年新订单汇总 包装机械-结合年度指标 | ||
+ | SELECT | ||
+ | c_salesregion.name as 销售区域, | ||
+ | CASE WHEN amountlastyear.amount is null THEN 0 ELSE amountlastyear.amount END as 上年遗留订单金额, | ||
+ | CASE WHEN neworderamount.amount_cny is null THEN 0 ELSE neworderamount.amount_cny END as 金额_人民币, | ||
+ | CASE WHEN salestarget.salestarget is null THEN 0 ELSE salestarget.salestarget END as 年度销售目标 | ||
+ | FROM | ||
+ | (( SELECT c_salesregion_id | ||
+ | FROM c_salestarget | ||
+ | WHERE c_salestarget_batch_id=1000004) | ||
+ | UNION | ||
+ | (--新订单汇总 按产品系列 | ||
+ | SELECT | ||
+ | c_salesregion.summarytoid2 AS region_summary_id | ||
+ | FROM c_order | ||
+ | INNER JOIN c_bpartner ON c_bpartner.c_bpartner_id=c_order.c_bpartner_id | ||
+ | INNER JOIN c_salesregion ON c_salesregion.c_salesregion_id = c_bpartner.c_salesregion_id | ||
+ | WHERE (c_order.C_DocTypeTarget_ID = 1000030) --sales order | ||
+ | AND (c_order.ad_client_id = 1000000) --tongji client | ||
+ | AND (c_order.DateOrdered BETWEEN '2011-01-01' AND '2011-12-31') --year 2011 | ||
+ | AND (c_order.ProductSeries='B') --包装机械 | ||
+ | GROUP BY c_salesregion.summarytoid2 | ||
+ | ) | ||
+ | ) region | ||
+ | LEFT OUTER JOIN | ||
+ | |||
+ | ( SELECT c_salesregion_id, salestarget | ||
+ | FROM c_salestarget | ||
+ | WHERE c_salestarget_batch_id=1000004 | ||
+ | ) salestarget ON salestarget.c_salesregion_id=region.c_salesregion_id | ||
+ | |||
+ | LEFT OUTER JOIN | ||
+ | (--新订单汇总 按产品系列 | ||
+ | SELECT | ||
+ | c_salesregion.summarytoid2 AS region_summary_id, | ||
+ | SUM(case | ||
+ | WHEN C_Currency.iso_code='CNY' THEN c_order.TotalLines | ||
+ | WHEN C_Currency.iso_code='USD' THEN c_order.TotalLines*6.7 | ||
+ | ELSE 0 | ||
+ | END) AS amount_cny | ||
+ | FROM c_order | ||
+ | INNER JOIN C_Currency ON C_Currency.C_Currency_ID = c_order.C_Currency_ID | ||
+ | INNER JOIN c_bpartner ON c_bpartner.c_bpartner_id=c_order.c_bpartner_id | ||
+ | INNER JOIN c_salesregion ON c_salesregion.c_salesregion_id = c_bpartner.c_salesregion_id | ||
+ | WHERE (c_order.C_DocTypeTarget_ID = 1000030) --sales order | ||
+ | AND (c_order.ad_client_id = 1000000) --tongji client | ||
+ | AND (c_order.DateOrdered BETWEEN '2011-01-01' AND '2011-12-31') --year 2011 | ||
+ | AND (c_order.ProductSeries='B') --包装机械 | ||
+ | GROUP BY c_salesregion.summarytoid2 | ||
+ | ) neworderamount | ||
+ | ON neworderamount.region_summary_id =region.c_salesregion_id | ||
+ | |||
+ | LEFT OUTER JOIN | ||
+ | ( SELECT c_salesregion_id, amount | ||
+ | FROM c_orderamount_lastyear | ||
+ | WHERE c_orderamount_lastyear_batch_id=1000003 | ||
+ | ) amountlastyear ON amountlastyear.c_salesregion_id = region.c_salesregion_id | ||
+ | |||
+ | LEFT OUTER JOIN | ||
+ | c_salesregion ON ( c_salesregion.c_salesregion_id = region.c_salesregion_id ) | ||
+ | ORDER BY 销售区域 | ||
+ | ; | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | ===讨论栏=== | ||
+ | * 一楼 | ||
+ | * 是否考虑过实施Adempiere和Pentaho相结合的方法来呈现数据?毕竟在这方面Adempere还是比较弱的,通过开源的BI来做的话就会非常好。 --Chopin | ||
=链接= | =链接= |
Latest revision as of 23:28, 18 April 2011
Contents
实施日志-22-JasperReports报表
- 本文属于机械装备制造业进销存实施案例——ADempiere ERP案例研究之一。
- 本文记录ADempiere ERP项目实施的过程
2010-11-11
JasperReports报表
今天主要调试JasperReports和iReport。
- JasperReports是一款报表打印组件,开放源代码并基于Java。
- “iReport...用来以可视化的方式设计生成JasperReport所使用的报表格式文件。因为JasperReports本身并未提供很好的可视化报表设计工具,iReport的出现正好弥补了这个缺陷。”
- --摘自裴贺先的《JasperReport 与 iReport 的配置与使用》。
友情提醒:ADempiere新近版本已经集成了JasperReports功能,在Adempiere的安装目录下以jasper可以搜索到两个jar包。所以安装了ADempiere了用户就不必另外安装JasperReports了。
iReport安装方法
以iReport-3.7.6和Linux操作系统为例
- 下载安装包 iReport-3.7.6.tar.gz
- 解压缩到文件夹: /opt/iReport-3.7.6
- 运行iReport:
$ cd /opt/iReport-3.7.6/bin $ sh ireport
在ADempiere中应用JasperReports
- 在iReport里新建报表并预览通过。
- Adempiere的安装目录下新建报表文件夹,然后把*.jrxml文件复制过去。
- 以System Administrator的角色登录。
- 在窗体Report & Process里新建Report。并在Jaspert Report栏填写*.jrxml文件的路径。
- 在窗体Menu中把新建的Report添加到菜单中。
- 以GardenWorld Administrator的角色登录。
- 打开刚才新建的Report,就可以看到JasperReport了。
后续测试
- 父子报表(Parent-Child report);
- 参数传递。主要是与销售订单当前记录的关联。
完成测试后就会用于销售订单的报表定制。
JasperReports相关链接
- Using Jasper Report Form in place of Standard Forms
- HOWTO Use Jasper On Financial Reports
- ADempiere/Compiere JasperReports Integration HowTo
- Jasper Forge官方网站
讨论栏
2010-11-30
销售订单查询语句
说明
- 为了用JasperReport创建销售订单,借助电子表格终于把SQL查询语句写好了,大概有140行。
- 难点在于引用Reference和Table的地方比较绕,需要写JOIN ... ON ...。
- 而且要费点功夫才能搞清楚ad_field表里的xxxx_id是指向哪个表的。 同时还有ad_column表里的ad_reference_id和ad_reference_value_id,也是有点费神的。
OOo Base存在问题
- 另外发现OOo Base存在一些问题:
- 首先是不稳定,主要是执行数据查询时有时会出现重复列,有时会出现记录显示不全。
- 其次是Queries的Design View设计不完善。在SQL View界面下写稍微复杂一点的SQL语句,一旦转到Design View界面,会就把SQL语句改换掉,接着就是报错。
- 于是我还是老老实实的用pgAdmin吧。:-)
SQL语句
SELECT c_order.c_order_id, C_Order.IsActive AS "有效", AD_Client_74.name AS "实体", AD_Org_82.name AS "组织", C_Order.contractno AS "合同编号", C_Order.DocumentNo AS "单据号", ref49_trl.name AS "业务类型", C_DocType_57_trl.name AS "目标单据类型", C_Order.IsSelfService AS "自助服务", C_Order.issueddate AS "合同签订日期", C_Order.signedplace AS "签订地点", C_Order.DateOrdered AS "下单日期", C_Order.DatePromised AS "承诺日期", C_BPartner_110.name AS "业务伙伴 ", C_BPartner_56.name AS "发票伙伴", C_BPartner_Location_73.name AS "伙伴位置", C_BPartner_Location_62.name AS "发票地址", AD_User_75.name AS "用户/联系人", ref120_trl.name AS "合同份数", AD_User_61.name AS "发票联系人", ref50_trl.name AS "配送规则", ref43_trl.name AS "优先级", M_Warehouse_81.name AS "仓库", C_Order.IsDropShip AS "直接供货", C_BPartner_111.name AS "直送伙伴", C_BPartner_Location_66.name AS "直送地址", AD_User_60.name AS "直送联系人", M_Shipper_80.name AS "承运人", ref42_trl.name AS "运输方式", ref51_trl.name AS "运费成本规则", M_FreightCategory_77.name AS "运费类别", C_Order.paymenttermdetail AS "付款条件详情", C_Order.FreightAmt AS "运费金额", ref37_trl.name AS "包装物的供应", ref36_trl.name AS "包装物回收", ref54_trl.name AS "包装标准", ref38_trl.name AS "是否薰蒸", ref32_trl.name AS "运输方式", ref33_trl.name AS "交货地点", C_Order.portofdestination AS "目的港口", C_Order.portofshipment AS "装运港口", ref48_trl.name AS "交货期起算方法", ref47_trl.name AS "交货期天数计算方法", C_Order.timeofdelivery AS "交货期(天数)", ref41_trl.name AS "I发票规则", M_PriceList_84.name AS "价格表", C_Currency_78_trl.description AS "币种", C_ConversionType_83.name AS "货币汇率类型", AD_User_58.name AS "销售代表", C_Order.IsDiscountPrinted AS "打印折扣", C_Charge_59.name AS "费用", C_Order.ChargeAmt AS "费用金额", ref103_trl.name AS "付款规则", C_PaymentTerm_76_trl.name AS "付款条件", ref39_trl.name AS "发票类型", C_Order.paymentremitted AS "预付款到账日期", ref45_trl.name AS "付款方式", C_Activity_71.name AS "活动", C_Campaign_69.name AS "营销战役", C_Order.TotalLines AS "行总计", C_Order.GrandTotal AS "总额", ref46_trl.name AS "单据状态", C_DocType_70_trl.name AS "单据类型", ref105_trl.name AS "处理订单", ref107_trl.name AS "已过账", C_Order.OrderType AS "订单类型", C_OrderSource_79.name AS "订单来源", ref55_trl.name AS "验收方式", C_Order.advancepayment AS "预付款期限", ref40_trl.name AS "是否要求预付货款", ref53_trl.name AS "合同生效方式", C_Order.priceisvaliddays AS "报价有效期限", ref31_trl.name AS "合同失效方式", ref34_trl.name AS "仲裁", ref52_trl.name AS "安装调试人员食宿及来回交通费用承担", C_Order.installationfeedetail AS "安装调试备注", ref44_trl.name AS "质保期", ref35_trl.name AS "售后服务", C_Order.warrantystartdate AS "质保期起始日期", C_Order.warrantyenddate AS "质保期停止日期" FROM C_Order LEFT OUTER JOIN AD_Client AS AD_Client_74 ON (AD_Client_74.AD_Client_ID=C_Order.AD_Client_ID) LEFT OUTER JOIN AD_Org AS AD_Org_82 ON (AD_Org_82.AD_Org_ID=C_Order.AD_Org_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref49_trl ON ((ref49_trl.ad_reference_id=1001000) AND (C_Order.businesstype=ref49_trl.value ) ) LEFT OUTER JOIN C_DocType_trl AS C_DocType_57_trl ON ((C_DocType_57_trl.C_DocType_ID=C_Order.C_DocTypeTarget_ID) AND (C_DocType_57_trl.ad_language='zh_CN')) LEFT OUTER JOIN C_BPartner AS C_BPartner_110 ON (C_BPartner_110.C_BPartner_ID=C_Order.C_BPartner_ID) LEFT OUTER JOIN C_BPartner AS C_BPartner_56 ON (C_BPartner_56.C_BPartner_ID=C_Order.Bill_BPartner_ID) LEFT OUTER JOIN C_BPartner_Location AS C_BPartner_Location_73 ON (C_BPartner_Location_73.C_BPartner_Location_ID=C_Order.C_BPartner_Location_ID) LEFT OUTER JOIN C_BPartner_Location AS C_BPartner_Location_62 ON (C_BPartner_Location_62.C_BPartner_Location_ID=C_Order.Bill_Location_ID) LEFT OUTER JOIN AD_User AS AD_User_75 ON (AD_User_75.AD_User_ID=C_Order.AD_User_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref120_trl ON ((ref120_trl.ad_reference_id=1000001) AND (C_Order.numberofcopies=ref120_trl.value ) ) LEFT OUTER JOIN AD_User AS AD_User_61 ON (AD_User_61.AD_User_ID=C_Order.Bill_User_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref50_trl ON ((ref50_trl.ad_reference_id=151) AND (C_Order.DeliveryRule=ref50_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref43_trl ON ((ref43_trl.ad_reference_id=154) AND (C_Order.PriorityRule=ref43_trl.value ) ) LEFT OUTER JOIN M_Warehouse AS M_Warehouse_81 ON (M_Warehouse_81.M_Warehouse_ID=C_Order.M_Warehouse_ID) LEFT OUTER JOIN C_BPartner AS C_BPartner_111 ON (C_BPartner_111.C_BPartner_ID=C_Order.DropShip_BPartner_ID) LEFT OUTER JOIN C_BPartner_Location AS C_BPartner_Location_66 ON (C_BPartner_Location_66.C_BPartner_Location_ID=C_Order.DropShip_Location_ID) LEFT OUTER JOIN AD_User AS AD_User_60 ON (AD_User_60.AD_User_ID=C_Order.DropShip_User_ID) LEFT OUTER JOIN M_Shipper AS M_Shipper_80 ON (M_Shipper_80.M_Shipper_ID=C_Order.M_Shipper_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref42_trl ON ((ref42_trl.ad_reference_id=152) AND (C_Order.DeliveryViaRule=ref42_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref51_trl ON ((ref51_trl.ad_reference_id=153) AND (C_Order.FreightCostRule=ref51_trl.value ) ) LEFT OUTER JOIN M_FreightCategory AS M_FreightCategory_77 ON (M_FreightCategory_77.M_FreightCategory_ID=C_Order.M_FreightCategory_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref37_trl ON ((ref37_trl.ad_reference_id=1001010) AND (C_Order.packagingprovide=ref37_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref36_trl ON ((ref36_trl.ad_reference_id=1001011) AND (C_Order.packagingrecycle=ref36_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref54_trl ON ((ref54_trl.ad_reference_id=1001001) AND (C_Order.packingstandard=ref54_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref38_trl ON ((ref38_trl.ad_reference_id=319) AND (C_Order.fumigation=ref38_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref32_trl ON ((ref32_trl.ad_reference_id=1001008) AND (C_Order.modeofshipment=ref32_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref33_trl ON ((ref33_trl.ad_reference_id=1001009) AND (C_Order.placeofdelivery=ref33_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref48_trl ON ((ref48_trl.ad_reference_id=1001002) AND (C_Order.timeofdeliverystarttype=ref48_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref47_trl ON ((ref47_trl.ad_reference_id=1001003) AND (C_Order.timeofdeliverydaytype=ref47_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref41_trl ON ((ref41_trl.ad_reference_id=150) AND (C_Order.InvoiceRule=ref41_trl.value ) ) LEFT OUTER JOIN M_PriceList AS M_PriceList_84 ON (M_PriceList_84.M_PriceList_ID=C_Order.M_PriceList_ID) LEFT OUTER JOIN C_Currency_trl AS C_Currency_78_trl ON ((C_Currency_78_trl.C_Currency_ID=C_Order.C_Currency_ID) AND (C_Currency_78_trl.ad_language='zh_CN')) LEFT OUTER JOIN C_ConversionType AS C_ConversionType_83 ON (C_ConversionType_83.C_ConversionType_ID=C_Order.C_ConversionType_ID) LEFT OUTER JOIN AD_User AS AD_User_58 ON (AD_User_58.AD_User_ID=C_Order.SalesRep_ID) LEFT OUTER JOIN C_Charge AS C_Charge_59 ON (C_Charge_59.C_Charge_ID=C_Order.C_Charge_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref103_trl ON ((ref103_trl.ad_reference_id=195) AND (C_Order.PaymentRule=ref103_trl.value ) ) LEFT OUTER JOIN C_PaymentTerm_trl AS C_PaymentTerm_76_trl ON ((C_PaymentTerm_76_trl.C_PaymentTerm_ID=C_Order.C_PaymentTerm_ID) AND (C_PaymentTerm_76_trl.ad_language='zh_CN')) LEFT OUTER JOIN ad_ref_list_trl_v AS ref39_trl ON ((ref39_trl.ad_reference_id=1001015) AND (C_Order.invoicetype=ref39_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref45_trl ON ((ref45_trl.ad_reference_id=1001007) AND (C_Order.paymentmethod=ref45_trl.value ) ) LEFT OUTER JOIN C_Activity AS C_Activity_71 ON (C_Activity_71.C_Activity_ID=C_Order.C_Activity_ID) LEFT OUTER JOIN C_Campaign AS C_Campaign_69 ON (C_Campaign_69.C_Campaign_ID=C_Order.C_Campaign_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref46_trl ON ((ref46_trl.ad_reference_id=131) AND (C_Order.DocStatus=ref46_trl.value ) ) LEFT OUTER JOIN C_DocType_trl AS C_DocType_70_trl ON ((C_DocType_70_trl.C_DocType_ID=C_Order.C_DocType_ID) AND (C_DocType_70_trl.ad_language='zh_CN')) LEFT OUTER JOIN ad_ref_list_trl_v AS ref105_trl ON ((ref105_trl.ad_reference_id=135) AND (C_Order.DocAction=ref105_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref107_trl ON ((ref107_trl.ad_reference_id=234) AND (C_Order.Posted=ref107_trl.value ) ) LEFT OUTER JOIN C_OrderSource AS C_OrderSource_79 ON (C_OrderSource_79.C_OrderSource_ID=C_Order.C_OrderSource_ID) LEFT OUTER JOIN ad_ref_list_trl_v AS ref55_trl ON ((ref55_trl.ad_reference_id=1001006) AND (C_Order.acceptancemethod=ref55_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref40_trl ON ((ref40_trl.ad_reference_id=319) AND (C_Order.advancepaymentrequired=ref40_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref53_trl ON ((ref53_trl.ad_reference_id=1001004) AND (C_Order.contracteffectiveway=ref53_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref31_trl ON ((ref31_trl.ad_reference_id=1001005) AND (C_Order.conditionofcontactinvalidation=ref31_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref34_trl ON ((ref34_trl.ad_reference_id=1001014) AND (C_Order.arbitration=ref34_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref52_trl ON ((ref52_trl.ad_reference_id=1001016) AND (C_Order.installationfeepaymentside=ref52_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref44_trl ON ((ref44_trl.ad_reference_id=1001012) AND (C_Order.warrantyperiod=ref44_trl.value ) ) LEFT OUTER JOIN ad_ref_list_trl_v AS ref35_trl ON ((ref35_trl.ad_reference_id=1001013) AND (C_Order.aftersaleservice=ref35_trl.value ) ) WHERE (C_Order.c_order_id =$P{C_Order_ID}) AND (C_Order.IsSOTrx='Y');
讨论栏
- 我觉得这样写报表还是比较繁琐的,不知道大家有没有更好的方法? --Peanut
- 建议:使用java程序或者存储过程将报表所需的原始数据统计汇总到几个报表数据表中,然后在通过报表控件从这些数据表中格式化输出到报表。--siantii
- 谢谢Siantii的建议!--Peanut
2010-12-02
JasperReport父子表问题
JasperReport父子表在客户端(Client)上的布署
- 今天用ireport建立父子报表,即包含子报表(subreport)的报表。在ireport上可以正常显示,但是在adempiere客户端却无法显示。
- 第二天把这个问题解决了,发现是iReport的版本问题。
- ADempiere 3.6.0 LTS 集成的JasperReport版本是3.7.3。而我用的iReport版本是3.7.6。今天重新下载了iReport-3.7.3,父子报表经编译后,在客户端(Client)可以正常显示。
JasperReport父子表在Web服务器上的布署
- JasperReport父子表在Web服务器上的布署
- JasperReport父子表在Web服务器上的布署,在官方文档Problem_with_subreports上发现子报表需要额外编译。后来经测试并不存在官方文档上所说的问题,详见2010-12-23实施日志。--2010年12月23日 Peanut Blake
- 我已经将官方文档Problem_with_subreports当中过时的内容进行了删除和修改。--2010年12月23日 Peanut Blake
讨论栏
2010-12-03
JasperReport大写金额的问题
- 父子表的问题解决了之后,今天用iReport来制作报表还算顺利。只是遇到中文大写金额的问题。
- 找到一篇文章在JasperReport报表中加入大写金额,不知道能否在ADempiere下实现。有空再试试。
2010-12-09
销售合同的报表格式调整
- 已经把销售合同的报表格式调整到位。
- 主要解决了以下问题:
- 页码问题。需要设置好Page_Number的有效范围。
- 文本框随内容自动调整高度问题;
- sub-report相对路径问题;
- 金额合计问题。需要新建变量,并设置好生效范围;
- ADempiere与JasperReports参数传递问题。
- 避免文本框跨页分断问题。将Band: Detail 1的属性Split Allowed设置为False。
- 文本框NULL值问题。将文本框属性Blank when Null设置为True。
2010-12-22
JasperReports导出PDF中文显示问题
问题描述
- 问题概述:
- JasperReports导出的PDF文件中,中文字符均显示为空白。
- 问题描述:
- JasperReports导出的PDF文件中,中文字符均显示为空白(不是空格、方框或乱码)。这个现象在iReport、ADempiere C/S 客户端、ADempiere B/S 浏览器端均存在。之前测试时只是检查了预览(或浏览)显示,没有检查导出的PDF文件,因此忽视了该问题。
- 系统环境:
- 操作系统:Linux/Debian Squeeze/Testing, amd64
- JVM:java-sun-1.6.0_22, 64bit
- iReport:3.7.3
- ADempiere:3.6.0 LTS
- 问题难点:
- 策略:
- 先在iReport中测试,成功后再进行ADempiere C/S 客户端测试,最后进行ADempiere B/S 浏览器端测试。
- 总体步骤:
- 先准备好字体
- 然后在iReport系统设置当中设置好字体
- 接着是对.jrxml报表中的元素属性进行设置
- 最后对ADempiere进行设置
具体步骤
Step 1 准备TTF字体文件
- 经测试,在ADempiere C/S客户端中JasperReports不支持TTC字体文件格式,因此要使用TTF字体文件。
- 为了保证开源方案的一致性,我们不采用微软的字体,例如微软的宋体、黑体等,而是采用开源的文泉驿字体。本测试中用文泉驿正黑字体 ( wqy-zenhei.ttc) .
- 由于文泉驿字体只提供TTC字体文件,因此需要转化。
- 找到工具ttc2ttf,使用其中的BREAKTTC.EXE程序。BREAK TTC这个名字大概是拆分TTC文件的意思吧。
- 在Windows操作系统下执行该程序。
BREAKTTC.EXE WQY-ZENHEI.TTC
- 该程序比较奇怪的地方是只认大写文件名,对于小写文件名会提示找不到该文件。
- 执行后拆分成3个文件——FONT00.TTF、FONT01.TTF、FONT02.TTF。分别是WenQuanYi Zen Hei, WenQuanYi Zen Hei Mono, WenQuanYi Zen Hei Sharp。
- 我们选用WenQuanYi Zen Hei,将文件更名为wqy-zenhei.ttf。
Step 2 在iReport设置字体引用
- 导入字体文件wqy-zenhei.ttf。
- 菜单:Tools -> Option -> Fonts -> Install Font
- 导出为字体扩展件wqy-zenhei.jar
- 菜单:Tools -> Option -> Fonts -> Export as extension
- 删除原导入字体文件wqy-zenhei.ttf
- 菜单:Tools -> Option -> Fonts -> Remove Font
- 导入wqy-zenhei.jar
- 菜单:Tools -> Option -> Classpath -> Add JAR
Step 3 元素PDF属性的设置
- 在iReport打开JRXML文件,例如 example.jrxml。
- 接下来对元素PDF属性的设置:
- Pdf Font name:有资料提到这个下拉框可能不会出现Classpath中引入的字体,我就遇到这种情况。这时可以直接手工输入字体名称,例如wqy-zenhei.ttf。
- Pdf Embedded:选不选均可。
- Pdf Encoding:选择Identity-H (Unicode with horizontal writing) 。
Step 4 iReport预览
- 点击Preview预览。检查显示是否正确。
- 点击Save保存图标,将报表保存为PDF格式,例如:example.pdf。
- 用Evince或Adobe Reader等工具打开导出的的PDF文件,检查显示是否正确。
- 经过以上步骤,iReport下的PDF中文测试就完成了。
Step 5 ADempiere布署
- ADempiere的C/S 客户端及B/S 浏览器端布署。
- ADempiere布署会涉及到的路径问题,一个是JasperReports文件本身的路径,另一个是JasperReports文件的元素PDF属性Pdf Font name所设置的字体路径。
- 路径可分为相对路径和绝对路径。
- 怎样集成JasperReports里PdfFontName举例用的是绝对路径:
- C/S布署:C:\Windows\Fonts\Arial.ttf
- B/S布署:http://192.168.2.109:8080/WebApp/arial.ttf
- 在这里不建议使用绝对路径,因为这样一来就不能同时布署C/S和B/S,除非你只采用一种布署方式。
- 我们建议采用相对路径,具体设置如下:
- 在iReport中将元素PdfFontName设置为:wqy-zenhei.ttf
- 在数据字典Report窗口将JasperReport路径设置为:example.jrxml
- 将example.jrxml文件复制到此路径下:$ADEMPIERE_HOME/reports/
- 将wqy-zenhei.ttf字体文件分别复制到两个路径下:
- $ADEMPIERE_HOME/
- $ADEMPIERE_HOME/reports/
- 注意:C/S客户端从$ADEMPIERE_HOME/路径读取字体文件,B/S浏览器端从$ADEMPIERE_HOME/reports/路径读取字体文件
- 最后在客户端及浏览器端分别进行报表导出PDF文件测试。
关于PDF中文问题的补充
关于jar字体包
- ADempiere不需要安装.jar字体包,只需要设置好.ttf字体文件即可。
关于WebApp.war
- 旧版的怎样集成JasperReports提到“If you are looking for an OS neutral solution you may add the font to your WebApp.war alongwith the .jrxml file and specify as pdfFontName the fullpath to it.”。
- 但是在我们的方案中,并不需要设置WebApp.war,同样可以做到与显示效果与操作系统无关。
关于iTextAsian.jar方案
- 有不少资料提到iTextAsian.jar方案(iTextAsian.jar 是一个亚洲字体包)。但是这个方法我们测试后不通过。
- 大概方法如下:
- 将iTextAsian.jar放到Classpath下;
- 元素PDF属性的设置如下:
- Pdf Font name:STSong-Light
- Pdf Embedded:True
- Pdf Encoding:选择UniGB-UCS2-H (Chinese Simplified)
一点技巧
- 在iReport当中对每一个元素都要进行PDF属性设置,这样比较繁琐。这里有一个小技巧,可以利用Style进行风格设定,统一设定Pdf Font name和Pdf Encoding。
关于Subreport子报表
SUBREPORT_DIR 子报表路径
- 旧版的怎样集成JasperReports提到:"When you run an Application Server Deployment, and you have a Subreport, then the Parameter SUBREPORT_DIR for establishing the location of the subreport must have a value like the one described in the section "Deploy reports on the application server":http://adempiereservename.domain/webApp/standalone.jrxml, or http://adempiereservename.domain:adempierewebport/webApp/standalone.jrxml, if the web port is other than 80. "
- 在实际使用中,不需要这么复杂,将SUBREPORT_DIR(子报表路径)设置为空,并将子报表与父报表统一放置在$ADEMPIERE_HOME/reports/路径下即可。
关于子报表无法显示的问题
- 旧版的怎样集成JasperReports提到:"When you have a report which contain a subreport into, you need to use the last with the compiled files (it’s .jasper file). Sometimes, if you use iReport to compile/edit your reports, maybe you compile the subreport with this tool. When you test the report from iReport, it work fine; but when you run the report from ADempiere, you can’t see nothing. "
- 旧版的怎样集成JasperReports说的是subreports(子报表)在ADempiere无法显示时,要在ADempiere内将.jrxml文件编译成.jasper文件,并放入路径:D:\Adempiere\jboss\server\adempiere\deploy\WebApp.ear\webApp.war\
- 在我们的实际测试当中,并不存在上述问题。
- 我们的子报表在iReport下编译,放在$ADEMPIERE_HOME/reports目录下。
- 同时在iReport中将子报表SUBREPORT_DIR(子报表路径)设置为空。
- 这样B/S和C/S方式均可正常显示了。
讨论栏
2011-03-05
销售统计分析报表
- 销售模块从12月运行到现在已经有三个月了,已经积累了一批数据,接下来进行销售统计分析报表的制作。
- 报表制作大概历时5天,共计7张报表。下载:销售统计分析表.PDF
几点体会
- 汇总报表可以为企业管理层提供分析数据和决策依据。报表做得直观、实用,成为管理层的有力工具,这样ERP实施才能获得管理层更多的信任和支持。
- 通过报表的汇总数据,可以很直观地发现数据录入的错误或缺漏,为ERP数据准确性提供了一种检查手段。
主要进行了以下工作:
数据库结构及数据的调整
- 原ERP没有上年遗留订单、本年销售指标等数据,于是新增了两个表—— c_order_lastyear、c_salestarget。
- 新增了一些字段,如在c_order表新增出货日期、产品类别等等。
- 根据新的市场区域划分调整了市场区域设置。
JasperReport
- SQL查询语句比较复杂,要综合使用UNION, GROUP BY, CASE WHEN等语句。曾经考虑过使用中间表,但是不便于操作和实时更新,于是未采用。
- CrossTab和Chart的学习和应用。主要难点有两个:
- 一是搞清楚CrossTab和Chart能做什么,不能做什么;
- 二是排序,尤其是中文字符串的排序。后来采用的方法是在中文前加数字前缀,如 01-国内、02-国外,同时SQL查询语句中用Order By先行排序,这样来解决问题。
附:用于JasperReport报表的SQL语句
--2011年新订单汇总 包装机械-结合年度指标 SELECT c_salesregion.name as 销售区域, CASE WHEN amountlastyear.amount is null THEN 0 ELSE amountlastyear.amount END as 上年遗留订单金额, CASE WHEN neworderamount.amount_cny is null THEN 0 ELSE neworderamount.amount_cny END as 金额_人民币, CASE WHEN salestarget.salestarget is null THEN 0 ELSE salestarget.salestarget END as 年度销售目标 FROM (( SELECT c_salesregion_id FROM c_salestarget WHERE c_salestarget_batch_id=1000004) UNION (--新订单汇总 按产品系列 SELECT c_salesregion.summarytoid2 AS region_summary_id FROM c_order INNER JOIN c_bpartner ON c_bpartner.c_bpartner_id=c_order.c_bpartner_id INNER JOIN c_salesregion ON c_salesregion.c_salesregion_id = c_bpartner.c_salesregion_id WHERE (c_order.C_DocTypeTarget_ID = 1000030) --sales order AND (c_order.ad_client_id = 1000000) --tongji client AND (c_order.DateOrdered BETWEEN '2011-01-01' AND '2011-12-31') --year 2011 AND (c_order.ProductSeries='B') --包装机械 GROUP BY c_salesregion.summarytoid2 ) ) region LEFT OUTER JOIN ( SELECT c_salesregion_id, salestarget FROM c_salestarget WHERE c_salestarget_batch_id=1000004 ) salestarget ON salestarget.c_salesregion_id=region.c_salesregion_id LEFT OUTER JOIN (--新订单汇总 按产品系列 SELECT c_salesregion.summarytoid2 AS region_summary_id, SUM(case WHEN C_Currency.iso_code='CNY' THEN c_order.TotalLines WHEN C_Currency.iso_code='USD' THEN c_order.TotalLines*6.7 ELSE 0 END) AS amount_cny FROM c_order INNER JOIN C_Currency ON C_Currency.C_Currency_ID = c_order.C_Currency_ID INNER JOIN c_bpartner ON c_bpartner.c_bpartner_id=c_order.c_bpartner_id INNER JOIN c_salesregion ON c_salesregion.c_salesregion_id = c_bpartner.c_salesregion_id WHERE (c_order.C_DocTypeTarget_ID = 1000030) --sales order AND (c_order.ad_client_id = 1000000) --tongji client AND (c_order.DateOrdered BETWEEN '2011-01-01' AND '2011-12-31') --year 2011 AND (c_order.ProductSeries='B') --包装机械 GROUP BY c_salesregion.summarytoid2 ) neworderamount ON neworderamount.region_summary_id =region.c_salesregion_id LEFT OUTER JOIN ( SELECT c_salesregion_id, amount FROM c_orderamount_lastyear WHERE c_orderamount_lastyear_batch_id=1000003 ) amountlastyear ON amountlastyear.c_salesregion_id = region.c_salesregion_id LEFT OUTER JOIN c_salesregion ON ( c_salesregion.c_salesregion_id = region.c_salesregion_id ) ORDER BY 销售区域 ;
讨论栏
- 一楼
- 是否考虑过实施Adempiere和Pentaho相结合的方法来呈现数据?毕竟在这方面Adempere还是比较弱的,通过开源的BI来做的话就会非常好。 --Chopin