Reporting Service的 Lookup函數

by adonis 27. 五月 2013 14:56

前言
SQL Server自 2008R2之後的 Reporting Service,開始支援了三個尋查的函數,分別是 Lookup、MultiLookup以及 LookupSet這三個函數,本期就稍微介紹一下這三種函數的使用時機。

查詢函數的使用(LOOKUP)

所謂的查閱(lookup),就是用自已資料集(Dataset)的一個欄位,來搜尋另一個資料集的資料。
例如,你有一個訂單資料表如下:

image

你想透過 OrderID,進行尋找其明細資料,如下:

image

由於兩個資料集都具有 OrderID的欄位,所以你可以透過第一個資料集的 OrderID來「尋查」第二個資料集的明細結果。

當我們提到了一個這個簡單的概念時,有個朋友問了我一個問題,為何要用 Lookup?既然兩個資料集都有相同的欄位,何不使用「inner join」的語法呢?兩個結果集合為一個結果不就好了?
這個問題我也思考了半天,後來發現,確實,如果這兩個資料集都在同一個資料庫,只需要使用 inner join的法即可,但是如果這兩個資料集並不是在同一個資料庫,甚至第一個資料集在 SQL Server,而第二個資料集在 Oracle時,是否還是可以這麼方便的使用 inner join 呢?所以這時應該就要派 lookup這類型的函數上場了。

首先,我們在報表先產生兩個資料集,分別是 Northwind資料庫的 Products以及Category,如下:

image

接下來,我們在報表中拉一個 Table進設計畫面,並且將 Products資料集的 ProductID以及 ProductName放入 Table中,並在第三個欄位加入運算式:

image

運算式中撰寫如下的 lookup函數:
=lookup(Fields!CategoryID.Value,Fields!CategoryID.Value ,Fields!CategoryName.Value,"Category")

有四個參數你必需要撰寫,第一個參數是 Products資料集的欄位 CategoryID,第二個參數是目的方 Category資料集的欄位 CategoryID,第三個參數則是你想看的目的方資料集的欄位,以本例而言是 CategoryName,第四個參數則是目的方資料集欄位是在哪個資料集名稱中,以本例是 Category資料集,最後你就會得到如下的結果:

image

其中「產品分類」這個欄位就是透過 Products資料集中的 CategoryID去 Category資料集中「尋查」的結果。

查詢函數之 MultiLookup函數
通常大家對 Lookup函數都不會有什麼特別的問題,會有問題的多半是 MultiLookup函數,如果說 Lookup函數的第一個參數是單一值的話,那麼 MultiLookup的第一個參數就是多值。
當然,有些人會問,如果第一個參數是多值,那會是一個什麼樣類型的資料呢?
答案就是像下面這樣子的資料:

image

事實上這種資料在實務中不太容易存在,為何?因為 ProductID這個欄位有多值,中間用逗號隔開,此舉已經違反第一正規化,如果真的將資料庫做如此設計的話, productid的欄位修改將非常不易。

不過,MultiLookup就是適用於這樣的時機,Lookup是傳一個參數,自然 MultiLookup就是傳多個參數了。

所以,我們把兩個資料集一樣準備好,如下:

image

同樣的,拉一個資料表,將 OrderTest的資料填入,如下圖:

image

運算式中,加入下面的函數:

=Join(multilookup(split(Fields!productid.Value,","),Fields!ProductID.Value,Fields!ProductName.Value,"Products"),",")

其中,multilookup函數中的第一個參數:split(Fields!productid,Value, ","),是將有逗號分隔的 productid欄位,放入陣列中。
第二個參數則是目的資料集的 ProductID欄位,第三個參數是最後呈現出來想看的結果是 ProductName,因為傳回來也是一個多值陣列,所以這邊我們利用 Join函數將他們用逗號分隔的方式把資料呈現。

最後畫面呈現的結果如下:

image

如果你有類似的需求,這個就是使用 multilookup的時後啦~~。

查詢函數之 LookupSet
相較於剛才的 multilookup,LookupSet使用的時機會比較常見,它所輸入的是單一參數查閱,但回傳的結果是多值,你可以利用 Join將資料呈現在畫面上。

我們首先先產生兩個資料集,如下:

image

接下來產生一個資料表,將訂單編號加入,並且設定運算式:

image

在運算式中,輸入下面的運算:

=Join(Lookupset(Fields!OrderID.Value,Fields!OrderID.Value,Fields!ProductID.Value,"OrderDetails"),",")

它其實和 Lookup非常的像,只是 Lookup回傳一個值,而 LookupSet回傳一個資料集,然後利用 Join將這個陣列拆解,最後得到如下的結果:

image

結語
本期大概算一個整理,SQL Server 2008 R2之後所増加的 Reporting Service的三個函數,其中 Lookup是用一個欄位去尋查另一個資料集的欄位,而 MultiLookup則是一個欄位(但有多值),尋查另一個資料集的欄位,LookupSet則是用一個欄位,回傳多結果值,等同於1對多的概念。

image

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List