常见的函数--帆软（3）

常见函数列表

-- -- -- -- ---- -- -- -- ---- -- -- -- ---- -- -- -- ---- -- -- -- --

常用的函数

主页：https://www.cnblogs.com/wan-ge1212/p/10256341.html

           常见的函数--帆软（2） - 倾晴雨轩 - 博客园 (cnblogs.com)

帆软全面网址介绍

：帆软全面网址介绍_倾晴雨轩的博客-CSDN博客

 常见函数列表

1. 描述

虽然FineReport中提供了这么多中函数以供选择使用，但是，在实际应用中，这些公式远远不能满足需求，有的需要将多个公式联合起来使用，这就需要使用者能够对FineReport内置的公式有一个大致的了解，下面我们介绍几种常见的应用。

字符串与数组转换

 

1. 概述

1.1 预期效果

用户有时候需要将字符串依据某个符号进行分列显示，即将字符串转换为数组。

例如单元格 A1 中有为「苹果汁,牛奶,柳橙汁,巧克力,牛肉干」的字符串，希望将该字符串中的每个值在不同单元格中分列显示，如下图所示：

或者将不同单元格中的字符串拼接在一起，即将数组转成字符串。

例如单元格 A1:A6 值为「苹果汁」「牛奶」「柳橙汁」「巧克力」「牛肉干」的数组，希望将其转为“;”分隔的字符串，如下图所示：

1.2 实现思路

使用 SPLIT 函数实现分割扩展单元格。

使用 JOINARRAY 函数实现将不同单元格中的字符串（数组）拼接在一起。

 2. 用 SPLIT 函数分割扩展单元格-字符串转换为数组

2.1 模板设计

创建普通报表，在 A2 单元格输入「苹果汁,牛奶,柳橙汁,巧克力,牛肉干」，使用“,”作为分隔符，然后在 B2 单元格输入公式=split(A2,",")，如下图所示：

注：有时字符串数据如“苹果汁|牛奶|柳橙汁|巧克力|牛肉干”，用“|”分隔，此时需要对“|”进行转义，即公式为=split(A1,"\\|")。 （\\|为转义字符）

设置 B2 单元格的扩展方向为纵向扩展，即将分割的数据中每个元素显示在不同单元格中，形成数组，如下图所示：

2.2 效果查看

保存模板并预览。

 

3. 用 joinarray 函数将数组转换为字符串

3.1 模板设计

创建普通报表，分别在 A2、A3、A4、A5、A6 单元格输入「苹果汁」「牛奶」「柳橙汁」「巧克力」「牛肉干」，合并 B2:B6 单元格，并输入公式=joinarray(A2:A6,";")，如下图所示：

3.2 效果查看

保存模板并预览

 

常见日期处理函数

1.描述

在对日期型数据进行操作时，经常会遇到要获取当前日期，获取当前时间，获取日期的年月日等等操作，下面我们就来介绍下日期的常用处理函数。

注：这是是讲的帆软处理日期函数，SQL在处理日期时所区别，如：获取当天日期SQL为,GETDATE()，而帆软为today()。

2.转化日期

DATE(year,month,day): 返回一个表示某一特定日期的系列数。

注：year,month,day若为小数将自动去掉小数取整，如：2001.5返回值为2001，2001.1返回值为2001。下面只讨论整数情况。

Year：代表年。

若为正整数不变，若为0或负整数，则取绝对值+1。如：year=-5,则year返回值为6，即(|-5|+1)。

Month：代表月份。

若month为1至12的整数，则函数把参数值作为月。

若month>12，则函数从年的一月份开始往上累加。如: DATE(2000,25,2)返回值为2002-01-02。

若month=<0，则函数从上年的12月份开始往减去相应的月数。如：DATE(2000,0,2)返回值为1999-12-02；DATE(2000,-1,2)返回值为1999-11-02。

注：日期同理，若超过最大天数，会从月的一号开始向上累加；小于月初天数则从上月末减去相应的月数。如：DATE(2000,3,0)返回值为2000年2月月末，即2000-02-29。

Day：代表日。

若日期小于等于某指定月的天数，则函数将此参数值作为日。

若日期大于某指定月的天数，则函数从指定月份的第一天开始往上累加。若日期大于两个或多个月的总天数，则函数把减去两个月或多个月的余数加到第三或第四个月上，依此类推。例如：DATE(2000,3,35)返回值为2000-04-04。

注:

若需要处理公式中日期的一部分，如年或月等，则可用此公式。

若年，月和日是函数而不是函数中的常量，则此公式最能体现其作用。

示例：

DATE(1978, 9, 19) 返回值为 1978-09-19。

DATE(1211, 12, 1) 返回值为 1211-12-01。

在单元格中输入：=date(2011,1,10)，其返回值为2011-01-10。

若月份大于12将从年的一月份开始往上累加，如在单元格中输入：=date(2011,13,10)，其返回值为2012-01-10。

3.获取当前日期、时间

3.1 当前日期（年月日）

today()即获取当前日期。

在单元格中输入=today()，若系统日期是2011年11月11号，则返回值为2011-11-11。

3.2 当前时间（时分秒）

now()即获取当前日期与时间。

在单元格中输入=now()，返回值类型为日期，yyyy-MM-dd hh:mm:ss；如：now()，格式：常规，返回的值2012-06-20 14:55:57。

4.获取日期的年月日

分别通过year(),month(),day()这三个函数来获取日期的年月日。

获取年份：在单元格中输入=year("2011-11-11")，返回值为2011。

获取月份：在单元格中输入=month("2011-11-11")，返回值为11。

获取日期：在单元格中输入=day("2011-11-11")，返回值为11。

注：若year(),month(),day()对应的年\月\日带有小数,将返回当前年或月或日，相当于year(today()),month(today()),day(today())。

5.将日期格式化

可以使用todate()函数将任何日期形式的数据转化为日期型，或者使用format()函数可以转化成任何日期形式的数据。

5.1 将日期格式的字符串转化成日期型

todate()可以将任何日期形式的数据转化成日期型即：yyyy-MM-dd型数据。

在单元格中输入=todate("2011/11/11")，返回值为2011-11-11。

在单元格中输入=todate("2/15/11","MM/dd/yy")，返回值为2011-02-15。

5.2 将日期型数据转化成其他格式的日期形式

format()可以将日期型数据转化成其他形式的数据。

在单元格中输入=format("2/15/2011","dd/MM/yyyy")，返回值为15/02/2011。

在单元格中输入=format("2/15/2011","yyyy-MM-dd")，返回值为2011-02-15。

6.日期的加减

可以通过datedelta()，monthdelta()，yeardelta()这三个函数来实现对日期的加减。

日期的加减：在单元格中输入=datedelta(today(),-1)，返回当前日期前一天的日期，若系统日期是2011-11-11，则返回值为2011-11-10。

注：公式中的-1表示减一天若是正数则表示加相应的天数。

月份的加减：在单元格中输入=monthdelta(today(),-1)，返回当前日期上一个月的日期，若系统时间是2011-11-11，则返回值为2011-10-11。

年份的加减：在单元格中输入=yeardelta(today(),-1)，返回当前日期上一年的日期，若系统日期是2011-11-11，则返回值为2010-11-11。

7.获取某月的第几天

可以通过dateinmonth(date, number)来获取某月的第几天。

在单元格中输入=dateinmonth(today(),1)，返回当前月的第一天，若系统日期是2011-11-11，则返回值为2011-11-01。

在单元格中输入=dateinmonth(today(),-1)，返回当前月的最后一天，若系统日期是2011-11-11，则返回值为2011-11-30。

8.阳历转化成农历

可以通过lunar(year,day,month)函数来实现将阴历转化成公历。

在单元格中输入：=lunar(2011,10,11)，返回的是2011年10月11号对应的农历日期即返回值为辛卯年九月十五。

在单元格中输入：=lunar(2011,10,1)，返回的是2011年10月1号对应的农历日期即返回值为辛卯年九月初五。

注：lunar公式支持的时间段为1900-2100年。

9.获取某月的当月、上月、去年同期等

9.1 当月

MONTH(today())

9.2 去年同期

YEARDELTA(today(),-1)

9.3 上上月

date(year(today()),MONTH(today())-2,day(today()))

9.4 上月

date(year(today()),MONTH(today())-1,day(today()))

9.5 去年同期上月

date(year(today())-1,MONTH(today())-1,day(today()))

数值常用处理函数

1. 求和

可以通过sum()函数求指定单元格区域或一系列数字的和，从而进行汇总合计。

在单元格中输入=sum(A1,B3,D5)，返回单元格A1、B3、D5扩展出来的所有数字的和。

在单元格中输入=sum(A1:B3)，返回A1到B3区域中的单元格扩展出来的所有数字的和。

2. 除

2.1 除

直接通过/进行除法运算，在单元格中输入=5/3，返回值为1.6666...7。

2.2 求余

通过%或mod()函数进行求余，在单元格中输入=5%3或=mod(5,3)，返回值都为2。

3. 最大值和最小值

可以通过max()和min()两个函数分别获取列表中的最大值和最小值。

取最大值：在单元格中输入=max(1,5,4,10,3,2)，可以看到最大值为10，因此返回值为10。

4. 四舍五入

可以通过round()函数进行四舍五入，round(number,num_digits)：返回某个数字按指定位数四舍五入后的数字。

在单元格中输入=round(12.49,0)，由于要舍入位数后的值为4小于5因此返回值为12。

在单元格中输入=round(12.49,1)，由于要舍入位数后的值为9大于5因此返回值为12.5。

5. 取整

可以通过trunc()函数将数字的小数部分截取，返回整数。

在单元格中输入=trunc(4.8)，返回值为4。

数组常用处理函数

1. 概述

对数组进行操作时，经常会遇到获取需要获取数组中某个位置的数据以及获取某个字符串在数组中的位置，下面我们来介绍下常用的数组函数。

2. 获取数组某个位置的数据

可以通过indexofarray(array, index)函数，返回数组array的第index个元素。

在单元格中输入=indexofarray(["a","b","c","d"],3)，获取数组中第三个位置的数据即返回值为c。

3. 获取某个字符串在数组中的位置

可以通过inarray(co, array)函数，返回co在数组array中的位置，如果co不在array中，则返回0。

在单元格中输入=inarray("b",["a","b","c","d"])，b在数组的位置是2，因此返回值为2。

在单元格中输入=inarray("e",["a","b","c","d"])，由于e不在数组中，因此返回值为0。

4. 过滤数组元素

可以通过greparray(array,fn)函数，根据fn条件来过滤数组，最后形成一个新的数组。

在单元格中输入=greparray([3,4,2,0,6,8,0], item != 0)，返回一个新的数组[3,4,2,6,8]。

5. 对数组元素进行批量处理

可以通过maparray(array,fn)函数，对数组中的每个元素根据fn进行计算，将计算结果保存到新的数组中。

在单元格中输入=maparray([3,4,2,-3,6,8,-5],if(item>0,1,0))，返回一个新的数组[1,1,1,0,1,1,0]。

6. 对数组元素进行排序

可以通过sortarray(array)函数，对数组元素进行排序。

在单元格中输入=sortarray([3, 4, 4, 5, 1, 5, 7])，返回一个新的数组[1,3,4,4,5,5,7]。

对日期求平均数

1. 描述

对于日期型数据求平均数，由于average()函数不支持对日期型数据求平均数因此求出的值是0，但若希望对日期求平均数要如何实现呢？

2. 思路

通过使用datetonumber()函数将日期型转化成毫秒数，然后使用average()函数对毫秒数求平均值，最后通过todate()函数将毫秒数的平均值转化为日期型即可。

3. 示例

假设数据集中已经存在一列数据是日期型的数据，数据如下：

 

将数据列拖入到A1单元格中。

3.1 将日期转化为毫秒数

在B1单元格中写入公式：=DATETONUMBER(A1)即将A1中的数据转化为毫秒数了。

3.2 求毫秒数的平均数并将其转化为日期型

在A2单元格中写入公式：=TODATE(CONCATENATE(AVERAGE(B1)))由于AVERAGE求出来的数是数字型的， 而TODATE要使用字符串的数据，因此要使用CONCATENATE()函数将字数字转化为字符串。

将B1单元格的列宽设置为0即可。

3.3 效果查看

点击设计器预览，可以看到其平均日期为2011-08-13，如下图所示：

 

根据分组设置页码

1. 概述1.1 预期效果

在对于分组模板进行页码统计时，希望页码统计只在当前分组进行，即分组了就重新进行编码。

例如华东地区这个组内有三页数据而华北地区这个组内有两页数据，则分别标记「组内页码」，如下图所示：

