深入浅出Oracle EBS之Excel报表开发

1、Doc Ref: <Document Reference Number> Oracle ERP最佳技术实践六月 19, 2007Oracle ERP最佳技术实践E-Business SuiteOracle核心应用技术Excel报表开发Author:黄建华Jianhua.HuangMSN:huajhuaCreation Date:March 14, 2007Last Updated:六月 19, 2007Document Ref:<Document Reference Number> Version:DRAFT 1AApprovals:<Approver 1>

2、<Approver 2> Copy Number_Document ControlChange Record3DateAuthorVersionChange Reference14-Mar-07Jianhua.HuangDraft 1aNo Previous DocumentReviewersNamePositionDistributionCopy No.NameLocation1Library MasterProject Library2Project Manager34Note To Holders:If you receive an electronic copy of th

3、is document and print it out, please write your name on the equivalent of the cover page, for document control purposes.If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.ContentsDocument Controlii1.Overview and Key Points21.1.读者要求21

4、.2.开发背景21.3.设计思路32.开发框架及其安装62.1.开发框架62.2.开发模版介绍132.3.框架安装173.基于模版的开发步骤193.1.开发步骤193.2.报表实例224.客户端安装过程244.1.安装并设置Excel244.2.Oracle客户端：免安装254.3.Oracle客户端：快速安装254.4.Oracle客户端：普通安装265.用户使用295.1.296.Open and Closed Issues for this Deliverable30Open Issues30Closed Issues30Open and Closed Issues for this D

5、eliverable 30Excel报表开发File Ref: 74ea718fcacc2e91ed8d8796d05bc09e.pdf (v. DRAFT 1A )Company Confidential - For internal use only1. Overview and Key Points1.1. 读者要求1.1.1. 最小要求本文档需要读者具至少有如下技能：1、 简单的VBA知识。2、 会使用Excel。3、 能够基于EBS写出报表SQL。1.1.2. 期望水平如果要深刻理解代码框架、进行优质的开发维护，那么需要：1、 熟练使用Excel，尤其是其中的高级功能如汇总函数、条件

6、格式、数据透视表等。2、 会使用VBA进行开发，控制Excel的表现形式、处理数据。3、 熟悉PL/SQL，对数据库的对象创建、性能优化有较深的感悟和处理能力。1.2. 开发背景1.2.1. 来自Excel的压力微软的Excel是如此的深入人心，不管是谁，凡是使用电脑办公或和IT沾边，都会使用，有些用户“玩转”起Excel，用出神入化来形容也决不为过。直观的二维表、随意拷贝、筛选、排序、引用、统计、公式、数据透视表、图表、可录制的宏、惊人的函数库、样式、条件格式这些诱人的功能使用起来是如此的简便。对用户来说，Excel比ERP更具亲和力、更有吸引力，而且他们喜欢把数据放在Excel中“把玩”。

7、不管什么报表，Reports、PL/SQL、SQL*Plus、BI Publisher（之前叫XML Publisher）、Discoverer，用户都希望能够方便的放到Excel中，自己做二次加工。然而，从报表结果到Excel，都需要Click很多次，而且新数据要和先前的二次加工结合起来，至少还得拷贝几下。不方便哪！1.2.2. Oracle的努力在PC桌面应用这一领域，Oracle也得放下架子，不得不致力于与Office的集成。比如AIM文档、BI Publisher Desktop都是基于Word的模版和VBA，早期的ADI Client也是基于Excel及其VBA。而现在和将来，Ora

8、cle将致力于发展Web ADI，其将充分发挥Excel的本地加工能力，确保数据和用户的二次分析有机结合，并把这种结合保存下来下次直接使用，这在EBS12版中已经实现！方便哪！然而大部分企业都未升级到EBS12，如果早日为用户和高层带来便捷的体验，看来惟有基于Excel做些开发了。1.3. 设计思路1.3.1. 总体设想在做Excel报表开发时，是基于如下一些基本思想：1、 不想做的过于复杂，只要能从Excel方便的连上DB，把数据拉到Sheet即可。2、 利用Excel本身的功能对这些数据进行二次加工，得到分析结果。3、 刷新数据时，能够保留这些二次加工，直接得到分析结果。4、 取数时要有简

