報表工具對比選型系列用例——過程計算

我們知道，報表呈現的資料常常並不是直接從資料庫（源）取出來的資料，而還要進行一些運算，報表工具通常也會提供一定的運算能力（如過濾、分組等）以應對這種需求。但是，情況複雜時，報表資料集上的運算可能要多個步驟才能完成，這時候就要考查報表工具對過程式計算的支援程度了。

用例說明

統計要求

列出指定時段的大客戶。所謂大客戶，定義為銷售額佔前一半的客戶，也就是把客戶銷售額從大到小排序後，前面若干個客戶的合計銷售額構成總銷售的一半，這些客戶被稱為大客戶。

報表式樣

資料結構

[銷售記錄表]

特點分析：

這個報表式樣很簡單，主要麻煩之處在於需要從原始資料集計算出大客戶後再呈現，而這個計算並不是簡單一步能完成的。

我們的重點是考查報表工具的過程計算能力，所以假定不使用 SQL 或資料來源的計算能力來完成。

潤乾報表

製作過程:

1、 配置並連線資料來源。

2、 設定資料集

潤乾報表提供了一個獨立的計算引擎，可以透過內建的指令碼實現對資料的計算並將結果返回給報表資料集，在報表中新增資料集，資料集型別使用指令碼資料集，指令碼如下：

2.1 A1 格：單元格做了資料來源的連線，然後 A6 單元格在執行完畢後關閉連線。

2.2 A2：從客戶銷售表中取數，此處根據客戶名稱做了彙總，並且按照銷售額降序排序。

2.3 A3：對銷售額進行求和操作並處以 2，取出總金額的一半，用於判斷大客戶。B3 設定初始值為 0，用於做銷售額累加操作

2.4 A4：對銷售額進行累加，取出累加金額大於 A3 中對應的 A2 的序號

2.5 A5：根據序號取 A2 中對應的值，並做為結果集返回給報表

3、 設計報表模板

指令碼資料集中返回的資料就是大客戶的資料資訊，所以製作報表就相當簡單, 設定如下：：

3.1、 A2、B2 單元格直接取資料集中的資料

3.2、 B3、B4 單元格直接用資料集彙總函式，對資料集計數取出大客戶數量，對銷售額用 avg 函式取出平均銷售額。

3.3、 設定邊框、金額顯示格式。

執行結果：

完成後點評

1、 0.5 小時，使用指令碼資料集中內建的語法，幾個單元格的處理就能快速實現需求。

2、 可以使用指令碼資料集，內建豐富語法規則，能快速處理各種複雜的資料統計要求。

3、 報表設計簡單，並且不需要輔助單元格，報表計算效率高。

帆軟報表

製作過程:

1、 配置並連線資料來源。

2、 設定資料集

增加資料庫查詢，SQL 為： SELECT 客戶,sum(銷售額) 銷售額 FROM DEMO. 客戶銷售表 group by 客戶 order by 銷售額 desc

3、 設計報表模板

3.1 B2 單元格取總銷售額的一半，用於判斷大客戶，單元格內對銷售額求和，並設定自定義顯示公式 $$$/2，帆軟對欄位彙總後如果再進行計算，無法直接在單元格里手動加表示式，只能設定自定義顯示公式。

3.2 A3、B3 單元格取數資料集中的客戶和銷售額欄位。

3.3 C3: 求累計金額，=B3+C3[A3:-1]，增加條件屬性，對非大客戶的資料進行隱藏。

3.4 B4: 大客戶數量，=count(B3[!0;!0]{C3 <= B2 || (C3[A3:-1] < B2 && C3 >= B2)})，用 count 函式統計累計銷售大於總銷售額一般的數量。

3.5 平均銷售額，=sum(B3[!0;!0]{C3 <= B2 || (C3[A3:-1] < B2 && C3 >= B2)}) / B4，設定單元格的格式保留兩位小數。

3.6 將不需要展現的行列設定隱藏。

報表結果

完成後點評

1、 用時 1 小時。