1.2 实现思路

首先通过「每页固定行数」来计算每组所需的总页数，如上图中华东 3 页，华北 2 页；

其次使用当前页 $$page_number 减去之前组的总页数获得该页在当前组的页码，如上图华北的第一页数据所在 $$page_number为 4，减去之前组总页数 3，就是该页在华北组的页码。

2. 操作步骤使用内置 DEMO 数据「销量」。2.1 报表设计

添加模板数据集 ds1，输入SQL 语句SELECT * FROM 销量，如下图所示：

新建报表，并将 B3 单元格设置为列表展示，如下图所示：

2.2 设置按组分页

右击 A3 单元格，选择单元格属性>其他>行后分页，如下图所示：

2.3 设置每页显示固定行数

右击 B3 单元格，选择条件属性>添加条件>行后分页，当满足公式&B3 % 10 == 0时，该属性起作用，如下图所示：

将 1-2 行设为重复标题行，使其在翻页时，每页重复显示，如下图所示：

2.4 进行页码设置

2.4.1 当前分组的页码与上个分组的页码之和

首先要获取当前分组为第几个分组，在 A1 单元格中输入公式：=&A3，并将其左父格设置为 A3 单元格。

在 B1 单元格中输入公式：=roundup(count(B3) / 10, 0) + B1[A3:-1]，获取当前分组的页码与上个分组的页码的总页码，如下图所示：

公式说明：

公式	说明
B1[A3:-1]	获取上一个 A3 分组内 B1 单元格的值
roundup(count(B3) / 10, 0)	roundup 为向上取整，由于我们设置了 10 行为一页，因此计算结果为当前分组（例如华北）共有几页
roundup(count(B3) / 10, 0) + B1[A3:-1]	获取当前分组的页码与上个分组的页码之和

2.4.2 显示当前页在当前分组中的页数

在 E1 单元格中输入公式：=if(A1= 1, $$page_number + "/" + roundup(count(B3) / 10, 0), $$page_number - B1[A3:-1] + "/" + roundup(count(B3) / 10, 0))，如下图所示：

公式说明：

公式	说明
$$page_number	当前页码
roundup(count(B3) / 10, 0)	当前分组（例如华北）共有几页
$$page_number + "/" + roundup(count(B3) / 10, 0)	用 + 符号进行字符串拼接 表示当前页在当前分组的第几页 如果 A1 即当前分组为 1，就直接为当前页/当前分组总页数；如果不为 1，则当前页码减去之前所有分组的总页数和。

A1、B1 都为中间运算结果，可以设置单元格格式>其他属性>不预览单元格内容，如下图所示：

同时可以在 D1 单元格输入「组内页码」，如下图所示：

2.5 效果查看

保存模板后，点击「分页预览」，

3. 模板下载

点击下载模板：根据分组设置页码.cpt

Count函数

1. 概述

1.1 函数作用

计算数组或数据区域中所含项的个数，例如统计「地区数」和「销售员个数」，如下图所示：

也可与其他函数嵌套使用，例如进行「条件计数」，计算除孙林以外的销售员个数，如下图所示：

在报表设计中，count 函数可以对单元格的个数计数，可以计算某个单元格扩展出来的个数，也可以求数组中元素的个数。

注：去重计数可通过组合 count 函数和 UNIQUEARRAY()函数实现 ，例如：count(UNIQUEARRAY(A1))。

1.2 函数解释

语法	count(value1,value2,…)	计算数组或数据区域中所含项的个数
参数1	value1,value2,…	可包含任何类型数据的参数

示例：

若 A1 单元格扩展了 5 个格子且都有数据， count(A1) 等于 5。

若 A1-A8 单元格中都有数据，count(A1:A8) 等于 8 。

count(2,3,4,5,7) 等于 5 。

1.3 注意事项

• count 函数会对空字符串引起的空值计数；但对 NULL 值引起的空值不计数。

• 需要统计个数可以是数组、单元格，单元格可以是单元格区域，也可以是扩展单元格。

2. 去重计数

2.1 模板准备

新建模板，新建数据集ds1 ,sql 语句为 SELECT * FROM 销量，设置如下模板样式，并将「ds1.地区」字段拽入 A2 单元格，将「ds1.销售员」拽入 B2 单元格。在 C2 单元格输入公式 =count(A2) ，在 D2 单元格输入公式 =count(B2)。如下图所示：

2.2 设置数据列展示方式

count 只统计单元格的个数，由于「地区」和「销售员」数据列都有重复数据，因此如果需要统计地区个数和销售员个数，则需要设置「ds1.地区」A2单元格和「ds1.销售员」B2单元格单元格显示格式为「分组」，相当于进行去重计数，如下图所示：

注：如果以「列表」格式展示数据，但却希望统计去重计数个数，则可使用 count(UNIQUEARRAY())公式。

2.3 设置单元格扩展方式

设置 C2 单元格的左父格为「无」，否则 C2 和 D2 单元格将跟随 B2 单元格扩展，无法正确计数，设置方法如下图所示：

2.4 效果查看

保存模板，效果见本文 1.1 节。

3. 条件计数count 函数中的参数为扩展单元格时，也可以与其他函数嵌套实现条件计数，格式为：count({}) ，其中 {} 内为计数条件。例如：count(A1{A1!=0})  统计 A1 单元格扩展出来的数据中不为 0 的个数。          count(A1{len(A1) != 0})统计 A1 单元格扩展出来的数据中不为空的个数。本文第二节示例中，在模板 E2 单元格加入公式：=count(B2{B2!='孙林'})，如下图所示：

保存模板，效果见本文 1.1 节。

若需要进行多条件判断，可嵌套公式，例如想要计算除了「孙林」并且销售地区为「华东」的销售员个数，可以使用公式：count(B2{B2!='孙林'&&A2='华东'}) 或者 count(B2{AND(B2!='孙林',A2='华东')})，如下图所示：

公式说明：

 

公式	说明
B2!='孙林'&&A2='华东' 或者 AND(B2!='孙林',A2='华东')	同时满足不是「孙林」并且销售地区为「华东」的销售员
count(B2{B2!='孙林'&&A2='华东'}) 或者 count(B2{AND(B2!='孙林',A2='华东')})	同时满足不是「孙林」并且销售地区为「华东」的销售员个数

注1：若需要多个条件中任一条件符合即计数，可使用 OR 函数。

注2：11.0.4 版本支持使用COUNTIFS 函数。

 

4. 模板下载

点击下载模板：count函数使用.cpt

Range 函数

