如何設計這種報表

by adonis 13. 十一月 2012 15:09

作    者:楊先民
審    稿:張智凱

前言
來解決報表製作的問題吧!本期是將上課時學員所提到的一些報表製作上的問題,解答之後寫成的雜誌文章內容,希望能夠透過思考的方式,一步步將你想要做的報表產生出來。

這樣的報表如何產生

在設計報表前,首先還是了解一下報表設計的原理和撰寫 T-SQL指令是相同的,也就是說,你有資料來源,並且畫出你想要看到的報表結果,接下來的事就是想辦法把你要的答案給產生出來。

而產生出來不外乎有兩種方式,第一種是使用 SQL指令產生想要的資料,再將資料加到報表控制項中,第二種則是 SQL指令只用來做基本查詢,查詢出來的結果傳送給前端報表控制項,報表控制項再利用它自身的機制或是報表運算式將結果二次處理。

就好比以排序為例,你可以使用 SQL指令先將資料來源排序,也可以用 SQL指令將資料取出,排序的工作是交由 Table控制項完成,總而言之有兩種方式可以產生你所想要報表。

現在,我們有個報表問題,你希望能夠產生如下圖的報表:

image

其實這是個很典型的矩陣報表,也就是所謂的交叉分析報表(樞紐分析報表),由兩個維度(年份與分類)包夾著量值所產生出來的分析資料,是最常見的應用。

然而,這個報表有個值並不是那麼自動的會幫你產生,也就是「成長%」的數值,以本圖為例,2009是資料比較的第一年,所以成長%沒有資料,而2010年的成長%是 -15.47%,也就是負成長,今年的資料需要和去年相比,得到的成長%會被記錄在加總下一行。

成長百分比是本例子的一個重要關卡,如果能正確顯示成長百分比,所有問題就會得到解答,然而,這個成功百分比該如何產生呢?
回想之前提到的兩種方式,一種是下 SQL指令把成長百分比計算出來,另一種是成長百分比是由矩陣控制項的運算式完成。

以本例而言,我是採用 SQL方式產生,因為我對 SQL指令較熟悉,只是如果你用的是 SQL Server 2008,可能並沒有這麼方便撰寫「與去年同期」的語法,如果是用 SQL Server 2012的話,也許會比較簡單。

不過,有些前置動作是可以先做的,例如我們可以先把資料與報表配置先產生出來,以本例而言,需要兩個維度與一個量值,所以我採用微軟的範例資料庫 Northwind當例子,使用 Category、OrderDetails、Products以及 Orders資料表關聯併行 group by指令先完成基本語法,如下:

SELECT YEAR(Orders.OrderDate) AS years, Categories.CategoryName, SUM([Order Details].Quantity) AS qty
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN Products ON [Order Details].ProductID = Products.ProductID INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID GROUP BY  YEAR(Orders.OrderDate), Categories.CategoryName

產生出來的資料結果如下:

image

這是我們的原始資料。

接下來拉一個矩陣控制項在報表,把年、分類與數量拖拉進去,並且做一些欄位的美觀整理。

image

其中, Sum(qty)旁邊的 Expr運算式為:

=Sum(Fields!qty.Value)/reportitems!Textbox19.Value

因為要算百分比,所以把 Expr那個 TextBox的數值屬性改為 percent百分比。

接下來,我們要生出一個資料列,直接如下圖,在原本的 Total下方加入一列:

image

新增的資料列就是用來呈現與去年同期的百分比計算,這個部分我考慮使用 SQL的函數完成,也就是 scale function,因為這個函數可以回傳單一值,並且嵌在原本的 SQL指令中,函數內容如下:

create function last_year
(@year int)
returns decimal(10,2)
begin

