cognos报表数据项表达式函数的应用.doc

ERPF报表维护手册 建设银行ERPF项目文档编号CCBBI_PM061110_001版 本 号V1.00密 级内部资料cognos报表数据项表达式函数的应用手册文档信息项目名称北京建总行ERPF报表项目项目经理张成浩文档名称cognos报表数据项表达式函数的应用手册批复信息负责人日期意见拟制审核批准文件控制版本记录日期作者版本变更说明2006-11-10郑广珠V1.0审阅记录日期审阅者意见分发记录日期接受人地点润信科技 第1页ERPF报表维护手册 建设银行ERPF项目目录1引言51.1编写目的51.2背景51.3参考资料51.4术语定义及说明52汇总函数62.1aggregate62.2average62.3count62.4maximum72.5median72.6minimum72.7moving-average72.8moving-total82.9percentage92.10percentile92.11quantile92.12quartile102.13rank112.14running-average112.15running-count122.16running-difference122.17running-maximum132.18running-minimum132.19running-total142.20standard-deviation142.21standard-deviation-pop152.22total152.23variance152.24variance-pop153业务日期/时间函数173.1_add_days173.2_add_months173.3_add_years173.4_age183.5_day_of_week183.6_day_of_year183.7_days_between183.8_days_to_end_of_month193.9_first_of_month193.10_last_of_month193.11_make_timestamp203.12_months_between203.13_week_of_year203.14_years_between203.15_ymdint_between214块函数224.1_firstFromSet224.2_remainderSet225供应商特定函数（Oracle函数）235.1add_months235.2ascii235.3char_length235.4chr235.5concat245.6decode245.7dump245.8greatest245.9initcap255.10instr255.11instrb255.12least265.13length265.14lengthb265.15lpad265.16ltrim275.17months_between275.18new_time275.19next_day275.20nls_initcap285.21nls_lower285.22nls_upper285.23nvl285.24replace295.25round295.26rpad295.27rtrim305.28sign305.29soundex305.30substr305.31substrb315.32to_char315.33to_date315.34to_number315.35translate325.36trunc325.37trunc325.38vsize325.39sysdate335.40user336Oracle Cast函数346.1cast_varchar346.2cast_timestamp347Oracle数学函数357.1Log358Oracle三角函数368.1acos368.2asin368.3atan368.4atan2368.5cos378.6cosh378.7sin378.8sinh378.9tan388.10tanh389通用函数399.1_format399.2_round399.3abs399.4cast399.5ceiling409.6character_length409.7coalesce409.8current_date409.9current_time419.10current_timestamp419.11exp419.12extract419.13floor429.14ln429.15localtime429.16localtimestamp429.17lower439.18mod439.19octet_length439.20position439.21power449.22sqrt449.23substring449.24trim449.25upper45润信科技 第45页1 引言1.1 编写目的整理cognos的report studio中在数据项表达式中比较常用的函数的应用。并可供以后需要用到的同事参考。1.2 背景在开发报表过程中积累的一些经验和总结。1.3 参考资料Cognos中的帮助文档和oracle的参考文档。1.4 术语定义及说明2 汇总函数2.1 aggregate用法：aggregate ( expr auto )aggregate ( expr for all | any expr , expr )aggregate ( expr for report )作用：根据表达式的聚合类型，返回使用相应的聚合函数计算得出的值。Example：aggregate (本期余额)根据数据项本期余额的聚合类型，返回使用相应的聚合函数计算得出的值。如果本期余额是sum（）得到，则返回所有本期余额的合计。2.2 average用法：average ( distinct expr auto )average ( distinct expr for all | any expr , expr )average ( distinct expr for report )作用：返回选定数据项的平均值。关键字distinct的作用是，当某个数据项有多个重复的项时，当中只有一个数据项有效。Example：average ( 本期余额 )返回所有本期余额的平均值2.3 count用法：count ( distinct expr auto )count ( distinct expr for all | any expr , expr )count ( distinct expr for report )作用：返回选定数据项的数量，但不包括NULL值。关键字distinct的作用是，当某个数据项有多个重复的项时，当中只有一个数据项有效。Example：count( 行号 )返回所有行号总的数量。2.4 maximum用法：maximum ( distinct expr auto )maximum ( distinct expr for all | any expr , expr )maximum ( distinct expr for report )作用：返回选定数据项的最大值。关键字distinct的作用是，当某个数据项有多个重复的项时，当中只有一个数据项有效。Example：Maximum (本期余额)返回数据项本期余额中的最大值。2.5 median用法：median ( expr auto )median ( expr for all | any expr , expr )median ( expr for report )作用：返回选定数据项的中间值。Example：median ( 本期余额 )返回数据项本期余额中的中间值2.6 minimum用法：minimum ( distinct expr auto )minimum ( distinct expr for all | any expr , expr )minimum ( distinct expr for report )作用：返回选定数据项的最小值。关键字distinct的作用是，当某个数据项有多个重复的项时，当中只有一个数据项有效。Example：minimum ( 本期余额 )返回数据项本期余额中的最小值2.7 moving-average用法：moving-average ( numeric_expr , numeric_expr at exp , expr prefilter )moving-average ( distinct numeric_expr , numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回指定的一组值在指定行数上的移动平均值。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：moving-average (本期余额,3)对应的每一行，返回当前行的数量值和当前行与前面两行共三行的平均值为当前行的一组值本期余额moving-average (本期余额,3)200NULL700NULL400433.3333200433.3333200266.6667500300.00002.8 moving-total用法：moving-total ( numeric_expr , numeric_expr at exp , expr prefilter )moving-total ( distinct numeric_expr , numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回指定的一组值在指定行数上的移动合计。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：moving-total (本期余额,3)对应的每一行，返回当前行的数量值和当前行与前面两行共三行的合计为当前行的一组值本期余额moving-total (本期余额,3)200NULL700NULL400130020013002008005009002.9 percentage用法：percentage ( numeric_expr at exp , expr prefilter )percentage ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：返回选定数据项占合计值的百分比。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：percentage (本期余额)返回每行本期余额的值所占总数的百分比2.10 percentile用法：percentile ( numeric_expr at exp , expr prefilter )percentile ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：返回在100范围内的某一值，用于表示等于或低于选定数据项的一部分所占百分比。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：percentile (本期余额)对应的每一行，返回表示等于或低于该行数据量的百分比本期余额percentile (本期余额)80017000.8756000.755000.6254000.54000.52000.252000.252.11 quantile用法：quantile ( numeric_expr, numeric_expr at exp , expr prefilter )quantile ( distinct numeric_expr, numeric_expr prefilter ) := for expr , expr | for report | auto作用：以指定范围的形式返回值的排名。它返回整数，以表示排名的任何范围，例如从1（最高）到100（最低）。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：quantile ( 本期余额, 4 )对应的每一行，返回该行本期余额的数量值、本期余额数量值的排名以及所有本期余额数量值分成4部分后的排名。本期余额Rank(本期余额)quantile ( 本期余额, 4 )80011700216003250042400534005320074200742.12 quartile用法：quartile ( numeric_expr at exp , expr prefilter )quartile ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：返回某一值相对于一组值的排名，由1（最高）到4（最低）之间的整数表示。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：quartile ( 本期余额 )对应的每一行，返回该行本期余额的数量值和本期余额的数量值分成四部分后的排名，由1（最高）到4（最低）之间的整数表示。本期余额quartile ( 本期余额 )450140013502300225032003150410042.13 rank用法：rank ( expr sort_order , expr sort_order at exp , expr prefilter )rank ( distinct expr sort_order , expr sort_order prefilter ) := for expr , expr | for report | auto作用：返回选定数据项的排名值。如果两个或多个行排名相同，则排名值（也称为Olympic排名）顺序中会存在一个缺口。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：rank (本期余额)返回本期余额数据项的数据量排名值本期余额rank (本期余额)6000015000025000024000042.14 running-average用法：running-average ( numeric_expr at exp , expr prefilter )running-average ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回一组值的运行平均值（包括当前行）。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：running-average (本期余额)对应的每一行，返回本期余额当前行与其前面所有行的平均值。描述本期余额AVGrunning-average FOR 描述现金757现金355现金655.33现金455存款343存款5442.15 running-count用法：running-count ( numeric_expr at exp , expr prefilter )running-count ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回一组值的运行计数（包括当前行）。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：running-count (本期余额)对应的每一行，返回本期余额当前行在所有行的位置计数。描述本期余额COUNTrunning-count FOR 描述现金741现金342现金643现金444存款321存款5222.16 running-difference用法：running-difference ( numeric_expr at exp , expr prefilter )running-difference ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回一组值的运行差额，该运行差额为当前行的值与前面行（包括当前行）的值之间的差额。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：running-difference (本期余额)对应的每一行，返回本期余额当前行与前面行的值之间的差额描述本期余额running-difference FOR 描述现金7NULL现金3-4现金63现金4-2存款3-1存款522.17 running-maximum用法：running-maximum ( numeric_expr at exp , expr prefilter )running-maximum ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回一组值的最大运行值（包括当前行）。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：running-maximum (本期余额)对应的每一行，返回本期余额当前行与前面所有行的最大值描述本期余额MAXrunning-maximum FOR 描述现金272现金373现金676现金777存款353存款5552.18 running-minimum用法：running-minimum ( numeric_expr at exp , expr prefilter )running-minimum ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回一组值的最小运行值（包括当前行）。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：running- minimum (本期余额)对应的每一行，返回本期余额当前行与前面所有行的最小值描述本期余额Minrunning- minimum FOR 描述现金727现金323现金623现金222存款333存款5332.19 running-total用法：running-total ( numeric_expr at exp , expr prefilter )running-total ( distinct numeric_expr prefilter ) := for expr , expr | for report | auto作用：按行返回一组值的运行合计（包括当前行）。定义函数范围。at option定义聚合级别，只能在关系数据源上下文中使用。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：running-total (本期余额)对应的每一行，返回本期余额当前行与前面所有行的数据值的合计。描述本期余额TOTALrunning-total FOR 描述现金7187现金31810现金61816现金21818存款383存款5882.20 standard-deviation用法：standard-deviation ( distinct expr auto )standard-deviation ( distinct expr for all | any expr , expr )standard-deviation ( distinct expr for report )作用：返回选定数据项的标准偏差。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：standard-deviation (本期余额)返回本期余额数据项对应的标准偏差2.21 standard-deviation-pop用法：standard-deviation-pop ( distinct expr auto )standard-deviation-pop ( distinct expr for all | any expr , expr )standard-deviation-pop ( distinct expr for report )作用：计算总体标准偏差并返回总体方差的平方根。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：2.22 total用法：total ( distinct expr auto )total ( distinct expr for all | any expr , expr )total ( distinct expr for report )作用：返回选定数据项的合计值。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：total (本期余额)返回所有本期余额数据项的数据值的总和2.23 variance用法：variance ( distinct expr auto )variance ( distinct expr for all | any expr , expr )variance ( distinct expr for report )作用：返回选定数据项的差量。关键字distinct的作用是，当某个行有多个重复的行时，当中只有一个行有效。Example：variance (本期余额)返回(SUM(本期余额2) - SUM(本期余额)2 / COUNT(本期余额) / (COUNT(本期余额) - 1)得到的值2.24 variance-pop用法：variance-pop ( distinct expr auto )variance-pop ( distinct expr for all | any expr , expr )variance-pop ( distinct expr for report )作用：返回一组数字除去其中无效数字之后的总体方差。Example：variance-pop(本期余额)返回(SUM(本期余额2) - SUM(本期余额)2 / COUNT(本期余额) / COUNT(本期余额)得到的值3 业务日期/时间函数3.1 _add_days用法：_add_days ( date_exp, integer_exp )作用：依据将integer_exp天添加至date_exp之后所生产的第一个参数，返回日期或日期时间。Example1：_add_days ( 2002-04-30 , 1 )结果为: 2002-05-01Example2：_add_days ( 2002-04-30 12:10:10.000, 1 )结果为：2002-05-01 12:10:10.000Example3：_add_days ( 2002-04-30 00:00:00.000, 1/24 )结果为：2002-04-30 01:00:00.0003.2 _add_months用法：_add_months ( date_exp, integer_exp )作用：依据将integer_exp个月添加至date_exp之后所生产的第一个参数，返回日期或日期时间。Example 1：_add_months ( 2002-04-30 , 1 )结果为：2002-05-30Example 2：_add_months ( 2002-04-30 12:10:10.000 , 1 )结果为：2002-05-30 12:10:10.0003.3 _add_years用法：_add_years ( date_exp, integer_exp )作用：依据将integer_exp年添加至date_exp之后所生产的第一个参数，返回日期或日期时间。Example 1：_add_years ( 2002-04-30 , 1 )结果为：2003-04-30Example 2：_add_years ( 2002-04-30 12:10:10.000 , 1 )结果为：2003-04-30 12:10:10.0003.4 _age用法：_age (date_exp )作用：返回从当前日期减去date_exp所得的数值。该值的格式为YYYYMMDD，其中YYYY代表年数，MM代表月数，DD代表天数。Example：Todays date=2003-02-05_age ( 1990-04-30 )结果为：120906（表示12年，9个月，6天）3.5 _day_of_week用法：_day_of_week ( date_exp, integer )作用：返回周内某日的对应值（1到7之间），其中1为每周第一天，并由第二个参数（1到7之间，1代表星期一，7代表星期日）指明。注意，依据ISO 8601标准，每周第一天为星期一，即日值为1。在北美地区，每周第一天为星期日，即日值为7。Example：_day_of_week ( 2003-01-01, 1 )结果为：33.6 _day_of_year用法：_day_of_year ( date_exp )作用：返回date_exp中的日值在当年中的序数（1到366）。亦称为Julian日。Example：_day_of_year ( 2003-03-01 )结果为：613.7 _days_between用法：_days_between ( date_exp1, date_exp2 )作用：返回表示两个日期表达式之间天数的正数或负数。如果date_exp1 date_exp2,则结果为负数。Example：_days_between ( 2002-04-30 , 2002-06-21 )结果为：-523.8 _days_to_end_of_month用法：_days_to_end_of_month ( date_exp )作用：返回一个数值，指示在日期表达式所提供的月份中剩余的天数。Example:_days_to_end_of_month ( 2002-04-20 14:30:22.123 )结果为：103.9 _first_of_month用法：_first_of_month ( date_exp )作用：依据将date_exp转换成同年同月但将日值设为1的日期之后所得的参数，返回日期或日期时间。Example1：_first_of_month ( 2002-04-20 )结果为：2002-04-01Example2：_first_of_month ( 2002-04-20 12:10:10.000)结果为：2002-04-01 12:10:10.0003.10 _last_of_month用法：_last_of_month ( date_exp )作用：依据date_exp中月份内的最后一天所对应的参数，返回日期或日期时间。Example1：_last_of_month ( 2002-01-14 )结果为：2002-01-31Example2：_last_of_month ( 2002-01-14 12:10:10.000 )结果为：2002-01-31 12:10:10.0003.11 _make_timestamp用法：_make_timestamp ( integer_exp1, integer_exp2, integer_exp3 )作用：返回由integer_exp1（年）、 integer_exp2（月）及 integer_exp3（日）构成的时间戳。Example：_make_timestamp ( 2002 , 01 , 14 )结果为：2002-01-14 00:00:00.0003.12 _months_between用法：_months_between ( date_exp1, date_exp2 )作用：返回表示date_exp1与date_exp2之间月数的正整数或负整数。如果date_exp1date_exp2，则返回负数。Example：_months_between ( 2002-01-30, 2002-04-03 )结果为：23.13 _week_of_year用法：_week_of_year ( date_exp )作用：依据ISO 8601标准，返回date_exp中的周在当年的序数，其中本年的周1是本年含有星期四的第一周，即1月4日所在的第一周。Example：_week_of_year ( 2003-01-01 )结果为：13.14 _years_between用法：_years_between ( date_exp1, date_exp2 )作用：返回表示date_exp1与date_exp2之间年数的正整数或负整数。如果date_exp1 date_exp2,则返回负值。Example：_years_between ( 2003-01-30, 2001-04-03 )结果为：13.15 _ymdint_between用法：_ymdint_between ( date_exp1, date_exp2 )作用：返回一个表示日期表达式date_exp1和date_exp2之间差值的数字。该值的格式为YYYYMMDD，其中YYYY表示年数，MM表示月数，DD表示天数。Example：_ymdint_between ( 1990-04-30 , 2003-02-05 )结果为：120906（代表12年，9个月，6天）4 块函数4.1 _firstFromSet用法：_firstFromSet ( set_exp, numeric_exp_max , numeric_exp_overflow )作用：返回在集中找到的前几个（不超过numeric_exp_max+numeric_exp_overflow ）成员。如果超过numeric_exp_max+numeric_exp_overflow，则只返回成员的最大数目。Example：（由于该函数主要是用在集合的成员中，平时用得比较少，文档也没详细说明用法，所以还没熟悉其用法）4.2 _remainderSet用法：_remainderSet (member_exp, set_exp , numeric_exp )作用：当set_exp的大小大于numeric_exp时，成员表达式将包括在返回的集中。Example：（由于该函数主要是用在集合的成员中，平时用得比较少，文档也没详细说明用法，所以还没熟悉其用法）5 供应商特定函数（Oracle函数）5.1 add_months用法：add_months ( date_exp, integer_exp )作用：返回向date_exp添加integer_exp个月之后所得的日期时间。Example：add_months ( 2002-04-30 12:10:10.000 , 1 )结果为：2002-05-30 12:10:10.0005.2 ascii用法：ascii ( string_exp )作用：返回一个数字，表示string_exp最左边字符的ascii代码值。Example：Ascii(A)结果为：655.3 char_length用法：char_length ( string_exp )作用：返回string_exp包含的字符数。Example：char_length(asdfsafsd)结果为：95.4 chr用法：chr ( integer_exp )作用：返回integer_exp指定的ASCII代码值所对应的字符。integer_exp应该在0和255之间。Example：chr (65)结果为：A5.5 concat用法：concat ( string_exp1, string_exp2 )作用：返回string_exp1与string_exp2串连之后所形成的字符串。Example：concat(asf,cd)结果为：asfcd5.6 decode用法：decode ( expr , search , result , search , result. , default )作用：DECODE逐一比较expr和每一个搜索值。如果expr等于搜索值，则返回相应结果。如果未找到匹配值，则返回默认值。如果默认值已被删除，则返回空值。Example：DECODE (warehouse_id, 1, Southlake,2, San Francisco, 3, New Jersey,4, Seattle,Non-domesti