1. 概述1.Range(from,to,step) 函数表示从整数 from 开始，以 step 为每一步的大小，直到整数 to （可以1.2 函数解释Range(from,to,st

参数	含义	参数格式
from	起始值，可以省略，省略默认是 1	整数
to	结束值，不能省略	整数
step	步长，可以省略，省略默认为 1	整数

注：form，to，step 三个参数不能为空字符串，且 to 参数必须有值。

示例：

Range(1,3,1) 返回 [1,2,3]

Range(3) 返回 [1,2,3]

Range(6,-1,-2) 返回 [6,4,2,0]

2. 应用场景

Range() 函数可以用来生成数字序列，也可以通过一些转化方法生成日期序列和小数序列。

2.1 数字序列

2.1.1 Range(to)

新建模板，在任意单元格中，写入公式：=Range(3)，并将其单元格属性设置为向下扩展、居中显示，预览模板后结果返回 1、2、3，效果如下图所示：

2.1.2 Range(from,to)

新建模板，在任意单元格中，写入公式：=Range(2,6)，并将其单元格属性设置为向下扩展、居中显示，预览模板后结果返回 2、3、4、5、6，效果如下图所示：

2.1.3 Range(from,to,step)

新建模板，在任意单元格中，写入公式：=Range(1,7,2)，并将其单元格属性设置为向下扩展、居中显示，预览模板后结果返回 1、3、5、7，效果如下图所示：

2.2 日期序列

2.2.1 Range(from,to)

新建模板，在任意单元格中，写入公式：=Range(Date(2016,10,20),Date(2016,10,24))，并将其单元格属性设置为向下扩展、居中显示，预览模板后结果返回 2016-10-20、2016-10-21、2016-10-22、2016-10-23、2016-10-24，效果如下图所示：

注：Range 函数生成日期序列，必须使用 date 函数将数字格式或者字符串格式转化为日期格式，日期格式默认为'yyyy-MM-dd'格式，也可以在单元格属性中修改其他日期显示方式。

2.2.2 Range(from,to,step)

新建模板，在任意单元格中，写入公式：=Range(Date(2016,10,16),Date(2016,10,24),2)，并将其单元格属性设置为向下扩展、居中显示，预览模板后结果返回 2016-10-16、2016-10-18、2016-10-20、2016-10-22、2016-10-24，效果如下图所示：

2.3 小数序列

Range 函数中 step 的步长要求为整数，若想得到小数序列，可以将  Range 中的 form，to，step  三个参数都扩大倍数使其成为一个整数，再将 Range () 后的结果缩小对应的倍数即可得到。

如想得到一个从 0 到 90，step 为 22.5 的小数序列，可先按 0 到 900，step 为 225 取序列，再将取得的序列除以10 得到期望的小数序列。

新建模板，在任意单元格中，写入公式：=Range(0,900,225)/10，将其单元格属性设置为向下扩展、居中显示，预览模板后结果返回0、22.5、45、67.5、90，效果如下图所示：

单元格显示图片(Toimage函数)

TOIMAGE函数显示单元格图片

 

1. 概述

1.1 版本

报表服务器版本	功能变动
11	-
11.0.4	支持 jpg、png、bmp 常用格式的图片，分别缓存为该格式下的文件，不会出现导出文件时体积暴涨的情况

1.2 函数作用

TOIMAGE 函数用于在报表中显示某一路径下的图片。支持 jpg、png、bmp 常用格式的图片，图片可以存储在本地磁盘，也可以存储在远程服务器，也可以为网页中图片。

1.3 函数解释

TOIMAGE(path)，显示指定路径下的图片。此处默认开启了图片缓存功能以加速报表的生成.如不需要缓存，请在参数后面追加值FALSE。

参数	含义	参数格式
path	图片路径，不可省略	"F:/FineReport_10.0/webroot/logo.png"  注：斜杠必须为 /
布尔	是否开启图片缓存功能，可省略，省略默认开启	true 开启，false 关闭
width	图片宽度，可省略，省略默认图片原宽度	整数：图片宽度像素  百分比：图片宽度缩放比
height	图片高度，可省略，省略默认图片原高度	整数：图片高度像素 百分比：图片高度缩放比

示例：

TOIMAGE("D:/1.jpg")

TOIMAGE("D:/1.jpg",false)

TOIMAGE("D:/1.jpg",true,200,300)

TOIMAGE("D:/1.jpg",true,"50%","200%")

1.4 注意事项

1）使用 TOIMAGE 函数显示单元格图片的模板，在选择菜单栏「文件>输出>模板（内置数据）」时，图片不会伴随输出。

2）若使用 TOIMAGE 函数的单元格属性设置了「样式>段间距」，那么导出或打印报表时，设置的「段间距」不生效。

3）使用 TOIMAGE 函数返回的图片，「图片布局」为「默认」。可以在「单元格属性>样式>对齐>图片布局」处选择其他布局方式。

4）不支持模拟计算，模拟计算详情参见：2.4节。

5）不支持 gif 格式。

2. 应用场景

TOIMAGE 函数中可以直接输入图片路径，也可以引用存储在数据库的图片路径字段。

2.1 直接输入

图片可以存储在本地磁盘，也可以存储在远程服务器上。图片存储的位置不同，path 路径的写法不同。

图片存储位置	path 路径	公式
本地磁盘	图片绝对路径，如 E:/图片/logo-fanruan.png	TOIMAGE("E:/图片/logo-fanruan.png")
报表服务器 webroot 目录下	省略工程所在目录的简化路径，如 help/logo-fanruan.png	TOIMAGE("help/logo-fanruan.png")
网页中图片	图片网络地址，如  https://www.fanruan.com/images/logo-fanruan.png	TOIMAGE("https://www.fanruan.com/images/logo-fanruan.png")  注：显示网页中图片时推荐使用 WEBIMAGE(path) ，可以提升 Web 图片加载速度。

注：报表服务器 webroot 为远程服务器时，设计器必须切换到对应远程服务器工作目录下。

2.2 引用数据库中的字段

1）若图片存储在本地磁盘 E 盘，路径为：E:/图片/logo-fanruan.png，将图片路径存在数据库表中，如下图所示：

2）将字段「path」拖到报表单元格中，右侧单元格属性选择「高级」，在「显示值」位置，输入公式 TOIMAGE($$$)，$$$ 表示当前单元格值，步骤如下图所示：

3）若存储在数据库的图片路径不完整，需要补全路径使图片显示。

如上述 E 盘中的图片，若在数据库中存储的路径为 ：logo-fanruan.png ，需要将「显示值」的公式修改为 :TOIMAGE("E:/图片/"+$$$)  。

注：如果用户数据库服务器和报表应用服务器分开的话，图片应该存放在报表应用服务器上，而不是数据库服务器上。

 

Value函数

1. 概述

1.1 函数作用

有时用户希望能直接在数据集中取出满足条件的行列数据，不必再将数据集字段拖拽到单元格后添加过滤条件取数，此时可以使用 value 函数。

 

1.2 函数解释

value 函数有多种写法，不同的参数组合对应不同的取数规则。

最简形式为：Value(设计器中的数据集名称,数据集列号)

例如：

=value("ds1",3) 取 ds1 数据集中第 3 列的数据，返回一个数组。

语法：

语法	VALUE(tableData,col)	返回tableData中列号为col的一列值。
参数1	tableData	必填 表示数据集名称，注意是「报表数据集」或者是「服务器数据集」名，而非数据库中的表名。
参数2	col	必填 列序号，整型；

1.3 注意事项

• value 函数参数设定有多种形式，可以直接在函数中输入，也可以引用单元格和模板参数。

例如：

value("ds1",1,2) 取 ds1 数据集中第 1 列第 2 行的数据。

value("ds1",A1,A2) 取 ds1 数据集中第 A1 单元格中值对应的列，第 A2 单元格中值对应的行的数据。

value('ds1',1,2,"牛肉干") 取 ds1 数据集中第 1 列数据，对应的第 2 列数据是 "牛肉干" 的值，返回第一列数据对应值。其中「牛肉干」可换成单元格数据。

value($p1,1,$p2) 取 p1 数据集中第 1 列第 p2 行的数据，其中 p1、p2 为模板参数，将 p1 赋值为数据集名称。

• 暂时不支持 value("数据集",1,-1) 写法，-1不生效。若您需要获取最后一行数据，可以使用类似公式VALUE('ds1',COUNT(value("ds1",3,4,"America")))替代实现。

2. 应用场景

示例数据：内置数据「CUSTOMER」

2.1 创建模板

新建一张模板，新建数据库查询 ds1：SELECT * FROM CUSTOMER，如下图所示：

2.2 使用公式取数

分别在单元格中输入如下公式：

输入公式	返回数值	效果	公式写法	写法说明
= value("ds1",3,2)	将返回 customer 表中的第三列第二行的数据 Washington		Value(tabledata,col,row)	返回 TableData 中列号为 col，行号为 row 的值。
=value("ds1",3) 注：由于返回的是数组，因此设置扩展属性为从上向下扩展。	将返回数据表中的第三列数据		Value(tableData,col)	返回 TableData 中列号为 col 的一列值
=value("ds1",3,4,"America") 注：由于返回的是数组，因此设置扩展属性为从上向下扩展。	返回数据表中第三列元素，且该列元素对应的第四列元素的值是 America 的所有数据		Value(tableData,targetCol,orgCol,element)	返回 TableData 中第 targetCol 列中的元素，这些列元素对应的第 orgCol 列的值为 element。
=value("ds1",3,4,"America",1)	返回数据表中第三列元素，且该列元素对应的第四列元素的值是 America 的所有数据中第一个值		Value(tableData,targetCol,orgCol,element,idx)	返回 Value(tableData,targetCol, orgCol, element)数组的第 idx 个值

 数据集函数概述

 

1. 概述

1.1 应用场景

将数据集中的数据列直接拖拽到单元格中使用时，如果想要「条件显示」某些数据列的值，那么可以使用数据集函数。

1.2 注意事项

1）参数面板中不支持使用。

2）不支持模拟计算，模拟计算详情参见：2.4节。

3）决策报表填报事件不支持使用数据集函数。

2. tablename.select

1）概述

语法	tablename.select(colname,筛选条件1&&筛选条件2&&......)	筛选出数据集某列中符合条件的数据，返回结果是一个数组，相同数据不会合并 注：当仅返回一条数据时，数据类型是「字符串」而不是数组。
参数1	tablename	表示数据集名称，注意是「报表数据集」或者是「服务器数据集」名，而非数据库中的表名。
参数2	colname	表示列名，不区分大小写。

2）注意事项

• 筛选条件中的判断既可以使用单等号，也可以使用双等号；

• 字符串也同时可以使用单引号或者是双引号，对结果均没有影响；

• tablename.select() 与 sql() 的区别主要在于 tablename.select() 是从数据集取数，sql() 是从数据库取数，不需要先定义一个数据集。详情参见 SQL 函数

• 在公式中，以 0 开头的字符串在匹配判断时，例如：ds1.select(colname,ID="003") 结果会返回对应 ID 为 0、03、003…… 的结果。若只希望返回 003 ，可以使用 EXACT 来做匹配判断。例如：ds1.select(colname,exact(ID,"003"))

• 如果想要进行模糊查询，可通过与 Find 函数 嵌套实现，例如公式：ds1.select(产品名称,FIND("苹果",产品名称)!=0)，如下图所示：

3）示例

例如数据集 ds1 取出内置 FRDemo 数据库中的「S产品」表，分别在单元格中输入以下公式：

公式	结果
在 A2 单元格中输入=ds1.select(产品名称)	返回数据集 ds1 产品名称列中的所有产品名称。
在 B2 单元格中输入=ds1.select(产品名称,库存量>20&&订购量 > 30)	返回数据集 ds1 库存量大于 20 且订购量大于 30 的产品。
在 C2 单元格中输入=ds1.select(产品名称,供应商="1"||库存量>30)	返回数据集 ds1 供应商为1或者库存量大于 30 的产品。

3. tablename.group

1）概述

语法	tablename.group(colname,筛选条件 1 && 筛选条件 2,升降序)	筛选出数据集某列中符合条件的数据，若相邻数据相同则进行合并，还可以按照该列进行升降序排列。
参数1	tablename	表示数据集名称，注意是「报表数据集」或者是「服务器数据集」名，而非数据库中的表名。
参数2	colname	表示列名，不区分大小写。
参数3	升降序	为布尔值，true 表示升序，false 表示降序。 注：若使用升降序参数，那么必须写筛选条件参数，若没有筛选条件，可以用 true 或者空格代替：例如=ds1.group(销售员,true,false)或者=ds1.group(销售员, ,false)

2）示例

如数据集 ds1 取出内置 FRDemo 数据库中的「销量」表：

公式	说明
在单元格中输入=ds1.group(销售员)	返回数据集 ds1 销售员列中的值，并且相邻数据若相同会进行合并。
在单元格中输入=ds1.group(销售员,地区 = "华东"&&销量 > 200)	返回数据集 ds1 华东地区销售总额超过 200 的销售员，并且相邻数据若相同会进行合并。
在单元格中输入=ds1.group(销售员,true,false)或=ds1.group(销售员,,false)	返回数据集 ds1 销售员列中的值，并且其中只要数据相同就会进行合并，结果为降序排列，中间的参数为过滤条件，若没有条件，可以用空替代或者使用 true 。
在单元格中输入=ds1.group(销售员,地区=="华东")	返回数据集 ds1 华东地区的销售员，并且相邻数据若相同会进行合并。
在单元格中输入=ds1.group(销售员,地区=="华东",true)	返回数据集 ds1 华东地区的销售员，并且会合并所有相同项，结果为升序排列。

4. tablename.select(#数字)

 

1）概述

语法	tablename.select(#数字)	返回数据集中的行号或者对应列数据
参数1	tablename	表示数据集名称，注意是「报表数据集」或者是「服务器数据集」名，而非数据库中的表名。
参数2	数字	表示列号。 如果tablename.select(#0)则输出数据表行号数组（数据条数） tablename.select(#1)则输出数据库表中第一列的数组数据

2）注意事项

 

• 填报场景下 ds1.select(#0) 这个公式如果联动计算有异常，需要检查父子格关系。例如出现下图所示的计算结果时：

• remoteEvaluate(String) 方法不支持 tablename.select 函数。

3）示例

例如数据集 ds1 取出内置 FRDemo 数据库中的「销量」表：

公式	结果
在单元格中输入=ds1.select(#0)	返回数据集中的行号
在单元格中输入=ds1.select(#1)	返回数据集中对应列数据

5. tablename.value(row,col/colname)

1）概述

语法	tablename.value(row,col/colname)	获取数据集 ds1 中某行某列的值。
参数1	tablename	表示数据集名称，注意是「报表数据集」或者是「服务器数据集」名，而非数据库中的表名。
参数2	row	表示行号
参数3	col/colname	表示列号或者列名

2）注意事项

• 报表的图表标题不支持该函数。

• 决策报表里的图表块不支持该函数。

• JavaScript 中不支持该函数。

3）示例

例如数据集 ds1 取出内置 FRDemo 数据库中的「销量」表：

公式	说明
在单元格中输入=ds1.value(3,2)	返回数据集 ds1 中第 3 行第 2 列的值
在单元格中输入=ds1.value(3,"销售员")	返回数据集 ds1 中第 3 行销售员列的值

6. 应用

6.1 根据不同条件选择使用哪个字段

在单元格中输入如下公式：

=if(条件,ds1.group(customerid),ds2.group(customerid))

并设置其扩展属性为从上到下。

公式说明：

公式	说明
=if(条件,ds1.group(customerid),ds2.group(customerid))	条件为真，单元格使用数据集 ds1 中的 customerid 列，否则使用 ds2 中的 customerid 列 。

6.2 对数据集函数返回的数据再进行运算

在单元格输入公式=sum(ds1.select(销量))，返回数据集 ds1 销量列的总和。

sum 求和公式也可以换用其他如 count、max 等。

7. 注意事项

7.1 数据集函数返回的数据进行扩展

直接将数据列拖拽到单元格时会自动从上到下扩展。但是使用数据集函数获得数据为一个数组，是显示在一个单元格中的，需要另外给单元格设置扩展属性，数据才会进行扩展。

例如在单元格输入公式=ds1.group(销售员,地区=="华东",true)，设置扩展房方向为「纵向」，如下图所示：

预览报表如下图所示：

 将日期型转化为中文形式

 

1. 描述

因政府、事业单位的正式文件中的落款日期都是中文的。

如：在 FineReport 中制作填报模板，使用了日期控件，希望在做填报时，将当前日期控件中选择的日期值（FR 中默认是yyyy-MM-dd的日期格式），能够以中文的方式输出显示，然后再入库，但入库的数据还是默认的数值型的 yyyy-MM-dd 的日期格式。

实现效果如下图：

 

2. 思路

使用NUMTO()函数，通过字符转化方式，将其转化为中文输出即可。

NUMTO(number,bool)：返回 number 的中文表示。

注：其中 bool 用于选择中文表示的方式，当没有 bool 时，采用默认方式（false）显示。

• 示例：NUMTO(2345,true)返回值为二三四五

• 示例：NUMTO(2345,false)返回值为二千三百四十五

• 示例：NUMTO(2345)返回值为二千三百四十五

 

3. 示例

3.1 控件设置

新建一张普通报表，右击任意单元格，选择控件设置，选择控件为日期控件，格式为 yyyy-MM-dd。如下：

 

3.2 属性设置

再右击此单元格，选择形态>公式形态，输入公式：REPLACE(NUMTO(YEAR($$$), true), "零", "〇") + "年" + NUMTO(MONTH($$$), false) + "月" + NUMTO(DAY($$$), false) + "日"

注：REPLACE(NUMTO(YEAR($$$),true),"零","〇")，表示将其中输出的中文的零替换为特殊字符 〇。

注：REPLACE 函数的具体使用，可参见文本函数。

 

4. 保存预览

保存模板后，选择填报预览。

在日期控件中选择好日期后，点击其他处，显示效果如上图所示。

注：有些日期的中文形式还想含有星期，公式如下所示。

示例：

【REPLACE(NUMTO(YEAR(today()),true),"零","〇")+"年"+format(today(),'MMMMM')+NUMTO(DAY(today()),false)+"日"+format(today(),'EEEEE')】返回值为【二O一八年二月十八日星期天】

 

5. 模板下载

点击下载模板：将日期型转化为中文形式.cpt

字符串与数组相加

1. 问题描述

字符串与数组的相加有两种方式，一是直接使用+号，另外一种是使用字符串连接函数 concatenate，那么这两个有区别吗？

 

2. 使用+号连接字符串及数组

字符串会和每个数组元素分别进行相加。

如公式：="a"+[1,2,3]+"b"，其结果是：a1b,a2b,a3b。

 

3. 使用 concatenate 函数连接字符串及数组

数组会被作为字符串与其他字符串串联。

如公式：=concatenate("a",[1,2,3],"b")，其结果是 a1,2,3b。

 

SQL函数

1. 概述

1.1 函数作用

数据集函数 能够从数据集中直接进行条件取数，但是有的时候用户希望某个单元格能够直接获取到数据库中的某个值，而不是先要定义一个数据集后，再去取数据。

这时就可以用 SQL 函数。

1.2 函数解释

语法	SQL(connectionName,sql,columnIndex,rowIndex)	返回的数据是从 connectionName 数据库中获取的 SQL 语句的表中的第 columnIndex 列第 rowIndex 行所对应的元素。
参数1	connectionName	数据连接名字，字符串形式，需要用引号如"FRDemo"；
参数2	sql	SQL 语句或者数据库存储过程，字符串形式，传参数、条件等可以在此拼接实现；
参数3	columnIndex	列序号，整型；
参数4	rowIndex	行序号，整型。

注：行序号可以省略，这样返回值为数据列。

1.3 注意事项

仅支持查询 sql 语句。

2. 取数据库中不带参数的指定内容

示例数据：内置数据库 FRDemo 中的 STSCORE 数据表。

从内置数据库「FRDemo」里的 STSCORE 表取第三行第三列数据值。

从表 STSCORE 中，可看到第 3 行第 3 列的值为 Alex，如下图所示：

现在若要直接在报表的单元格中显示数据值：Alex，而不是通过先定义一个数据集后，再去取数据的方式，使用 sql() 公式，此时只需在单元格中输入：=sql("FRDemo","SELECT * FROM STSCORE",3,3)即可，预览就可看到 Alex 值，如下图所示：

3. 取数据库中带有参数的指定内容

示例数据：内置数据库 FRDemo 中的 STSCORE 数据表。

3.1 SQL 参数为普通参数

需要取出班级为 Class1 的第 3 列所有值。

在单元格中输入：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3)，显示效果（班级为 Class1 的第 3 列所有值），如下图所示：

公式说明：

公式	说明
"FRDemo"	数据连接名
"SELECT * FROM STSCORE where CLASSNO = 'Class1' "	SQL 语句；查询 CLASSNO 为 Class1 的数据
3	列序号，第三列的数据

若需要显示某个具体值，如显示 Jonny （即班级为 Class1 的第 3 列第 4 行的值），写法如下：

=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = 'Class1' ",3,4)

3.2 SQL 参数为变量

若参数值为变量如为报表参数或者是某个单元格，则写法如下：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$class+"' ",3,4) 或=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+A1+"' ",3,4)

例如希望过滤控件选择不同班级，显示不同班级下所有的同学的名字。

首先设置模板参数「class」，然后在单元格中输入公式：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$class+"' ",3)，如下图所示：

显示效果如下图所示：

如果传递的参数是获取当前单元格的值，即用 $$$ 作为参数时，字符串类型同样需要拼接单引号，例如：

=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO = '"+$$$+"' ",3,4)

注1：如果参数或者单元格值有多个，那么 SQL 函数的写法如下：=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO in ('"+$class+"') and COURSE  in ('"+$COURSE+"') ",3,4)

注2：class 参数返回值的分隔符需为','，具体请查看下拉复选框参数联动。

3.3 SQL 参数为变量且需要拼接

在 SQL 中还可以使用 IF 函数进行判断并拼接模板参数，例如希望实现当参数 class 为空时，选择全部学生姓名，可输入公式：

=sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '"+class+"'"),3) 

公式说明：

公式	说明
"SELECT * FROM STSCORE where 1=1 "	将 SQL 语句两边加上引号作为字符串
+if(len(class)== 0,"","and CLASSNO = '"+class+"'"	这里的+是指字符串拼接符号 将前面的 SQL 语句通过+进行拼接 当参数「class」为空，查询语句相当于：SELECT * FROM STSCORE  当参数「class」不为空时，查询语句相当于：SELECT * FROM STSCORE  WHERE 1=1 and CLASSNO ='"+$class+"'
sql("FRDemo","SELECT * FROM STSCORE where 1=1 "+if(len(class)== 0,"","and CLASSNO = '"+class+"'"),3)	当参数「class」为空，相当于：sql("FRDemo","SELECT * FROM STSCORE",3)   当参数「class」不为空时，查询语句相当于：sql("FRDemo","SELECT * FROM STSCORE  WHERE 1=1 and CLASSNO ='"+$class+"'",3)

如果在 SQL 中参数为模糊查询时，可使用如下公式：

=sql("FRDemo","SELECT * FROM STSCORE where CLASSNO like '%"+$class+"%' ",3,4)

 

利用sql进行排序

1. 描述

在对数据进行展示之前，如果没有对数据进行排序，会使数据看起来一片混乱，不能清晰地看到各数据之间的关系。而排序，也有各种各样的需求，如按时间排序，按数字排序，按中文排序等，如果我们只会使用使用设计器的高级排序，扩展后排序，将无法满足各种特殊的排序需求。

2. 解决方案

通过sql直接在数据库中排序，这样不仅性能最佳，而且各数据库对各种特殊的排序需求也比较支持，这里以Oracle为例。

3. 示例

3.1 简单的升序、降序

使用asc进行升序排序，如select * from table order by id asc     注：asc可以省略，默认为升序

使用desc进行降序排序，如select * from table order by id desc 

3.2 空值排序

如果我们想要排序后空值永远在前面可以使用nulls first，如：

同理如果想要空值永远排在后面可以使用nulls last，如：

3.3 随机排序

如果想要查询出来的数据在每次展示的时候，展示顺序各不相同，则可以使用随机排序：

select * from emp order by dbms_random.value()

3.4 中文排序

中文的排序默认是以ASCII码来排序的，由于ASCII码排序与拼音排序大部分相同，常常导致人们误以为默认根据拼音来排序，为了证明这点，我特意创造了个繁体字：

order by name 等同于 order by ascii(name)

按拼音排序可以使用 order by nlssort(name, 'NLS_SORT=SCHINESE_PINYIN_M')

按部首排序可以使用 order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M')

按笔画排序可以使用order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M')

3.5 自定义排序

使用instr( )函数来自定义：

使用decode( )函数来自定义：

使用case when..then...来自定义（不推荐）：

 

 

Cnmoney函数

 

1. 问题描述

在票据类汇总报表中，为防止随意涂改作假，常需将金额转换为大写的人民币形式，此时可使用Cnmoney()函数直接进行转换。

2. 公式的使用说明

Cnmoney(number,unit)：返回人民币大写。

其中，number：需要转换的数值型的数。

unit：单位，"s","b","q","w","sw","bw","qw","y","sy","by","qy","wy"分别代表“拾”，“佰”，“仟”，“万”，“拾万”，“佰万”，“仟万”，“亿”，“拾亿”，“佰亿”，“仟亿”，“万亿”。

注：单位可为空，如果为空，则直接将number转换为人民币大写，否则先将number与单位的进制相乘，然后再将相乘的结果转换为人民币大写。

3. 示例

CNMONEY(1200)等于壹仟贰佰圆整。

CNMONEY(12.5,"w")等于壹拾贰万伍仟圆整。

CNMONEY(56.3478,"bw")等于伍仟陆佰叁拾肆万柒仟捌佰圆整。

CNMONEY(3.4567,"y")等于叁亿肆仟伍佰陆拾柒万圆整。

同样，有时也需要将数字用中文来表示，此时可使用NUMTO()函数。

 

Switch函数：Switch函数多条件赋值

1. 概述

1.1 问题描述

当需要判断条件多的时候，使用 IF 函数 可能会觉得用要对每种情况都进行判断，比较麻烦，那么可以使用 switch 函数与NVL函数结合进行多条件赋值。

例如希望对班级进行设置：当前值是 Class1 则显示一班，如果是 Class2，则显示二班，如果是 Class3，则显示三班，否则则显示四班，如下图所示：

1.2 实现思路

使用 switch 函数与NVL函数结合进行多条件赋值。

2. 操作步骤

3.1 报表设计

3.1.1 数据准备

新建数据查询 ds1，数据查询语句如下：SELECT * FROM stscore，如下图所示：

3.1.2 模板设计

1）设置模板样式，如下图所示：

注：插入斜线可参见 插入 2.7 节。

2）设置公式

单击 A2 单元格，右键单击「单元格元素>插入数据列>高级」，或者双击单元格，点击「高级」，在自定义显示的值中填入公式：nvl(switch($$$,'Class1','一班','Class2','二班','Class3','三班'),'四班')，如下图所示：

公式说明：

公式	说明
switch($$$,'Class1','一班','Class2','二班','Class3','三班')	如果数据为 Class1，则赋值为一班，Class2 则赋值为二班，Class 三则赋值为三班
nvl(switch($$$,'Class1','一班','Class2','二班','Class3','三班'),'四班')	返回第一个不是空的字段，即数据如果不是一班、二班或者三班，则返回「四班」

3.2 效果查看

1）PC端

保存模板，点击分页预览，如下图所示：

注：若希望不分页展示，可以选择「数据分析预览」。

2）移动端

 

NVL函数

 

1. 函数用法

NVL 函数的使用方法：NVL(value1,value2,value3,...)：在所有参数中返回第一个不是 null 的值。

注：6.5 之前的版本 NVL 只支持 2 个参数，现在升级到支持多个参数。

下面以填报的示例来说明 NVL 多参数的用法。

2. 需求

填报应用中，可能会遇到一组单元格中，只需将其中不为空的值保存至数据库的某个字段，如下图所示，对于语文成绩级别这个字段，可能有四个值，入库的时候只保存选择的级别。

 

3. 示例

3.1 创建数据

在数据库中新建一个表，表名为 C，新建如下数据：

然后，添加其报表数据集 ds1，SQL 语句为：select * from C

3.2 表样设计

如下图所示，拖动字段到对应单元格并做相应合并：

3.3 控件设置

设置 B5、C5、E5、E6、E7、E8 为文本类型控件即可。

3.4 条件属性设置

对 E5 单元格，设置条件属性：不等于 4 时，公式为$$$!=4，赋新值为空，如下：

对 E6 单元格：设置为不等于 3 时，公式为$$$!=3，赋新值为空；

对 E7 单元格：设置为不等于 2 时，公式为$$$!=2，赋新值为空；

对 E8 单元格：设置为不等于 1 时，公式为$$$!=1，赋新值为空。

3.5 其他属性设置

为了保证“优秀”等不设置控件的单元格在添加记录时能默认添加，可设置单元格属性表-其他属性>插入行策略>原值，如下图：

3.6 报表填报属性设置

在 NUMBERQ 的值中，输入=NVL(E5,E6,E7,E8)，取出第一个不为空的值进行填报，如下：

3.7 保存与预览模板

在设计器中，点击填报预览，效果：

点击增加记录后，点击提交，如下：

提交成功时，效果如下：

刷新页面，可见提交成功，提交值就是第一个不为空的值 3，如下：

4. 模板下载

点击下载模板：NVL函数.cpt

Let函数

 

1. 函数作用

报表中，若使用到复杂的FR脚本表达式，如：=if (很长很长的公式 > 0，执行语句 1(很长很长的公式)，执行语句 2(很长很长的公式))，首先想到的是：将很长很长的公式先放在一个单元格（如：A1）中，然后在另一个单元格中，输入=if(a1>0, 执行语句1(a1), 执行语句2(a1))。对于这样的很长公式，一般公式中还会有部分内容是一些其他的简单运算，若再使用单元格去求算一下，最终求算最终结果时就得引用多个单元格。这样的赋值方式，不但多占用空间内存使得重复计算，其性能往往也不是很好。据此 FR 已增加 LET 公式，可将其很长的公式直接赋值，且可直接使用此公式求解最终结果（复杂的脚本表达式），还可提高其性能。

2. 使用说明

2.1 LET()函数说明

LET(变量名,变量值,变量名,变量值,...,表达式)：局部变量赋值函数，参数的个数 N 必须为奇数, 最后一个是表达式，前面是 N-1(偶数)为局部变量赋值对。

• 变量名：必须是合法的变量名，以字母开头，可包括字母，数字和下划线。

• 表达式：根据其前面的 N-1 个参数赋值后，需计算的结果表达式，且这些变量赋值只在这个表达式内部有效。

示例：

LET(a, 5,b, 6, a+b)等于 11。

2.2 示例

下面根据如上所遇的情况示例介绍，您可根据实际情况，参照示例使用此公式。

1）一个很长的公式为((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)，若将其赋给 a，需计算 IF(a>1000,(a+200)/a,(a-200)*a)的结果表达式。

2）通常的做法：在任意单元格（如：A1）中，输入((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)，然后在另一个单元格（如：B1）中，输入=IF(A1>1000,(A1+200)/A1,(A1-200)*A1)。

3）而计算时是将 A1 中的值，对应带入其结果表达式中的。如这边带入的话就是计算=IF((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))>1000,((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))+200)/(((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)),((((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1))-200)*(((10+20+30)*MAX(10,20,30)+DATEDIF("2001/2/28","2004/3/20","D"))*COS(0.5)/ROUND(2.15, 1)))

不仅多占用单元格且使得表达式重复计算，其性能往往也不是很好。

4）LET 公式的写法：只需在单元格中直接赋值和写入结果表达式。在一个单元格中，直接输入=LET(a, ((10 + 20 + 30) * MAX(10, 20, 30) + DATEDIF("2001/2/28", "2004/3/20", "D")) * COS(0.5) / ROUND(2.15, 1), IF(a> 1000, (a+ 200) / a, (a - 200) * a))即可执行结果表达式，从而得出最终结果。

2.3 总结

对比可看出，使用 LET 公式明显比通常的做法来得简单，且公式中若再有其他简单运算，如：最大值、平均值等等，也可不必再次占用其他单元格然后再引用此类单元格，这边可直接一步到位求算结果（亦可在 LET 中直接写一些常用的计算公式），这样也大大地提高了性能。

 

Round函数

1. 概述

1.1 应用场景

在制作报表时，某数据列如收入金额是数字类型，其中数据包含小数，且小数位数不超过 2 位，对该列进行求和（使用公式 sum）等处理时，会发现最终的结果如：123456.409999996，即小数位数超过 2 位。

此时您可能会有疑问，为什么小数位数不是 2 位？若您的报表对有效位数比较敏感，如金额汇总，总金额最多精确到分，即小数位数最多 2 位，该怎么办？

1.2 功能简介

此情况时，可使用 round() 函数对计算后的结果按指定位数四舍五入来解决。

 

2. 示例

2.1 函数说明

1）概述

语法	ROUND(number,num_digits, boolean)	返回某个数字按指定位数舍入后的数字。
参数1	number	需要进行舍入的数字
参数2	num_digits	指定的位数，按此位数进行舍入。 如果 num_digits 大于 0，则舍入到指定的小数位。 如果 num_digits 等于 0，则舍入到最接近的整数。 如果 num_digits 小于 0，则在小数点左侧进行舍入。
参数3	boolean	因浮点数存在精度计算丢失问题，导致计算结果里可能带上 9999、0000 这些，因此加入第三个参数来控制是否需要去除 9999、0000。 false 表示需要过滤 9999、0000 这些数据；true 表示保留，参数为空则默认为 false。

2）注意事项

• 2020-07-08 及之后版本的 JAR 包才会有第三个参数。

• 2020-07-08 及之后版本的 JAR 包 number 参数支持字符串。

3）示例

公式	结果
ROUND(2.15, 1)	2.2
ROUND(2.149, 1)	2.1
ROUND(-1.475, 2)	-1.48
ROUND(21.5, -1)	20
ROUND(1.99999999, 8)	2
ROUND(1.99999999, 8, true)	1.99999999

2.2 操作步骤

报表中的销量列求和，设计如下：

如上对收入 sum 求和后精度变大了。

遇到这样的情况时，可使用 round 函数对求和结果按 2 位小数位四舍五入，即 B5 单元格中的使用方法。

 

Map函数

 

1. 函数作用

在制作报表时，我们可能需要根据某一单元格的值对数据集进行检索并返回对应的值。

此情况时，可使用 map() 函数，根据数据集的名字，找到对应的数据集，找到其中索引列的值为 key 所对应的返回值。 map() 函数首先检索模板数据集，再检索服务器数据集。

示例：

MAP(1001,"employee",1,2)返回 employee 数据集，第 1 列中值为 1001 那条记录中第 2 列的值。

MAP(1001,"employee","name","address")返回 employee 数据集，name 列中值为 1001 那条记录中 address 列的值。注：只返回第一个找到的值。

 

2. 使用说明

MAP(object,string,int,int)：根据数据集的名字，找到对应的数据集，找到其中索引列的值为 key 所对应的返回值。

object：索引值，需要查询的内容。

string：数据集的名字，定义的数据查询的名字。

int：索引值所在列序号。

int：返回值所在列序号。

注：后两个参数也可以写列名代替。根据数据集的名字,找到对应的数据集,找到其中索引列的值为key所对应的返回值。数据集的查找方式是依次从报表数据集找到服务器数据集。索引列序号与返回值序列号的初始值为1。

3. 示例

3.1 报表设计

3.1.1 数据准备

新建数据查询 ds1 ，SQL 语句如下SELECT * FROM 供应商，如下图所示：

3.1.2 模板设计

新建普通报表，分别给 A1~A5 单元格赋值，单击 A2 单元格，右键选择单元格元素，选择插入公式，公式如下：

MAP(A1, "ds1", "公司名称", "供应商ID")返回 ds1 数据集，“公司名称”列中值为 A1 那条记录中“供应商 ID”列的值。

MAP(A1, "ds1", 2, 1)返回 ds1 数据集，第 2 列中值为 A1 那条记录中第1列的值。

MAP("妙生", "ds1", "公司名称", "供应商ID")返回 ds1 数据集，“公司名称”列中值为“妙生”那条记录中“供应商 ID”列的值。

MAP("妙生", "ds1", 2, 1)返回 ds1 数据集，第2列中值为“妙生”那条记录中第1列的值。

3.2 效果预览

1）PC端

保存模板，点击分页预览，如下图所示：

2）移动端

4. 模板下载

点击下载模板：Map函数.cpt

 

treelayer函数

 

1. 概述

语法：treelayer(TreeObject, Int, Boolean, String)

定义：返回一个树对象 TreeObject 第 n 层的值，一般为树数据集，或下拉树、视图树等树对象，并且可以设置返回值类型及分隔符。

 

详细解释：

对象	定义	值
TreeObject	tree 对象	例如：$tree
Int	想要获得层级的数值	最上层为 1 ，第二层为 2 ，依此类推，若无则返回最底层
Boolean	返回值类型	false：返回值类型为数组，默认值 true：返回值类型为字符串
String	当返回值类型为字符串时的分隔符	以双引号表示，默认为逗号：","

2. 示例

以一个下拉树控件展示 FRDemo 数据库中的部门层级树为例，来讲解 treelayer 函数的作用。

2.1 新建数据集

2.1.1 新建数据库查询

新建普通报表，新建数据集 ds1，SQL 语句为：select * from department，如下图所示：

2.1.2 新建树数据集

新建一个树数据集 Tree1，数据集为 ds1，依赖字段为 did，父标记字段为 fid，如下图所示：

2.1.3 数据集预览

树数据集效果预览如下图所示：

 

2.2 添加控件

在参数栏添加两个控件：一个下拉树控件和一个文本控件，如下图所示：

2.2.1 下拉树控件

下拉树控件的控件名称修改为 tree。勾选 多选和结果返回完整层次路径。

数据字典选择自动构建，依靠树数据集 Tree1 构建，实际值和显示值都选择 department。如下图所示：

2.2.2 文本控件

文本控件的控件值选择公式：treelayer($tree, true, "\',\'")，如下图所示：

 

2.4 效果预览

保存模板，点击预览，勾选总部>人力资源部>人力资源文员、总部>市场部>业务员，如下图所示：

根据文本控件值中填入的公式，预览时的返回值和分隔符也会有所不同，详情如下表所示：

公式	返回值	分隔符	图示
treelayer($tree, true, "\',\'")	人力资源文员 业务员	','
treelayer($tree, 2)	人力资源部 市场部	,
treelayer($tree, 2, true, "\',\'")	人力资源部 市场部	','

3. 模板下载

点击下载模板：treelayer函数.cpt

日期函数应用

1. 概述

在使用 FineReport 进行可视化展示时，经常会需要对日期数据进行处理，本文介绍几种日期类型数据的处理应用场景。

2. 获取月份或日期的时候显示 2 位

2.1 问题描述

在使用公式month()或者是day()时，如果月份或日期是一位数，则显示出来的也只有一位数，比如说 1 月 9 号，获取月份时显示的是 1，而不是 01，获取日期时，显示的是 9，而不是 09，如果需要获取到 01 或者是 09，这个该如何通过公式实现呢？

2.2 解决方案

通过公式或者自定义函数实现。

2.3 操作步骤

2.3.1 公式实现

1）使用 RIGHT、CONCATENATE、MONTH 组合函数实现。

例如显示当前时间（2021-08-31）对应的月份和天数且以两位数字显示。

在单元格分别输入：

月份公式：=right(concatenate('0',month(today())),2)

天数公式：=right(concatenate('0',day(today())),2)

公式说明：

公式	说明
concatenate('0',month(today())) concatenate('0',day(today()))	通过 concatenate 将获取到的月份或者天数前面拼接一个 0，比如说 11 月，则显示 011，如果是 2 月，则显示 02；如果是 31 号，则显示 031
right(concatenate('0',month(today())),2) right(concatenate('0',day(today())),2)	通过 right 方法，获取右边的 2 位数字，比如说 2 月，截取 2 位数字，则为 02，比如 12 月，截取右边的两位，则为 12；比如 31号，则截取右边的两位 31

效果查看：

2）通过 FORMAT 来格式化字符串。

在单元格分别输入：

月份公式：=FORMAT(MONTH("2021-08-31"), "00")

天数公式：=FORMAT(day("2021-08-31"), "00")

公式说明：

公式	说明
MONTH("2021-08-31") day("2021-08-31")	返回月份数字：8 返回天数数字：31
FORMAT(MONTH("2021-08-31"), "00") FORMAT(day("2021-08-31"), "00")	返回 format 格式，也就是

效果查看：

2.3.2 自定义函数实现

如果觉得公式比较复杂，则可以通过自定义函数实现，代码如下：

package com.fr.function;
import com.fr.script.AbstractFunction;
public class Add0 extends AbstractFunction {      
    public Object run(Object[] args) {      
        String result = args[0].toString();      
        if(result.length() == 1)    
            result = '0' + result;    
        return result;   
    }
}

自定义函数的详细定义步骤请参照 自定义函数。

3. 利用日期函数生成特定编号

有些情况下需要生成特定字符且带「年月日时分秒」这样的字符串，类似 china20170726144516 这样的编号，可以直接利用时间公式和字符串拼接函数来实现。

在单元格中写入公式：="china" + FORMAT(now(), "yyyMMddhhmmss") 结果为：china20180101124516，即字符串“china”后面加当前的「年月日时分秒」。

注：一般编号非必要的情况下，不要全用数字，因为在导入导出到 EXCEL 时数字有可能出错。

两个表里数组字段的映射关联

 

1. 描述

有时，在新建数据集过程中，可能会遇到这样的需求：将2个表进行数组字段的映射关联，形成一个最终表。一般情况下，可以直接通过表与表之间通过JOIN关联实现，但如果某个字段以数组存在时，JOIN方法就不好处理了。

如图：表1、表2

实现效果：

2. 思路

1）在MYSQL数据库中通过group_concat()函数与find_in_set()函数结合实现。

2）在Sql Server数据库中通过stuff()函数与charindex()函数结合实现。

3. 示例(一)

Mysql版本：5.5.28

在Mysql中实现，SQLl脚本如下：

1. select a.*, group_concat(lesson) as LESSONNAME from t2 a, t1 b where find_in_set(b.id, lessonid) group by name  

4. 示例(二)

Sql Server版本及运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64)  Feb 10 2012 19:39:15  Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

在Sql Server中实现，SQLl脚本如下：

1. ;with tb as ( select a.*, lesson as lessonname from t2 a, t1 b where charindex(','+b.id+',',','+lessonid+',')>0 ) select id,name,lessonid, [val]=stuff( (select ',' +[lessonname] from tb as kb where kb.id = ka.id for xml path('')),1,1,'') from tb as ka group by id,name,lessonid  

注：上述实现方案仅供参考，实际情况可再此基础上进一步延伸。

 

 

分组合并字符串

1. 描述

在针对数字类型的数据时，我们有分组求和，求最大最小值等操作。

而在处理字符串类型的数据时，有时我们也需要将同分组的数据合并拼接到一起，实现效果如下图：

2. 解决方案

利用报表设计器的功能实现或是利用相关数据库的函数来实现。

3. 示例

3.1 通过设计器实现

1）数据集

添加数据集ds1，sql语句为：SELECT * FROM 部门

2）模板设置

将字段拖进相应的单元格中，模板设置如下：

3）单元格属性

选择B1单元格，右击选择“扩展”，将B1单元格扩展方向设置为“不扩展”，如下图所示：

4）保存预览

保存预览，即可上图所示效果。

3.2 通过sql函数实现

通过设计器设置的方法虽然简单，但在大量数据的前提下，通过sql预先处理好数据会使报表性能得到很大的提高。

下面介绍三大主流数据库各自的处理方法。（数据表结构均与上面的例子一致）

1）Oracle数据库

1. select 总部门, wmsys.wm_concat(部门名称) as 部门名称 from 部门 group by 总部门  

2）sql server数据库

1. SELECT 总部门, stuff((select ','+部门名称 from 部门 b where a.总部门=b.总部门 for xml path('')),1,1,'') as 部门名称 FROM 部门 a group by a.总部门  

3）mysql数据库

1. select 总部门, group_concat(部门名称) from 部门 group by 总部门  

4. 补充说明

设计器和Oracle的方法可以通过replace函数来修改字符之间的拼接符，sqlserver和mysql可以直接在函数里指定相应的拼接符。

其中mysql的group_concat函数完整语法格式为group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

 

Join连接

1. 描述

由于一些特殊的数据展示，比如需要在一张报表上同时展示客户信息和对应的订单详情，这个时候就会用到客户表和订单表里的内容，虽然可以建立多个数据集然后通过单元格过滤来实现，但个人感觉还是不够直接，能不能有更便捷的方法呢？答案肯定是：有！

2. 思路

在创建数据集时，对事先相关表通过SQL的Join方法进行关联，然后直接使用。

3. Join介绍

通过图文并茂的方式对SQL的Join进行简单的介绍：join大致分为以下七种情况：

 

 

4. 示例

4.1 准备数据

1. DROP TABLE [dbo].[test_a] GO CREATE TABLE [dbo].[test_a] ( [id] int NULL , [name] varchar(255) NULL ) GO -- ---------------------------- -- Records of test_a -- ---------------------------- INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'1', N'苹果') GO GO INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'2', N'橘子') GO GO INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'3', N'菠萝') GO GO INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'4', N'香蕉') GO GO INSERT INTO [dbo].[test_a] ([id], [name]) VALUES (N'5', N'西瓜') GO GO ----------------------------------------------------------- DROP TABLE [dbo].[test_b] GO CREATE TABLE [dbo].[test_b] ( [id] int NULL , [name] varchar(255) NULL ) GO -- ---------------------------- -- Records of test_b -- ---------------------------- INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'1', N'梨子') GO GO INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'2', N'苹果') GO GO INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'3', N'草莓') GO GO INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'4', N'桃子') GO GO INSERT INTO [dbo].[test_b] ([id], [name]) VALUES (N'5', N'香蕉') GO GO   