2、 累計實現可以透過內建的層次函式 LAYERTOTAL(B1, C1, D1) 實現，也可以透過層次座標, 本例使用的是層次做表方式，內建功能比較豐富。

3、 可以根據條件公式動態設定報表行列的動態隱藏。

4、 需要增加輔助單元格，會增加額外資源的耗用，尤其是客戶數量多時，報表中是取出了所有客戶資訊（小客戶數量常常多得多），然後再判斷隱藏，資料量大時報表計算效能會有影響。

Smartbi

製作過程

1、 配置並連線資料來源。

2、 設定資料集

採用原生 SQL 資料集，直接透過 SQL 語句取數就行：

select 客戶,sum(銷售額) 金額 from 客戶銷售表 group by 客戶 order by 金額 desc。

3、 設計報表模板

報表的設計在 excel 內完成，藉助豐富的 excel 函式，smartbi 解決這種格間運算也不是太麻煩。如幾個關鍵計算：

3.1 D1：所有客戶的銷售總金額，D2 中寫入表示式 =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)，根據累積金額，標識當出現累積金額大於總金額一半的時候 標識為 1，後面累計值依然大於總金額一半，數值累計為 2/3/4（用於隱藏後面不要的資料）等

3.4 C4 格：=MATCH(1,SSR_GetSubCells(E3,A3),0)，根據 E3 的標識，查詢第一個出現的 1 所在的序號，該序號即標識大客戶數。

3.5 C5 格：平均銷售額相對簡單，=D4/C4，並設定報表中資料顯示格式

3.6 隱藏行設定，需要隱藏掉非大客戶資料，不支援表格內定義隱藏表示式，這裡根據 E3 值大於 1 的就隱藏，要藉助平臺給電子表格設定宏（需要寫 js 程式碼）實現：

執行結果

完成後點評：

1、 用時：1.5 小時。

2、 完全在 excel 中操作，容易上手，操作起來比較方便。excel 函式豐富，這個報表主要是其查詢類函式的使用。

3、 隱藏不需要（除大客戶外）的客戶資料時，不支援表格內定義隱藏表示式，需要 js 程式碼才能實現，難度較大。

4、 需要增加輔助單元格，會增加額外資源的耗用，尤其是客戶數量多時，報表中是取出了所有客戶資訊（小客戶數量常常多得多），然後再判斷隱藏，資料量大時報表計算效能會有影響。

永洪 BI

製作過程：

1、 配置並連線資料來源。

2、 設定資料集

使用 SQL 語句資料集：select 客戶,sum(銷售額) 金額 from 客戶銷售表 group by 客戶 order by 金額 desc

3、 設計報表模板

3.1 第一列增加了個按銷售額取數，實際中發現資料集中雖然按照銷售額排序了，但是直接取出客戶名稱欄位時，即使不排序，也不會按照資料集原有資料排序，所以前邊增加了個銷售額欄位，並且設定降序排序。

3.2 第四列中取出了總銷售額以及對應的累計銷售額，累計銷售額直接拖拽，設定下格間計算未累計就行

3.3 大客戶數量單元格使用格間計算，裡邊表示式為：

var a=0;

for(var i=1;i<=ridx-1;i++){

if(cell(i,3)>=cell(0,3)/2){

a=i;

break;

}

}

透過 js 語法，對行列迴圈，判斷累計值是否大於銷售額總計的一半，返回對應行數也就是數量。

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 第一列和第四列是輔助列，右側有個隱藏列設定，將這兩列隱藏掉

3.6 隱藏行設定，永洪不支援單元格隱藏行表示式，如果要隱藏掉非大客戶資料，需要透過 js 語句實現，可以在 js 中獲取到報表計算後的行屬性，根據 3.3 中獲取的大客戶數量動態設定非大客戶（報表計算後行號超過大客戶數量的行）行高為 0，來達到隱藏的效果，這個和 Smartbi 有些類似，這裡就不具體實現了。

執行結果

完成後點評

1、 用時 2 小時，格間計算那幾個單元格處理時間較長，比較考驗開發能力。