declare @lastvalue decimal(10,2)
declare @returnvalue decimal(10,2)
declare @nowvalue decimal(10,2)
set @lastvalue =( SELECT sum(Quantity) as qty
FROM             Orders INNER JOIN
                          [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                          Products ON [Order Details].ProductID = Products.ProductID INNER JOIN
                          Categories ON Products.CategoryID = Categories.CategoryID
                          where YEAR (OrderDate) = @year -1
                          group by YEAR(OrderDate)
                          )
if @lastvalue is null
    set @returnvalue = 0.
    else
    begin
set @nowvalue =( SELECT sum(Quantity) as qty
FROM             Orders INNER JOIN
                          [Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
                          Products ON [Order Details].ProductID = Products.ProductID INNER JOIN
                          Categories ON Products.CategoryID = Categories.CategoryID
                          where YEAR (OrderDate) = @year 
                          group by YEAR(OrderDate)
                          )
    set @returnvalue = ((@nowvalue-@lastvalue) / @lastvalue )*100
    end
    return @returnvalue

end

這個程式簡單介紹一下,參數為year,輸入年份之後,會先檢查去年加總資料,若沒有值(NULL),則傳回0,程式結束,如果去年有值,再計算今年的加總資料,並且使用
((@nowvalue-@lastvalue) / @lastvalue )*100 計算成長百分比傳回。

接下來,更改原本的報表資料集設定,把寫好的函數嵌入進來,變成如下:
SELECT YEAR(Orders.OrderDate) AS years, Categories.CategoryName, SUM([Order Details].Quantity) AS qty, dbo.last_year(YEAR(Orders.OrderDate)) AS diff…..(後面省略)

就只是加入一個函數到欄位中,這樣就可以將這個欄位加入到剛才多增的資料列,變成如下圖:

image

在此要注意,如果直接選擇 diff欄位,預設會變成 sum(diff),所以你必需自己手動的把這個欄位改成只要顯示 diff欄位即可。

大致上已經完成,只要稍做修改就好,將運算式精確的改成

=IIF(Fields!diff.Value=0,"",cstr(Fields!diff.Value)+"%")

如果運算式為0,則顯示空字串,不然則是顯示數字加%的資料(利用 cstr函數轉型為字串)。

設定完成後,就可以看到大功告成的結果如下圖:

image

有沒有看到?成長的 %也有顯示出來喔!

以上,就是示範如何產生你想看到的報表,這裡先把想要看到的結果畫出來,然後看這個資料要用 SQL端產生,還是在報表端,利用報表控制項功能或是運算式完成,依你熟悉的工具為主,像我比較熟悉 SQL指令,所以本例我是用 SQL運算式,自已撰寫函數達成需求,希望對各位有所幫助。

image

Tags:

評論 (45) -

cours de theatre
cours de theatre United States
2017/9/30 上午 07:23:04 #

Very informative article post. Keep writing.

回覆

dic phoenix
dic phoenix United States
2017/10/7 上午 12:47:09 #

I loved your blog.Thanks Again. Keep writing.

回覆

buy hacklink
buy hacklink United States
2017/10/12 下午 09:14:30 #

I am so grateful for your blog.Thanks Again. Awesome.

回覆

look at more info
look at more info United States
2017/10/14 下午 04:30:40 #

Thanks a lot for the article. Really Cool.

回覆

dragon city hack mod
dragon city hack mod United States
2017/10/15 下午 03:59:46 #

Im obliged for the article post.Thanks Again. Want more.

回覆

why not try this out
why not try this out United States
2017/10/17 下午 03:17:39 #

I appreciate you sharing this post.Really thank you! Really Great.

回覆

sletrokor
sletrokor United States
2017/10/17 下午 08:49:40 #

Thanks so much for the article post.Really looking forward to read more. Want more.

回覆

sex pills
sex pills United States
2017/10/19 上午 07:53:48 #

Awesome article.Really looking forward to read more. Really Cool.

回覆

Click Here
Click Here United States
2017/10/19 下午 06:51:25 #

Thanks for sharing, this is a fantastic blog.Really thank you! Keep writing.

回覆

Osimi SeaView
Osimi SeaView United States
2017/10/21 上午 04:07:10 #

Very informative blog post.Really looking forward to read more. Really Cool.

回覆

prix carte grise
prix carte grise United States
2017/10/21 上午 07:45:33 #

I really liked your blog article.Really thank you! Really Great.

回覆

can ho vung tau
can ho vung tau United States
2017/10/28 下午 12:19:44 #

Thanks so much for the post.Really looking forward to read more. Keep writing.

回覆

EZ Battery Reconditioning Scam
EZ Battery Reconditioning Scam United States
2017/10/30 上午 11:14:48 #

Im thankful for the post.Much thanks again. Really Cool.

回覆

pogoda plock
pogoda plock United States
2017/10/30 下午 07:01:27 #

Thank you ever so for you blog article.Much thanks again. Really Cool.

回覆

life leadership
life leadership United States
2017/11/1 上午 11:29:00 #

Really informative article post.Really thank you! Cool.

回覆

phentaslim review
phentaslim review United States
2017/11/3 上午 11:20:21 #

Very neat article.Thanks Again. Much obliged.

回覆

spinal stenosis holistic treatment
spinal stenosis holistic treatment United States
2017/11/15 上午 10:17:36 #

I value the blog post.Thanks Again. Much obliged.

回覆

avocat criminel montreal
avocat criminel montreal United States
2017/11/16 下午 08:51:14 #

Thanks-a-mundo for the blog article.Really looking forward to read more. Really Cool.

回覆

Im obliged for the blog.Thanks Again. Fantastic.

回覆

fashion
fashion United States
2017/11/24 上午 12:24:55 #

Very informative article.Thanks Again. Great.

回覆

Chad Boonswang and Jeffrey Goodman
Chad Boonswang and Jeffrey Goodman United States
2017/11/26 下午 08:29:25 #

Enjoyed every bit of your article.Much thanks again. Keep writing.

回覆

Chad Boonswang SEO
Chad Boonswang SEO United States
2017/11/27 上午 02:40:32 #

I really enjoy the article post. Want more.

回覆

cash for cars scammer
cash for cars scammer United States
2017/11/29 下午 07:03:42 #

Im grateful for the blog. Great.

回覆

porno
porno United States
2017/12/1 下午 07:09:48 #

A big thank you for your blog post. Awesome.

回覆

Business Credit For Small Business Loan
Business Credit For Small Business Loan United States
2017/12/3 上午 07:20:47 #

I really like and appreciate your article.Really looking forward to read more. Cool.

回覆

I cannot thank you enough for the blog post.Really thank you! Will read on...

回覆

Giovanni Carleo
Giovanni Carleo United States
2017/12/14 上午 11:51:07 #

Appreciate you sharing, great blog article.Thanks Again. Want more.

回覆

why not check here
why not check here United States
2017/12/14 下午 06:47:12 #

Major thankies for the blog article.

回覆

Christmas Songs
Christmas Songs United States
2017/12/15 上午 01:21:34 #

Fantastic post. Great.

回覆

canon driver software
canon driver software United States
2017/12/16 下午 08:24:54 #

Appreciate you sharing, great blog post.Much thanks again. Cool.

回覆

tips lose weight
tips lose weight United States
2017/12/17 上午 02:48:57 #

wow, awesome blog article.Thanks Again. Really Great.

回覆

Awesome blog post.Thanks Again.

回覆

Mid-market
Mid-market United States
2017/12/17 下午 08:00:30 #

I really liked your blog post.Really thank you! Great.

回覆

fast cash
fast cash United States
2017/12/20 下午 09:23:54 #

I loved your post.Thanks Again. Will read on...

回覆

canon drivers
canon drivers United States
2017/12/23 上午 11:07:00 #

wow, awesome blog article.Much thanks again. Fantastic.

回覆

Really appreciate you sharing this blog.Thanks Again. Keep writing.

回覆

SOCCER HIGHLIGHTS
SOCCER HIGHLIGHTS United States
2017/12/26 下午 03:05:41 #

Looking forward to reading more. Great blog.Really thank you! Fantastic.

回覆

canon printer series
canon printer series United States
2017/12/27 下午 08:38:31 #

Im thankful for the article post. Keep writing.

回覆

drivers hp
drivers hp United States
2018/1/2 上午 11:13:00 #

Major thanks for the post. Great.

回覆

find this
find this United States
2018/1/2 下午 07:21:56 #

Really enjoyed this blog.Much thanks again.

回覆

online slots real money usa
online slots real money usa United States
2018/1/4 下午 09:22:18 #

Very neat article.Much thanks again. Really Cool.

回覆

hp printer driver
hp printer driver United States
2018/1/5 下午 04:38:54 #

Awesome blog article.Thanks Again. Cool.

回覆

FBA
FBA United States
2018/1/6 上午 08:43:49 #

Thanks for the blog post.Much thanks again. Will read on...

回覆

colocation miami
colocation miami United States
2018/1/10 上午 08:57:51 #

Wow, great article post.Much thanks again. Cool.

回覆

新增評論




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






NET Magazine國際中文電子雜誌

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

月分類Month List