注：需要手动建test_a，test_b两个表。

4.2 示例介绍

1）Inner join

产生A和B的交集。

1. SELECT * FROM test_a INNER JOIN test_b ON test_a.name = test_b.name   

2）Full outer join

产生A和B的并集。对于没有匹配的记录，则以null做为值。

1. SELECT * FROM test_a FULL OUTER JOIN test_b ON test_a.name = test_b.name   

3）Left outer join 

产生表A的完全集，而B表中匹配的则有值，没匹配的以null值取代。

1. SELECT * FROM test_a LEFT OUTER JOIN test_b ON test_a.name = test_b.name   

4）Left outer join on where

产生在A表中有而在B表中没有的集合。

1. SELECT * FROM test_a LEFT OUTER JOIN test_b ON test_a.name = test_b.name WHERE test_b.name IS NULL   

5）RIGHT OUTER JOIN

产生表B的完全集，而A表中匹配的则有值，没匹配的以null值取代。

1. SELECT * FROM test_a RIGHT OUTER JOIN test_b ON test_a.name = test_b.name   

注:考虑到Mysql对full join 不支持的原因，在Mysql如果要实现full join的效果可采取变通的方式:

1. SELECT * FROM test_a LEFT OUTER JOIN test_b ON test_a.name = test_b.name UNION SELECT * FROM test_a RIGHT OUTER JOIN test_b ON test_a.name = test_b.name   