2、 格間計算中可以用 js 語法進行資料的計算，適合開發人員操作，比較靈活, 但同時內建函式少，比較考驗開發人員能力。

3、 累計設定比較方便，直接用內建的語法就行。

4、 排序設定有問題，資料集中設定了資料的排序，但是報表裡預設不會按照相應的順序排序（也可能沒找到方法）。

5、 需要增加輔助列來實現，比如累計列，如果資料量大會額外佔用記憶體空間。

6、 可以設定隱藏列，但是設定後設計介面也就無法看到該列，無法還原，而且如果資料集變動而隱藏列裡引用了變動前的欄位，很難更改。

7、 需要增加輔助單元格，會增加額外資源的耗用，尤其是客戶數量多時，報表中是取出了所有客戶資訊（小客戶數量常常多得多），然後再判斷隱藏，資料量大時報表計算效能會有影響。並且隱藏掉非大客戶資訊時，需要寫複雜的 js，難度較大。

億信

製作過程

1、 配置並連結資料來源

2、 設定資料集

直接透過 SQL 語句取數就行：SQL 語句為：select customer,sum(sales) as sales from sales group by customer order by sum(sales) desc

3、 設計報表模板

3.1 A1：=sum(TEST.sales)/2 計算所有客戶的銷售總金額的一半，並在右側屬性裡設定隱藏屬性

3.2 A3：=TEST.customer 浮動維欄位，同時在右側設定排序的依據是 B3。

3.3 C3：=self.leftcell.value+self.upcell.value 實現金額累計計算

3.4 D3：=if(GRID1.C3.upcell.value<GRID1.A1,1,null) 將累計金額大於 A1 單元格值的資料標識為 1，不符合條件的標識為 null，方便後面統計數量。

3.5 B4：=sum(GRID1.D3$)，根據 D3 的標識統計出來大客戶數量（D3 中大客戶返回 1，對 1 求和就是大客戶數量）

3.6 B5：=GRID1.D3$.select(@.value=1).select(true,@.leftcell(2).value).avg() 先使用陣列的方式過濾出來對應的資料，然後再進行求平均的計算

3.7 選中第 3 行設定顯示錶達式為：<#=if(GRID1.D3=1,1,0)#>，D3 單元格標識出了本條資料是否是大客戶，這裡根據 D3 單元格值控制本行是否顯示（如果為 1 則返回 1 表示顯示，否則返回 0 不顯示）

執行結果

完成後點評：

1、 製作用時：1.5 小時

2、 內建函式比較豐富，本表中常用操作基本上都是透過內建函式實現。

3、 可以根據條件公式動態設定報表行列的動態隱藏，比較方便。

4、 需要增加輔助單元格，會增加額外資源的耗用，尤其是客戶數量多時，報表中是取出了所有客戶資訊（小客戶數量常常多得多），然後再判斷隱藏，資料量大時報表計算效能會有影響。

總結

1. 這個例子報表結果格式相對比較簡單，各個報表工具基本上都能實現。

2. 對於本例中考查的過程計算，帆軟、Smartbi、永洪、億信這四款工具實現方法基本一致，都是透過輔助行列取出銷售額總計、累計銷售額，然後進行資料判斷來確定哪些客戶是大客戶並對資料統計，最後將輔助行列再隱藏掉。整個過程有一定的難度和繁瑣度，這是因為報表只能執行狀態式計算，用來處理過程計算就只能採取這種迂迴的辦法。

3. 這幾款產品之間來看，帆軟、Smartbi、億信基本上一致，都是使用內建函式或者方法實現，隱藏行列也比較方便。而永洪累計實現方便，直接透過滑鼠設定就行，但是做資料統計時就要寫複雜的 javascript 語法來統計，這塊實現難度比較大，而且隱藏行列設定也不太方便。永洪的複雜報表功能相比其它三款要更弱一點，帆軟在這四款中相對更好，這和之前測試其它案例的結論也一致。

