集算报表V5.0高级教程[1]

集算报表V5.0 高级教程

第1章前言

1.1 使用对象

《集算报表V5.0高级教程》适用于已经初步掌握了报表设计的入门知识，希望进一步学习集算报表各种高级设计功能的报表设计者。

1.2 文档目的

本文档是为了用户学习集算报表的各种高级设计功能而编写的。

通过学习本文档，用户可以深入地掌握和理解非线性报表模型，掌握动态参数和动态宏的应用及两者的区别，可以理解和掌握报表组的业务背景和设计方法，可以更加充分地理解各种高级统计图的业务背景和使用方法。

目录

第1章前言 (2)

1.1使用对象 (2)

1.2文档目的 (2)

第2章多源关联分片 (5)

2.1多层交叉报表 (5)

2.1.1一个例子 (5)

2.1.2扩展模型 (6)

2.1.3主格模型 (6)

2.2纵向分片 (9)

2.3横向分片 (11)

2.4扩展区域与静态区域混合 (12)

2.5行列对称 (14)

2.6多片扩展 (16)

2.6.1一个例子 (16)

2.6.2主格认定规则 (18)

2.7多源关联分片的概念与特征总结 (19)

第3章不规则分组 (20)

3.1分组报表 (20)

3.1.1一个例子 (20)

3.1.2group() (21)

3.2不完全分组 (23)

3.3按段分组 (24)

3.3.1一个例子 (24)

3.3.2plot (25)

3.4不规则分组概念与特征总结 (26)

第4章动态格间运算 (27)

4.1占比报表 (27)

4.2累积报表 (29)

4.2.1一个例子 (29)

4.2.2位移坐标的简单写法 (30)

4.2.3临时变量的做法 (31)

4.3比值报表 (31)

4.3.1一个例子 (31)

4.3.2绝对层次坐标 (32)

4.4同期比 (35)

4.4.1一个例子 (35)

4.4.2位移坐标 (36)

4.4.3坐标缺省表示法 (38)

4.5分组汇总 (39)

4.5.1例子 (39)

4.5.2格集 (40)

4.6条件汇总 (43)

4.6.1例子 (43)

4.6.2格集的条件表达式 (44)

4.7复杂同期比 (44)

4.7.1例子 (44)

4.7.2$运算符 (46)

4.8排名 (47)

4.8.1一个例子 (47)

4.8.2根坐标表示法 (50)

4.9独立格运算 (51)

4.10序号 (52)

4.10.1组内序号例子 (52)

4.10.2&运算符 (54)

4.10.3组间序号例子 (54)

4.10.4seq() (55)

4.11动态格间运算的概念与特征总结 (55)

第2章多源关联分片

2.1 多层交叉报表

2.1.1一个例子

我们先看下面这个多层交叉报表：

从上面这个报表可以看出，这是一个多层的交叉报表，上边有两层动态横向扩展的上表头，左边有两层动态纵向扩展的左表头，中间的交叉点进行汇总统计，统计值和上表头、左表头息息相关。我们看一下这样的报表在集算报表中是如何设计的：

1、首先定义数据集：

ds1：SELECT 客户.地区,客户.城市,订单明细.数量,订单明细.折扣,订单明细.单价,订单.

雇员ID,订单.订购日期,订单明细.产品ID FROM 订单明细,订单,客户WHERE 客户.客户ID = 订单.客户ID AND 订单.订单ID = 订单明细.订单ID and 订单.订购日期is not null and 客户.地区in ('华南','西南') and year(订单.订购日期)>=1998

2、写入单元格表达式，如下图所示：

（1）在A3单元格录入表达式：=ds1.group(year(订购日期);year(订购日期):1)+"

年"

（2）在C3单元格录入表达式：=ds1.group(month(订购日期);month(订购日期):1)+"月"

（3）在D1单元格录入表达式：=ds1.group(地区;地区:1)，扩展方向：横向

（4）在D2单元格录入表达式：=ds1.group(城市;城市:1)，扩展方向：横向

（5）在D3单元格录入表达式：=ds1.sum(数量*单价)

从上图可以看出，上表头分别通过D1格和D2格横向扩展形成，左表头通过A3格和