6）right outer join on where

产生在B表中有而在A表中没有的集合。

1. SELECT * FROM test_a RIGHT OUTER JOIN test_b ON test_a.name = test_b.name WHERE test_a.name IS NULL   

7）FULL OUTER JOIN WHERE

产生（A表中有但B表没有）和（B表中有但A表中没有）的数据集。

1. SELECT * FROM test_a FULL OUTER JOIN test_b ON test_a.name = test_b.name WHERE test_a.name IS NULL OR test_b.name IS NULL   

8）cross join

表A和表B的数据进行一个N*M的组合，即笛卡尔积(交差集)。一般来说，我们很少用到这个语法。因为这种集合的使用对于性能来说非常危险，尤其是表很大。

 

行转列/列转行

1. 描述

由于一些特殊的数据表结构，往往在满足客户的需求方便会很难实现，但用户又不希望去改变表的结构，那我们怎么来实现这一功能呢？

转换为：

备注：以上操作均在   Microsoft SQL Server 2008 实测，您在操作时可能因软件版本的不同或有所差异！请自行矫正…

2. 思路

可以在创建数据集时，直接对相关表通过SQL的行转列(列转行)方法来实现，然后将结果保存并使用。

3. 示例(一)行转列

3.1 准备数据

1. -- ---------------------------- -- Table structure for test_table -- ---------------------------- DROP TABLE [dbo].[test_table] GO CREATE TABLE [dbo].[test_table] ( [name] varchar(255) NULL , [cp] varchar(255) NULL , [price] int NULL ) GO -- ---------------------------- -- Records of test_table -- ---------------------------- INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'香蕉', N'20') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'苹果', N'25') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'梨', N'30') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小明', N'菠萝', N'24') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'香蕉', N'5') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'苹果', N'16') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'梨', N'9') GO GO INSERT INTO [dbo].[test_table] ([name], [cp], [price]) VALUES (N'小兰', N'菠萝', N'33') GO GO   