9、单的参数如日期等，这样可以提高速度、过滤不关心的数据。5、 需要简单的密码验证，不想让任何人拿到Excel文件都能刷新数据。1.3.2. Excel如何连接Oracle最终采用VBA ADO连接，使用Windows自带的Microsoft ODBC for Oracle驱动，因为这两个不用额外安装，减少维护量。此外，要连接Oracle，必须至少装有一个Oracle的客户端，数据库客户端、ADI、Developer、Discoverer、Workflow等等都可以。注：之前考虑过用Microsoft的Query功能，但不好控制；有人建议用Microsoft OLE DB Provider for

10、 Oracle，性能要好点。1.3.3. 参数与性能考虑参数的基础作用是为用户过滤出想要的数据，这个毋庸置疑；然而其对于性能从而对用户满意度的影响也是不可小觑的。如果参数是基于View的，比如Oracle的Web ADI和Discoverer，其可能引起严重的性能问题。因为View可能是经过汇总函数如Sum、集合操作如Union等出来的，优化器很难将作用在外层的过滤条件“渗透”到内层基表，也就是，对用户来说，数据是过滤过了，但对数据库运算来说，其还是将所有涉及到的表、所有数据，经过耗时的关联、汇总后，才开始过滤，这足以导致某些报表要运行半个小时甚至更长。有个显而易见的方法可以解决：在ADO不使

11、用View，而是采用完整的Select语句，在查询前，根据用户参数拼出Select语句，将参数尽量作用在最底层的基表。这个完美的方法遇到了一个问题，SQL语句太长时，ADO报错！为了统一开发模式，看来只能在ADO中使用简单的View了，那么该如何避免性能问题呢？为此我们可采用另外一个方法：创建了一个公共的参数表cux_rpt_parameters，有15个Varchar2的字段和一个session_id、user_id字段，该表仅有一条session_id为00000000的测试数据，用dbms_stats.gather_table_stats收集后，优化器基本都会将其作为驱动表；创建View

12、时将参数表加入基表的连接条件中，并用session_id=userenv('SESSIONID')过滤；用户查询数据前，将参数连同当前的userenv('SESSIONID')插入到参数表，查询后Rollback。此方案中不同用户使用不同的记录，查询后又回滚，这样既保证参数表永远仅保持1条记录（注：查询时会有2条），又保证了多用户并发时不会产生锁等待。1.3.4. 多组织问题Oracle EBS中的数据是分组织的，一个用户不应当看到其它组织比如其他工厂的数据。我们的应对方案很简单：定义用户时为其指定允许查询的组织范围，这样在创建View时就可以根据这个用户过滤组

13、织数据了。1.3.5. 数据放在什么位置Excel是多Sheet的，用户可能也希望将不同日期的数据放在不同的Sheet中；每个报表都有基本的标题、一些超过“标准”的警示（如小于5单元格用红色填充）、一些可能的汇总行、计算列等等。这样，ADO将数据从DB查出后，最终放到Excel中时，需要做出如下考虑：1、 目标Sheet可以指定，不过初始时要有个模版页，我们起名为“Data”，如果用户指定了目标页如“XXXX”，VBA中首先将“Data”拷贝为“XXXX”，这样可以保留用户的二次加工如计算列、汇总行、条件格式等。2、 基础数据应该从指定的行开始显示，其上部分用户加入的标题、汇总行、Logo等，

14、都可以保留。3、 基础数据应该有固定的列数，其后部分用户加入的汇总列等，都可以保留。4、 有些时候，用户可能需要给数据表编个序号，这个我们安排在第一列。1.3.6. 简单的权限控制模型考虑到维护量，这种报表不可能大规模使用，所以采取“用户-报表”的模型直接控制一个用户到底能运行哪些报表，中间不采用诸如职责的组；这二者及其关系我们可以在表里直接维护，也可在ERP中开发简单的Form来统一维护；而用户权限的验证、密码的修改、密码安全性的实施，可以通过一个Package cux_rpt_utils来集中完成。VBA运行报表前，将用户名/密码/报表名称/版本传递给cux_rpt_utils.valid