C3格纵向扩展形成，中间的交叉点利用sum函数进行汇总，于是一个多层交叉报表很轻松就完成了。

这个例子用到了非线性报表模型中的扩展模型以及主格和附属格的模型，介绍如下：2.1.2扩展模型

扩展单元格：

当单元格的数据值表达式为集合表达式时，该单元格默认为可扩展单元格。可以为扩展单元格设置一个扩展方向，扩展可以有横纵两个方向，即横向扩展和纵向扩展，但一个扩展格同时只能有一个扩展方向。

横向扩展：

当可扩展单元格的扩展方向为横向时，该单元格进行的扩展称为横向扩展。此时该单元格会横向进行复制，复制出的单元格的数据值依次为表达式的结果数据值，表达式返回几个值，单元格就复制几个。

复制出来的新单元格的所有属性都引用被复制单元格的属性；

纵向扩展：

当可扩展单元格的扩展方向为纵向时，该单元格进行的扩展称为纵向扩展。此时该单元格会纵向进行复制，复制出的单元格的数据值依次为表达式的结果数据值，表达式返回几个值，单元格就复制几个。

复制出来的新单元格的所有属性都引用被复制单元格的属性；

不可扩展：

当单元格的数据值表达式为单值表达式时，该单元格默认为不可扩展单元格。

2.1.3主格模型

主格和附属格：

单元格进行扩展的过程中，缺省情况下，相对于其右（下）边的单元格而言，扩展格是主动复制的，被称为其它格（其右/下的格）的主格，而其右（下）的单元格是被动跟随复制的，被称为扩展格的附属格或子格。主格和附属格是相对的概念，即某格是另一格的主格

或附属格，不存在单独的主格和附属格。

从主格的定义中看，显然只有扩展格才能是其它格的主格！

要注意的是，在缺省情况下，扩展格左（上）的格并不是其附属格，只有右（下）的格才是其附属格。

特别地，某个单元格的主格属性可以人为被改变。

左主格和上主格：

单元格进行纵向扩展时，我们又称其为其它格（其右/下的格）的左主格，而横向扩展时则称为上主格。一个单元格可以既有左主格又有上主格。

特别地，某个单元格的左主格或者上主格属性可以人为的被改变，即可以人为的把某个纵向扩展格右边的单元格设为他的左主格，或者把某个横向扩展格下方的单元格设为他的上主格。

多层扩展：

多个单元格同时进行扩展时，一个扩展格可以既是某些格的主格又是另一个扩展格的附属格，这种情况下就形成多层扩展的情况。如果主格A1的附属格是B1，而B1还有附属格C1，那么B1称为A1的1级子格（或1级附属格），C1称为A1的2级子格（或2级附属格），同时A1称作B1的1级主格，A1称作C1的2级主格。如果C1再有附属格D1，那么A1是D1的3级主格，而D1是A1的3级子格。显然，B1是D1的2级主格，D1是B1的2级子格。

同一行（列）上有两个或更多的扩展格。左（上）边的扩展格将是右（下）边的扩展格的主格，右（下）边的扩展格是其左（上）边扩展格的附属格，同时又是其右（下）边单元格的主格。

图示：

直接主格和直接附属格：

单元格进行多层扩展时，若主格A的附属格B不再有任何同方向的主格是该主格A的附属格，则称B为A在该方向上的直接附属格，也称为一级附属格；反之，A称为B的直接主格，也称为一级主格

扩展变化规则：

多层扩展时，扩展次序是从主到次的，即先扩展主格，然后扩展其附属格，再扩展其二级附属格，依此类推。

单元格进行横向扩展时，会将其同列的上主格拉大，把其附属单元格复制，特别地，

如果其某个上主格不在同列上，则该上主格不会被拉大；

单元格进行纵向扩展时，会将其同行的左主格拉大，把其附属单元格复制，特别地，如果其某个左主格不在同行上，则该左主格不会被拉大。

一般地，主格能够主动进行扩展复制，称为主动扩展格；附属单元格被主单元格带动着复制，称为被动复制格。由于附属单元格同时又可能是别的单元格的主格，本身还可以进行主动扩展复制，因此主动扩展格和被动复制格是相对的。既不能主动扩展复制，也不能被动复制的单元格，我们称为不可复制格，或者叫固定格。