3.2  静态SQL(推荐)

1. SELECT * FROM test_table PIVOT ( MAX (price) FOR cp IN (香蕉, 苹果, 梨, 菠萝) ) a   

 

3.3  动态SQL

1. declare @ck varchar(8000) set @ck='' --初始化变量@ck select @ck=@ck+','+ cp from test_table group by cp -- 变量多值赋值，将结果 ,香蕉,苹果,梨,菠萝 赋值给变量@ck set @ck=stuff(@ck,1,1,'') --去掉首个',' 将 香蕉,苹果,梨,菠萝 赋值给变量@ck --第一个字符串 apple 中删除从第 2 个位置（字符 b）开始的3个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个新的字符串。 --例：SELECT STUFF('apple', 2, 3, 'test'); 返回ateste set @ck='select * from test_table pivot (max(price) for cp in ('+@ck+'))a' --拼接sql并赋值给变量@ck exec(@ck) --执行sql语句   

4. 示例(二)列转行

4.1 准备数据

1. -- ---------------------------- -- Table structure for test_table_2 -- ---------------------------- DROP TABLE [dbo].[test_table_2] GO CREATE TABLE [dbo].[test_table_2] ( [name] varchar(255) NULL , [香蕉] int NULL , [苹果] int NULL , [梨] int NULL , [菠萝] int NULL ) GO -- ---------------------------- -- Records of test_table_2 -- ---------------------------- INSERT INTO [dbo].[test_table_2] ([name], [香蕉], [苹果], [梨], [菠萝]) VALUES (N'小明', N'85', N'75', N'65', N'55') GO GO INSERT INTO [dbo].[test_table_2] ([name], [香蕉], [苹果], [梨], [菠萝]) VALUES (N'小兰', N'90', N'80', N'70', N'60') GO GO   

4.2 静态SQL(推荐)

1. SELECT name,产品,数值 FROM test_table_2 UNPIVOT ( 数值 FOR 产品 IN ( [香蕉], [苹果], [梨], [菠萝] ) ) t   

4.3  动态SQL

1. declare @ck nvarchar(3000) select @ck= isnull(@ck+',','')+quotename(Name) from syscolumns where ID= object_id('test_table_2') and Name not in('name') order by Colid --获取到表test_table_2中除了字段name的所有字段名(以’,’)隔开并将结果赋值给@ck --@ck=香蕉,苹果,梨,菠萝 set @ck='select name,[产品],[数值] from test_table_2 unpivot ([数值] for [产品] in('+@ck+'))b' --@ck=select name,[产品],[数值] from test_table_2 unpivot ([数值] for [产品] in(香蕉,苹果,梨,菠萝))b exec(@ck) --执行sql语句   

关键字：行列互换，行列转换，行转列，列转行

 

Null的处理

1. 描述

我们在建立数据集时，有时候可以会在原表基础上对字段进行加减乘除的运算，此时如果遇到字段中有Null或0值的情况，我们该怎么处理呢？

2. 思路

可以对Null或0值通过数据库表达式进行转换再运算。

3. 示例

3.1 示例(字段为空)

当在做二个字段相乘，一个有数据值，一个为空值时，这时您会发现语句并不会报错，但是这种结果并不是您想要的，您的需求是当数据列值为空时，用1来表示。OK，现在我们就以实例（分不同的数据库）来解答！

准备数据

1. -- ---------------------------- -- Table structure for test_c -- ---------------------------- DROP TABLE [dbo].[test_c] GO CREATE TABLE [dbo].[test_c] ( [cp] varchar(255) NULL , [price] int NULL , [numb] int NULL ) GO -- ---------------------------- -- Records of test_c -- ---------------------------- INSERT INTO [dbo].[test_c] ([cp], [price], [numb]) VALUES (N'苹果', N'80', N'20') GO GO INSERT INTO [dbo].[test_c] ([cp], [price], [numb]) VALUES (N'橘子', N'35', null) GO GO INSERT INTO [dbo].[test_c] ([cp], [price], [numb]) VALUES (N'菠萝', N'45', N'0') GO GO INSERT INTO [dbo].[test_c] ([cp], [price], [numb]) VALUES (N'香蕉', N'24', null) GO GO INSERT INTO [dbo].[test_c] ([cp], [price], [numb]) VALUES (N'西瓜', N'60', N'15') GO GO   

 

cp	price	numb
苹果	80	20
橘子	35
菠萝	45	0
香蕉	24
西瓜	60	15

 

1）SQL Server 数据库--使用Isnull()函数

1. SELECT cp, price, numb, price * isnull(numb, 1) AS priceb --isnull(numb, 1) 当numb的值为空值时，就返回1 FROM test_c;   

2）Oracle 数据库-使用nvl()函数

1. select cp, price, numb, price * nvl(numb, 1) as priceb --nvl(numb, 1) 当numb的值为空值时，就返回1 from test_c;   

 

 

3）MySQL 数据库-使用Coalesce()函数

1. SELECT cp, price, numb, price * coalesce(numb, 1) AS priceb -- coalesce(numb, 1) 当numb的值为空值时，就返回1 FROM test_c;   

 

 

3.2 示例(字段为0)

当我们在做数据集sql运算的时候，可能会遇见字段相除的情况，这个时候可要注意，分母不能为0，例：

1. SELECT cp, price, numb, price /numb AS priceb FROM test_c;   

会报如下错误

1. [SQL]SELECT cp, price, numb, price /numb AS priceb FROM test_c [Err] 22012 - [SQL Server]遇到以零作除数错误。   

如果遇到分母为0的情况，我们可这样处理。

以sql server为例

1. SELECT cp, price, numb, price / CASE WHEN numb = 0 THEN Null ELSE numb END AS priceb --CASE WHEN numb = 0 THEN Null ELSE numb END 解释：当numb = 0时，返回null FROM test_c;   

注：分母不能为空，但分子能为空

 

通过Sql实现的组内排序(排名)

1. 环境

注意事项：由于9.0设计器内置的是sqlite数据库，在使用上很多语法都不支持，请将sqlite数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

迁移方法：使用第三方软件迁移内置FRDemo数据库到指定数据库

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                      Copyright (c) Microsoft Corporation

                      Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

2. 描述

对于一些特殊的数据表（年报，月报），可能只需要对它某一个时间点（某一年或某一个月）的数据进行分析，此时在你的脑海里肯定会闪现一个词--【组内排序】。设置单元格父格，进行分组扩展就能实现。但我们知道，这种方法是浏览器（客户端）获取到数据，通过决策报表处理再展示的，当我们面对的是大数据时，考虑到效率问题，上面的方法是否是最佳选择？有没有更好的选择方案呢？答案是肯定的！