15、ate_login，获得验证结果。如果通过，则进一步从数据库获得报表对应的View。字段级权限的权限控制，采取明确排除的方式，定义了一个表来保存哪些用户不能看哪些报表的哪些字段。1.3.7. 数据库方面的权限考虑EBS的APPS是绝对不能用的，因为它什么事都能干，正式环境的话连开发人员都拿不到密码；客户化用户CUX也是不能用的，因为其有对CUX表的所有权限；于是规划一个最小权限的用户如CUXRPT比较合适；同时也要考虑视图更新等的维护便利性。1、 “权限控制模型”中的表创建在CUX下，Package为了修改方便也维护在CUX下，然后把Package的执行权限授给CUXRPT。2、 公共参数表创

16、建在CUX下，并把查询权限授给CUXRPT。3、 其他任何Schema下的表和APPS下的视图，只有确切需要时，才向DBA申请，把查询权限授给CUXRPT；报表稳定后，也可收回表权限，在APPS下直接创建View然后授权给CUXRPT。1.3.8. 运行日志可以简单记录用户的运行日志，这样可以获得哪些用户在哪些时间用了哪些报表发生了哪些问题，为日后跟踪和分析留下必要的线索。1.3.9. 开发模版和客户化思路并非所有报表，其列数都一样，都需要运行额外的宏（初始化日期参数、生成数据透视表、生成图表等等），所以也要求我们设计一个开发模版，并留出客户化的函数调用，这样整个Excel报表代码分为两部分：

17、1、 公共模版部分，所有报表都一样，有4个模块，模版有变时可直接覆盖。2、 客户化部分，每个报表可以按照约定做出修改，仅有1个Custom模块。1.3.10. 尚待改进的地方1、 用户密码目前没有加密，直接保存在表中。2、 VBA中连接数据库的密码，也是写死在代码中。3、 因为每个报表对应的就是一个Excel文件，直接拷贝给用户使用，如果以后有任何修改，更新都比较麻烦。2. 开发框架及其安装2.1. 开发框架2.1.1. 表：用户定义用户保存在表CUX_RPT_USERS中，主要字段如下：Column NameType空否默认DescriptionUSER_IDNUMBERN内部ID，唯一性U

18、SER_NAMEVARCHAR2(30)N用户名，唯一性ENCRYPT_PASSWORDVARCHAR2(4000)N当前密码LAST_ENCRYPT_PASSWORDVARCHAR2(4000)N最后一个历史密码LAST_PASSWORD_DATEDateNSYSDATE最后一次修改密码的时间EMAIL_ADDRESSVARCHAR2(240)YEMAIL地址DESCRIPTIONVARCHAR2(240)Y用户说明EFFECTIVE_DATEDATENSYSDATE有效日期DISABLE_DATEDATEY失效日期2.1.2. 表：用户的组织用户的组织保存在表CUX_RPT_USER_OR

19、G中，主要字段如下：Column NameType空否默认DescriptionUSER_ORG_IDNUMBERN内部ID，唯一性USER_IDNUMBERN用户内部ID，和组织ID共同构成唯一性ORGANIZATION_IDNUMBERN组织ID，和用户D共同构成唯一性，从hr_all_organization_units_vl取数DESCRIPTIONVARCHAR2(240)Y用户说明EFFECTIVE_DATEDATENSYSDATE有效日期DISABLE_DATEDATEY失效日期2.1.3. 表：报表定义报表保存在表CUX_RPT_REPORTS中，主要字段如下：Column N

