BIEE分析及报表函数使用详解

常见BIEE分析及报表函数使用详解系统中目前存在预制的标准函数，使用现有函数将有助于您更好的完成复杂格式和逻辑的报表。1.聚合函数1.AGGREGATEAT此函数根据指定的级别聚合列。使用AGGREGATEAT可确保始终在关键字AT之后指定的级别执行度量聚合，而无论WHERE子句如何语法AGGREGATE(exprATlevel[,level1,levelN])其中：expr是至少引用一个度量列的任何表达式。level是要在其中聚合的级别。您可以选择性地指定多个级别。AGGREGATE(基础项.预算金额AT时间.时间层次.年)要使用级别，就需要使用时间层次2.AVG此函数计算结果集中表达式的平均值。它必须采用数值表达式作为参数。语法AVG(numExpr[byExpr])其中：numExpr是求值结果为数值的任何表达式。Expr是维度列，可以设置AVG的范围如AVG(基础项.预算金额BY时间.年份)计算年内平均金额AVGDISTINCT此函数计算表达式中所有不同值的平均值。3.BOTTOMN按从1到n的顺序依次排列表达式参数中最小的n个值，1对应于最小的数值。BOTTOMN函数对结果集中返回的值执行运算。请求可以只包含一个BOTTOMN表达式。语法BOTTOMN(Expr,integer)其中：Expr求出数值的任意表达式。Integer任意正整数。表示结果集中显示的最低级别编号，1是最低的级别。一般用于取得排名的数据，隐藏和排除字段都是不会影响结果的，跟表提示一起使用时，会先BottomN然后再提示4.COUNT此函数计算表达式中包含非空值的行数。该表达式通常为列名，此种情况下，将返回该列中包含非空值的行数。语法：COUNT(expr[BYexpr1])其中：expr为任意表达式。expr1可以指定范围。如COUNT(基础项.预算金额BY时间.年份)COUNTDISTINCT此函数对COUNT函数进行相异处理。5.Max&Min计算满足数值表达式参数的行的最大值（最大数值）。语法Max(numExpr)其中：numExpr求出数值的任意表达式。可以使用Max(numExprbyDim)设置Max的范围如MAX(事实-销售订单头.订单金额BY时间维.年)VSMAX(事实-销售订单头.订单金额)6.MEDIAN此函数计算符合数值表达式参数要求的行的中间值。如果有偶数个行，则中间值为两个中间行的平均值。此函数始终返回双精度值。语法MEDIAN(numExpr[BYexpr1])其中：numExpr是求值结果为数值的任何表达式。expr1可以指定范围7.NTILE此函数根据用户指定的范围确定值的排名。它返回整数，表示任意排名范围。换句话说，生成的排序数据集分为多个维片，每个维片中值的数目大致相当。numTiles=100的NTile返回的结果一般称为“百分比段”(数值从1到100，100表示最高排名)。该值不同于OracleBIPERCENTILE函数的结果，该函数在SQL92中符合所谓的“百分比排名”，并返回从0到1的值。语法NTILE(numExpr,numTiles)其中：numExpr是求值结果为数值的任何表达式。numTiles是非空值正整数，表示维片数量。如果numExpr参数不为空值，则该函数返回介于请求的范围中的表示排名的整数。该函数可以用于求得比如说，销售额排名前25%的区域，就设置numTiles为4，然后筛选该参数值等于4的8.PERCENTILE此函数计算符合数值表达式参数要求的每个值的百分比排名。百分比段排名范围介于0(第1个百分比段)和1(第100个百分比段)之间，包含两端的值。根据结果集内的值计算百分比段。语法PERCENTILE(numExpr)其中：numExpr是求值结果为数值的任何表达式。PERCENTILE(基础项.预算金额)*1009.RANK计算满足数值表达式参数的每个值的级别。级别1分配给最大数值，下一个连续的整数(2,3,4,...)依次分配给每个后继的级别。如果某些值相等，则分配相同的级别,同时，下一级别数字为前面出现的个数（例如，1,1,1,4,5,5,7...）。根据结果集中的值计算级别。语法RANK(Expr1,Expr2[byExp1,Exp2])其中：Expr求出数值的任意表达式。by是定下RANK的范围如设置RANK(事实-销售订单行.销量by时间维.年)最后能够达到类似于TopN的效果，而且，对于表提示器也是生效的，当然得建立过滤器同时，这样会有一个局限在于，Rank的序列生成方式是(1,1,1,4,5,5,7...)这样的，所以设置过滤小于10，可能返回的值不止9个，就像上面左图一样10.STDDEV此函数返回一组值的样本标准差。返回类型始终为双精度值。STDEV_SAMP是STDDEV的同义词。语法STDDEV([ALL|DISTINCT]numExprBYexpr)其中：numExpr是求值结果为数值的任何表达式。如果指定ALL，则会为数据集内的所有数据计算标准差。如果指定DISTINCT，则会在计算中忽略所有重复项。如果未指定任何内容(默认值)，则会考虑所有数据。expr可以设置范围如STDDEV(基础项.预算金额BY时间.年份)11.STDDEV_POP此函数使用总体方差和标准差的计算公式返回一组值的标准差。语法STDDEV_POP([ALL|DISTINCT]numExprBYexpr)其中：numExpr是求值结果为数值的任何表达式。如果指定ALL，则会为数据集内的所有数据计算标准差。如果指定DISTINCT，则会在计算中忽略所有重复项。如果未指定任何内容(默认值)，则会考虑所有数据。expr可以设置范围如STDDEV_POP(基础项.预算金额BY时间.年份)12.SUM此函数计算通过累加符合数值表达式参数要求的所有值得出的总和。语法SUM(numExprBYexpr)其中：numExpr是求值结果为数值的任何表达式。expr可以设置范围SUMDISTINCT此函数计算通过累加符合数值表达式参数要求的所有不同值得出的总和。13.TOPN按从1到n的顺序依次排列表达式参数中最大的n个值，1对应于最大的数值。TOPN函数对结果集中返回的值执行运算。请求可以只包含一个TOPN表达式。语法TopN(Expr,integer)其中：Expr求出数值的任意表达式。Integer任意正整数。表示结果集中显示的最低级别编号，1是最高的级别。一般，我们是新建一个TOPN的指标，然后将这个指标排除或者隐藏起来TOPN对表提示器无效，因为是先TOPN再进行提示的2.运行聚合函数运行聚合函数与函数聚合相似，它们都采用一组记录作为输入，但运行聚合函数不会为整组记录输出一个聚合，而会根据迄今为止出现的记录输出聚合。14.MAVG此函数计算结果集内最后n行数据(包括当前行)的移动平均值。第一行的平均值等于第一行的数值表达式。第二行的平均值通过计算前两行数据的平均值得出。第三行的平均值通过计算前三行数据的平均值得出，依此类推，直到您到达第n行为止，第n行的平均值根据最后n行数据计算得出。语法MAVG(numExpr,integer)其中：numExpr是求值结果为数值的任何表达式。integer是任意正整数。它表示最后n行数据的平均值。比如，这里是3，那么就是求上两行数据的平均值，第一行为第一行数据的平均值，第二行是前两行的平均值，第三行是前三行的平均值，后面每一行都是左边前三行的平均值15.MSUM此函数计算最后n行数据(包括当前行)的移动总和。第一行的总和等于第一行的数值表达式。第二行的总和通过计算前两行数据的总和得出。第三行的总和通过计算前三行数据的总和得出，依此类推。在您到达第n行以后，根据最后n行数据计算总和。语法MSUM(numExpr,integer)其中：numExpr是求值结果为数值的任何表达式。integer是任意正整数。它表示最后n行数据的平均值。16.RSUM此函数根据迄今为止出现的记录来计算运行总和。第一行的总和等于第一行的数值表达式。第二行的总和通过计算前两行数据的总和得出。第三行的总和通过计算前三行数据的总和得出，依此类推。语法RSUM(numExpr)其中：numExpr是求值结果为数值的任何表达式。在Answers中，您还可以使用以下替代语法：RSUM(expression1[BYexpression2[,expression3[,...]]])其中：expression1,expression2,expression3...可以是任何列引用或列引用中的算术表达式。BY子句导致RSUM计算从任何BY列值不同于上一行的行重新开始。17.RCOUNT此函数采用一组记录作为输入，并计算迄今为止出现的记录数量。语法RCOUNT(expr)其中：expr是任何数据类型的表达式。在Answers中，您还可以使用以下替代语法：RCOUNT(expression1[BYexpression2[,expression3[,...]]])其中：expression1,expression2,expression3...可以是任何列引用或列引用中的算术表达式。BY子句导致RCOUNT计算从任何BY列值不同于上一行的行重新开始。18.RMAX&RMIN此函数采用一组记录作为输入，并根据迄今为止出现的记录显示最大值。指定数据类型必须是可排序的数据类型。语法RMAX(expr)其中：expr是任何数据类型的表达式。该数据类型必须是具有关联的排序顺序的数据类型。在Answers中，您还可以使用以下替代语法：RMAX(expression1[BYexpression2[,expression3[,...]]])其中：expression1,expression2,expression3...可以是任何列引用或列引用中的算术表达式。BY子句导致RMAX计算从任何BY列值不同于上一行的行重新开始。3.字符串函数19.ASCII1.ConcatCONCAT(strExpr1,strExpr2||strExpr3)其中：strExprs是求值结果为逗号分隔字符串和字符串连接运算符||(双竖线)的表达式。CONCAT函数首先将strExpr2与strExpr3连接在一起以生成中间字符串，然后将strExpr1和中间字符串连接在一起以生成最终字符串。示例此示例请求返回显示的结果。SELECTDISTINCTCONCAT('abc','def'||'ghi')FROMemployee结果：abcdefghi2.Left从字符串左方返回指定字符数。语法Left(strExpr,integer)其中：strExpr求出字符串的任意表达式。Integer任意正整数，表示要从字符串左方返回的字符数。3.Lower将字符串转换成小写字母。语法Lower(strExpr)其中：strExpr求出字符串的任意表达式。4.Replace使用一个或多个其它字符替换指定字符表达式中的一个或多个字符。语法Replace(strExpr1,strExpr2,strExpr3)其中：strExpr1求出字符串的任意表达式。这是要替换其中字符的字符串。strExpr2求出字符串的任意表达式。第二个字符串指定第一个字符串中被替换的字符。strExpr3求出字符串的任意表达式。第三个字符串指定要替换至第一个字符串的字符。5.Right从字符串左方返回指定字符数。语法Right(strExpr,integer)其中：strExpr求出字符串的任意表达式。Integer任意正整数，表示要从字符串右方返回的字符数。6.Substring从固定字符数开始，将新字符串创建至原始字符串。语法Substring(strExprFROMstartPos)其中：strExpr求出字符串的任意表达式。startPos任意正整数，表示从结果所在的字符串左边开始的字符数。7.TrimBoth除去字符串中的前导和尾部字符。语法Trim(B