同一报表中可能同时有纵向扩展格和横向扩展格，如果它们的子格有重叠部分，则这些子格就即有左主格又有上主格，在扩展时会被即向下又向右复制，形成一片矩形单元格区域，从而做到交叉扩展。相应地，在这种机制下，多层交叉也不难实现。

在交叉扩展中，有的单元格有可能既被横向扩展向右复制，也被纵向扩展向下复制，可是，单元格的横向扩展与纵向扩展这两种扩展是相互独立的，既可以先进行横向扩展，也可以先进性纵向扩展，并不会影响扩展之后的结果。

例3.3.2-2：

横向扩展后，上主格A1被拉大，

附属格A2被复制

例3.3.2-3：

例3.3.2-4：

例3.3.2-5：

2.2 纵向分片

现在，我们把2.1.1中的报表做一些改变，首先，我们在原有数据集的基础上增加如下数据集的定义：

ds3：SELECT 雇员.雇员ID,订单明细.产品ID,雇员.职务,雇员.姓氏||雇员.名字as 姓名FROM 雇员,订单,订单明细WHERE 雇员.雇员ID = 订单.雇员ID AND 订单.订单ID = 订单明细.订单ID ORDER BY 订单.订购日期ASC

然后在报表下方追加一行，增加一个纵向扩展格，如下图所示：

该报表的预览效果如下图所示：

从上图可以看出，该报表出现了两片纵向扩展区域，这两片纵向扩展区域看起来毫无关系，但是格线严格对齐。下面我们对这个报表继续做改变，在新增加的行中写入计算表达式，如下图所示：

（1）在B4单元格输入表达式：= ds3.group(职务;职务:1)

（2）在C4单元格输入表达式：= ds3.group(雇员ID;雇员ID:1)

设置显示值为：ds3.姓名

（3）在D4单元格输入表达式：=ds1.sum(数量*单价,雇员ID==C4)

设置显示格式为：￥#0.00

D4格中的表达式为：=ds1.sum(数量*单价,雇员ID==C4)。其含义是：雇员ID为C4单元格的雇员在D2城市的销售额；可以看出，这个表达式既和上表头D2格有关联，又和左表头C4格有关联，我们再看一下预览效果：

从上图可以看出，上下两片本来没有关系的扩展区域，通过D4格的表达式和上表头D2格关联起来，变成了上下两片紧密关联的扩展区域。

2.3 横向分片

现在，我们把2.2中的报表做些改变，首先，我们在原有数据集的基础上增加如下数据集的定义：

ds2：SELECT 类别.类别ID,类别.类别名称FROM 类别

ds4：SELECT 产品.类别ID,产品.产品ID FROM 产品

然后在报表右侧追加一列，增加一个横向扩展格，如下图所示：

该报表的预览效果如下图所示：

从上图可以看出，该报表出现了两片横向扩展区域，这两片横向扩展区域看起来毫无关系，但是格线严格对齐。下面我们对这个报表继续做改变，在新增加的行中写入计算表达式，如下图所示：

（1）在E2单元格输入表达式：=ds4.group(类别ID,类别ID!=null;类别ID:1) 设置显示值为：ds2.select (类别名称,类别ID==value(),1)

扩展方向：横向扩展

（2）在E3单元格输入表达式：=ds1.sum(数量*单价,产品ID in ds4.select(产品ID)) 设置显示格式为：￥#0.00

（3）在E4单元格输入表达式：=ds1.sum(数量*单价,产品ID in ds4.select(产品ID) and 雇员ID==C4)

设置显示格式为：￥#0.00

E3格中的表达式为：=ds1.sum(数量*单价,产品ID in ds4.select(产品ID))。其含义是：年月为C3时产品ID为E2的产品销售额之和；可以看出，这个表达式既和上表头E2格有关联，又和左表头C3格有关联。

E4格中的表达式为：=ds1.sum(数量*单价,产品ID in ds4.select(产品ID) and 雇员ID==C4)。其含义是：C4单元格的销售员对产品ID为E2的产品销售额之和；可以看出，这个表达式既和上表头E2格有关联，又和左表头C4格有关联。