20、ameType空否默认DescriptionREPORT_IDNUMBERN内部ID，唯一性REPORT_CODEVARCHAR2(30)N报表名称，唯一性DESCRIPTIONVARCHAR2(240)Y报表说明EFFECTIVE_DATEDATENSYSDATE有效日期DISABLE_DATEDATEY失效日期2.1.4. 表：报表版本报表保存在表CUX_RPT_REPORT_VERSIONS中，主要字段如下：Column NameType空否默认DescriptionVERSION_IDNUMBERN内部ID，唯一性REPORT_IDNUMBERN报表IDSQL_TEXTVARCHAR2

21、(4000)N报表脚本，一般是：视图名称，大写VERSION_NUMBERVARCHAR2(30)N'1.0'版本号，如果报表版本和这个不一致，运行时将警告EFFECTIVE_DATEDATENSYSDATE有效日期DISABLE_DATEDATEY失效日期DESCRIPTIONVARCHAR2(240)Y报表说明2.1.5. 表：用户-报表关系用户和报表的关系保存在表CUX_RPT_USER_REPORTS中，主要字段如下：Column NameType空否默认DescriptionUSER_REPORT_IDNUMBERN内部ID，唯一性REPORT_IDNUMBERN报表

22、内部ID，和USER_ID共同构成唯一性USER_IDNUMBERN用户内部ID，和REPORT_ID共同构成唯一性DESCRIPTIONVARCHAR2(240)Y说明EFFECTIVE_DATEDATENSYSDATE有效日期DISABLE_DATEDATEY失效日期2.1.6. 表：字段权限字段权限保存在表CUX_RPT_COLUMN_PRIV中，主要字段如下：Column NameType空否默认DescriptionCOLUMN_PRIV_IDNUMBERN内部ID，唯一性USER_REPORT_IDNUMBERN用户报表关系内部ID，和COLUMN_NAME构成唯一COLUMN_N

23、AMEVARCHAR2(30)N视图名称，从all_tab_columns取数PRIVILEGEVARCHAR2(1)NN是否有权限，默认NDESCRIPTIONVARCHAR2(240)Y说明EFFECTIVE_DATEDATENSYSDATE有效日期DISABLE_DATEDATEY失效日期2.1.7. Form：用户和报表维护用户和报表的维护可直接在表里进行，也可通过Form CUXRPTFND，该Form有两个标签页：一个定义用户，同时维护明细块用户对应的报表；另一个定义报表，同时维护明细块报表对应的用户。定义用户：定义报表：2.1.8. 表：公共参数用户运行时的参数保存在CUX_RP

24、T_PARAMETERS中，主要字段如下：Column NameType空否默认DescriptionPARAMETER1VARCHAR2(240)Y参数1PARAMETER2VARCHAR2(240)Y参数2VARCHAR2(240)Y略去其它参数字段PARAMETER15VARCHAR2(240)Y参数15USER_IDNUMBERY用户IDSESSION_IDVARCHAR2(40)N用户会话ID2.1.9. 表：使用日志用户使用报表的日志保存在CUX_RPT_ACCESS中，主要字段如下：Column NameType空否默认DescriptionACCESS_IDNUMBERN内部I

25、D，唯一性LOCAL_HOST_NAMEVARCHAR2(100)Y客户端机器名LOCAL_LOGIN_USER VARCHAR2(100)Y客户端登录操作系统地用户名LOCAL_WINDOWS_VERSTIONVARCHAR2(100)Y客户端Windows版本LOCAL_OFFICE_VERSIONVARCHAR2(100)Y客户端Office版本LOCAL_ORGANIZATION_NAMEVARCHAR2(100)Y客户端Office中的组织名称LOCAL_USER_NAMEVARCHAR2(100)Y客户端Office中的用户名称LOCAL_PATHVARCHAR2(500)Y客户端

26、报表文件全路径LOCAL_FILE_NAMEVARCHAR2(500)Y客户端报表文件名REPORT_IDNUMBERY报表内部IDACCESS_DATEDATEYSYSDATE访问时间USER_IDNUMBERY用户内部IDMESSAGESVARCHAR2(4000)Y运行时消息，可能是错误消息2.1.10. View：用户用户表含有密码，所以不能直接授权给其他DB用户，需要构造一个视图后再授权：CREATE OR REPLACE VIEW CUX.CUX_RPT_USERS_V ASSELECT user_id, user_name, email_address, description,