3. 思路

我们可以直接在新建数据集时将其处理成带组内排序字段的结果集，然后将数据交付给浏览器直接展示。大家都知道，数据在服务器端处理肯定比在客户端处理快的多。

4. 示例

4.1 准备数据

1. -- ---------------------------- -- Table structure for zsh_0220 -- ---------------------------- DROP TABLE [dbo].[zsh_0220] GO CREATE TABLE [dbo].[zsh_0220] ( [t_time] int NULL , [code] int NULL , [name] varchar(255) NULL , [cl] int NULL ) GO -- ---------------------------- -- Records of zsh_0220 -- ---------------------------- INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'110000', N'北京市', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'120000', N'天津市', N'3861') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'130000', N'河北省', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'140000', N'山西省', N'6651') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'150000', N'内蒙古自治区', N'4030') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'210000', N'辽宁省', N'4448') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'110000', N'北京市', N'8122') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'120000', N'天津市', N'3524') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'130000', N'河北省', N'9006') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'140000', N'山西省', N'6288') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'150000', N'内蒙古自治区', N'7787') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'210000', N'辽宁省', N'6288') GO GO   

 

 

4.2 执行(组内)排名sql

1. SELECT t_time, code, name, CL, row_number () OVER (partition BY t_time ORDER BY cl) AS 组内排名1, --T_time组内，cl排名 row_number () OVER (ORDER BY cl) AS 排名1_1, --所有cl的排名 rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名2, --T_time组内，cl排名 rank () OVER (ORDER BY cl) AS 排名2_1, --所有cl的排名 dense_rank () OVER (partition BY t_time ORDER BY cl) AS 组内排名3, --T_time组内，cl排名 dense_rank () OVER (ORDER BY cl) AS 排名3_1 --所有cl的排名 FROM zsh_0220 ORDER BY t_time,code;  

关键点分析：

1. OVER (partition BY t_time ORDER BY cl)  

解释：按t_time分组（示例将t_time分为2005,2006二个组）， cl排序（默认：升序）。降序可设置ORDER BY cl desc

函数ROW_NUMBER() OVER ()  ,RANK() OVER ()RANK(),DENSE_RANK() OVER ()的区别在这就不详细介绍了，可参考：开窗函数-排名

4.3 预览结果：

执行上述sql语句，可得到如下效果：

条件的分析函数有：

row_number() over(partition by … order by …) 

rank() over(partition by … order by …) 

dense_rank() over(partition by … order by …) 

count() over(partition by … order by …) 

max() over(partition by … order by …) 

min() over(partition by … order by …) 

sum() over(partition by … order by …) 

avg() over(partition by … order by …) 

first_value() over(partition by … order by …) 

last_value() over(partition by … order by …) 

lag() over(partition by … order by …) 

lead() over(partition by … order by …) 

等等…有兴趣的朋友可以试试，这里就不详解了！

 

 

替换函数(Replace,Trim,Rtrim,

1. 描述

大家平时在设计报表的时候，很多人都遇到过这样的问题，明明传递参数进去了，为什么检索不到数据呢？可在数据表中查看是有数据的呀？最后得出的结论：由于表中字段内容存在各种各样的问题导致的，比方字符串中间有多余的空格，换行符，制表符等等…

2. 思路

我们可以在新建数据集时，对过滤字段通过数据库自带的TRIM( ), RTRIM( ), LTRIM( ), REPLACE()等函数将其进行处理。

3. 函数使用范围

MySQL： TRIM( ), RTRIM( ), LTRIM( ), Replace()

Oracle：RTRIM( ), LTRIM( ), Replace()

SQL Server：RTRIM( ), LTRIM( ), Replace()

4. 示例

4.1 准备数据

1. -- ---------------------------- -- Table structure for zsh_0220 -- ---------------------------- DROP TABLE [dbo].[zsh_0220] GO CREATE TABLE [dbo].[zsh_0220] ( [t_time] int NULL , [code] int NULL , [name] varchar(255) NULL , [cl] int NULL ) GO -- ---------------------------- -- Records of zsh_0220 -- ---------------------------- INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'110000', N' 北京市', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'120000', N'天津 市', N'3861') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2015', N'130000', N'河北省 ', N'7631') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'110000', N'北京市', N'8122') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'120000', N' 天 津 市', N'3524') GO GO INSERT INTO [dbo].[zsh_0220] ([t_time], [code], [name], [cl]) VALUES (N'2016', N'130000', N'河北省', N'9006') GO GO   

4.2 LTRIM(参数)介绍

语法：【参数】是表达式或者二进制数据。

返回内容：varchar 或 nvarchar

1. SELECT * FROM dbo.[zsh_0220] where LTRIM (name) ='${dq_r}' --dq_r 默认值：北京市 --LTRIM()：返回删除了前空格之后的字符表达式。   

1. select LTRIM('  我的前面有空格') --返回：‘我的前面有空格’  

4.3 RTRIM(参数)介绍

语法：【参数】是表达式或者二进制数据。

返回内容：varchar 或 nvarchar

1. SELECT * FROM dbo.[zsh_0220] where LTRIM (name) ='${dq_r}' --dq_r 默认值：河北省 --RTRIM()：返回删除了后空格之后的字符表达式。   

1. select RTRIM('我的前面有空格      ') --返回：‘我的后面有空格’  

4.4 TRIM ()介绍

语法：TRIM ( [ [位置] [要移除的字串] FROM ] 字串)。

返回内容：varchar 或 nvarchar

1. --TRIM()：将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除。 SELECT TRIM( '.' FROM '. test .') AS Result; --返回:删除了首尾的’.’的结果’ test ’   

1. SELECT TRIM(' zsh ') --返回：’zsh’ 默认是删除首尾空格  

4.5 Replace()介绍（推荐使用）

语法：Replace(参数1,参数2,参数3)：返回 nvarchar 如果输入自变量之一是 nvarchar 数据类型; 否则，将返回 varchar。如果任何一个参数为NULL，则返回NULL。

参数解释：

参数1:为要搜索字符串表达式。

参数2:为要查找的子字符串。

参数3:用来替换的字符串。

Replace()：用另一个字符串值替换出现的所有指定字符串值。

1. SELECT * FROM dbo.[zsh_0220] where replace(name,' ','') ='${dq_r}' --dq_r 默认值：天津市 -- replace(name,' ','')会替换掉name中所有的空格，然后与$dq_rj(天津市)进行匹配。  

1. SELECT REPLACE('abcdefghicde','cde','xxx'); --返回：'abxxxfghixxx' --解释：用’xxx’替换掉了'abcdefghicde'中所有的’cde’   

分析函数-CUME_DIST()

注意事项：由于8.0设计器内置的是sqlite数据库，在使用上很多语法都不支持，请将sqlite数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

       迁移方法：使用第三方软件迁移内置FRDemo数据库到指定数据库

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                       Copyright (c) Microsoft Corporation

                       Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

 

1. 问题描述

有时候，一些特殊的数据分析，我们很难通过报表单元格界面设计出来！比如我手里有一张企业的薪资表，现在想分析某个人薪资在本部门或全公司处于一个什么水平，就很难实现。在本部门，比他薪资高的人占多少？比他薪资低的又占多少？在本部门……等等。

 

2. 实现思路

现在很多数据库都有自己的分析函数，通过指定的函数我们能很容易的得出想要的结果，比如CUME_DIST()函数！

 

3. 函数介绍

分析函数CUME_DIST()：–CUME_DIST 小于等于当前值的行数/分组内总行数  

语法：CUME_DIST( )   OVER ( [ partition_by_clause] order_by_clause )    

解释：通过 partition_by_clause 将划分为分区函数应用到的 FROM 子句生成的结果集。 如果未指定，则此函数将查询结果集的所有行视为单个组。 order_by_clause 确定在其中执行该操作的逻辑顺序。 order_by_clause 是必需的。  

返回类型：CUME_DIST 返回的值范围大于 0 并小于或等于 1的数值。 

4. 示例

4.1 准备数据

使用以下SQL构建表：

1. -- ----------------------------  
2. -- Table structure for ZSH_170222  
3. -- ----------------------------  
4. DROP TABLE [dbo].[ZSH_170222]  
5. GO  
6. CREATE TABLE [dbo].[ZSH_170222] (  
7. [PART] varchar(255) NULL ,  
8. [NAME_C] varchar(255) NULL ,  
9. [PAY] int NULL   
10. )  
11. GO  
12. -- ----------------------------  
13. -- Records of ZSH_170222  
14. -- ----------------------------  
15. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'小明', N'9741')  
16. GO  
17. GO  
18. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'小兰', N'6908')  
19. GO  
20. GO  
21. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'李东', N'6336')  
22. GO  
23. GO  
24. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'杨澜', N'9089')  
25. GO  
26. GO  
27. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'黄伟', N'1646')  
28. GO  
29. GO  
30. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'赵丽', N'4486')  
31. GO  
32. GO  
33. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'技术部', N'张军', N'3538')  
34. GO  
35. GO  
36. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘伟', N'2143')  
37. GO  
38. GO  
39. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'张强', N'6522')  
40. GO  
41. GO  
42. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄渤', N'1247')  
43. GO  
44. GO  
45. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'赵丽', N'7975')  
46. GO  
47. GO  
48. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘东', N'2990')  
49. GO  
50. GO  
51. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'张伟', N'4266')  
52. GO  
53. GO  
54. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄俊', N'4815')  
55. GO  
56. GO  
57. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'黄伟', N'7788')  
58. GO  
59. GO  
60. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘康', N'4605')  
61. GO  
62. GO  
63. INSERT INTO [dbo].[ZSH_170222] ([PART], [NAME_C], [PAY]) VALUES (N'综合部', N'刘冰', N'6184')  
64. GO  
65. GO  

 

 

4.2 使用分析函数进行分析

执行sql：

1. SELECT  
2.     part,  
3.     name_c,  
4.     pay,  
5.   
6.   CUME_DIST () OVER (ORDER BY pay) AS cat_1,  
7.     CUME_DIST () OVER (PARTITION BY part ORDER BY pay) AS cat_2  
8. FROM  
9.     ZSH_170222  
10. ORDER BY  
11.     part,  
12.     pay    

结果预览与分析：

 

 

Cat_1: 没有PARTITION BY ，在整个公司里分析 

所有数据均为1组，总行数为17  

第一行（黄伟）：小于等于1646的行数为2，因此，2/17= 0.117647058823529  

第二行（张军）：小于等于3538的行数为5，因此，5/17= 0.294117647058824  

第三行（赵丽）：小于等于3538的行数为7，因此，7/17= 0.411764705882353  

…  

第十七行（赵丽君）：小于等于7975的行数为15，因此，15/17= 0.882352941176471  

  

Cat_2: 按照部门（技术部/综合部）分析

技术组的行数为7,  

第一行（黄伟）：小于等于1646的行数为1，因此，1/7= 0.142857142857143  

第二行（张军）：小于等于3538的行数为2，因此，2/7= 0.285714285714286  

…  

第七行（小明）：小于等于9741的行数为7，因此，7/7= 1  

综合部的行数为10,  

第一行（黄渤）：小于等于1247的行数为1，因此，1/10= 0.1  

第二行（刘伟）：小于等于2142的行数为2，因此，2/10= 0.2  

…  

第十行（赵丽君）：小于等于7975的行数为10，因此，10/10= 1 

 

 

表达式-CASE

1. CASE介绍

计算条件列表，并返回多个可能的结果表达式之一。

CASE 表达式有两种格式：

☆ CASE 简单表达式:它通过将表达式与一组简单的表达式进行比较来确定结果。

☆ CASE 搜索表达式:它通过计算一组布尔表达式来确定结果。

这两种格式都支持可选的 ELSE 参数。

CASE 可用于允许使用有效表达式的任意语句或子句。 

例如，可以在 SELECT、UPDATE、DELETE 和 SET 等语句以及 select_list、IN、WHERE、ORDER BY 和 HAVING 等子句中使用 CASE。

2. 示例分析

2.1 准备数据

1. -- ---------------------------- -- Table structure for zsh_170225 -- ---------------------------- DROP TABLE [dbo].[zsh_170225] GO CREATE TABLE [dbo].[zsh_170225] ( [name] varchar(255) NULL , [sex] varchar(255) NULL ) GO -- ---------------------------- -- Records of zsh_170225 -- ---------------------------- INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'张三', N'1') GO GO INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'李四', N'2') GO GO INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'王五', null) GO GO INSERT INTO [dbo].[zsh_170225] ([name], [sex]) VALUES (N'赵六', N'1') GO GO   

 