我们再看一下预览效果：

从上图可以看出，上下两片本来没有关系的扩展区域，通过E3、E4格的表达式和左表头C3、C4分别格关联起来，变成了左右两片紧密关联的扩展区域。

2.4 扩展区域与静态区域混合

现在，我们把2.3中的报表做一些改变，在报表中分别增加两行两列，如图所示：

该报表的预览效果如下图所示：

接下来我们在新加的行列中增加一些表达式，对不同扩展区域的数据分别进行统计，如图所示：

（1）在E3单元格输入表达式：=sum(D3{D3>10000})

设置显示格式为：￥#0.00

（2）在E5单元格输入表达式：=sum(D5{D5>10000})

设置显示格式为：￥#0.00

（3）在D4单元格输入表达式：=sum(D3{D3>10000})

设置显示格式为：￥#0.00

（4）在D6单元格输入表达式：=sum(D5{})

设置显示格式为：￥#0.00

（5）在G3单元格输入表达式：=sum(F3{})

设置显示格式为：￥#0.00

（6）在G5单元格输入表达式：=sum(F5{})

设置显示格式为：￥#0.00

（7）在F4单元格输入表达式：=sum(F3{F3>10000})

设置显示格式为：￥#0.00

（8）在F6单元格输入表达式：=sum(F5{})

设置显示格式为：￥#0.00

E3格中的表达式为：=sum(D3{D3>10000})。其含义是：订购日期为C3的且销售额大于1000的之和；

D3格中的表达式为：=sum(F3{})。其含义是：订购日期为C3的销售额之和。

从上图可以看出，整个报表横向纵向被分成了好几片单元格，有的是扩展的，有的是固定行的，固定区域和动态扩展区域混合在一起，既互相独立又有一定的数据联系，这种报表就是最典型的多源分片报表。

2.5 行列对称

从前面的几个例子可以看出，集算报表横向扩展横向分片的能力和纵向是完全对称的，报表中，经常出现类似的需求。

行列对称的能力不仅仅体现在扩展和分片的能力上，还体现在左表头固定，表体横向滚动以及横向分页时左表头每页重复的能力上。

就拿2.4中的报表例子来说，该报表横向已经很宽了，浏览的时候很可能屏幕不够大，需要固定左表头，横向滚动着浏览才方便；打印的时候，一张A4纸也肯定容不下这么宽的报表，只能横向分页，分页的时候左表头很可能需要重复，此时如何实现？

下面看一下集算报表中的做法：

1、将A、B、C列设为：“左表头”，将第1行和第2行设为：“报表头”。

2、发布报表时在tag标签中将needScroll属性设为：“yes”

3、预览报表：

通过报表右侧和下方的滚动条可以拖动数据，而报表头固定。

4、发布报表

5、打印

打印时报表表头固定，可以在每页中重复打印，方便用户浏览数据

2.6 多片扩展

2.6.1一个例子

我们看下面这个报表：

这是一个很常见的主子报表，主表和子表往往存储在不同的物理表中，而且通常是一对多的关系。在一般的报表工具中，这种报表往往利用专门的子表控件来实现，虽然功能实现了，但是存在的缺点是：主子表之间不容易共享数据，不容易进行表间数据的运算。集算报表利

用其多源关联分片模型，很轻松地在单个报表中实现了主子表的功能。下面我们介绍一下该

报表的制作方法：

1、首先定义数据集：

ds1：SELECT DISTINCT 订单.订单ID,订单.客户ID,订单.发货日期,订单.到货日期,订单.货主名称,订单.运货商,订单.运货费,订单.货主城市FROM 订单

ds2：SELECT 订单明细.产品ID,订单明细.单价,订单明细.折扣,订单明细.数量,订单明细.订单ID FROM 订单明细

2、定义单元格的表达式

（1）在B2单元格输入表达式：= ds1.select(订单ID:1,,,订单ID)

（2）在D2单元格输入表达式：= ds1.发货日期

设置显示格式为：yyyy年MM月dd日

（3）在F2单元格输入表达式：= ds1.到货日期