27、 effective_date, disable_date FROM cux.cux_rpt_users;2.1.11. View：参数公共参数表结合用户组织表，可以构造一个综合的参数视图，供各个具体的报表使用：CREATE OR REPLACE VIEW CUX.CUX_RPT_PARAMETERS_V ASSELECT DISTINCT anization_id, para.parameter1, para.parameter2, para.parameter3, para.parameter4, para.parameter5, para.parameter6, para.p

28、arameter7, para.parameter8, para.parameter9, para.parameter10, para.parameter11, para.parameter12, para.parameter13, para.parameter14, para.parameter15 FROM cux.cux_rpt_users usr, cux.cux_rpt_user_org uo, cux.cux_rpt_parameters para WHERE usr.user_id = para.user_id AND usr.user_id = uo.user_id AND p

29、ara.session_id = userenv('SESSIONID');2.1.12. Package：参数处理和权限控制参数处理和权限控制集中在包cux_rpt_utils中，过程和函数说明如下：1、 过程：set_parameters作用：用来将用户的参数Insert到公共参数表。参数：15个参数值。逻辑：删除session_id = userenv('SESSIONID')。2、 函数：validate_login作用：验证用户的密码和报表权限、版本比较。参数：用户名、密码、报表代码、报表版本。返回：Y/成功、N/失败、W/警告。逻辑：首先验证用户名和

30、密码，如果通过再验证是否具有运行指定报表的权限，如果有，比较版本，如果版本不一样，则给出警告；把错误信息和警告信息放入Package的变量l_message中、把各种ID和值缓存到对应的l_xxxxxx变量中。3、 函数：change_password作用：修改密码。参数：用户名、旧密码、新密码。返回：Y/成功、N/失败。逻辑：首先验证用户名和旧密码，如果通过再验证新密码是否符合密码安全性要求，如长度必须大于6、不能和最后一次密码相同等等；把错误信息放入Package的变量l_message中。4、 过程：log_msg作用：保存用户运行报表的信息、遇到的错误至使用日志表中。逻辑：直接插入表。