Name	Sex
张三	1
李四	2
王五
赵六	1

 

2.2 演示sql_1

简单 CASE 表达式︰CASE 简单表达式的工作方式如下：将第一个表达式与每个 WHEN 子句中的表达式进行比较，以确定它们是否等效。 如果这些表达式等效，将返回 THEN 子句中的表达式。

1. Select name,sex, CASE sex WHEN '1' THEN '男' --如果sex字段值为1就sex=’男’ WHEN '2' THEN '女' --如果sex字段值为2就sex=’女’ ELSE '其他' END --否则sex=’其他’ AS sex_n FROM zsh_170225   

2.3 演示sql_2

搜索 CASE 表达式︰计算结果，按顺序指定，对比每个 WHEN 子句。

1. SELECT name, sex, CASE WHEN sex = '1' THEN '男' --如果sex字段值为1就sex=’男’ WHEN sex = '2' THEN '女' --如果sex字段值为2就sex=’女’ ELSE '其他' END --否则sex=’其他’ AS sex_n FROM zsh_170225   

这两种方式，可以实现相同的功能。

简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式。 还有一个需要注意的问题，Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 

1. --比如说，下面这段SQL，你永远无法得到“K2”这个结果 CASE WHEN col_1 IN ( 'a', 'b') THEN 'K1' WHEN col_1 IN ('a') THEN 'K2' ELSE'其他' END   

3. Case主流用法介绍:

1）使用带有 CASE 简单表达式的 SELECT 语句

在 SELECT 语句中，CASE 简单表达式只能用于等同性检查，而不进行其他比较。 下面的示例使用 CASE 表达式更改产品系列类别的显示，以使这些类别更易于理解。

1. SELECT ProductNumber, Category = CASE ProductLine WHEN 'R' THEN 'Road' -- 当ProductLine =’R’ 时, Category=’ Road’ WHEN 'M' THEN 'Mountain' -- 当ProductLine =’M’ 时, Category='Mountain' WHEN 'T' THEN 'Touring' -- 当ProductLine =’T’ 时, Category='Touring' WHEN 'S' THEN 'Other sale items' -- 当ProductLine =’S’ 时, Category=' Other sale items ' ELSE 'Not for sale' --否则Category=' Not for sale ' END, Name FROM Production.Product ORDER BY ProductNumber;    

2）使用带有 CASE 搜索表达式的 SELECT 语句

在 SELECT 语句中，CASE 搜索表达式允许根据比较值替换结果集中的值。 下面的示例根据产品的价格范围将标价显示为文本注释。

1. SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber ;     

3）在 ORDER BY 子句中使用 CASE

在 ORDER BY 子句中使用 CASE 表达式，以根据给定的列值确定行的排序顺序。 在第一个示例中，会计算 SalariedFlag 表中 HumanResources.Employee 列的值。 SalariedFlag 设置为 1 的员工将按 BusinessEntityID 以降序顺序返回。 SalariedFlag 设置为 0 的员工将按 BusinessEntityID 以升序顺序返回。 在第二个示例中，当 TerritoryName 列等于“United States”时，结果集会按 CountryRegionName 列排序，对于所有其他行则按 CountryRegionName 排序。

1. SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END; --如果SalariedFlag=1时按照BusinessEntityID字段的降序排 --如果SalariedFlag=0时按照BusinessEntityID字段的升序排   

1. SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName ELSE CountryRegionName END; -- 当CountryRegionName= 'United States'时, 按TerritoryName字段的升序排,否则就按照CountryRegionName字段的升序排   

4）在 UPDATE 语句中使用 CASE

在 UPDATE 语句中使用 CASE 表达式，以确定为 VacationHours 设置为 0 的员工的 SalariedFlag 列所设置的值。 如果 VacationHours 减去 10 小时后会得到一个负值，则 VacationHours 将增加 40 小时；否则 VacationHours 将增加 20 小时。 OUTPUT 子句用于显示前后的休假时间值。

1. UPDATE HumanResources.Employee SET VacationHours = ( CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) --如果VacationHours - 10.00) < 0时, VacationHours= VacationHours + 40否则VacationHours = VacationHours + 20.00 END ) OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0;     

5）在 HAVING 子句中使用 CASE

下面的示例在 HAVING 子句中使用 CASE 表达式，以限制由 SELECT 语句返回的行。 该语句返回为每个作业标题中的最大每小时速率 HumanResources.Employee 表。 HAVING 子句将职位限制为两类员工：一是最高每小时薪金超过 40 美元的男性员工，二是最高每小时薪金超过 42 美元的女性员工。

1. SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING (MAX(CASE WHEN Gender = 'M' THEN ph1.Rate ELSE NULL END) > 40.00 -- Gender = 'M'时, ph1.Rate字段,否则null OR MAX(CASE WHEN Gender = 'F' THEN ph1.Rate ELSE NULL END) > 42.00) ---- Gender = 'F'时, ph1.Rate字段,否则null ORDER BY MaximumRate DESC;     

开窗函数-排名

注意事项：由于设计器内置的是sqlite数据库，在使用上很多语法都不支持，请将sqlite数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

迁移方法：使用第三方软件迁移内置FRDemo数据库到指定数据库

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                     Copyright (c) Microsoft Corporation

                     Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

1. 问题描述

对于一些特殊的数据表，我们想对它的某一数据列进行排序，表中又不存在排序字段，但又不想在模板中进行排序处理，此时我们就需要在建立数据集时直接通过sql进行处理了。

2. 实现思路

在创建数据集时用sql的开窗排名函数处理，然后进行直接调用。

3. 函数介绍

下面主要解析四种常用的排序开窗函数: 

3.1、ROW_NUMBER() OVER ()  

ROW_NUMBER直接分组，前面的序号唯一且连续

3.2、RANK() OVER ()RANK()

RANK()并列排序，值相同序号并列，后面的值跳跃

3.3、DENSE_RANK() OVER ()

DENSE_RANK并列排序，值相同序号并列，后面的值连续

3.4、NTILE(n) OVER ()

不常用，NTILE(4)将所有的行分为4组，然后10/4=2余2，表示每组2行，前面的2行+1（3行）

4. 示例

4.1准备数据

1. -- ----------------------------  
2. -- Table structure for test_table  
3. -- ----------------------------  
4. DROP TABLE [dbo].[test_table]  
5. GO  
6. CREATE TABLE [dbo].[test_table] (  
7. [id] int NOT NULL IDENTITY(1,1) ,  
8. [name] nvarchar(50) NULL ,  
9. [price] nvarchar(50) NULL   
10. )  
11. GO  
12.   
13. -- ----------------------------  
14. -- Records of test_table  
15. -- ----------------------------  
16. SET IDENTITY_INSERT [dbo].[test_table] ON  
17. GO  
18. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'141', N'香蕉', N'20')  
19. GO  
20. GO  
21. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'142', N'苹果', N'25')  
22. GO  
23. GO  
24. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'143', N'梨', N'17')  
25. GO  
26. GO  
27. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'144', N'菠萝', N'24')  
28. GO  
29. GO  
30. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'145', N'西瓜', N'24')  
31. GO  
32. GO  
33. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'146', N'油桃', N'16')  
34. GO  
35. GO  
36. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'147', N'哈密瓜', N'9')  
37. GO  
38. GO  
39. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'148', N'水蜜桃', N'33')  
40. GO  
41. GO  
42. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'149', N'柚子', N'10')  
43. GO  
44. GO  
45. INSERT INTO [dbo].[test_table] ([id], [name], [price]) VALUES (N'150', N'橘子', N'8')  
46. GO  
47. GO  
48. SET IDENTITY_INSERT [dbo].[test_table] OFF  
49. GO  

4.2 排名sql

1. SELECT name, price, ROW_NUMBER () OVER (ORDER BY price DESC) AS '排名1', --ROW_NUMBER直接分组，前面的序号唯一且连续 RANK () OVER (ORDER BY price DESC) AS '排名2', --RANK()并列排序，值相同序号并列，后面的值跳跃 DENSE_RANK () OVER (ORDER BY price DESC) AS '排名3', --DENSE_RANK并列排序，值相同序号并列，后面的值连续 NTILE (4) OVER (ORDER BY price DESC) AS '排名4' --不常用，NTILE(4)将所有的行分为4组，然后10/4=2余2，表示每组2行，前面的2行+1（3行） FROM test_table;  

 

 

4.3 预览效果

执行上述sql语句，可得到如下效果：

 

 

开窗函数-逐层累计

 

分析函数-NTILE()

1. 环境

注意事项：由于设计器内置的是sqlite数据库，在使用上很多语法都不支持，请将sqlite数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

迁移方法： 使用第三方软件迁移内置FRDemo数据库到指定数据库

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                      Copyright (c) Microsoft Corporation

                      Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

2. 描述

有时我们在设计报表的过程中，可能会遇到这样的需求：将数据按照某一字段平均分组，比如一张成绩表（100人）按某一科目成绩（高低）顺序分为4组，将第一组-优，第二组-良，第三组-较差，第四组-不及格 的情况。像这样需要将一个有序整体按照一定的比例划分数据的情况。

一般思路：先将成绩按照一定顺序排名后按名次平均分为4组，1-25名为第一组，26-50为第二组，51-75为第三组，76-100为第四组，最后给对应所在组评级。这种实现思路太过于繁琐，实现起来也较难！今天分享一个快速实现的函数【NTILE函数】，使用它可以起到事半功倍的效果。

 

 

2. 思路

在数据集中用sql的【NTILE (N) OVER(ORDER BY 字段)】函数处理，然后进行直接调用。

注：NTILE(Num)，Num为要等分的组数（量）。

语句结构：

1. CASE NTILE (N) OVER (ORDER BY GRADE DESC) WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' WHEN 4 THEN 'D' . . . WHEN N THEN 'X' END  

3. 示例

3.1 准备数据

打开SQL Server库中的【STSCORE】表：

 

 

3.2 NTILE (N)实现的Sql

1. SELECT STUDENTNO AS 学号, GRADE AS 科目, CASE NTILE (4) OVER (ORDER BY GRADE DESC) WHEN 1 THEN '优' WHEN 2 THEN '良' WHEN 3 THEN '较差' WHEN 4 THEN '不及格' END AS '级别' FROM "STSCORE" WHERE COURSE = 'Chemistry' ORDER BY STUDENTNO;  

注：case when then else end 用法请参考：CASE用法。

3.3 效果预览

执行SQL脚本，效果如下：

 

 

分析函数-环比lag()

1. 环境

注意事项：由于设计器内置的是sqlite数据库，在使用上很多语法都不支持，请将sqlite数据库文件迁移到主流数据库(MySql、MSSQL、Oracle…)后运行。

迁移方法：使用第三方软件迁移内置FRDemo数据库到指定数据库

本文运行环境：Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 

                     Copyright (c) Microsoft Corporation

                     Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

2. 描述

对于一些有规律的数据统计表，有时候需要对其分组求每层上期或环比来做分析统计操作，称求【环比值】。

如图：

 

 

3. 思路

在数据集中用sql的【lag() over(PARTITION BY 分组字段 order by 逐层字段)】函数处理，然后进行直接调用。

注：LAG()统计函数可以在一次查询中取出同一字段的前N行数据的值，LAG非常高效。

LAG(a,b,c)：a是从其他行返回的表达式； b是希望检索的当前行分区的偏移量，是一个正的偏移量，一个往回检索以前的行的数目；c是在b表示的数目超出了分组的范围时返回的值。

4. 示例

3.1 准备数据

打开SQL Server库中的【大陆汽车销售】表：

 

 

3.2 上期或环比sql

1. SELECT [年份], [月份], [销量], NULLIF (lag ([销量], 1, 0) OVER ( partition BY [年份] ORDER BY [月份] ),0) AS 上月销量, round( ( [销量] - NULLIF ( lag ([销量], 1, 0) OVER ( partition BY [年份] ORDER BY [月份] ), 0 ) ) / NULLIF ( lag ([销量], 1, 0) OVER ( partition BY [年份] ORDER BY [月份] ), 0 ) * 100, 2, 3 ) AS '环比(%)' FROM 大陆车辆销售;   

公式解释：

1. NULLIF()：如果两个指定的表达式相等，则返回空值。语法NULLIF ( expression1 , expression2 ) LAG ([销量], 1, 0)：组内前一个月的的销量值。 ROUND(a,b)：通常用于四舍五入求值，通常函数中会传入两个参数，第一个参数是要四舍五入的数字，第二个参数就是位数。  

3.3 效果预览

执行SQL脚本，效果如下：