设置显示格式为：yyyy年MM月dd日

（4）在B4单元格输入表达式：= ds1.客户ID

（5）在F4单元格输入表达式：= ds1.货主名称

（6）在B5单元格输入表达式：= ds1.运货商

（7）在D5单元格输入表达式：= ds1.运货费

设置显示格式为：￥#0.00

（8）在F5单元格输入表达式：= ds1.货主城市

（9）在B8单元格输入表达式：= ds2.select(产品ID:1,订单ID==B2,,产品ID)

（10）在C8单元格输入表达式：= ds2.单价

设置显示格式为：￥#0.00

（11）在D8单元格输入表达式：= ds2.折扣

设置显示格式为：#0.0

（12）在E8单元格输入表达式：= ds2.数量

（13）在F8单元格输入表达式：=C8*E8

设置显示格式为：￥#0.00

3、设置左主格属性

将A1、A2、A3、A4、A5、A6、A7、A8、A9单元格的左主格设为B2单元格。

4、预览报表

这个例子中，我们发现，左主格是人为指定的，并不是缺省的，这用到了集算主格模型中的主格认定规则，主格的认定包括缺省认定和人为认定，下面我们对该理论进行介绍：

2.6.2主格认定规则

缺省主格认定

单元格横向扩展时，上方横向扩展单元格缺省为它的上主格，下方单元格缺省为它的附属格；如果上方没有横向扩展格，则上主格缺省为`0 格

单元格纵向扩展时，左边纵向扩展单元格缺省为它的左主格，右边单元格缺省为它的附属格；如果左边没有纵向扩展格，则左主格缺省为`0 格

人为改变主格规则

除了上面提到的缺省情况外，我们允许人为地改变单元格的主格。可以将某个单元格的左主格设置成某个纵向扩展格、上主格设置的某个横向扩展格，左主格和上主格是分别设置的。

为了符合扩展变化的规则，我们可以知道人为设置主格需要满足一些条件：

左主格必须是纵向扩展格，上主格必须是横向扩展格，否则设置无效。

不允许出现循环设置的情况，即设置A的主格是B，B的主格是C，C的主格又是A，出现循环设置时认为设置有误，报表无法计算。显然，在缺省的情况下是不可能出现循环设置的，而在人为设置时必须避免这种情况的出现。

横向扩展格不允许有左主格，纵向扩展格不允许有上主格。

人为设置时，可能发生左（上）主格在右（下）边的情况，而且主格也不一定和附属格在同一行（列）上。

例3.3.2-1：

2.7 多源关联分片的概念与特征总结

从前面的报表例子，我们可以总结出多源关联分片的概念：

多源是指一个报表的数据来源来自多个物理数据表（或类似数据体），甚至是多个物理数据库。这里的“多个”常常不是两个三个，而是七八个乃至十几个。

多源往往带来分片，正是由于分片，使得报表设计必须直接基于多源进行，而不能先将多源转成单源进行。有相当一部分分片报表无论如何也不可能转化成单源处理，部分能转成单源的报表处理也非常繁琐。

分片是指报表的纵向或横向或双向同时被分成了多个区域，每个区域独立扩展或者其中一个扩展区域为另一个扩展区域的子扩展区域，也可能是扩展区域和静态区域的混合。

关联是指不同的扩展区域或者扩展与静态区域之间，数据存在着关联，不同区域之间的数据还可能进行运算。

第3章不规则分组

3.1 分组报表

3.1.1一个例子

我们先看下面这个分组报表：

从上面的这个报表可以看出，这是一个分组报表，左边按照地区维度进行了完全的分组，整个报表被分成东北、华北、华东、华南、西北、西南等6组，我们看一下这样的报表在集算报表中是如何设计的：

1、首先定义数据集：

ds1：SELECT 客户.城市,客户.地区,订单明细.数量,订单明细.单价FROM 客户,订单,订单明细WHERE 客户.客户ID = 订单.客户ID AND 订单.订单ID=订单明细.订单ID

2、写入单元格表达式，如下图所示：

从上图可以看出，该报表最关键的是A2格和B2格，利用group函数对数据集进行了分组，并把分组结果在报表中进行纵向扩展。