31、5、 函数：get_message作用：返回最新消息。返回：直接返回l_message。6、 函数：get_xxxxxx作用：获得各种值，比如用户ID、报表SQL等。返回：直接返回l_xxxxxx。2.1.13. Function：取得组织列表和字段权限列表取得组织列表：CREATE OR REPLACE FUNCTION get_rpt_user_org(p_user_id IN NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(4000);BEGIN FOR a_rec IN (SELECT anization_code FROM cu

32、x.cux_rpt_user_org uo, _organization_definitions org WHERE anization_id = anization_id AND uo.effective_date <= trunc(SYSDATE) AND nvl(uo.disable_date, trunc(SYSDATE) >= trunc(SYSDATE) AND uo.user_id = p_user_id ORDER BY 1) LOOP l_result := l_result | ',' | a_rec.o

33、rganization_code; END LOOP; l_result := substr(l_result, 2); RETURN l_result;END;字段权限列表：CREATE OR REPLACE FUNCTION get_rpt_column_priv(p_user_id IN NUMBER, p_report_id IN NUMBER) RETURN VARCHAR2 IS l_result VARCHAR2(4000);BEGIN FOR a_rec IN (SELECT DISTINCT cp.column_name FROM cux.cux_rpt_user_repor

34、ts ur, cux.cux_rpt_column_priv cp WHERE ur.user_id = p_user_id AND ur.report_id = p_report_id AND cp.effective_date <= trunc(SYSDATE) AND nvl(cp.disable_date, trunc(SYSDATE) >= trunc(SYSDATE) AND cp.privilege = 'N' ORDER BY 1) LOOP l_result := l_result | ',' | a_rec.column_name

35、; END LOOP; l_result := substr(l_result, 2); RETURN l_result;END;2.1.14. Sheet：参数和运行页用户输入的参数不通过VBA中的表单来处理，而是直接用一个“Parameter”Sheet来保存，分为“报表设置”和“报表参数”两部分，为了在VBA中引用方便，该Sheet中的一些单元定义了书签：参数类型书签单元格作用报表设置USER_NAMED4保存用户名，这样用户运行报表时不需要每次都输入用户名USER_DESCRIPTIONE4成功登录后，将把该用户对应的描述从表中取到这里AUTO_FRESHD5打开Excel时是否自动重

36、新取数IS_SEQ_NEEDEDD6每行前面加行号，需要手工增删Data模版第一列DATA_BEGIND7数据从那一行开始显示RESULT_SHEETD8运行结果页。Data是默认的模版和结果页报表参数PARAMETER1D9参数1，不同报表含义可能不一样PARAMETER2D10参数2，不同报表含义可能不一样PARAMETER10D18参数10，不同报表含义可能不一样，目前最多启用10个当然了，要求用户不能将该Sheet删除，也不能增、删行、列，不能修改书签。此外，“修改密码”和“运行报表”这两个功能按钮也放在这个页面中。具体请看如下截图：2.1.15. Sheet：结果页Data为默认的结

37、果页，也就是用户不能删除这个页，如果没有什么二次加工，那么该页实际上可以为空，什么都不需要；不过通常至少有一行显示列标题：2.1.16. Sheet：Help页本页放空，供开发者放置帮助内容之类的。2.1.17. VBA模块：Utils_Password仅仅用来将密码输入用*号表示。2.1.18. VBA模块：Utils仅仅用来将统一处理消息显示、判断Sheet是否存在、删除指定Sheet、添加指定Sheet。2.1.19. VBA模块：DBUtils仅仅用来将统一处理数据库连接、执行数据查询、事务处理。2.1.20. VBA模块：Custom针对不同报表需要写客户化代码的地方；这里面的函数都

38、是CUX开头，都是在Reports模块中的适当时机来调用的。客户化时机如下：1、 Cux_Auto_Open：打开Exel时运行2、 Cux_Init：初始化后执行3、 Cux_Pre_Query：查询前执行，一般用来验证条件4、 Cux_After_Query：查询后执行，可用来设置公式、创建透视图5、 Cux_Post_Query：每行查询出来后触发，一般不用具体代码和注释请直接打开VBA查看。2.1.21. VBA模块：Reports核心代码，唯一的两个用户入口点“修改密码”、“运行报表”就是分别调用这里面的Change_Password和Query_Data。2.1.22. VBA模块

39、：Change_Password逻辑“修改密码”的代码比较简单，逻辑如下：1、 打开数据库连接，如果失败，则报错，结束2、 输入旧密码3、 输入新密码，两次，如果不同，则重新输入新密码4、 调用数据库包cux_rpt_utils.change_password，修改密码5、 不管成功与否，都从数据库取出Message显示；如果成功，同时把新密码保存在内存变量中2.1.23. VBA模块：Query_Data逻辑“运行报表”的代码比较复杂，逻辑如下：2.2. 开发模版介绍2.2.1. 模版功能开发模版Template.xls实现了如下简单的功能：1、 取出指定日期范围内的库存事务处理，View是

40、CUX_RPT_TEMPLATE_VVCREATE OR REPLACE VIEW CUX_RPT_TEMPLATE_VV ASWITH para AS (-日期等参数，统一在这里准备好SELECT anization_id, trunc(to_date(nvl(para.parameter1, to_char(SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD') date_begin, trunc(to_date(nvl(para.parameter2, to_char(SYSDATE, 'YYYY-MM-DD&#

41、39;), 'YYYY-MM-DD') + 0.99999 date_end FROM cux_rpt_parameters_v para-测试用数据，因为直接在PL/SQL中运行时cux_rpt_parameters_v没有数据UNION ALLSELECT 82, trunc(to_date('2007-03-29', 'YYYY-MM-DD') date_begin, trunc(to_date('2007-03-29', 'YYYY-MM-DD') + 0.99999 date_end FROM dual

42、WHERE cux_rpt_utils.get_user_id = 0)SELECT anization_code, anization_name, mmt.subinventory_code, mst.segment1 item_code, trunc(mmt.transaction_date) transaction_date, mmt.transaction_quantity, mmt.transaction_uom FROM inv.mtl_material_transactions mmt, inv.mtl_system_items_b mst, apps

43、.org_organization_definitions org, para WHERE anization_id = anization_id AND mmt.inventory_item_id = mst.inventory_item_id AND anization_id = anization_id AND anization_id = anization_id AND mmt.transaction_date BETWEEN para.date_begin AND para.date_end AN

44、D mmt.subinventory_code LIKE 'ZCP%' AND mst.segment1 LIKE '100E%'2、 根据结果自动生成“PivotSheet”和“PivotChart”。数据透视表：对应的图表：具体内容请直接看Template.xls。2.2.2. Custom模块代码VBA其它模块代码不用看，仅需关注Custom模块，完整代码如下：'本模块内容，不能删除过程和函数，请根据下面的注释修改；具体开发和维护请阅读MD030_MFG_Excel报表开发Option Explicit'打开Exel时运行Sub Cux_A

45、uto_Open() '1、初始化参数，如设置日期为系统日期 'parameter1.Value = DateEnd Sub'初始化后执行Sub Cux_Init() '数据库，这里比较麻烦，目前密码写死，如果密码修改了，维护比较麻烦 'GTDEV CUXRPT connection_url = "DRIVER=Microsoft ODBC for Oracle;UID=CUXRPT;PWD=handdev;SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=9)(PORT

46、=1590)(CONNECT_DATA=(SID=GTDEV);" 'GTPROD Oracle connection_url = "DRIVER=Microsoft ODBC for Oracle;UID=CUXRPT;PWD=k8n3vd2p9;SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=3)(PORT=1620)(CONNECT_DATA=(SID=GTPROD);" field_count = 7 '字段数，不同报表需要修改 report_code = &quo

47、t;Excel报表模版" '报表名称，不同报表需要修改 version_number = "1.0" '版本号End Sub'查询前执行，一般用来验证条件Function Cux_Pre_Query() As Boolean Cux_Pre_Query = False '这里可对参数做些验证，不通过则Cux_Pre_Query = False即可，一般不用改 '举例：非空 If Len(Trim(parameter1.Value) = 0 Then MsgBox "开始日期不能为空", , "信

48、息", Err.HelpFile, Err.HelpContext Cux_Pre_Query = False Exit Function End If If Len(Trim(parameter2.Value) = 0 Then MsgBox "结束日期不能为空", , "信息", Err.HelpFile, Err.HelpContext Cux_Pre_Query = False Exit Function End If '举例：大小值 If parameter2.Value < parameter1.Value Then

49、MsgBox "结束日期不能小于开始日期", , "信息", Err.HelpFile, Err.HelpContext Cux_Pre_Query = False Exit Function End If Cux_Pre_Query = True End Function'查询后执行，可用来创建透视图Sub Cux_After_Query() Dim a_sheet As Worksheet On Error GoTo ErrorHandler '1、可在这里设置公式、条件格式等，全局变量data_begin_row保存数据开始行号，t

50、otal_row保存数据结束行号 '2、下面这句删除所有非核心Sheet，核心Sheet是Parameter和Data，对于允许用户设置目标Sheet的报表来说，这句要注释掉 Delete_All_Result_Sheet '3、这下面写数据透视表、图表的代码，无此需求的报表请把下面代码注释掉，有此需求的报表请改写下面代码 Set a_sheet = ActiveWorkbook.Sheets.Add a_sheet.Name = "PivotSheet" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDataba

51、se, SourceData:="Data!A1:G36000").CreatePivotTable TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="数据透视表" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("数据透视表&

52、quot;).SmallGrid = False With ActiveSheet.PivotTables("数据透视表").PivotFields("组织") .Orientation = xlPageField .Position = 1 End With With ActiveSheet.PivotTables("数据透视表").PivotFields("日期") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("数据透视表").PivotFields("子库") .Orientation = xlColumnField .Position = 1 End With Wit