我們知道代箭，報表呈現(xiàn)的數(shù)據(jù)常常并不是直接從數(shù)據(jù)庫（源）取出來的數(shù)據(jù)，而還要進(jìn)行一些運算涕刚，報表工具通常也會提供一定的運算能力（如過濾嗡综、分組等）以應(yīng)對這種需求。但是杜漠，情況復(fù)雜時极景，報表數(shù)據(jù)集上的運算可能要多個步驟才能完成察净，這時候就要考查報表工具對過程式計算的支持程度了。
統(tǒng)計要求
列出指定時段的大客戶盼樟。所謂大客戶氢卡，定義為銷售額占前一半的客戶，也就是把客戶銷售額從大到小排序后晨缴，前面若干個客戶的合計銷售額構(gòu)成總銷售的一半译秦，這些客戶被稱為大客戶。
報表式樣
數(shù)據(jù)結(jié)構(gòu)
[銷售記錄表]
特點分析：
這個報表式樣很簡單击碗，主要麻煩之處在于需要從原始數(shù)據(jù)集計算出大客戶后再呈現(xiàn)筑悴，而這個計算并不是簡單一步能完成的。
我們的重點是考查報表工具的過程計算能力稍途，所以假定不使用 SQL 或數(shù)據(jù)源的計算能力來完成阁吝。
制作過程:
1、 配置并連接數(shù)據(jù)源晰房。
2求摇、 設(shè)置數(shù)據(jù)集
潤乾報表提供了一個獨立的計算引擎，可以通過內(nèi)置的腳本實現(xiàn)對數(shù)據(jù)的計算并將結(jié)果返回給報表數(shù)據(jù)集殊者，在報表中新增數(shù)據(jù)集与境，數(shù)據(jù)集類型使用腳本數(shù)據(jù)集，腳本如下：
2.1 A1 格：單元格做了數(shù)據(jù)源的連接猖吴，然后 A6 單元格在執(zhí)行完畢后關(guān)閉連接摔刁。
2.2 A2：從客戶銷售表中取數(shù)，此處根據(jù)客戶名稱做了匯總海蔽，并且按照銷售額降序排序共屈。
2.3 A3：對銷售額進(jìn)行求和操作并處以 2，取出總金額的一半党窜，用于判斷大客戶拗引。B3 設(shè)置初始值為 0，用于做銷售額累加操作
2.4 A4：對銷售額進(jìn)行累加幌衣，取出累加金額大于 A3 中對應(yīng)的 A2 的序號
2.5 A5：根據(jù)序號取 A2 中對應(yīng)的值矾削，并做為結(jié)果集返回給報表
3、 設(shè)計報表模板
腳本數(shù)據(jù)集中返回的數(shù)據(jù)就是大客戶的數(shù)據(jù)信息豁护，所以制作報表就相當(dāng)簡單, 設(shè)置如下：：
3.1哼凯、 A2、B2 單元格直接取數(shù)據(jù)集中的數(shù)據(jù)
3.2楚里、 B3断部、B4 單元格直接用數(shù)據(jù)集匯總函數(shù)，對數(shù)據(jù)集計數(shù)取出大客戶數(shù)量班缎，對銷售額用 avg 函數(shù)取出平均銷售額蝴光。
3.3她渴、 設(shè)置邊框、金額顯示格式虱疏。
運行結(jié)果：
完成后點評
1惹骂、 0.5 小時，使用腳本數(shù)據(jù)集中內(nèi)置的語法做瞪，幾個單元格的處理就能快速實現(xiàn)需求对粪。
2、 可以使用腳本數(shù)據(jù)集装蓬，內(nèi)置豐富語法規(guī)則著拭，能快速處理各種復(fù)雜的數(shù)據(jù)統(tǒng)計要求。
3牍帚、 報表設(shè)計簡單儡遮，并且不需要輔助單元格，報表計算效率高暗赶。
制作過程:
1鄙币、 配置并連接數(shù)據(jù)源。
2蹂随、 設(shè)置數(shù)據(jù)集
增加數(shù)據(jù)庫查詢十嘿，SQL 為： SELECT 客戶,sum(銷售額) 銷售額 FROM DEMO. 客戶銷售表 group by 客戶 order by 銷售額 desc
3、 設(shè)計報表模板
3.1 B2 單元格取總銷售額的一半岳锁，用于判斷大客戶绩衷，單元格內(nèi)對銷售額求和，并設(shè)置自定義顯示公式 $$$/2激率，帆軟對字段匯總后如果再進(jìn)行計算咳燕，無法直接在單元格里手動加表達(dá)式，只能設(shè)置自定義顯示公式乒躺。
3.2 A3招盲、B3 單元格取數(shù)數(shù)據(jù)集中的客戶和銷售額字段。
3.3 C3: 求累計金額嘉冒，=B3+C3[A3:-1]宪肖，增加條件屬性，對非大客戶的數(shù)據(jù)進(jìn)行隱藏健爬。
3.4 B4: 大客戶數(shù)量，=count(B3[!0;!0]{C3 <= B2 || (C3[A3:-1] < B2 && C3 >= B2)})么介，用 count 函數(shù)統(tǒng)計累計銷售大于總銷售額一般的數(shù)量娜遵。
3.5 平均銷售額，=sum(B3[!0;!0]{C3 <= B2 || (C3[A3:-1] < B2 && C3 >= B2)}) / B4壤短，設(shè)置單元格的格式保留兩位小數(shù)设拟。
3.6 將不需要展現(xiàn)的行列設(shè)置隱藏慨仿。
報表結(jié)果
完成后點評
1、 用時 1 小時纳胧。
2镰吆、 累計實現(xiàn)可以通過內(nèi)置的層次函數(shù) LAYERTOTAL(B1, C1, D1) 實現(xiàn)，也可以通過層次坐標(biāo), 本例使用的是層次做表方式跑慕，內(nèi)置功能比較豐富万皿。
3、 可以根據(jù)條件公式動態(tài)設(shè)置報表行列的動態(tài)隱藏核行。
4牢硅、 需要增加輔助單元格，會增加額外資源的耗用芝雪，尤其是客戶數(shù)量多時减余，報表中是取出了所有客戶信息（小客戶數(shù)量常常多得多），然后再判斷隱藏惩系，數(shù)據(jù)量大時報表計算性能會有影響位岔。
制作過程
1、 配置并連接數(shù)據(jù)源堡牡。
2抒抬、 設(shè)置數(shù)據(jù)集
采用原生 SQL 數(shù)據(jù)集，直接通過 SQL 語句取數(shù)就行：
select 客戶,sum(銷售額) 金額 from 客戶銷售表 group by 客戶 order by 金額 desc悴侵。
3瞧剖、 設(shè)計報表模板
need-to-insert-img
報表的設(shè)計在 excel 內(nèi)完成，借助豐富的 excel 函數(shù)可免，smartbi 解決這種格間運算也不是太麻煩抓于。如幾個關(guān)鍵計算：
3.1 D1：所有客戶的銷售總金額，D2 中寫入表達(dá)式 =D1/2, 取總銷售額的一半浇借。
3.2 D3 格：累積金額捉撮，=SSR_GetCell(D3,B3,-1)+C3。
3.3 E3：=IF(SSR_GetSubCells(D3,B3)>D2,1+SSR_GetCell(E3,B3,-1),0)妇垢，根據(jù)累積金額巾遭，標(biāo)識當(dāng)出現(xiàn)累積金額大于總金額一半的時候 標(biāo)識為 1，后面累計值依然大于總金額一半闯估，數(shù)值累計為 2/3/4（用于隱藏后面不要的數(shù)據(jù)）等
3.4 C4 格：=MATCH(1,SSR_GetSubCells(E3,A3),0)灼舍，根據(jù) E3 的標(biāo)識，查找第一個出現(xiàn)的 1 所在的序號涨薪，該序號即標(biāo)識大客戶數(shù)骑素。
3.5 C5 格：平均銷售額相對簡單，=D4/C4刚夺，并設(shè)置報表中數(shù)據(jù)顯示格式
3.6 隱藏行設(shè)置献丑，需要隱藏掉非大客戶數(shù)據(jù)末捣，不支持表格內(nèi)定義隱藏表達(dá)式，這里根據(jù) E3 值大于 1 的就隱藏创橄，要借助平臺給電子表格設(shè)置宏（需要寫 js 代碼）實現(xiàn)：
運行結(jié)果
完成后點評：
1箩做、 用時：1.5 小時。
2妥畏、 完全在 excel 中操作邦邦，容易上手，操作起來比較方便咖熟。excel 函數(shù)豐富圃酵，這個報表主要是其查找類函數(shù)的使用。
3馍管、 隱藏不需要（除大客戶外）的客戶數(shù)據(jù)時郭赐，不支持表格內(nèi)定義隱藏表達(dá)式，需要 js 代碼才能實現(xiàn)确沸，難度較大捌锭。
4、 需要增加輔助單元格罗捎，會增加額外資源的耗用观谦，尤其是客戶數(shù)量多時，報表中是取出了所有客戶信息（小客戶數(shù)量常常多得多）桨菜，然后再判斷隱藏豁状，數(shù)據(jù)量大時報表計算性能會有影響。
制作過程：
1倒得、 配置并連接數(shù)據(jù)源泻红。
2、 設(shè)置數(shù)據(jù)集
使用 SQL 語句數(shù)據(jù)集：select 客戶,sum(銷售額) 金額 from 客戶銷售表 group by 客戶 order by 金額 desc
3霞掺、 設(shè)計報表模板
need-to-insert-img
3.1 第一列增加了個按銷售額取數(shù)谊路，實際中發(fā)現(xiàn)數(shù)據(jù)集中雖然按照銷售額排序了，但是直接取出客戶名稱字段時菩彬，即使不排序缠劝，也不會按照數(shù)據(jù)集原有數(shù)據(jù)排序，所以前邊增加了個銷售額字段骗灶，并且設(shè)置降序排序惨恭。
3.2 第四列中取出了總銷售額以及對應(yīng)的累計銷售額，累計銷售額直接拖拽耙旦，設(shè)置下格間計算未累計就行
3.3 大客戶數(shù)量單元格使用格間計算脱羡，里邊表達(dá)式為：
var a=0;
for(var i=1;i<=ridx-1;i++){
if(cell(i,3)>=cell(0,3)/2){
a=i;
break;
}
}
通過 js 語法，對行列循環(huán)，判斷累計值是否大于銷售額總計的一半轻黑，返回對應(yīng)行數(shù)也就是數(shù)量。
3.4 大客戶平均銷售額同樣的做法：
var a=0;
for(var i=1;i<=ridx-2;i++){
if(cell(i,3)>=cell(0,3)/2){
a=cell(i,3)/i;
break;
}
}
3.5 第一列和第四列是輔助列琴昆，右側(cè)有個隱藏列設(shè)置氓鄙，將這兩列隱藏掉
3.6 隱藏行設(shè)置，永洪不支持單元格隱藏行表達(dá)式业舍，如果要隱藏掉非大客戶數(shù)據(jù)抖拦，需要通過 js 語句實現(xiàn)，可以在 js 中獲取到報表計算后的行屬性舷暮，根據(jù) 3.3 中獲取的大客戶數(shù)量動態(tài)設(shè)置非大客戶（報表計算后行號超過大客戶數(shù)量的行）行高為 0态罪，來達(dá)到隱藏的效果，這個和 Smartbi 有些類似下面，這里就不具體實現(xiàn)了复颈。
運行結(jié)果
完成后點評
1、 用時 2 小時沥割，格間計算那幾個單元格處理時間較長耗啦，比較考驗開發(fā)能力。
2机杜、 格間計算中可以用 js 語法進(jìn)行數(shù)據(jù)的計算帜讲，適合開發(fā)人員操作，比較靈活, 但同時內(nèi)置函數(shù)少椒拗，比較考驗開發(fā)人員能力似将。
3、 累計設(shè)置比較方便蚀苛，直接用內(nèi)置的語法就行在验。
4、 排序設(shè)置有問題枉阵，數(shù)據(jù)集中設(shè)置了數(shù)據(jù)的排序译红，但是報表里默認(rèn)不會按照相應(yīng)的順序排序（也可能沒找到方法）。
5兴溜、 需要增加輔助列來實現(xiàn)侦厚，比如累計列，如果數(shù)據(jù)量大會額外占用內(nèi)存空間拙徽。
6刨沦、 可以設(shè)置隱藏列，但是設(shè)置后設(shè)計界面也就無法看到該列膘怕，無法還原想诅，而且如果數(shù)據(jù)集變動而隱藏列里引用了變動前的字段，很難更改。
7来破、 需要增加輔助單元格篮灼，會增加額外資源的耗用，尤其是客戶數(shù)量多時徘禁，報表中是取出了所有客戶信息（小客戶數(shù)量常常多得多）诅诱，然后再判斷隱藏，數(shù)據(jù)量大時報表計算性能會有影響送朱。并且隱藏掉非大客戶信息時娘荡，需要寫復(fù)雜的 js，難度較大驶沼。
制作過程
1炮沐、 配置并鏈接數(shù)據(jù)源
2、 設(shè)置數(shù)據(jù)集
直接通過 SQL 語句取數(shù)就行：SQL 語句為：select customer,sum(sales) as sales from sales group by customer order by sum(sales) desc
3回怜、 設(shè)計報表模板
3.1 A1：=sum(TEST.sales)/2 計算所有客戶的銷售總金額的一半大年，并在右側(cè)屬性里設(shè)置隱藏屬性
3.2 A3：=TEST.customer 浮動維字段，同時在右側(cè)設(shè)置排序的依據(jù)是 B3鹉戚。
3.3 C3：=self.leftcell.value+self.upcell.value 實現(xiàn)金額累計計算
3.4 D3：=if(GRID1.C3.upcell.value<GRID1.A1,1,null) 將累計金額大于 A1 單元格值的數(shù)據(jù)標(biāo)識為 1鲜戒，不符合條件的標(biāo)識為 null，方便后面統(tǒng)計數(shù)量抹凳。
3.5 B4：=sum(GRID1.D3$)遏餐，根據(jù) D3 的標(biāo)識統(tǒng)計出來大客戶數(shù)量（D3 中大客戶返回 1，對 1 求和就是大客戶數(shù)量）
3.6 B5：=GRID1.D3$.select(@.value=1).select(true,@.leftcell(2).value).avg() 先使用數(shù)組的方式過濾出來對應(yīng)的數(shù)據(jù)赢底，然后再進(jìn)行求平均的計算
3.7 選中第 3 行設(shè)置顯示表達(dá)式為：<#=if(GRID1.D3=1,1,0)#>失都，D3 單元格標(biāo)識出了本條數(shù)據(jù)是否是大客戶，這里根據(jù) D3 單元格值控制本行是否顯示（如果為 1 則返回 1 表示顯示幸冻，否則返回 0 不顯示）
運行結(jié)果
need-to-insert-img
完成后點評：
1粹庞、 制作用時：1.5 小時
2、 內(nèi)置函數(shù)比較豐富洽损，本表中常用操作基本上都是通過內(nèi)置函數(shù)實現(xiàn)庞溜。
3、 可以根據(jù)條件公式動態(tài)設(shè)置報表行列的動態(tài)隱藏碑定，比較方便流码。
4、 需要增加輔助單元格延刘，會增加額外資源的耗用漫试，尤其是客戶數(shù)量多時，報表中是取出了所有客戶信息（小客戶數(shù)量常常多得多）碘赖，然后再判斷隱藏驾荣，數(shù)據(jù)量大時報表計算性能會有影響外构。
這個例子報表結(jié)果格式相對比較簡單，各個報表工具基本上都能實現(xiàn)播掷。
對于本例中考查的過程計算审编，帆軟、Smartbi歧匈、永洪割笙、億信這四款工具實現(xiàn)方法基本一致，都是通過輔助行列取出銷售額總計眯亦、累計銷售額，然后進(jìn)行數(shù)據(jù)判斷來確定哪些客戶是大客戶并對數(shù)據(jù)統(tǒng)計般码，最后將輔助行列再隱藏掉妻率。整個過程有一定的難度和繁瑣度，這是因為報表只能執(zhí)行狀態(tài)式計算板祝，用來處理過程計算就只能采取這種迂回的辦法宫静。
這幾款產(chǎn)品之間來看，帆軟券时、Smartbi孤里、億信基本上一致，都是使用內(nèi)置函數(shù)或者方法實現(xiàn)橘洞，隱藏行列也比較方便捌袜。而永洪累計實現(xiàn)方便，直接通過鼠標(biāo)設(shè)置就行炸枣，但是做數(shù)據(jù)統(tǒng)計時就要寫復(fù)雜的 javascript 語法來統(tǒng)計虏等，這塊實現(xiàn)難度比較大，而且隱藏行列設(shè)置也不太方便适肠。永洪的復(fù)雜報表功能相比其它三款要更弱一點霍衫，帆軟在這四款中相對更好，這和之前測試其它案例的結(jié)論也一致侯养。
潤乾事實上也可以采用隱藏格手段實現(xiàn)敦跌，但我們在這里則提供了一種與眾不同的方法。潤乾增加了一個計算層逛揩，使用腳本數(shù)據(jù)集柠傍，可以更方便地實現(xiàn)過程式計算，返回給報表就是處理后的數(shù)據(jù)結(jié)果息尺，報表中不再做特別處理携兵，整體過程更為簡單，而且消耗資源也比使用輔助格要少得多搂誉，性能會更優(yōu)徐紧。仍然是和之前測試案例的結(jié)論一致，潤乾是這幾款產(chǎn)品中擁有最強(qiáng)計算能力的產(chǎn)品，計算層明顯拉開了和其它產(chǎn)品的差距并级，這對于高效開發(fā)復(fù)雜報表是至關(guān)重要的拂檩。
我們再通過一個例子在看計算層的意義，查詢股票最長連續(xù)上漲的天數(shù)：
數(shù)據(jù)源是一個文本嘲碧，記錄各支股票的每天的收盤價：
這個報表的樣式也很簡單稻励，但計算過程要更為繁瑣：需要先將按日期過濾后的數(shù)據(jù)集按股票代碼分組，再計算出每組股票連續(xù)上漲的天數(shù)愈涩，然后再過濾出超過 5 天的股票望抽。
這種復(fù)雜的過程，如果沒有計算層的協(xié)助履婉，使用隱藏格就是個非常麻煩的過程煤篙，大體描述如下：
1、 取出文本數(shù)據(jù)集毁腿，放入單元格中
2辑奈、 設(shè)置排序，按股票代碼和日期排序
3已烤、 按股票代碼分組鸠窗，做成兩層報表
4、 在明細(xì)行增加輔助格計算連續(xù)上漲的天數(shù)
5胯究、 在分組行計算最大連續(xù)上漲的天數(shù)
6稍计、 將明細(xì)行隱藏
7、 將非指定日期和不滿足條件的分組隱藏裕循。
這個過程丙猬，對于帆軟這種格間計算能力較強(qiáng)的報表工具，還只是繁瑣费韭；而 Smartbi茧球、永洪和億信這些就不只是繁瑣的問題，中間的計算也很難表達(dá)星持。并且抢埋，無論是哪一款工具，都會導(dǎo)致整個報表臃腫督暂，有大量的隱藏格（遠(yuǎn)遠(yuǎn)多于呈現(xiàn)出來的格子）揪垄。
要避免這些，實際使用的辦法常常會借助自定義數(shù)據(jù)源逻翁，用 Java 讀出數(shù)據(jù)把結(jié)果集計算好饥努，或者把數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫用 SQL 來算。無論哪種方法八回，都依然非常繁瑣酷愧，嚴(yán)重影響開發(fā)效率驾诈。
但是，如果像潤乾報表一樣有個可解釋執(zhí)行的計算層的話溶浴，那就會很簡單乍迄，實現(xiàn)這個邏輯也不需要幾行代碼：
A1 :=file(“F:/ 股票信息.txt”).import@t().select(left(string(Date),7)==rq), 讀取股票信息中的數(shù)據(jù)，并根據(jù)報表中傳入的 rq 參數(shù)進(jìn)行數(shù)據(jù)過濾士败，取某月數(shù)據(jù)
A2：=A1.sort(Date).group(SID)闯两，按照日期排序，并根據(jù) SID 字段分組
A3：go=A2.new(SID, ~.group@i(Closing>Closing\[-1\]).max(~.len()):ts)谅将，Closing[-1] 取當(dāng)前記錄上一條的收盤價漾狼，A3 中根據(jù)同一只股票當(dāng)天收盤大于前一天收盤價來取最大連續(xù)上漲天數(shù)。
A4：return A3.select(ts>=5)饥臂，取出 A3 中最大連續(xù)上漲天數(shù)大于 5 的數(shù)據(jù)邦投，返回給報表數(shù)據(jù)集使用。
由于腳本數(shù)據(jù)集中已經(jīng)處理好了數(shù)據(jù)擅笔，報表中制作就相當(dāng)簡單了，就不用具體解釋了屯援。
我們用了三篇文章對比了這五款以復(fù)雜報表能力為宣傳點的報表工具:
總體結(jié)論如下：
潤乾的優(yōu)勢非常明顯猛们，即使不考慮它特有的計算層，其復(fù)雜報表能力也是這五款產(chǎn)品中最強(qiáng)的狞洋。再加上計算層弯淘，可以說是遙遙領(lǐng)先，和其它幾款產(chǎn)品相比已經(jīng)差出檔次了吉懊。
帆軟居次庐橙，如果拋開潤乾特有的計算層，帆軟的復(fù)雜報表能力和潤乾相比還是會弱借嗽，但相差不算很大了态鳖，而且帆軟友好的界面能夠再加分，可以認(rèn)為潤乾和帆軟是第一檔的產(chǎn)品恶导，把復(fù)雜報表作為宣傳點都算是名符其實的浆竭。
Smartbi 就要弱一些，模型基本實現(xiàn)了惨寿，但表達(dá)式細(xì)節(jié)和潤乾帆軟差得不少邦泄，可以算作是第二檔的產(chǎn)品。把復(fù)雜報表作為宣傳點裂垦，在 BI 領(lǐng)域中也還勉強(qiáng)說得過去顺囊。
永洪和億信，嚴(yán)格地說蕉拢，其復(fù)雜報表能力還處于沒入門的階段特碳，和其它幾款產(chǎn)品相比的差距非常大诚亚。只能算作第三檔甚至不入檔的產(chǎn)品了，其實這兩款產(chǎn)品本來是 BI 色彩很強(qiáng)的產(chǎn)品测萎，把復(fù)雜報表作為宣傳點就有點名不符實了亡电。