4. 潤乾事實上也可以採用隱藏格手段實現，但我們在這裡則提供了一種與眾不同的方法。潤乾增加了一個計算層，使用指令碼資料集，可以更方便地實現過程式計算，返回給報表就是處理後的資料結果，報表中不再做特別處理，整體過程更為簡單，而且消耗資源也比使用輔助格要少得多，效能會更優。仍然是和之前測試案例的結論一致，潤乾是這幾款產品中擁有最強計算能力的產品，計算層明顯拉開了和其它產品的差距，這對於高效開發複雜報表是至關重要的。

進一步的例子

我們再透過一個例子在看計算層的意義，查詢股票最長連續上漲的天數：

資料來源是一個文字，記錄各支股票的每天的收盤價：

這個報表的樣式也很簡單，但計算過程要更為繁瑣：需要先將按日期過濾後的資料集按股票程式碼分組，再計算出每組股票連續上漲的天數，然後再過濾出超過 5 天的股票。

這種複雜的過程，如果沒有計算層的協助，使用隱藏格就是個非常麻煩的過程，大體描述如下：

1、 取出文字資料集，放入單元格中

2、 設定排序，按股票程式碼和日期排序

3、 按股票程式碼分組，做成兩層報表

4、 在明細行增加輔助格計算連續上漲的天數

5、 在分組行計算最大連續上漲的天數

6、 將明細行隱藏

7、 將非指定日期和不滿足條件的分組隱藏。

這個過程，對於帆軟這種格間計算能力較強的報表工具，還只是繁瑣；而 Smartbi、永洪和億信這些就不只是繁瑣的問題，中間的計算也很難表達。並且，無論是哪一款工具，都會導致整個報表臃腫，有大量的隱藏格（遠遠多於呈現出來的格子）。

要避免這些，實際使用的辦法常常會藉助自定義資料來源，用 Java 讀出資料把結果集計算好，或者把資料匯入資料庫用 SQL 來算。無論哪種方法，都依然非常繁瑣，嚴重影響開發效率。

但是，如果像潤乾報表一樣有個可解釋執行的計算層的話，那就會很簡單，實現這個邏輯也不需要幾行程式碼：

A1 :=file(“F:/ 股票資訊.txt”).import@t().select(left(string(Date),7)==rq), 讀取股票資訊中的資料，並根據報表中傳入的 rq 引數進行資料過濾，取某月資料

A2：=A1.sort(Date).group(SID)，按照日期排序，並根據 SID 欄位分組

A3： go=A2.new(SID, ~.group@i(Closing>Closing\[-1\]).max(~.len()):ts)，Closing[-1] 取當前記錄上一條的收盤價，A3 中根據同一只股票當天收盤大於前一天收盤價來取最大連續上漲天數。

A4：return A3.select(ts>=5)，取出 A3 中最大連續上漲天數大於 5 的資料，返回給報表資料集使用。

由於指令碼資料集中已經處理好了資料，報表中製作就相當簡單了，就不用具體解釋了。

複雜報表最後的結論

我們用了三篇文章對比了這五款以複雜報表能力為宣傳點的報表工具:

總體結論如下：

1. 潤乾的優勢非常明顯，即使不考慮它特有的計算層，其複雜報表能力也是這五款產品中最強的。再加上計算層，可以說是遙遙領先，和其它幾款產品相比已經差出檔次了。

2. 帆軟居次，如果拋開潤乾特有的計算層，帆軟的複雜報表能力和潤乾相比還是會弱，但相差不算很大了，而且帆軟友好的介面能夠再加分，可以認為潤乾和帆軟是第一檔的產品，把複雜報表作為宣傳點都算是名符其實的。

3. Smartbi 就要弱一些，模型基本實現了，但表示式細節和潤乾帆軟差得不少，可以算作是第二檔的產品。把複雜報表作為宣傳點，在 BI 領域中也還勉強說得過去。

4. 永洪和億信，嚴格地說，其複雜報表能力還處於沒入門的階段，和其它幾款產品相比的差距非常大。只能算作第三檔甚至不入檔的產品了，其實這兩款產品本來是 BI 色彩很強的產品，把複雜報表作為宣傳點就有點名不符實了。