Programming MS Office 2000 Web Components第三章第一節

譯者說明：歡迎通路我的Blog:  http://blog.csdn.net/daidaoke2001/

譯文中的錯誤或不當之處望不吝指出，這也是我堅持翻譯工作的最大動力。

譯文中标注了多個問号的地方，是我也不明白的地方，如果你知道如何翻譯，請告訴我，

讓我們一起提高。

我的Email：[email protected]

如需轉載，請事先通知。

第三章           圖 表 組 件

至此我們已經熟悉了電子表格元件，現在讓我們來研究圖表元件的功能和程式設計模型。和前一章中一樣，在介紹了一個特定功能後，我會講述該功能的一些有趣用途。本書第二部分中，您會看到大部分功能在實際中的運用。

在本章中也會示範一些使用您自己的代碼來為圖表元件添加功能的巧妙方法。剛開始的時候您可能會覺得圖表元件要比電子表格和透視元件複雜一些，但它具有一些可供您在解決方案中使用的強大功能。此外，它還提供了許多“配置選項”，您可以通過調節這些選項來根據您自己的愛好定制圖表。

第一節           圖 表 組 件 基 礎

圖表元件是一個COM控件，它就是由開發Microsoft Excel2000中的圖表生成功能的小組開發的。它以位于界面的COM控件形式，以及位于記憶體中的GIF生成引擎形式來提供基本的商業圖表生成功能。在現在這個第一個版本中，圖表控件支援所有能在Excel中找到的二維圖表類型（等高線圖類型除外），另外還支援 極坐标圖，堆積餅圖，以及填充散布圖表類型。目前這個版本不包括任何三維圖表類型或效果。

本章中将會讨論的一個新特性是圖表控件能夠在一個元件的整個圖表空間中顯示多個圖表。圖表控件實際上是由一個包含一個或多個圖表的完整圖表空間組成的，這些圖表都共享同一組相同的類别。（我會在本節後部詳細解釋術語“類别”）通常，在一個時刻，你隻會在控件中包含一個圖表；然而，在同一個圖表空間中包含多個圖表，使得可以很容易的快速比較相似資訊。在本章結尾處我會詳細講述“圖表空間”。

和電子表格元件一樣，圖表元件具有許多基本特性和一些獨特的術語。在我們讨論更進階的主題之前，您應該熟悉這些特性和術語。

圖表生成的術語

       在開發Excel2000圖表生成功能的一個新特性時，OWC小組組織了大量的可用性測試，來檢查是否我們的設計易于使用。如果您從來沒有聽說過這種測試，我可以解釋一下，測試是這樣的，我們從整個西雅圖地區的公司和家庭中找來一些真正的顧客，向他們展示一個新特性的原型。我們要求這些人執行各種各樣的任務，觀察他們，以了解他們如何解決問題，以及是否我們的設計能夠有效的幫助他們完成任務。我們常常發現現實情況與我們的期望相去甚遠――這意味着必須回頭重新設計這個特性。

       在一個測試中，我們向測試人員展示我們在Excel中建立的各種圖表，并将圖表中的某部分圈起來。我們要求他們告訴我們他們認為這些被圈起來的部分應該叫什麼。從邏輯上說，您會期望我們找到一些共性，發現那個大多數人已經将它和某一特定元素關聯起來的名稱。然後我們就可以在我們的文檔，程式設計模型，使用者界面等地方使用這個名稱。但是結果使我們十分沮喪，我們發現完全不能給這些圖表元素提供一個标準名稱。您可能認為人們應該知道哪個是x軸，哪個是y軸，但是許多人并不會記得多少數學課上學到的東西，而常常将它們搞混。

注釋

如果您正努力回憶哪個軸是X軸，哪個軸是Y軸，讓我告訴您，X軸是水準方向的，Y軸是垂直方向的。當然，大多數圖表都有被稱為類别軸和數值軸的軸，而它們的方向是依賴于特定圖表類型的。散布圖和氣泡圖具有X和Y軸，是以這些圖表上的各點之間要比較兩個(或三個)值。在雷達圖或極坐标圖這樣的圖表類型中，軸實際上是從圖表的中心延伸出去，是以X和Y軸就沒有意義了。

       因為看來并不存在描述一個圖表的元素的通用語言，是以讨論建立和操作圖表就十分困難。為了獲得任何對于圖表元件的特性和程式設計模型的了解，我們都必須首先定義一系列用于元件的關鍵術語，并确定它們代表什麼元素。許多術語在不同的圖表類型中具有特殊的含義，是以，如果你在下面讨論中遇到一個您不熟悉的圖表類型，可參考“支援的圖表類型”一節，檢視該圖表類型相對應的螢幕截圖和介紹。

系列

       系列是圖表元件中最重要的結構之一。實際上，這個元件中的大部分内部結構都是圍繞着系列的，圖3-1中标記出了一個柱狀圖中的系列。請注意每個系列與圖表的圖例中的一項相對應。

圖3-1。一個包含兩個系列的柱形圖。

       一個系列代表了希望以某種方式顯示的多個資料點的一個序列。人們通常認為是圖表具有一個獨特的類型（例如線形圖，條形圖，或者餅圖）。但是在圖表控件中，具有獨特類型的是系列――這意味着您可以通過設定一個系列為線形，而另一個系列為柱型來建立一個組合圖表。一個系列中的所有資料點通常具有相同的顔色（但您可以改變這個顔色，我們後面會提到）。另外，例如趨勢線和錯誤條這樣的元素是和特定的系列相關的。

       預設情況下，圖例上的一項代表一個系列，但是如果需要，您可以隐藏圖例中的某項。（後面将解釋具體怎樣做）

類别

       類别要比系列難解釋一些。圖3-2标記出了一個柱形圖中包含的類别标簽。

圖3-2。柱形圖中的類别。

       所有圖表都有類别的概念，但并不是所有圖表都有類别軸。在圖3-2中，銷售代表的名字是類别，每個系列包含各個類别上的一個單一資料點。大多數圖表中，類别和系列的交叉點建立一個資料點。不過要注意的是，有些特别的系列和其它系列不同，它可能并不包含包含某個類别的資料點。例如，圖表控件從所有系列中合并所有的類别，但在系列－類别的交集處不繪制資料點（？？？）。

       類别軸在三個重要的方面不同于數值軸。

u       各類别沒有内在的順序。

u       沒有最小和最大的類别。

u       一個資料點僅配置設定給一個特定的類别。

       如果銷售人員是您的類别軸，特定的銷售數量就很自然與特定的銷售人員相對應，在銷售人員之間不存在資料點。而一個值軸有一個确定的最小值和最大值，在軸上的空間被平均分割為多個單元，當從最小值移向最大值時單元對應的值逐漸增加。是以不管位于軸的何處，每個資料點都能被标記。

       散布圖和氣泡圖沒有類别軸，因為它的資料點是由X坐标和Y坐标定義的，氣泡圖表的資料點還具有一個代表氣泡尺寸的值。雖然這些圖表類型沒有類型軸，資料點仍然可以屬于某個特定的類别，并且你也可以獲得一個給定資料點的類别名。将額外的資訊編碼到散布圖或氣泡圖中的資料點的機制是很有用的，它允許您在使用者将滑鼠停留在一個資料點上時顯示這個資訊。

注釋

顯然，在真實的世界中，銷售額可能常常屬于多個銷售代表。但大部分銷售資訊系統都将每個銷售代表對銷售的貢獻配置設定合存儲在一個數值中，這個數值會被标注在圖表中。資料在類别中整齊分布的目的是使得可以使用連續的數值來比較離散的類别。例如，數值1.4567454可以在一個範圍在0到2之間的數值軸中被标注，但是這個數值并不與類别在軸上的離散分布的容器相對應？？？

數值，數值，數值

       在大多數較簡單的圖表類型中，您隻需要考慮一組數值。表3-3顯示了以銷售人員和年來标注的銷售量。這個圖表中每個資料點隻包含一個數值――對于大部分簡單圖表類型來說都是這樣的。

圖3-3。柱形圖中的數值。

       另一方面，散布圖和氣泡圖表，則要求每個資料點必須包括兩個或三個數值。在一個散布圖表中，每個資料點有一個X值和Y值，這兩個值的組合定義了一個二維空間，也就是笛卡兒空間中的點（X,Y）。氣泡圖表增加了第三個數值：一個決定了氣泡以（X,Y）為中心的半徑的氣泡尺寸值。可以選擇使用氣泡的尺寸值代替半徑來代表氣泡的面積。

       當使用盤高－盤低－收盤圖時，情況會更複雜一些，因為在這個圖中又是三個值确定一個資料點，不過它們現在被稱為盤高值，盤低值，和收盤值。一個開盤－盤高－盤低－收盤(OHLC)圖表中每個資料點關聯4個數值，當然，額外的那個數值被稱為開盤值。（這些類型的圖表常常被稱為股票圖表，因為它們通常主要是被用來顯示股票資料。）

       當處理一個極坐标圖時，您需要提供另一組數值：R值和Theta值。R值定義了資料點距圖表中心的距離，Theta值代表了與通過原點的水準線之間的角度。

軸

       您可能非常熟悉“軸”這個術語。在圖表元件中，軸的含義和它在幾何學上的含義基本上是相同的。不過，圖表控件給軸添加了另一層含義，即将軸指定為類别軸或數值軸。在表3－4中，标明了類别軸和數值軸。

表3－4。      柱形圖中的類别和數值軸。

       一個類别軸被分割成多個相等的段――每個代表一個單獨的類别。而資料點被标注在每個類别的中間。對于類别軸來說，不存在最小值和最大值的概念。而另一方面，數值軸因為是一個連續的軸，是以它具有最小值和最大值。在數值軸上，資料點在最小值和最大值之間被标注在它們應該位于的地方。

       因為類别軸和數值軸的位置依賴于不同的圖表類型，是以“類别”和“數值”隻是邏輯的名稱，而不能簡單的直接映射為X軸和Y軸。例如，柱形圖中的類别軸是沿底部(X)水準伸展的，而數值軸(Y)則是位于左方垂直上升的。但是在條形圖中，位置正好相反，類别軸位于左邊，而數值軸位于底部。可以參考“支援的圖表類型”以了解各類型圖表的更多資訊。

縮放比例

       雖然您一定聽說過術語“軸”，但您可能以前并沒有聽說過術語“縮放比例”。雖然您通常隻在數值軸上使用縮放比例，但是其實每個軸都具有縮放比例，縮放比例定義了它對應的軸的度量刻度，決定了軸的最小和最大值。縮放比例也決定了軸上的間隔是線性的，還是對數的。線性尺度将軸分割成從最小值線性增加到最大值的平均的各段（例如，20，40，60，80，100）。對數尺度也将軸分割成平均的各段；但從一個段到下一個段是以對數，而不是線性來遞增的（例如，1，10，100）。

       當本章後面部分讨論分割軸時，以及第6章中，我們會更加詳細的讨論軸的縮放比例，在第6章中，我們還會看到如何通過“放大”來更加詳細的顯示您的資料中的一部分。

趨勢線

       圖表元件支援在您的圖表中為每個系列建立一個趨勢線。和在Excel中一樣，一個趨勢線常被用來顯示各系列中資料的趨勢。如果曆史中的各值一直保持遞增或遞減，那麼如果您需要預測将來會出現哪個值，就可以使用趨勢線來進行趨勢分析和預測。和在Excel中一樣，圖表控件提供幾種不同的趨勢計算方法，包括線性，對數，多項式，指數，以及權。然而，圖表元件不提供Excel中的移動平均數趨勢線類型。圖3－5顯示了一個趨勢圖的例子。

誤差線

       圖3－6顯示了一個誤差線，誤差線是從資料點延伸出去的短線段，它指出了您的資料的某種不确定性，也就是誤內插補點。每個資料點可以顯示一個誤差線，它表明資料點的真實值可能是誤差範圍中的任何一點。

       和趨勢線一樣，誤差線是和系列相關的。系列中的每個資料點都會顯示一個誤差線，而這個系列的誤差線的集合可以用來顯示一個正的誤差總量，一個負的誤差總量，或者同時顯示這兩個。誤差總量可以以百分比的形式（例如+10%, -10%, 或 +/-10%），相對值的形式(例如 +2, -2,或 +/-2)，或者自定義誤差的形式(例如, 對于資料點10來說，上屆是12，下屆是8)來表示。誤差線也可以進行資料綁定，如果進行了資料綁定，則圖表控件将各結果列中的值看作是每個資料點的自定義誤內插補點。雖然Excel圖表也為誤差總量提供了标準誤差和标準偏差選項，但圖表控件本身并不支援這些功能。當然，您可以自己計算這些數值，并在圖表中使用自定義的誤差總量來顯示它們。

              為什麼要定義一個單獨的縮放比例對象？

您可能疑惑于為什麼縮放比例的概念不隻是軸的一部分。畢竟，似乎是縮放比例決定了一個軸的最小和最大值。但是，縮放比例的最小和最大值決定了被稱為繪圖區的可視區域的尺寸。軸顯示标記和标簽，但是是縮放比例為繪圖區的尺度确定了準确的點/值比例。通過将縮放比例從軸上分離出去，圖表控件就能夠支援沒有可視軸的圖表類型。

資料标簽

       資料标簽是位于資料點附近的一小段文字，您可以通過設定它來顯示資料點的值、在系列中所占的百分比、類型名、系列名、或氣泡的尺寸。（參見圖3－7的例子）您可以在一個資料标簽中顯示這些資訊片斷的任意組合。此外，你還可以控制字型，顔色，邊框格式等屬性――甚至可以設定在每個資訊片斷之間使用哪種間隔字元。

圖3－7。      柱形圖中的資料标簽。

       顯然，當需要在您的資料點附近顯示數字，尤其是需要比較那些可能值非常接近的資料點時，資料标簽是十分有用的。在需要顯示一個資料點在整個系列中所占的百分比時（例如在餅圖中），使用資料标簽顯示百分比當然也是很有用的。在沒有類别軸的圖表（例如散布圖和氣泡圖）中使用資料标簽顯示資料點的類别名也是一個很好的顯示額外的類别資訊的方法。

       和趨勢線和誤差線一樣，資料标簽是和系列相關聯的。您不能對一個單獨的資料點進行格式化，隐藏或顯示的操作。所有針對資料标簽的操作都會影響系列中的所有資料點。

支援的圖表類型

       當我在介紹圖表元件時，通常我從一個開發者的口中聽到的第一個問題就是：“它支援那些圖表類型？”圖表類型是圖表生成功能的基礎，是以多多益善。在我的書架上有一本包含各種圖表類型，圖表元素和資訊圖形學技巧的參考書，它幾乎有450頁！（這本書是：《資訊圖形：全面圖解指南[操作圖形，1997]》，和其它任何Edward Tufte的書一樣，對任何從事資訊圖形學的人來說，它都是一個極好的資源。）

       圖形元件的第一個版本包括Excel2000中所有二維圖表類型的集合（不包括等高線類型），另外，還包括極坐标圖，堆積餅圖，以及填充散布圖類型。在這個版本的圖表控件中不包含任何三維圖表類型或效果，也不支援Excel提供的奇特的填充效果。

       讓我們看一些圖表元件支援的圖表類型的例子，并讨論可以使用它們來顯示哪種資料。

柱形圖和條形圖

       柱形圖和條形圖是在商業中最常用的圖表類型，圖表為每個資料點顯示一個從數值軸上的零點延伸到資料點的被填充的條。

       大多數人并不在意柱形圖和條形圖之間的差別――畢竟，它們基本上是相同的，隻是伸展的方法不同。圖形元件使用術語“柱”來描述沿螢幕的上下方向擴充的垂直列，使用術語“條”來描述橫跨螢幕延伸的水準條。表3－8顯示了一個柱形圖和一個條形圖的例子。

圖3－8。      柱形圖（上面）和條形圖。

       這些圖表類型常用于顯示那些包含不需要以某種特定次序顯示的類别的資料。與線圖不同，柱形圖和條形圖并不描述次序或級别的概念。

       和許多圖表類型一樣，柱形圖和條形圖有一些圖表子類型。預設的子類型，叫做簇狀柱形圖或簇狀條形圖，在每個類别中不同系列的标記條緊挨在一起。（圖3－8顯示了一個簇狀柱形圖和一個簇狀條形圖。）當不同的系列之間完全無關或它們不應在界面上被合并時，簇狀子系統是最有用的子系統。例如，如果您在一個系列中标記預算值，而在另一個系列中标注實際值，那麼您不會需要合并這兩種數值。而是會需要将這兩種柱形或條形并排放在一起，以便于比較。

       堆積柱形圖和堆積條形圖以一個堆疊一個的形式顯示各系列。在這種圖表中，柱或條的長度代表了類别對應的所有資料點的總和。表3－9顯示了一個堆積柱形圖的例子。

       圖3－9。 一個堆積柱形圖。

       在顯示那些可以，并應該被合并起來，以便在視覺效果上描述每個類别總和的資料時，堆積柱形圖和堆積條形圖是很有用的。例如，如果您以國家和産品來标記銷售量資訊，您應該需要使用堆積柱形圖來顯示每個國家（類别）所有産品的總銷售量，條仍然根據每個産品的銷售量的準确值來進行分段――即高銷售量的産品會擁有較長的段，而銷售情況不好的産品會擁有較短的段。當不必考慮相對關系，而需要考慮每個類别的總和時堆積圖表十分有用。

       最後，百分比堆積圖表子類型有點象餅圖：貫穿繪畫區畫一個條，或柱，然後将條，或柱根據每個系列的資料點所代表的百分比分割為多個段。這種子圖表類型和堆積子圖表類型的關鍵差別在于：各段的長度是資料點占類别中各資料點總和的百分比，而不是資料點的具體值。因為所有條的長度都相同（100％），是以這樣的圖表不能用來比較不同類别的總和。不過，這種類型的圖表在檢視餅圖顯示的相同類型的資訊時很有用，但與餅圖不同的是，它能同時檢視來自多個類别和系列的資訊。

       這裡描述的大部分其它圖表類型都具有同樣的子類型集合――簇狀，堆積，和百分比堆積。在後面的介紹中，我不再重複定義這三種子類型，不過需要的時候，我會作簡單的說明。請回頭參考本節，以了解這些子類型的介紹，以及哪種類型的資料适合于使用這些子類型顯示。

餅圖，堆積餅圖，和圓環圖

       餅圖在商業圖表中也很常見，它們隻能顯示最低密度的有用資訊，并且也是最低效的，這幾乎是一個極大的缺點。但是，它們的簡潔也使它們非常易于了解，極具說服力。例如，當顯示市場占有率的分類資訊時，極大或極小的一塊所帶來的視覺效果确實是強有力的。圖3－10顯示了一個典型的餅圖。

圖3－10。一個餅圖。

       餅圖最重要的獨特之處，是圖例顯示類别值，而不是系列值。大部分圖表在圖例中顯示各系列，但因為餅圖隻顯示一個系列，是以圖例被用來顯示對應各顔色餅“塊”的類别的标記。

       顯然餅圖在顯示百分比占有率或總量的細目分類資訊時是很有用的。餅圖之是以隻顯示一維資料的原因剛才我們已經提到了，那就是它隻能顯示一個系列的資料點。

       不過，堆積餅圖和圓環圖類型可以同時顯示多個系列，這與百分比堆積柱形圖可以同時顯示多個系列和類别的資料很相似。堆積餅圖和圓環圖之間真正的唯一差別就是圓環圖在中心有一個洞（如果您願意，可以稱為“圓環洞”）。圖3－11描述了這樣的資訊，首先是一個堆積餅圖，其後是一個圓環圖。

       圖3－11。一個堆積餅圖和一個圓環圖。

       我承認這兩個圖表類型有一些奇怪；實際上，我建議隻有在沒有其它圖表類型能夠滿足需要的特殊環境中才使用它們（例如，在環繞爆炸地點的同心環中顯示土壤中化學組成的百分比）。同心環可能會誤導人們，因為它們的相對大小和順序其實是抽象手鋸，并不是基于某種數值的。

折線，平滑折線，以及面積圖表

       折線圖表和面積圖表屬于較為簡單的圖表類型，似乎它們應該常被使用，但遺憾的是，并不是這樣的。圖3－12顯示了一個典型的折線圖和面積圖的樣子。

       折線圖和面積圖在顯示那些類别具有實際意義的次序的資料時十分有用，比如說一系列的日期或時間。例如，對于在一系列的日期上标記銷售量或在一系列小時上标記股票價格的情況，使用折線圖來顯示比使用柱形圖來顯示更有效。這是因為在圖表中的各資料點之間畫上連線使得很容易說明哪兒有一個上升的趨勢，哪兒有一個下降的趨勢。

       折線圖和面積圖之間真正的唯一差別是，在面積圖中，類别軸和折線之間的部分被系列的顔色所填充。對于一個系列的值比另一個系列的值大時發生的覆寫情況，面積圖表處理起來比較困難――除非您使用的是我們前面提到的堆積子類型。因為系列是按照順序依次繪制的，最後的系列會覆寫前面繪制的任何系列。是以隻有在您确認一個系列的值一直都比後續的各系列的值高時，才應該使用不堆積的面積圖。

圖3－12。一個折線圖和一個面積圖。

       和柱形圖、條形圖一樣，折線圖和面積圖也包括簇狀、堆積和百分比堆積子類型。不過，術語“簇狀”通常不用來描述預設的子類型，這些預設子類型隻是簡單的被稱為折線圖和面積圖，而不加任何特别的限定。

       折線圖具有一個柱形圖和條形圖沒有的子類型。折線圖中的折線可以畫成“筆直的”或者“平滑的”。當然，使用平滑線來繪制的圖表被稱為平滑折線圖。在這種圖表類型中，圖表控件使用有弧度的線條進行繪制，以消除參差不齊的峰谷。

散布圖和氣泡圖

       有人認為，散布圖能夠成為比前面所介紹的較簡單的圖表類型更強有力的分析工具，但是不幸的是，它在商業圖表中很少用到。雖然散布圖具有系列和類别，但它也是通過兩個值（而不是一個）來确定一個資料點的位置的。散布圖中的每個資料點都具有一個X值和一個Y值，這兩個值組合起來确定資料點在繪圖區中的位置。

       散布圖和氣泡圖的關鍵差別是氣泡圖中的資料點是具有動态大小的圓形。氣泡圖中的資料點包括名為氣泡尺寸的第三個值，它決定了氣泡的半徑或面積。圖3－13顯示了典型的散布圖和氣泡圖。

圖3－13。一個散布圖和一個氣泡圖。

       在比較兩個不同的值以揭示互相關系或配置設定結構時散布圖和氣泡圖十分有用。例如，使用散布圖繪制一個部門的士氣高低與部門收益之間的關系可以揭示一個明顯的相關性：即高昂的士氣通常會增加部門的收益。（至少，大部分雇員願意相信它。）（？？）

       散布圖包含一些獨特的子類型。預設的子類型叫做标記散布圖，它使用标記點（例如菱形或正方形這樣的小幾何形狀）來代表資料點。不同的形狀常用來代表不同的系列。您也可以選擇使用平滑或者筆直的線段來連接配接每個系列中的标記點。還有，您還可以選擇完全沒有任何标記點的線段。圖表元件包括一個Excel中完全沒有包含的子類型。這個子類型允許您填充由資料點和連接配接線組成的多邊形，這樣就形成了“填充散布圖”。

填充散布圖的樂趣

您可以嘗試一下随書CD光牒中一個相當有創造性的填充散布圖的示範例子。打開檔案夾Chap03夏的DrawWithChart.htm檔案，點選圖表界面，以建立一個填充多邊形的各點。然後輕按兩下滑鼠完成這個多邊形。這個示範例子是由Jeff Couckuyt編寫的，他是一位極為優秀的圖表元件開發者，這個例子僅僅使用了圖表控件和一個填充散布圖表類型。

       記住氣泡圖表有和面積圖表相同的覆寫的問題。一個大的氣泡會隐藏任何它下方的資料點，是以隻有在您确定覆寫的可能性很小的時候才使用氣泡圖表，否則，請考慮将氣泡填充為透明的。

雷達圖表

       雷達圖似乎在美國并不怎麼使用，但我知道在亞洲國家常用它來描繪例如食品中的營養成分這樣的資料。圖3－14顯示了這個有趣和有用的圖表類型的樣子。

圖3－14。一個雷達圖。

       雷達圖和其它簡單圖表類型一樣，具有類别，系列和數值。但是，雷達圖在一個環繞圖表的圓環中繪制類别名稱，并包括從圖表的中心延伸導每個類别名稱的輪輻。每個輪輻就是一個數值軸。每個類别的各資料點在相應輪輻上的相應點上，以相應的系列顔色進行繪制。

然後圖表将各系列中的各資料點用線段連接配接起來，還可以選擇将線段和原點之間的區域使用系列的顔色進行填充。填充雷達圖表具有面積圖和氣泡圖具有的相同的老問題，是以要小心使用填充子類型，除非您确定您的資料不會造成覆寫（或者您根本不關心這個問題）。

       雷達圖也支援我們前面提到的平滑線子類型。另外，您還可以選擇在平滑線和直線子類型上繪制資料點的标記。

盤高－盤低－收盤圖和開盤－盤高－盤低－收盤圖

       需要顯示股票和金融證券資訊的人會對這兩種圖表類型感興趣。盤高－盤低－收盤圖類型(HLC圖表)為每個類别顯示一個線段。每個線段從盤低值延伸到盤高值，并且專門通過一個小核對符号來訓示收盤值。圖3－15顯示了一個HLC圖表的例子。

圖3－15。一個盤高－盤低－收盤圖。

       在任何時候，如果您的資料中每個給定時段包含一列資料，并且在這列資料中有一個特别的數值需要被标記，那麼您應該考慮使用這個圖表類型。例如，您可以使用HLC圖表來顯示一段時間内的溫度讀數。

       開盤－盤高－盤低－收盤圖（OHLC圖）在HLC圖的資料上添加了一段額外的資訊：開盤值。該圖表使用一個在開盤值和收盤值之間填充的矩形來顯示開盤值，如圖3－16所示。

       填充的矩形既可以是系列的顔色，也可以是黑色，這依賴與收盤值和開盤值之間的內插補點是正還是負。正的內插補點會顯示正常的系列顔色，而負的內插補點會顯示黑色。這就向觀察者顯示了某段時間内值發生了升高還是降低。這樣他或她就能确定數值是變好還是變壞了，以及變好和變壞的程度任何。

圖3－16。開盤－盤高－盤低－收盤圖。

       HLC和OHLC圖表類型依然保留了系列的概念。但是，在HLC圖表類型中，圖表元件在同一水準位置上放置不同的系列。換句話說，控件會在同一個地方重複的繪制各個系列。通常，這個圖表類型在資料點隻有一個系列時非常有用。另一方面，OHLC圖表能夠管理多個系列，并将多個系列的條緊挨着進行繪制。

極坐标圖

       極坐标圖形類型是Chart元件中出現的一個新圖表類型，Excel圖表功能中并不包含它。極坐标圖，這個微軟的Office使用者一直以來都在要求的圖表，在顯示角度和距離之間的關系時十分有用。極坐标圖表常用于音頻領域和無線電領域中，例如，可以用來顯示麥克風的拾音器的強度和方向。圖3－17描述了一個典型的極坐标圖表。

圖3－17。極坐标圖。

       極坐标圖表包括平滑線子類型，并且您可以選擇是否線上上顯示資料點的标記。

組合圖表

       在本章開始處我們提到過，圖表生成最大的秘密之一就是一個圖表實際上并不包括一個圖表類型。而是每個單獨的系列包含一個圖表類型。如果一個圖表中的所有系列碰巧包含的都是相同的類型，那麼Chart對象的Type屬性會傳回這個類型。但是，您可以使用這個特性來建立更加複雜的組合圖表，在其中，您可以将一些系列繪制成柱形或條形，而将另一些系列繪制成線性。

       不是所有的圖表類型都能夠被組合，在這個版本的Office中，圖表元件隻允許您對柱形，折線形，和面積圖表類型進行組合。顯示商業資料時最常用的組合圖表是柱形圖和折線圖的組合。

裝載資料

       您已經了解了各種圖表元素的名稱，以及圖表控件能夠顯示那些圖表類型，現在您需要學習如何将資料裝載到圖表中。和大多數Offic Web元件一樣，圖表控件可以從多種資料源中裝載資料，并且裝載的過程可以在設計環境中使用圖表向導來完成，或通過編寫代碼來完成。圖表元件可以綁定到所有其它的Office Web元件上――電子表格元件，透視表元件，以及資料源元件――還有所有實作了IDataSource接口（這個一個微軟IE和VB中的資料源控件所具有的标準接口，在微軟開發者網絡圖書館和OLE DB SDK中有關于它的文檔）的其它控件，ADO的Recordset對象，甚至是文本資料的數組或使用分隔符分隔的一系列資料組成的字元串。

       将資料裝載到圖表控件中的常用方法是：告訴圖表應該從哪裡獲得資料，以及資料源的哪部分應該用于目前圖表類型所需要的系列，類别和值。在程式設計模型中，圖表控件将這些可以綁定到資料源的某部分的圖表元素稱為dimensions。當綁定到一個電子表格時，您指定的資料源的“部分”就是一個range的引用，例如A1:C1。如果綁定到一個OLE DB資料源時，您就應指定使用結果集中的哪一個列名或者列的下标。而對于透視控件，您應指定使用哪一個透視軸。（我們将在第4章中進一步讨論這個問題），而對于文本資料來說，隻有一種“部分”存在：數組或具有分隔符的字元串本身。

       圖表向導通過為您提供一個簡單的指定這種綁定資訊的使用者界面，幫您完成了大部分的綁定工作。但實際上圖表向導隻是調用了圖表控件的公共程式設計模型，是以如果通過編寫代碼，您能夠完成任何圖表向導的功能，并能完成圖表向導所不具備的功能。圖表向導的使用是相當明白的，它的線上幫助也包含了大部分的使用方法。因為本書主要着眼于使用Office Web元件開發定制的解決方案，是以我不會在這裡詳細介紹如何使用圖表向導。而會深入到那些當通過程式設計将資料裝載到圖表中時您必須編寫的代碼中去。

使用文本資料裝載圖表

       下面的子過程，取自随書CD光牒中Chap03檔案夾下的LoadFromLiteral.htm檔案，它顯示了如何将文本資料裝載到圖表控件中：

'------------------------------------------------------------------------

' LoadChartWithLiteral()

' 目的: 将文本資料裝載到圖表中

' In:      cspace        ChartSpace對象的引用

'          vSeries       變量數組或以tab分割的字元串，代表各系列名。

'          vCategories   變量數組或以tab分割的字元串，代表各類别名。

'          avValues      變量二維數組或以tab分割的字元串，代表各數值；

'                        外圍數組中的一項代表一個系列。

'

Sub LoadChartWithLiteral(cspace, vSeries, vCategories, avValues)

    ' 局部變量

    Dim cht    ' 我們将在圖表空間中建立的Chart對象。

    Dim ser    ' 臨時系列

' 獲得常量對象以便我們可以在腳本中使用常量名稱。注意：隻是在VBScript中需要這樣做，

' 而在VBA中并不需要。

    Set c = cspace.Constants

' 清除圖表空間中。

cspace.Clear

    ' 在圖表空間中建立一個圖表。

    Set cht = cspace.Charts.Add()

    cht.HasLegend = True

    ' 現在調用SetData函數來綁定各個dimensions

' 第二個參數是c.chDataLiteral, 說明最後一個參數是一個變量數組或是一個

    ' 以tab分隔的字元串。

    cht.SetData c.chDimSeriesNames, c.chDataLiteral, vSeries

    cht.SetData c.chDimCategories, c.chDataLiteral, vCategories

    ' 當使用文本資料裝載圖表時，您必須使用數值分别裝載每個系列。

    For each ser In cht.SeriesCollection

        ser.SetData c.chDimValues, c.chDataLiteral, avValues(ser.Index)

    Next 'ser

End Sub 'LoadChartWithLiteral()

       當使用文本資料裝載圖表時，資料既可以包含在變量數組中，也可以包含在以tab分隔的字元串中，每一個元素或标記都代表了一個不同的數值。在随書CD光牒的檔案中，我使用了Array函數将文本資料以數組的形式進行傳遞。在微軟的VBScript和VBA中都支援這個函數。

       SetData方法被用來傳遞文本資料，但是請注意第二個參數（通常是資料源的下标）是常量chDataLiteral。這個等于-1的常量，告訴圖表下一個參數是文本資料，而不是一個資料源的一部分。

       還要注意的是，在将文本資料傳遞給圖表時您必須使用Wcseries對象（代表一個系列的對象）的SetData方法。因為一個圖表控件自身隻能接受一維數組的值，如果允許您将文本資料傳遞給WCChart對象（代表一個圖表的對象）的SetData方法，它将無法判斷一個值應該屬于哪一個系列。前面的過程通過簡單的周遊系列集合，并将相應的數組的值傳遞給目前系列的SetData方法來解決了這個問題。

綁定到電子表格元件

       下面的子過程，來自随書CD光牒中Chap03子目錄下的LoafFromSpreadsheet.htm檔案，它示範了如何将圖表元件綁定到電子表格元件的區域上。

'-------------------------------------------------------------------------

' BindChartToSpreadsheet()

'

' 目的: 将一個圖表綁定到源電子表格中的指定區域上。

' 傳入參數:cspace           ChartSpace對象的引用

'          sheet            Spreadsheet對象的引用

'          srngSeries       字元串區域的引用，其中定義了系列的名稱

'          srngCategories   字元串區域的引用，其中定義了類别的名稱

'          srngValues       字元串區域的引用，其中包括了各個數值

'

Sub BindChartToSpreadsheet(cspace, sheet, srngSeries, srngCategories, _

                           srngValues, fSeriesInCols)

    ' 局部變量

    Dim cht          ' 将在圖表空間中建立的Chart對象

    Dim ser          ' 臨時系列

    Dim rngValues    ' 存儲各數值的Range對象

    Set c = cspace.Constants

    cspace.Clear

    ' 首先告訴圖表它的資料來自于電子表格

    Set cspace.DataSource = sheet

    ' 在圖表空間中建立一個圖表。

    Set cht = cspace.Charts.Add()

    cht.HasLegend = True

    ' 現在調用SetData方法來綁定各個dimensions

    ' 第二個參數是0，說明如果有多個資料源，應該使用第一個資料源。

    cht.SetData c.chDimSeriesNames, 0, srngSeries

    cht.SetData c.chDimCategories, 0, srngCategories

    ' 電子表格子隻能綁定到一維區域上，

    ' 是以周遊系列集合，并單獨為每個系列設定數值。

    Set rngValues = sheet.Range(srngValues)

    For Each ser In cht.SeriesCollection

        If fSeriesInCols Then

            ser.SetData c.chDimValues, 0, _

                rngValues.Columns(ser.Index + 1).Address

        Else

            ser.SetData c.chDimValues, 0, _

                rngValues.Rows(ser.Index + 1).Address

        End If

    Next 'ser

End Sub 'BindChartToSpreadsheet()

       現在說明這個例子的一些相關資訊。首先，為了将圖表和電子表格空間綁定起來，我将圖表控件的DataSource屬性設定為電子表格控件的執行個體。在接收到指向電子表格控件執行個體的指針後，圖表控件就可以向電子表格控件請求某個區域中的單元格的數值。

       第二，使用SetData方法首先綁定系列名稱維，然後是類别維。您必須使用這個順序來綁定圖表的各維：先是系列名稱，然後是類别名稱，最後是數值。在一些少見的例子中，您隻有一個系列的資訊，你可以跳過設定系列名稱維，而隻綁定類别和數值。這樣做會使控件使用預設的名稱“Series”來為您建立一個系列，您可以通過設定WCSeries對象的Name或Caption屬性來改變這個名稱。

       我要談到的最後一點是，當綁定到電子表格元件上時，Chart元件需要接收到每個系列的準确的區域引用。遺憾的是，您不能将一個二維區域的數值傳遞給圖表，并讓它自動判斷各數值屬于那一個系列和類别。您必須将一個一維區域的引用傳遞給每個WCSeries對象的SetData方法的最後一個參數。前面的例子使用一種常用的方式來完成這個任務：使用電子表格元件的Range對象來獲得在二維區域中每列或每行的一個區域引用。并依次将它傳遞給各系列的SetData方法。标志fSeriesInCols說明一個指定系列的各數值是在電子表格中按列排列還是按行排列。如果标志為真，将使用列的集合；如果為假，将使用行的集合。

       還需要說明的是，例子中在系列的下标值上加上1。這是因為為了保持與Excel程式設計模型的相容性，列和行集合的下标是以1為基礎的。而圖表元件的系列的下标是以0為基礎的。将系列的下标加1，進而獲得相對應的區域中列或行的下标。

綁定到資料源元件

       下面的方法，來自随書CD光牒中Chap03子目錄下的LoadFromDSC.htm檔案，它示範了如何将圖表元件綁定到從資料源元件(DSC)傳回的一個記錄集上。（我們将在第5章中詳細的讨論DSC。）

'------------------------------------------------------------------------

' BindChartToDSC()

'

' 目的: 将圖表綁定到資料源元件的一個記錄集上。（這個例子建立一個餅圖。）

' 輸入參數:cspace        ChartSpace對象的引用

'          dsc           資料源控件的引用

'          sRSName       綁定到的資料源控件中記錄集的名字

'          sCategories   包含類别的結果列的名稱

'          sValues       包含數值的結果列的名稱

'

Sub BindChartToDSC(cspace, dsc, sRSName, sCategories, sValues)

    ' 局部變量

    Dim cht    ' 将在圖示空間中建立的Chart對象

    Dim ser    ' 臨時系列

    Set c = cspace.Constants

    cspace.Clear

    ' 首先告訴圖表它的資料來自資料源控件。

    Set cspace.DataSource = dsc

    ' 接着告訴它資料源控件中它将會被綁定到的記錄集

    cspace.DataMember = sRSName

    ' 在圖表空間中建立一個餅圖。

    Set cht = cspace.Charts.Add()

    cht.HasLegend = True

    cht.Type = c.chChartTypePie

    ' 現在調用SetData函數綁定各維，第二個參數為0，說明如果有多個資料源，應該使用第一個

    ' 在這個餅圖的例子中，我們将手工添加一個系列，并使用這個系列的SetData方法

    Set ser = cht.SeriesCollection.Add()

    ser.SetData c.chDimCategories, 0, sCategories

    ser.SetData c.chDimValues, 0, sValues

    ' 最後，為這個例子添加一些資料标簽，因為它是一個餅圖。

    Set dls = ser.DataLabelsCollection.Add()

    dls.HasPercentage = True

    dls.HasValue = False

End Sub 'BindChartToDSC()

       DSC既可以從圖示元件和透視元件中提取資料，也可以為二者提供資料。它也實作了Visual Basic和IE的資料源控件所實作的相同的資料源COM接口（IDataSource），是以，這段代碼可以被這些環境中的任何其它有效的資料源控件所使用。

       ChartSpace對象的DataSource和DataMember屬性是這個例子的基礎。因為他們是由Visual Basic和IE所建立起的資料綁定标準的一部分，是以你常會在這些環境中的其它資料綁定控件中看到它們。DataSource屬性被設定為隻想提供資料的控件（在這個例子中是DSC），而DataMember屬性被設定為命名所需要的特定資料集的字元串。因為一個DSC可以同時暴露多個資料集，是以DataMember屬性被用來告訴圖表控件應該請求那個資料集。如果您不設定DataMember屬性。圖示控件會請求預設的資料集，具體哪個是預設資料集，是由DSC決定的。

       本例中使用SetData方法的方式與在BindChartToSpreadsheet方法中使用SetData方法的方式極為相似，除了函數調用中的“資料部分”（SetData函數的最後一個參數）現在變成了DSC傳回的記錄集中一個列的列名。資料部分還可以是列的列下标（0，1，2，等等）。圖表控件将把資料部分的值傳給給ADO的Fields集合對象的Item方法，是以任何這個Item方法認為有效的參數都可以在SetData方法的最後一個參數中被使用。一般來說，如果您認為列名不會在将來改變，但列的順序會在将來改變，那麼您應該使用列名，而如果您認為将來列名會改變，而列的順序不會變化，那麼應該使用列的下标。

       前面提到過，餅圖的一個有趣的特性是圖例中的項是類别，而不是系列。是以，當綁定一個餅圖時，您應該将您希望顯示在圖例中的列綁定到類别維上，而不是綁定到系列維上。這也适用于堆積餅圖和圓環圖。

       本例中最後一段代碼建立了一些資料标簽（我們剛剛讨論過）來顯示每個資料點在總體中所占的百分比。圖表控件可以為每個資料點顯示許多數值，預設是顯示實際的數字數值。因為我隻希望顯示每一片所占地百分比，是以我将HasPercentage屬性設為True，而将HasValue屬性設為False。

       雖然這個特定的例子建立的是一個餅圖，但是您可以在綁定到DSC上時使用任何支援的圖表類型。我選擇在這個例子中建立餅圖隻是為了說明在使用餅圖時是如何實作綁定的。

綁定到資料集

       下面的函數，來自随書CD光牒中Chap03子目錄下的LoadFromRecordset.htm檔案，它示範了如何将一個圖表控件綁定到一個ADO記錄集對象上：

'------------------------------------------------------------------------

' BindChartToRecordset()

'

' 目的: 将一個圖表綁定到一個記錄集上（本例中建立的是一個散布圖。）

' 傳入參數:cspace        ChartSpace對象的引用

'          rst           要綁定到的Recordset對象的引用

'          sfldSeries    系列的字段名

'          sfldXValues   包含X數值的字段名

'          sfldYValues   包含Y數值的字段名

'

Sub BindChartToRecordset(cspace, rst, sfldSeries, sfldXValues, _

    sfldYValues)

    ' Local variables

    Dim cht    ' Chart object that we'll create in the chart space

    Dim ser    ' Temporary series pointer

    Dim ax     ' Temporary axis pointer

    ' Grab the Constants object so that we can use constant names in

    ' the script. Note: This is needed only in VBScript -- do not include

    ' this in VBA code.

    Set c = cspace.Constants

    ' Clear out anything that is in the chart space

    cspace.Clear

    ' First tell the chart that its data is coming from the Recordset

    Set cspace.DataSource = rst

    ' Create a Scatter chart in the chart space

    Set cht = cspace.Charts.Add()

    cht.HasLegend = True

    cht.Type = c.chChartTypeScatterMarkers

    ' Now call SetData to bind the various dimensions

    ' Second parameter is zero, meaning the first data source should be

    ' used if there are multiple data sources

    ' In this example of a Scatter chart, we will bind

    ' two value dimensions: X and Y

    cht.SetData c.chDimSeriesNames, 0, sfldSeries

    cht.SetData c.chDimXValues, 0, sfldXValues

    cht.SetData c.chDimYValues, 0, sfldYValues

    ' Finally, let's add some axis labels using

    ' the column names as the axis captions

    Set ax = cht.Axes(c.chAxisPositionBottom)

    ax.HasTitle = True

    ax.Title.Caption = sfldXValues

    ax.Title.Font.Name = "Tahoma"

    ax.Title.Font.Size = 8

    ax.Title.Font.Bold = True

    ax.NumberFormat = "#,##0"

    Set ax = cht.Axes(c.chAxisPositionLeft)

    ax.HasTitle = True

    ax.Title.Caption = sfldYValues

    ax.Title.Font.Name = "Tahoma"

    ax.Title.Font.Size = 8

    ax.Title.Font.Bold = True

    ax.NumberFormat = "$#,##0"

    ' Let's also set the marker size a bit smaller than normal

    For Each ser In cht.SeriesCollection

        ser.Marker.Size = 5

    Next 'ser

End Sub 'BindChartToRecordset()

       毫無疑問，您會立刻注意到這個例子除了使用的是散布圖之外，它和前面包括DSC的例子很相似，這是因為在Visual Basic和IE中ADO的記錄集對象本身就是一個有效的資料源。它也實作了與DSC相同的資料源接口。這使得可以采用和使用DSC時設定圖表控件的DataSource屬性一樣的方式将控件的DataSource屬性指向記錄集對象。但是，記錄集對象被定義為隻能暴露一個資料集，是以在綁定到記錄集上時不需要設定DataMember屬性。

       因為在本例中使用的圖表類型是散布圖，是以代碼設定了兩個數值維：X值和Y值。在前面“支援的圖表類型”一節中講到過，散布圖對每個資料點使用兩個數值，是以我們需要将X數值維綁定到記錄集中的一列上，而将Y數值維綁定到另一列上。（您可以将它們都綁定到同一列上，但是這樣會産生一個高度相關的散布圖！）

良好架構造成的一次意外

當我将本章交給圖表元件開發者們浏覽時，一位開發者提出，實際上元件并不“支援”從記錄集中加載，而且OWC小組也沒有正式測試過這個功能。但我們在内部編寫的幾乎所有示範和實際的頁面都使用了這個方法，是以，實際上這個功能是相當穩定的。

将記錄集裝載到圖表元件中能夠正常工作的原因是良好架構造成的一次意外。當Visual Basic和IE開發小組選擇了标準的資料源接口（IDataSource）時，ADO小組認為讓Recordset對象實作這個接口是很有意義的，因為這樣它就能從GetDataMember方法中友善的傳回底層的IRowset接口。因為圖表控件在從DSC中裝載資料時使用IDataSource接口，是以Recordset對象正好能夠很好的工作。對于圖表控件來說，記錄集對象和其它資料源控件是完全一樣。

       當綁定到記錄集對象時，您必須确定記錄集正在使用微軟Windows遊标引擎（WCE）或者能夠被浏覽和遊曆。WCE是一個ADO元件，它能夠在任何OLE DB 記錄集上提供了浏覽、排序、過濾和其它功能，而不必考慮記錄集的來源或原始功能。為了使用這個引擎，需将您的ADO連接配接或記錄集的CursorLocation屬性設定為adUseClient，如果您所使用的環境不能識别這個常量，可以使用它的值――3來代替。為了保證圖表控件能夠在記錄集中進行遊曆，您可以使用adOpenStatic遊标類型，它的值也是3。在記錄集的Open方法的CursorType參數中，或Recordset對象的CursotType屬性中使用adOpenStatic或者3（第4章中會詳細讨論WCE）。

       請浏覽随書CD光牒中LoadFromRecordset.htm檔案裡的完整源碼清單來檢視設定這個必須設定的記錄集屬性的例子。當浏覽這個檔案時，注意例子使用列名作為軸的标題。散布圖具有兩個數值軸（X軸和Y軸），是以您應該為這些軸設定标題來解釋您在這些軸上顯示的是什麼數值。使用記錄集中的列名來标注這些軸是一個簡便的方法；當然，如果記錄集中的列名難以了解，您可以使用您自己的标題來進行設定。

綁定到透視元件

       圖表元件最後一個可能的資料來源是透視元件。（在下一章中将詳細的讨論這個元件。）和您所猜想的一樣，這個元件也實作了與VB和IE中所有有效的資料源所暴露的資料源接口相同的接口，是以下面這個從随書CD光牒的Chap03檔案夾下的LoadFromPivot.htm檔案中取出的例子，和我們前面所讨論的DSC的例子很相似，但是也有一些重要的不同：

'------------------------------------------------------------------------

' BindChartToPivot()

'

' Purpose: Binds a chart to a PivotTable component

' In:      cspace          reference to the ChartSpace object

'          ptable          reference to the PivotTable object

'          fSeriesInCols   Boolean flag indicating whether the series

'                          of the chart should come from the column

'                          axis or the row axis of the PivotTable control

'

Sub BindChartToPivot(cspace, ptable, fSeriesInCols)

    ' Local variables

    Dim cht    ' Chart object that we'll create in the chart space

    Dim ax     ' Temporary axis reference

    Dim fnt    ' Temporary font reference

    ' Grab the Constants object so that we can use constant names in

    ' the script. Note: This is needed only in VBScript -- do not include

    ' this in VBA code.

    Set c = cspace.Constants

    ' Clear out anything that is in the chart space

    cspace.Clear

    ' First tell the chart that its data is coming from the

    ' PivotTable component

    Set cspace.DataSource = ptable

    ' Create a chart in the chart space

    Set cht = cspace.Charts.Add()

    cht.HasLegend = True

    cht.Type = c.chChartTypeBarClustered

    ' Now call SetData to bind the various dimensions

    ' Second parameter is zero, meaning the first data source should be

    ' used if there are multiple data sources

    If fSeriesInCols Then

        cht.SetData c.chDimSeriesNames, 0, c.chPivotColumns

        cht.SetData c.chDimCategories, 0, c.chPivotRows

    Else

        cht.SetData c.chDimSeriesNames, 0, c.chPivotRows

        cht.SetData c.chDimCategories, 0, c.chPivotColumns

    End If 'fSeriesInCols

    ' Set the values dimension. The value

    ' you pass for the data reference (the last parameter)

    ' is the index of the total you want to use.

    ' Since there is only one total in this example,

    ' we pass zero, indicating the first one.

    cht.SetData c.chDimValues, 0, 0

    ' Finally, let's add an axis title to the value

    ' axis, using the label on the pivot total

    ' as the caption, and set the number format

    Set ax = cht.Axes(c.chAxisPositionBottom)

    ax.HasTitle = True

    ax.Title.Caption = ptable.ActiveView.DataAxis.Totals(0).Caption

    Set fnt = ax.Title.Font

    fnt.Name = "Tahoma"

    fnt.Size = 8

    fnt.Bold = True

    ax.NumberFormat = ptable.ActiveView.DataAxis.Totals(0).NumberFormat

End Sub 'BindChartToPivot()

       和前面的記錄集合DSC例子一樣，為了将圖表控件綁定到透視控件，首先您将圖表控件的DataSource屬性設者為指向透視表控件的一個執行個體。和記錄集的例子一樣，透視表控件隻暴露一個資料集，是以您不必改變DataMember屬性的預設設定。

       本例和DSC的例子的關鍵差別是用于SetData方法最後一個參數的特定常量。您應該記得，這個最後參數定義了您需要将哪部分的資料綁定到指定的圖表維。在透視表控件中，有效的邏輯部分是行和列的透視軸，以及試圖中的所有總和。在透視表控件中，行軸指顯示在表左下方的所有标簽，而列軸指顯示在橫穿表頂部的所有标簽（下一章将更多的講述這些軸）。圖表控件都必須知道的所有資訊就是：您需要将哪個軸綁定到系列名稱維上，哪個軸綁定到類别維上。

       本例中綁定圖表控件的各個值維的方式有一些不同。因為一個透視表報表能夠同時顯示許多總和值，是以圖表控件必須知道您需要将哪個總和用于指定的值維。這是通過将一個總和的下标傳遞給SetData方法的最後一個參數來指明的。在BindChartToPivot例子中，我們使用了簇形柱形圖，是以每個資料點隻有一個數值。我們告訴圖表控件使用下标0處的總和（即第一個，因為是從0開始的）作為資料點的數值。注意這是在透視表控件的目前視圖中有效總和中的下标――而不是資料源中所有可能的總和。

       但透視表控件确實實作了一個資料源控件所需要的标準COM接口，它隻是不知道如何傳回ADO記錄集和OLE DB行集合。圖表控件中包含了專門的代碼，以實作讀取透視表控件中顯示的交叉資料，并恰當地忽略了子總計和外部總計，如果使用這些總計，将會扭曲圖表的刻度，并不是所有的資料綁定控件都能将透視表控件用作它們的資料源。

圖表和軸的标題

       圖表因為以易于了解，可視的方式顯示大量的資訊，而成為一種高效的機制。但是，繪制在圖表中的資料幾乎不能自我描述；是以您經常需要為您的圖表和在圖表中顯示的軸添加一個描述性的标題。

為多值的圖表使用多個總和

       散布圖、氣泡圖、極坐标圖，和OHLC圖表類型都有一個相同的地方：它們需要使用多于一個的數值來确定圖表中的一個資料點。散布圖需要X值和Y值，氣泡圖也需要這兩個值，還有一個氣泡尺寸值，極坐标圖需要Theta和R值，而OHLC圖，就像它的名稱所表明的一樣，需要4個數值。因為透視表報表一次可以顯示多個總和，是以常常希望将這些總和映射為不同的圖表數值，以便形成一個單個資料點。

       您可以通過在SetData方法最後一個參數中指定透視表視圖中總和的下标來完成這個任務。例如，為了将第一個總和綁定到散布圖的X上，而将第二個總和綁定到Y上，您應該書寫這樣的代碼：

cht.SetData c.chDimXValues, 0, 0    ' First total

cht.SetData c.chDimYValues, 0, 1    ' Second total

       有時您需要在一個資料點隻使用一個數值的圖表類型中使用這些多個總和，例如堆積柱形圖。在這種情況下，您可能希望将總和的标題顯示為一個嵌套類别或一個嵌套系列的标簽。您可以使用一個特殊的常量來完成這個任務：

cht.SetData c.chDimCategories, 0, c.chPivotRowAggregates

       這個設定将使用顯示在行軸上的任何總和标題作為分級的類别軸的嵌套類别名稱。為了得到您需要的結果，您還應該将PivotView對象的TotalsOrientation屬性設定為plTotalOrientationRow常量，以便總和的标題顯示在每一行上。

       可能您需要将多個總和在圖表中顯示為不同的系列。為了完成這個任務，将PivotTable報表上的列軸保持為空，添加上您需要顯示為不同的系列的總和，然後書寫下面的代碼：

cht.SetData c.chDimSeriesNames, 0, c.chPivotColAggregates

       這段代碼将在圖表中為PivotTable報表中的每個總和建立一個系列。請參考随書CD光牒中Chap03檔案夾下的PivotTotalsAsSeries.htm例子檔案，并檢視它的具體做法。

       如果您将多個圖表添加到圖表控件中（在“圖表空間中的多個圖表”一節中會詳細介紹），您可以為每個圖表設定它們自己的标題。實際上，您可以為圖表控件自身設定一個全局的标題，他會顯示在所有單獨的圖表的上方。可以使用所有的基礎字型屬性（字型名稱，大小，是否黑體，是否斜體，是否具有下劃線，顔色）來格式化圖表标題。您也可以設定它們的背景色，或者讓它們保持透明。對于軸标題也是一樣的。

       預設情況下，新建立的圖表不會具有圖表标題和軸标題。您可以在設計階段使用屬性工具箱來添加圖表标題和軸标題，也可以使用代碼在運作階段設定。下面這段來自随書CD光牒Chap03檔案夾下的AddTitles.htm檔案的方法，示範了如何添加一個圖表标題：

'------------------------------------------------------------------------

' SetChartTitle()

'

' Purpose: Sets the chart's title

' In:      cht      reference to a chart

'          sTitle   new title caption

'

Sub SetChartTitle(cht, sTitle)

    Dim fnt    ' Temporary font reference

    ' If the title is nonblank

    If Len(sTitle) > 0 Then

        ' Add a title if necessary

        cht.HasTitle = True

        ' Set the caption and its font formatting

        cht.Title.Caption = sTitle

        Set fnt = cht.Title.Font

        fnt.Name = "Tahoma"

        fnt.Size = 10

        fnt.Bold = True

    Else

        ' Title is blank. Remove it.

        cht.HasTitle = False   

    End if

End Sub 'SetChartTitle()

       您可以使用與添加圖表标題相同的方法添加軸标題，除了一個差別：方法的第一個參數接受一個Axis對象的引用，而不是一個圖表對象的引用。

       還要注意您既可以将标題的字型或者背景的Color屬性設定為一個RGB顔色值，也可以将它設定為一個IE的顔色名稱――例如，“FireBrick”或者“PapayaWhip”。這也适用于圖表控件中任何使用顔色的地方，例如圖表的背景色，繪圖區的顔色，系列的顔色，等等。

軸标簽

       預設情況下，圖表控件會在您所有軸上設定标簽，以便顯示資料點位于刻度上的何處（值軸），或資料點屬于那個類别（類别軸）。您可能需要在運作階段使用代碼，或在設計階段使用屬性工具箱調整這些标簽的一些外觀。

       對于值軸來說，标簽顯示了軸上全部值刻度的數字點。這些數字最初都沒有被格式化，除非資料源是一個單元格包含了明确的數字格式化資訊的電子表格。為了改變預設的數字格式，可以将軸的NumberFormat屬性設定為一個新的數字格式名稱或者格式字元串。您可以使用的格式名稱的清單位于電子表格控件的幫助檔案（通過點選電子表格上的幫助按鈕可以打開這個檔案）中的“電子表格中的數字格式化”主題下。您也可以象在Excel中一樣建立一個自定義的格式字元串，在自定義數字格式中可以使用的符号可以在Excel2000的幫助檔案中找到，相關的概要主題标題是“建立一個自定義的數字格式。”

       對于值軸和類别軸，您都可以選擇丢棄一些标簽――例如，每五個标簽中隻顯示第五個。這對于那些包含太多标簽的軸來說很有用，可以使軸上的标簽不至于重疊，還有對于那些可以從保留的标簽中推導出那些被丢棄的标簽的圖表來說，這也很有用，例如，一系列的日期。 類别軸預設是不取消标簽的。但是假設您在類别軸上包含一大組的時間。您可以通過設定相應WCAxis對象的TickLabelSpacing屬性來選擇取消一部分标簽，即可以采用在設計階段選擇标簽并使用屬性工具箱的方式來完成，也可以通過運作階段的代碼來完成。這個屬性的設定決定了在顯示的标簽之間要跳過多少标簽。注意這個屬性隻影響标簽，這意味着軸仍然會在标簽應該顯示的地方顯示标記符号。不過，您也可以通過将TickMarkSpacing屬性調整為TickLabelSpacing屬性相同的值來減少一些标記符号。

圖例

       預設情況下，新建立的圖表不包含圖例。如果您計劃顯示多個系列的資料點，您可能需要為您的圖表添加一個圖例，以說明哪種顔色對應哪個系列。既可以在設計階段通過選擇圖表，并使用屬性工具框來添加圖例，也可以在運作階段通過代碼将WCChart對象的HasLegend屬性設定為True來添加圖例。

       最初，圖例預設為每個系列包含一項，但是您可以改變它。為了隐藏一個圖例的項，使用WCLegend對象的LegendEntries集合來獲得您需要隐藏的圖例項，然後将獲得的對象的Visible屬性設為False。在設計階段，您可以簡單的選擇圖例項，并通過按Delete鍵來删除它。

       如果您在圖表控件中包含了多個圖表，您可以為每個單獨的圖表建立一個圖例，或者您也可以為整個控件建立一個圖例。例如，如果您在顯示多個餅圖，可能為所有的餅圖隻顯示一個圖例更有意義，因為顔色/類别的映射對每個圖表都是一樣的。如果一組系列在各圖表之間的顯示是不相同的，那麼這些在圖例中顯示系列的圖表類型能從獨立的圖例中獲益。

       對于在圖例中顯示系列的多個圖表來說（除了餅圖，堆積餅圖和圓環圖之外的圖表），為它們使用同一個圖例有一個相當惱人的問題：圖表控件在初始化圖例時，會為每個圖表中每個出現的系列在圖例中添加一項。如果您在五個圖表中顯示兩個系列，圖表空間圖例會包含10個圖例項，相同的兩項重複了五次。删除額外的圖例項的方法和前面提到的一樣，可以在設計階段選擇并删除它們，或在代碼中使用WCLegendEntry對象的Visible屬性。

附錄：英文原文

Chapter 3

The Chart Component

Now that you have an understanding of the Spreadsheet component, let's examine the functionality and programming model of the Chart component. As in the previous chapter, after describing a given feature, I will present a few interesting uses for it. In Part II of the book, you will see many of these ideas actually implemented.

This chapter will also show you some clever ways to add functionality to the Chart component using your own code. The Chart component might seem a bit less complex than the Spreadsheet or PivotTable components at first, but it has some powerful features that you can use in your solutions. Plus, it offers many "knobs" that you can adjust to customize a chart to your liking.

[Previous] [Next]

The Basics of the Chart Component

The Chart component is a COM control that was built by the same team that developed charting in Microsoft Excel 2000. It provides basic business charting both as an onscreen COM control and as an in-memory, GIF-generating engine. In this first version, the Chart control supports all the two-dimensional chart types found in Excel (except for the Contour type) with the addition of the Polar, Stacked Pie, and Filled Scatter chart types. This version does not contain any three-dimensional chart types or effects.

One new feature that I will discuss in this chapter is the Chart control's ability to display more than one chart within the overall chart space of the component. The Chart control is actually composed of an entire chart space containing one or more charts that all share the same set of categories. (I will explain the term "categories" in more detail later in this section.) Normally, you will have only one chart in the control at a time; however, having multiple charts in the same chart space makes it easier to compare similar information at a glance. I will describe chart spaces in more detail toward the end of the chapter.

Like the Spreadsheet component, the Chart component has a number of basic features and some distinct terminology that you should become familiar with before we move on to more advanced topics.

The Nomenclature of Charting

During the development of a new feature in Excel 2000 charting, the OWC team conducted a number of usability tests to determine whether our design was easy to use. For those who have never heard of such tests, we take real customers from companies and homes throughout the Seattle area and put them in front of a prototype for a new feature. We ask the people to perform a number of tasks and watch to see how they approach them and whether the design was effective in helping them accomplish the assignment. Often, we find that our expectations were far from reality—meaning we have to go back and redesign the feature.

In one particular test, we showed people various charts we created in Excel with certain parts circled. We asked them to tell us what they thought the name of the circled part should be. Logically, you would expect us to find some commonality and discover the "name" that most people already associate with a particular element. We could then use that name in our documentation, programming models, onscreen user interface, and so on. Much to our dismay, we discovered absolutely no standard names for elements within a chart. You might expect that people would know which is the X axis and which is the Y axis, but many people don't remember much from their math classes and commonly mix them up.

NOTE

For those whose minds are now racing to remember which is which, the X axis is horizontal and the Y axis is vertical. Of course, most charts have what's called a category axis and a value axis, and their orientation depends on the particular chart type. Scatter and Bubble charts have X and Y axes since they compare two (or three) values against each other. In chart types such as Radar or Polar, the axes actually extend from the center of the chart, so X and Y are meaningless.

Since no common language seems to exist for describing elements of a chart, it is quite hard to talk about creating and manipulating charts. To gain any understanding of the Chart component's features and programming model, we must first define a number of key terms used in the component and look at what elements they represent. Many of the terms have specific connotations in different chart types, so if you see a chart type in the following discussion that you are not familiar with, refer to the "Supported Chart Types" for a screen shot and a description.

Series

A series is one of the most important constructs of the Chart component. In fact, most of the internal structures in this component are oriented around the series. Figure 31 points out the series in a Column chart. Notice that each series correlates to an entry in the chart's legend.

Figure 3-1.

A Column chart containing two series.

A series represents a sequence of data points that you want to display in a certain manner. People commonly think that a chart has a particular type (such as a Line chart, Bar chart, or Pie chart). But in the Chart control, it's the series that has a particular type—which means you can create a combination chart by setting one series as a Line type and another as a Column type. All the data points in a series commonly have the same color (though you can override this, as we will see later). Plus, elements such as trendlines and error bars are attached to particular series.

By default, an entry in the legend exists for each series, but you can hide specific entries in the legend if you want. (I will explain how to do that later.)

Categories

Categories are a little harder to explain than series. Figure 3-2 points out the category labels contained in a Column chart.

Figure 3-2.

Categories in a Column chart.

All charts have a notion of categories, but not all charts have a category axis. In Figure 3-2, the sales representatives' names are the categories, and each series contains a single data point in each category. In most charts, the intersection of a category and a series creates a data point. Note, however, that a particular series might not have a data point for a given category although the other series do. When this is the case, the Chart control merges all categories from all series and simply does not plot a data point at that series-category intersection.

Category axes differ from value axes in three important ways:

• There is no inherent ordering of categories.
• There is no minimum and no maximum category.
• Data points are neatly assigned to a specific category.

If Salesperson is your category axis, specific sale amounts are naturally attributed to specific salespeople, and no data points sit between salespeople. A value axis has a defined minimum and maximum, and the space along the axis is evenly divided into units that increase as you move from the minimum to the maximum. Each data point is therefore plotted wherever it lies along the axis.

A Scatter or Bubble chart does not have a category axis because its data points are defined by X and Y coordinates as well as a bubble size for Bubble charts. Although these chart types do not have category axes, the data points can still belong to specific categories and you can retrieve the category name for a given data point. This mechanism is useful for encoding extra information into the data points of a Scatter or Bubble chart, allowing you to display that information when the user's mouse hovers over a data point.

NOTE

Of course, in the real world, sales often can be attributed to more than one sales representative. However, most sales information systems perform an allocation and store each representative's contribution to the sale as a specific value that is then plotted on the chart. The point about data fitting neatly into a category is made to contrast discrete categories from continuous values. For example, a value of 1.4567454 can be plotted along a value axis between 0 and 2, but it does not fit into a discrete "bucket" on the axis the way data in categories do.

Values, Values, Values

In most of the simpler chart types, you have only one set of values to worry about. Figure 3-3 shows the sales values plotted by year by salesperson. This chart contains only one value per data point—which holds true for most of the simple chart types.

Figure 3-3.

Values in a Column chart.

Scatter and Bubble charts, on the other hand, introduce the necessity for having two or three values for every data point. In a Scatter chart, each data point has an X value and a Y value, the combination of which defines an (X,Y) point in two-dimensional, Cartesian space. A Bubble chart adds a third value: a bubble size value that determines the radius of the bubble centered at the (X,Y) point. Optionally, the bubble size value can be set to represent the area of the bubble instead of the radius.

Things get a little more complicated when using a High-Low-Close (HLC) chart because again three values make up each data point, except that they are now called the high value, low value, and close value. An Open-High-Low-Close (OHLC) chart has four values associated with each data point, and the extra value is called, not surprisingly, the open value. (These types of charts are often called Stock charts because they are most commonly used to display such data.)

When dealing with a Polar chart, you need to provide another set of values: R values and Theta values. The R value defines the distance of the data point from the center of the chart, and the Theta value represents the angle away from a horizontal line passing through origin.

Axes

The term "axes" is probably more familiar to you. In the Chart component, axes have essentially the same meaning that they do in the world of geometry. However, the Chart control adds another layer of meaning to an axis by referring to it either as a category axis or a value axis. In Figure 3-4, the category and value axes are labeled.

Figure 3-4.

Category and value axes in a Column chart.

A category axis is subdivided into equal segments—one for each distinct category, and data points are plotted in the middle of each category. No notion of a minimum or maximum exists for a category axis. A value axis, on the other hand, is a continuous axis that has a minimum and a maximum. Along a value axis, data points are plotted where they would fall between the minimum and maximum points.

Since the placement of the category and value axes depends on the chart type, "category" and "value" are logical names that do not directly map to X and Y. For example, a Column chart has the category axis extending horizontally along the bottom (X) and the value axis rising vertically on the left (Y). But in a Bar chart, the placements are reversed, with the category axis on the left and the value axis along the bottom. For more on the various chart types, see "Supported Chart Types."

Scaling

Although you have certainly heard the term "axis," you might not have heard the term "scaling" before. Every axis has a scaling, though you commonly use a scaling with a value axis. A scaling defines a measurement scale for its axis, determining the minimum and maximum values for the axis. The scaling also determines whether the intervals along the axis are linear or logarithmic. A linear scale subdivides the axis into even segments that increase linearly from the minimum value to the maximum value (for instance, 20, 40, 60, 80, 100). A logarithmic scale also divides the axis into even segments; however, the increment from one segment to the next is logarithmic instead of linear (for example, 1, 10, 100).

We will discuss axis scaling in more detail later in the chapter when we talk about split axes, as well as in Chapter 6, where we will see how to "zoom in" to show a portion of your data in more detail.

Trendlines

The Chart component supports the creation of one trendline for each series in your chart. As in Excel, a trendline is used to show the trend of data in a series. Trendlines are commonly used in trend analysis and forecasting when you want to predict what a certain value will be in the future if it keeps increasing or decreasing at the historical rate. Like Excel, the Chart control offers a few different trendline calculations, including linear, logarithmic, polynomial, exponential, and power. However, the Chart control does not offer the moving average trendline type found in Excel. Figure 3-5 shows an example of a trendline.

Error Bars

Error bars, shown in Figure 3-6, are short line segments extending from the data points that indicate some uncertainty about your data, known as the error amount. Each data point can display an error bar, which indicates that the data point's true value can be anywhere within the error range.

Why a Separate Scaling Object?

You might be wondering why the concept of scaling is not just part of an axis. After all, it seems that the scaling determines the minimum and maximum values of an axis. However, the scaling's minimum and maximum values determine the dimensions of the viewable region called the plot area. An axis displays tick marks and labels, but it is the scaling that determines the exact pixel/value ratio for that dimension of the plot area. By separating the scaling from the axis, the Chart control can support charts that have no visible axes.

Figure 3-5.

A trendline in a Scatter chart.

Figure 3-6.

Error bars in a Column chart.

Like trendlines, error bars are attached to a series. An error bar is displayed for each data point in the series, and the collection of error bars for that series can be set to show a positive error amount, a negative error amount, or both. The error amount can be expressed as a percentage (such as +10%, -10%, or +/-10%), a relative value (such as +2, -2, or +/-2), or a custom amount (for example, an upper bound of 12 and a lower bound of 8 for a data point of 10). Error bars can also be data-bound, in which case the Chart control treats the values in the result columns as custom error values for each data point. Although Excel charting also provides Standard Error and Standard Deviation options for the error amounts, the Chart control does not yet natively support these. Of course, you could calculate these values yourself and use custom error amounts to display them in the chart.

Data Labels

A data label is a small piece of text placed next to a data point that you can set to display the data point's value, percentage in the series, category name, series name, or bubble size. (See Figure 3-7 for an example.) You can display any combinations of these pieces of information in a data label. Plus, you can control the font, color, and border formatting attributes—even which separator character string to use between each piece of information.

Figure 3-7.

Data labels in a Column chart.

Data labels are obviously useful when you want to show numbers next to your data points, especially for comparing data points that might be very close to each other. The percentage contribution is of course useful any time you want to show the percent a data point contributes to the overall series, such as in a Pie chart. Showing the category name is a nice way to display extra categorical information in chart types that do not have category axes, such as Scatter and Bubble charts.

Like trendlines and error bars, data labels are attached to a series. You cannot format, hide, or show a data label for an individual data point. All manipulations to data labels affect all data points in the series.

Supported Chart Types

The first question I usually hear from a developer's mouth when I speak about the Chart component is, "What chart types are supported?" Chart types are the bread and butter of charting, so the more types the merrier. I have a reference book on my shelf that contains almost 450 pages of different chart types, chart elements, and information graphics techniques! (This book, Information Graphics: A Comprehensive Illustrated Reference [Management Graphics, 1997], is an excellent resource for anyone involved in information graphics, as are any of Edward Tufte's books.)

The first version of the Chart component includes the full set of two-dimensional chart types found in Excel 2000 (except for the Contour type), with the addition of Polar, Stacked Pie, and Filled Scatter chart types. The Chart control does not have any three-dimensional chart types or effects in this version, nor does it support the fancy fill effects offered in Excel.

Let's look at several examples of the supported chart types and discuss what kind of data they are useful for displaying.

Column and Bar Charts

The most typical chart types used in business, Column and Bar charts, show a filled bar for each data point, extending from the zero point on the value axis to the data point.

Most people don't make much of a distinction between Column and Bar charts—after all, they are essentially the same, they just extend in different directions. The Chart component uses the term "Column" to refer to a vertical column that extends up and down the screen and the term "Bar" for a horizontal bar that extends across the screen. Figure 3-8 shows an example of a Column chart and a Bar chart.

Figure 3-8.

A Column chart (top) and a Bar chart.

These chart types typically are useful for data containing categories that do not need to appear in any particular order. Unlike Line charts, Column and Bar charts don't portray a sense of order or progression.

As with many of the chart types, there are a few Column and Bar chart subtypes. The default subtype, called Clustered Column or Bar, plots bars from different series adjacent to each other within each category. (Figure 3-8 shows a Clustered Column chart and a Clustered Bar chart.) The Clustered subtype is the most useful subtype when the different series are fairly unrelated or when they should not be aggregated visually. For example, if you plot a budget in one series and the actual amount spent in the other, you do not want to aggregate those values. Instead, you would want to compare the bars or columns side by side.

Stacked Column and Bar charts display the different series as stacked upon one another. In such charts, the length of the column or the bar represents the sum of the data points for the category. Figure 3-9 shows an example of a Stacked Column chart.

Figure 3-9.

A Stacked Column chart.

Stacked Column and Bar charts are useful for displaying data in which the series' values can and should be aggregated to depict a visual total for each category. For example, if you plot sales information by country and by product, you might want to use a Stacked Column chart to show the total sales for each country (the category) across all products. The bar is still segmented by the exact value each product contributes—meaning top-selling products will have longer segments, while products that do not sell well will have shorter segments. Stacked charts are useful when it is not as necessary to assess the relative contribution as it is to assess the total for each category.

Finally, the 100% Stacked subtype is a bit like a Pie chart: it draws a bar or column all the way across the plot area and then subdivides the bar or column into segments representing the percent contribution of each series' data point. The key difference between this subtype and the Stacked subtype is that the length of each segment is the percentage of the data point's contribution to the total of the data points in that category, not the literal value. Since all the bars are the same length (100%), such a chart is not useful for comparing one category's total to another. However, this type of chart is useful for viewing the same type of information a pie chart shows, but for many categories and series at once.

Most of the other chart types described here have the same set of subtypes—Clustered, Stacked, and 100% Stacked. I will not redefine each of these three subtypes in the descriptions that follow, but I will indicate when they are available. Refer back to this section for a description of the subtypes and which types of data are appropriate to display using them.

Pie, Stacked Pie, and Doughnut Charts

Pie charts are also common in business charting, which is almost a shame since they provide the least dense (and the least efficient) display of information available. However, their simplicity also makes them very understandable, and often, very persuasive. For example, when showing a breakdown of market share information, the effect of an extremely large or small slice is indeed powerful. Figure 3-10 shows a typical Pie chart.

Figure 3-10.

A Pie chart.

The important quirk of Pie charts is that the legend shows the category values instead of the series values. Most charts show the various series in the legend, but since a Pie chart shows only one series, the legend is used to show the category labels that correspond to the colored pie "slices."

A Pie chart is obviously useful for showing the percentage contribution or breakdown of a total. A Pie chart shows only one dimension of data because, as stated a moment ago, it can display only one series of data points.

The Stacked Pie and Doughnut chart types, however, can show multiple series at once, much like the 100% Stacked Column chart can display data for many series and categories at once. The only real difference between the Stacked Pie chart and the Doughnut chart is that the Doughnut chart has a hole in the middle (the "doughnut hole" if you will). Figure 3-11 depicts the same information in first a Stacked Pie chart and then a Doughnut chart.

Figure 3-11.

A Stacked Pie chart and a Doughnut chart.

I admit that these are somewhat bizarre chart types; in fact, I recommend using them only in those unique circumstances under which no other chart type will suffice (for example, displaying the percentage chemical makeup of soil in concentric rings around a bomb explosion site). The concentric circles can be misleading for abstract data because their relative size and order are not based on any numeric value.

Line, Smooth Line, and Area Charts

Line charts and Area charts fall within the group of simpler chart types but unfortunately are not used as often as they should be. Figure 3-12 shows what a typical Line chart and Area chart look like.

Line and Area charts are useful for displaying data in which the categories actually have a meaningful order, such as a series of dates or times. For example, plotting sales over a series of dates or plotting stock prices over a series of hours is more effectively displayed in a Line chart than in a Column chart. This is because it is easier to tell whether there's a trend up or down when lines are drawn between the data points in a chart.

The only real difference between a Line chart and an Area chart is that in the Area chart, the section between the category axis and the line is filled with the series color. The occlusion that occurs when one series' values are higher than another's can make Area charts somewhat difficult to work with—unless you are using the Stacked subtype we discussed earlier. Since the series are drawn over each other in order, the last series drawn will cover any series previously drawn. Use nonstacked Area charts only when you know that a series has consistently higher values than all the series that follow it.

Figure 3-12.

A Line chart and an Area chart.

As in the Column and Bar chart cases, Line charts and Area charts have the Clustered, Stacked, and 100% Stacked subtypes. However, the term "Clustered" is not commonly used to describe the default subtypes, and these defaults are simply called Line chart and Area chart without any special distinction.

Line charts have one other subtype that Column and Bar charts do not. Lines in a chart can be drawn either "straight" or "smoothed." It should come as no surprise that charts drawn with smoothed lines are called Smooth Line charts. Instead of drawing the line straight from one data point to another, the Chart component draws the line on a curve so that there are no jagged peaks or valleys.

Scatter and Bubble Charts

Scatter charts are used less often in business presentations, which is unfortunate considering that they can be a more powerful analysis tool than the simpler chart types described earlier. Although a Scatter chart has series and categories, it also has two values (rather than one) that determine the location of a data point. Each data point in a Scatter chart has an X value and a Y value, and the combination of the two determines its placement on the plot area.

The key difference between a Scatter chart and a Bubble chart is that the data points in a Bubble chart are circles that have dynamic size. A data point in a Bubble chart contains a third value called bubble size, which determines either the radius or the area of the bubble. Figure 3-13 shows typical Scatter and Bubble charts.

Figure 3-13.

A Scatter chart and a Bubble chart.

Scatter charts and Bubble charts are useful for comparing two different values to discover a correlation or distribution pattern. For example, using a Scatter chart to plot a department's morale budget allocation against its revenue might reveal a strong correlation, showing that a high morale budget commonly increases revenue. (At least, most employees would like to think this is true!)

Scatter charts have a few unique subtypes. The default subtype is simply called Scatter Markers and uses markers (small geometric shapes such as diamonds or squares) to indicate the data points. Different shapes are used to indicate the different series. You can choose to connect the markers of each series with a smoothed or straight line. Furthermore, you can choose to have lines without any markers at all. The Chart component includes a final subtype that is not included in Excel. It allows you to fill in the polygon made from the data points and connecting lines, creating a Filled Scatter chart.

Fun with Filled Scatter Charts

You can try a rather creative Filled Scatter chart demo on the companion CD. Open the DrawWithChart.htm file in the Chap03 folder, and click the chart surface to create points of a filled polygon. Double-click the mouse to end the shape. This demo was written by Jeff Couckuyt, one of the extremely talented Chart component developers, and was created using only the Chart control and a Filled Scatter chart type.

Keep in mind that Bubble charts have the same problem of occlusion as Area charts. A large bubble will hide any data points underneath it, so only use Bubble charts when you know the chance of occlusion is low; otherwise, consider setting the bubble fill to transparent.

Radar Charts

Radar charts do not seem to be typically used in the United States , but I understand that they are much more common in Asian countries for portraying data such as nutritional information about food products. Figure 3-14 shows what this interesting and useful chart type looks like.

Figure 3-14.

A Radar chart.

A Radar chart has categories, series, and values like the other simple chart types; however, Radar charts plot the category labels in a circle surrounding the chart and contain spokes extending from the center of the chart out to each category label. Each spoke is a value axis. The data points for each category are plotted on the corresponding spoke's scale at the appropriate point and in the appropriate series color. The chart then joins the data points of each series with a line and optionally fills the series color from the line toward the origin. Filled Radar charts have the same old problem of occlusion that Area and Bubble charts have, so beware of using the Filled subtype unless you know that your data will not cause occlusion (or unless you are not concerned about it).

Radar charts also support the Smooth Line subtype we discussed earlier. Plus, you have the option of plotting data point markers in both the Smooth Line and Straight Line subtypes.

High-Low-Close and Open-High-Low-Close Charts

Anyone displaying information about stocks or financial securities will be interested in these two chart types. The High-Low-Close chart type (or HLC chart) displays a line segment for each category. Each line segment extends from the low to the high value and features a small tick mark that denotes the close value. Figure 3-15 shows an example of an HLC chart.

Figure 3-15.

A High-Low-Close chart.

You should consider using this chart type any time you have data containing a range of values for a given period and a special value that needs to be marked within the range. For example, you could use an HLC chart to display temperature readings over an extended period of time.

An Open-High-Low-Close chart (or OHLC chart) adds one extra piece of information to the data of an HLC chart: the open value. The chart indicates the open value by displaying a filled rectangle between the open value and the close value, as Figure 3-16 shows.

The filled rectangle either will be the series color or it will be black, depending on whether the difference between the close value and the open value is positive or negative. Positive differences get the normal series color, while negative differences are shown as black. This shows the viewer whether the values increased or decreased during the specified period so that he or she can ascertain whether the value improved or worsened and by how much.

Figure 3-16.

An Open-High-Low-Close chart.

Both the HLC and the OHLC chart types still maintain the notion of series. However, in an HLC chart type, the Chart component places different series in the same horizontal position. In other words, the control will draw multiple series over one another. Generally, this chart type is most useful with only one series of data points. OHLC charts, on the other hand, can manage multiple series and draw the bars of multiple series next to one another.

Polar Charts

The Polar chart type is the one new chart type that appears in the Chart component but not in Excel charting. Polar charts, which Microsoft Office users have been requesting for some time, are useful for displaying relationships between angles and distances. Polar charts are commonly used in the audio and radio fields, for example, to show the power and direction of a microphone's pickup. Figure 3-17 depicts a typical Polar chart.

Figure 3-17.

A Polar chart.

The Polar chart includes the Smooth Line subtype, and you can choose whether to display data point markers with the lines.

Combination Charts

As mentioned at the beginning of the chapter, one of the great secrets of charting is that a chart does not really have a chart type. Instead, each individual series has a type, and if all series in a chart happen to have the same type, the Chart object's Type property returns that type. However, you can use this distinction to create more complex combination charts, in which you plot some series as columns or bars while plotting others as lines.

Not all chart types can be combined, and in this version of Office the Chart component allows you to combine only the Column, Line, and Area chart types. The most common combination chart used for business data is a mixture of the Column and Line chart types.

Loading Data

Now that you know what the various chart elements are called and what chart types the Chart control can display, you need to learn how to load a chart with data. Like most of the Office Web Components, the Chart control can load data from a variety of sources, and the loading can be performed by using the Chart Wizard in a design environment or by writing code. The Chart component binds to all the other Office Web Components—the Spreadsheet, PivotTable, and Data Source components—as well as to all other controls that implement the IDataSource interface (the standard interface for a data source control in Microsoft Internet Explorer or Microsoft Visual Basic, documented in the Microsoft Developer Network Libraries and the OLE DB SDK), ADO Recordset objects, and even literal arrays or delimited strings of data.

The general approach to loading data into the Chart control is to tell the chart where it should retrieve data from and what "parts" of the data source should be used for the series, categories, and values the current chart type requires. In the programming model, the Chart control refers to these chart elements as dimensions that you can bind to some part of the data source. When binding to a spreadsheet, the "part" of the data source you specify is a range reference, such as A1:C1. For an OLE DB data source, you specify what column name or ordinal index of the resultset to use. For the PivotTable control, you specify which pivot axis to use. (We will discuss this further in Chapter 4.) In literal data, only one "part" exists: the array or delimited string itself.

The Chart Wizard performs much of the binding for you, presenting you with a simple user interface for specifying this information. However, the Chart Wizard merely calls the public programming model of the Chart control, so by writing your own code you can do anything the Chart Wizard does—and more. The Chart Wizard is fairly self-explanatory, and its online help covers much of its use. Since this book focuses on developing custom solutions with the Office Web Components, I will not detail using the Chart Wizard here. Instead, I'll dive into the code you need to write when programmatically filling the chart with data.

Loading the Chart with Literal Data

The following subroutine, taken from the LoadFromLiteral.htm file in the Chap03 folder on your companion CD, shows how to load the Chart control with literal data:

'------------------------------------------------------------------------ ' LoadChartWithLiteral() ' Purpose: Loads the chart with literal data ' In:      cspace        reference to the ChartSpace object '          vSeries       variant array or '                        tab-delimited string of series names '          vCategories   variant array or '                        tab-delimited string of category names '          avValues      array of variant array or '                        tab-delimited string of values; '                        one entry in the outer array per series ' Sub LoadChartWithLiteral(cspace, vSeries, vCategories, avValues)     ' Local variables     Dim cht    ' Chart object we'll create in the chart space     Dim ser    ' Temporary series     ' Grab the Constants object so that we can use constant names in     ' the script. Note: This is needed only in VBScript -- do not include     ' this in VBA code.     Set c = cspace.Constants     ' Clear out anything that is in the chart space     cspace.Clear     ' Create a chart in the chart space     Set cht = cspace.Charts.Add()     cht.HasLegend = True     ' Now call SetData to bind the various dimensions     ' Second parameter is c.chDataLiteral, meaning the last parameter     ' is a variant array or a tab-delimited string     cht.SetData c.chDimSeriesNames, c.chDataLiteral, vSeries     cht.SetData c.chDimCategories, c.chDataLiteral, vCategories     ' When loading the chart with literal data, you must     ' load each series with values individually     For each ser In cht.SeriesCollection         ser.SetData c.chDimValues, c.chDataLiteral, avValues(ser.Index)     Next 'ser End Sub 'LoadChartWithLiteral()

When loading the chart with literal data, the data can be contained either in an array of variants or in a tab-delimited string, each element or token representing a different value. In the file on your companion CD, I pass the literal data as an array using the Array function that is supported in Microsoft VBScript as well as in Microsoft VBA.

The SetData method is used to pass the literal data, but note that the second argument (normally the data source index) is the constant chDataLiteral. This constant, which is equal to -1, tells the chart that the next argument is literal data and not part of a data source.

Also note that you must use the SetData method of the WCSeries object (the object representing a series) when passing literal values to the chart. Since the Chart control itself can accept only a one-dimensional array of values, if it allowed you to pass literal values to the SetData method of the WCChart object (the object representing a chart), it would have no way of knowing which values belong to which series. The previous procedure handles this by simply looping through the series collection and passing the appropriate array of values to the current series' SetData method.

Binding to the Spreadsheet Component

The following subroutine, taken from the LoadFromSpreadsheet.htm file in the Chap03 folder on your companion CD, shows how to bind a Chart component to ranges in the Spreadsheet component:

'------------------------------------------------------------------------- ' BindChartToSpreadsheet() ' ' Purpose: Binds a chart to specified ranges in the source spreadsheet ' In:      cspace           reference to the ChartSpace object '          sheet            reference to the Spreadsheet object '          srngSeries       string-based range reference to where the '                           series names come from '          srngCategories   string-based range reference to where the '                           category names come from '          srngValues       string-based range reference to where the '                           values are ' Sub BindChartToSpreadsheet(cspace, sheet, srngSeries, srngCategories, _                            srngValues, fSeriesInCols)     ' Local variables     Dim cht          ' Chart object that we'll create in the chart space     Dim ser          ' Temporary series     Dim rngValues    ' Range object of values     ' Grab the Constants object so that we can use constant names in     ' the script. Note: This is needed only in VBScript -- do not include     ' this in VBA code.        Set c = cspace.Constants     ' Clear out anything that is in the chart space     cspace.Clear     ' First tell the chart that its data is coming from the spreadsheet     Set cspace.DataSource = sheet     ' Create a chart in the chart space     Set cht = cspace.Charts.Add()     cht.HasLegend = True     ' Now call SetData to bind the various dimensions     ' Second parameter is zero, meaning the first data source should be     ' used if there are multiple data sources     cht.SetData c.chDimSeriesNames, 0, srngSeries     cht.SetData c.chDimCategories, 0, srngCategories     ' The spreadsheet can bind to one-dimensional ranges only,     ' so loop through the series collection and set the values     ' for each series individually     Set rngValues = sheet.Range(srngValues)     For Each ser In cht.SeriesCollection         If fSeriesInCols Then             ser.SetData c.chDimValues, 0, _                 rngValues.Columns(ser.Index + 1).Address         Else             ser.SetData c.chDimValues, 0, _                 rngValues.Rows(ser.Index + 1).Address         End If     Next 'ser End Sub 'BindChartToSpreadsheet()

I would like to point out a few things about this example. First, to bind the Chart and Spreadsheet controls, I set the Chart control's DataSource property to the instance of the Spreadsheet control. By receiving the pointer to the Spreadsheet control instance, the Chart control can now ask the Spreadsheet control for cell values in the specified ranges.

Second, the SetData method is used to bind first the Series Names dimension and then the Categories dimension. You must bind the chart dimensions in this order: series names first, then categories, then values. In the rare case in which you have only one series of information, you can skip the Series Names dimension and just bind categories and values. Doing so will create one series for you with the default name Series, which you can change by setting the Name or Caption property of the WCSeries object.

My last point is that when binding to the Spreadsheet component, the Chart component needs to receive explicit range references for the values in each series. Unfortunately, you cannot just hand the chart a two-dimensional range of values and let it automatically figure out which series and categories the values belong to. Instead, you must pass a one-dimensional range reference to the last parameter in the SetData method for each WCSeries object. The previous example does this in a generic fashion by using the Spreadsheet component's Range object to yield a range reference for each column or row in the two-dimensional range, which in turn gets passed to the series' SetData method. The fSeriesInCols flag indicates whether the values for a given series are arranged down a column or across a row in the spreadsheet. If the flag is True, the Columns collection is used; if False, the Rows collection is used.

Also note that this example adds 1 to the series' index value. This is because the Columns and Rows collections are 1-based for compatibility with Excel's programming model, while the Chart component's series index is 0-based. Adding 1 to the series index yields the corresponding column or row in the range.

Binding to the Data Source Component

The following method, taken from the LoadFromDSC.htm file in the Chap03 folder on the companion CD, shows how to bind a Chart component to a Recordset returned from the Data Source component (DSC). (We'll discuss the DSC more thoroughly in Chapter 5.)

'------------------------------------------------------------------------ ' BindChartToDSC() ' ' Purpose: Binds a chart to a Recordset in the Data Source component. '          (This example creates a Pie chart.) ' In:      cspace        reference to the ChartSpace object '          dsc           reference to the Data Source control '          sRSName       name of Recordset to bind to in the '                        Data Source control '          sCategories   name of the result column containing categories '          sValues       name of the result column containing values ' Sub BindChartToDSC(cspace, dsc, sRSName, sCategories, sValues)     ' Local variables     Dim cht    ' Chart object that we'll create in the chart space     Dim ser    ' Temporary series     ' Grab the Constants object so that we can use constant names in     ' the script. Note: This is needed only in VBScript -- do not include     ' this in VBA code.     Set c = cspace.Constants     ' Clear out anything that is in the chart space     cspace.Clear     ' First tell the chart that its data is coming from     ' the Data Source control     Set cspace.DataSource = dsc     ' Next tell it what Recordset within the Data Source control     ' it will bind to     cspace.DataMember = sRSName     ' Create a Pie chart in the chart space     Set cht = cspace.Charts.Add()     cht.HasLegend = True     cht.Type = c.chChartTypePie     ' Now call SetData to bind the various dimensions     ' Second parameter is zero, meaning the first data source should be     ' used if there are multiple data sources     ' In this example of a Pie chart, we will add one     ' series manually and use the SetData method there     Set ser = cht.SeriesCollection.Add()     ser.SetData c.chDimCategories, 0, sCategories     ser.SetData c.chDimValues, 0, sValues     ' Finally, for this example, add some     ' data labels since it's a Pie chart     Set dls = ser.DataLabelsCollection.Add()     dls.HasPercentage = True     dls.HasValue = False End Sub 'BindChartToDSC()

The DSC can retrieve data from and provide data to both the Chart component and the PivotTable component. It also implements the same data source COM interface (IDataSource) that Visual Basic and Internet Explorer data source controls implement, so this code can be used for any other valid data source control used in those environments.

The DataSource and DataMember properties of the ChartSpace object are fundamental to this example. Since they are part of the data-binding standards established by Visual Basic and Internet Explorer, you will commonly see them on other data-bound controls in those environments. The DataSource property is set to point at the control providing the data (in this case the DSC), and the DataMember property is set to a string value naming the specific data set desired. Because a DSC can expose many data sets at once, the DataMember property is used to tell the Chart control which data set to request. If you leave the DataMember property blank, the Chart control will ask for the default data set, the identity of which is determined by the DSC.

The SetData method is used in this example much the same way it was used in the BindChartToSpreadsheet method, except that the "data part" (the last parameter to SetData) is now the name of a column in the Recordset returned from the DSC. Alternatively, this data part can be the ordinal index of the column (0, 1, 2, and so on). The Chart control will pass this to the ADO Fields collection object's Item method, so any value that is valid for that method can be used as the last parameter to the SetData method. Typically, you use column names if you don't expect them to change over time but do expect the ordinal positions to change; you use ordinal indexes if you don't expect them to change but do expect the column names to change.

As stated earlier, an interesting twist of Pie charts is that the entries in the legend are categories, not series. Therefore, when binding a Pie chart, you should bind the column you want to appear in the legend to the Categories dimension rather than the Series dimension. This also applies to Stacked Pie charts and Doughnut charts.

The last code block in this example creates some data labels (which we just discussed) to show the percentage each data point contributes to the whole. The Chart control can show a number of values for each data point, and by default the actual number value is displayed. Since I wanted to show only the percentage contribution of each slice, I set the HasPercentage property to True and HasValue to False.

Although this particular example creates a Pie chart, you can use any of the supported chart types when binding to the DSC. I chose to create a Pie chart in this example only to show how binding is performed using the Pie chart type.

Binding to a Recordset

The following function, taken from the LoadFromRecordset.htm file in the Chap03 folder on the companion CD, shows how to bind a Chart control to an ADO Recordset object:

'------------------------------------------------------------------------ ' BindChartToRecordset() ' ' Purpose: Binds a chart to a Recordset. (This example creates a '          Scatter chart.) ' In:      cspace        reference to the ChartSpace object '          rst           reference to Recordset object to bind to '          sfldSeries    name of the series field '          sfldXValues   name of the field containing the X values '          sfldYValues   name of the field containing the Y values ' Sub BindChartToRecordset(cspace, rst, sfldSeries, sfldXValues, _     sfldYValues)     ' Local variables     Dim cht    ' Chart object that we'll create in the chart space     Dim ser    ' Temporary series pointer     Dim ax     ' Temporary axis pointer     ' Grab the Constants object so that we can use constant names in     ' the script. Note: This is needed only in VBScript -- do not include     ' this in VBA code.     Set c = cspace.Constants     ' Clear out anything that is in the chart space     cspace.Clear     ' First tell the chart that its data is coming from the Recordset     Set cspace.DataSource = rst     ' Create a Scatter chart in the chart space     Set cht = cspace.Charts.Add()     cht.HasLegend = True     cht.Type = c.chChartTypeScatterMarkers     ' Now call SetData to bind the various dimensions     ' Second parameter is zero, meaning the first data source should be     ' used if there are multiple data sources     ' In this example of a Scatter chart, we will bind     ' two value dimensions: X and Y     cht.SetData c.chDimSeriesNames, 0, sfldSeries     cht.SetData c.chDimXValues, 0, sfldXValues     cht.SetData c.chDimYValues, 0, sfldYValues     ' Finally, let's add some axis labels using     ' the column names as the axis captions     Set ax = cht.Axes(c.chAxisPositionBottom)     ax.HasTitle = True     ax.Title.Caption = sfldXValues     ax.Title.Font.Name = "Tahoma"     ax.Title.Font.Size = 8     ax.Title.Font.Bold = True     ax.NumberFormat = "#,##0"     Set ax = cht.Axes(c.chAxisPositionLeft)     ax.HasTitle = True     ax.Title.Caption = sfldYValues     ax.Title.Font.Name = "Tahoma"     ax.Title.Font.Size = 8     ax.Title.Font.Bold = True     ax.NumberFormat = "$#,##0"     ' Let's also set the marker size a bit smaller than normal     For Each ser In cht.SeriesCollection         ser.Marker.Size = 5     Next 'ser End Sub 'BindChartToRecordset()

You will no doubt quickly notice that this example is similar to the previous example involving the DSC, except that this example uses a Scatter chart. This is because the ADO Recordset object is itself a valid data source in Visual Basic and Internet Explorer, and it implements the same data source interface as the DSC. This makes it possible to set the Chart control's DataSource property to point to the Recordset object just as you would set it when using the DSC. However, the Recordset object by definition has only one data set to expose, so the DataMember property does not need to be set when binding to it.

Because the chart type used in this example is a Scatter chart, the code sets two value dimensions: X Values and Y Values. As described earlier in the section on supported chart types, a Scatter chart uses two values for each data point, so we need to bind the X Values dimension to one column in the Recordset and the Y Values dimension to another column. (You could bind them to the same column, but that would make for a highly correlated Scatter chart!)

An Accident of Good Architecture

When I sent this chapter to the Chart component developers for review, one of them commented that the component does not actually "support" loading from a Recordset and that the OWC team had not officially tested this scenario. However, almost all the demos and real pages we wrote internally use this method, so in actuality, this scenario was quite well tested.

The reason that loading the Chart component from a Recordset works is an accident of good architecture. When the Visual Basic and Internet Explorer development teams selected the standard data source interface (IDataSource), the ADO team decided that it made sense for the Recordset object to implement this interface since it could easily return the underlying IRowset interface from the GetDataMember method. Because the Chart control uses IDataSource when loading data from a DSC, it all just worked. To the Chart control, the Recordset object looks like any other data source control.

When binding to a Recordset object, you must ensure that the Recordset is using the Microsoft Windows Cursor Engine (WCE) or is capable of being sorted and scrolled. The WCE is an ADO component that provides scrolling, sorting, filtering, and more on any OLE DB Rowset regardless of the Rowset's source or native capabilities. To use this engine, set the CursorLocation property of your ADO Connection or Recordset object to adUseClient, which has a value of 3 if you are in an environment that does not recognize constants. To ensure that the Chart control can scroll around the Recordset, you can use the adOpenStatic cursor type, which also has a value of 3. Use adOpenStatic or 3 for the CursorType parameter of the Recordset's Open method or for the CursorType property of the Recordset object. (Chapter 4 will discuss the WCE in more detail.)

For an example of setting the necessary Recordset properties, see the full source listing in the LoadFromRecordset.htm file on the companion CD. When viewing this file, note the use of the column names for the axis captions. Scatter charts have two value axes (X and Y), so you should give those axes titles and explain what values you are showing on them. Using the column names in the Recordset can be an easy way to label these axes; of course, you can set your own captions if the column names in the Recordset are not intelligible.

Binding to the PivotTable Component

The last possible source of data for the Chart component is the PivotTable component. (We will talk more about this component in the next chapter.) As you might expect, this component also implements the same data source interface that all valid data sources expose in Visual Basic and Internet Explorer, so the following example, taken from the LoadFromPivot.htm file in the Chap03 folder on the companion CD, looks similar to the DSC example we discussed earlier but has a few important differences:

'------------------------------------------------------------------------ ' BindChartToPivot() ' ' Purpose: Binds a chart to a PivotTable component ' In:      cspace          reference to the ChartSpace object '          ptable          reference to the PivotTable object '          fSeriesInCols   Boolean flag indicating whether the series '                          of the chart should come from the column '                          axis or the row axis of the PivotTable control ' Sub BindChartToPivot(cspace, ptable, fSeriesInCols)     ' Local variables     Dim cht    ' Chart object that we'll create in the chart space     Dim ax     ' Temporary axis reference     Dim fnt    ' Temporary font reference     ' Grab the Constants object so that we can use constant names in     ' the script. Note: This is needed only in VBScript -- do not include     ' this in VBA code.     Set c = cspace.Constants     ' Clear out anything that is in the chart space     cspace.Clear     ' First tell the chart that its data is coming from the     ' PivotTable component     Set cspace.DataSource = ptable     ' Create a chart in the chart space     Set cht = cspace.Charts.Add()     cht.HasLegend = True     cht.Type = c.chChartTypeBarClustered     ' Now call SetData to bind the various dimensions     ' Second parameter is zero, meaning the first data source should be     ' used if there are multiple data sources     If fSeriesInCols Then         cht.SetData c.chDimSeriesNames, 0, c.chPivotColumns         cht.SetData c.chDimCategories, 0, c.chPivotRows     Else         cht.SetData c.chDimSeriesNames, 0, c.chPivotRows         cht.SetData c.chDimCategories, 0, c.chPivotColumns     End If 'fSeriesInCols     ' Set the values dimension. The value     ' you pass for the data reference (the last parameter)     ' is the index of the total you want to use.     ' Since there is only one total in this example,     ' we pass zero, indicating the first one.     cht.SetData c.chDimValues, 0, 0     ' Finally, let's add an axis title to the value     ' axis, using the label on the pivot total     ' as the caption, and set the number format     Set ax = cht.Axes(c.chAxisPositionBottom)     ax.HasTitle = True     ax.Title.Caption = ptable.ActiveView.DataAxis.Totals(0).Caption     Set fnt = ax.Title.Font     fnt.Name = "Tahoma"     fnt.Size = 8     fnt.Bold = True     ax.NumberFormat = ptable.ActiveView.DataAxis.Totals(0).NumberFormat End Sub 'BindChartToPivot()

As in the earlier Recordset and DSC examples, to bind the Chart control to the PivotTable control, you start by setting the Chart control's DataSource property to point to an instance of the PivotTable control. As in the Recordset example, the PivotTable control has only one data set to expose, so you do not need to change the DataMember property from its default setting.

The critical difference between this example and the DSC example is the use of special constant values for the last parameter to the SetData method. As you will remember, this last parameter specifies which part of the data you want to bind to the specified chart dimension. In a PivotTable control, the logical parts available are the row and column pivot axes and all the totals in the view. In a PivotTable control, the row axis refers to all the labels displayed down the left side of the table, and the column axis refers to all the labels displayed across the top of the table (more on these axes in the next chapter). All the Chart control needs to know is which axis you want to bind to the Series Names dimension and which axis you want to bind to the Categories dimension.

Binding the various values dimensions of the Chart control is slightly different. Since a PivotTable report can display many totals at once, the Chart control needs to know which total you want to use for the specified values dimension. You indicate this by passing the ordinal index of the total as the last parameter to the SetData method. In the BindChartToPivot example, we use a Clustered Bar chart so that each data point has only one value. We tell the Chart control to use the total at index zero (the first one, since this is 0-based) for the data point values. Note that this is the ordinal index of the available totals shown in the PivotTable control's current view—not all the possible totals in the data source.

While the PivotTable control does implement the standard COM interface for a data source control, it does not know how to return ADO Recordsets or OLE DB Rowsets. The Chart control has special code for knowing how to read the PivotTable control's crosstab data display and properly ignore subtotals and grand totals, which if used, would skew the chart's scale. Therefore, even though the Chart control can consume data from the PivotTable control, not all data-bound controls will be able to use the PivotTable control as their data source.

Chart and Axis Titles

Charts can be effective mechanisms for displaying large quantities of information in a quick-to-assimilate visual manner. However, the data plotted in charts is rarely self-explanatory; often you will want to add a descriptive title to your chart or to the axes displayed within it.

Using Multiple Totals for Multivalued Charts

The Scatter, Bubble, Polar, and OHLC chart types all have something in common: they need more than one value to determine a data point on the chart. A Scatter chart requires both an X value and a Y value, a Bubble chart needs both those values as well as a bubble size value, a Polar chart needs Theta and R values, and an OHLC chart needs the four values its name suggests. Since PivotTable reports can show more than one total at a time, it's often desirable to map those totals to the different chart values to make a single data point.

You can do this by specifying the index of the total in the PivotTable view as the last parameter to the SetData method. For example, to bind the first total to X and the second total to Y in a Scatter chart, you would write this code:

cht.SetData c.chDimXValues, 0, 0    ' First total cht.SetData c.chDimYValues, 0, 1    ' Second total

Sometimes you want to use those multiple totals with a chart type that uses only one value per data point, such as a Stacked Column chart. In this case, you might want the caption of the total to appear as a nested category or a nested series label. To do this, you use a special constant:

cht.SetData c.chDimCategories, 0, c.chPivotRowAggregates

This setting would use any total captions appearing on the row axis as nested category names on a hierarchical category axis. To get the results you want, you also should set the TotalsOrientation property of the PivotView object to the plTotalOrientationRow constant so that the total captions are displayed on each row.

Perhaps you want to display multiple totals as different series in the chart. To do this, leave the column axis on the PivotTable report empty, add the totals you want to appear as different series, and then write the following code:

cht.SetData c.chDimSeriesNames, 0, c.chPivotColAggregates

This code will create a series in the chart for each total in the PivotTable report. Refer to the sample file PivotTotalsAsSeries.htm in the Chap03 folder on your companion CD to see what this looks like.

If you add more than one chart to the Chart control (detailed in the section "Multiple Charts in 'Chart Space'"), you can give each chart its own title. In fact, you can give the Chart control itself a global title that will be displayed above all the individual charts. Chart titles can be formatted with all the basic font attributes (name, size, bold, italic, underline, and color). You can also set their backgrounds to a specific color or leave them transparent. The same is true for axis titles.

By default, newly created charts will have neither a chart title nor axis titles. You can add chart and axis titles either at design time using the Property Toolbox or at runtime using code. The following method, taken from the AddTitles.htm file in the Chap03 folder on the companion CD, shows how to add a chart title:

'------------------------------------------------------------------------ ' SetChartTitle() ' ' Purpose: Sets the chart's title ' In:      cht      reference to a chart '          sTitle   new title caption ' Sub SetChartTitle(cht, sTitle)     Dim fnt    ' Temporary font reference     ' If the title is nonblank     If Len(sTitle) > 0 Then         ' Add a title if necessary         cht.HasTitle = True         ' Set the caption and its font formatting         cht.Title.Caption = sTitle         Set fnt = cht.Title.Font         fnt.Name = "Tahoma"         fnt.Size = 10         fnt.Bold = True     Else         ' Title is blank. Remove it.         cht.HasTitle = False        End if End Sub 'SetChartTitle()

You add an axis title the same way you add a chart title, except that the first parameter of the method accepts an Axis object reference instead of a Chart object reference.

Also note that you can set the Color property for the title's font or background to either an RGB color value or to one of the Internet Explorer color names—for instance, "FireBrick" or "PapayaWhip". This also applies to any use of color in the Chart control, such as the chart's background color, plot area color, series color, and so on.

Axis Labels

By default, the Chart control will include labels on all your axes to show where a data point lies on the scale (for value axes) or which category a data point belongs to (for category axes). You might want to adjust a few aspects of these labels using either code at runtime or the Property Toolbox at design time.

For value axes, the labels show numeric points on the overall value scale of the axis. These numbers initially have no formatting unless the source is a spreadsheet with cells containing explicit numeric formatting. To change the default number formatting, set the axis's NumberFormat property to a new number format name or string. The list of named formats you can use appears in the Spreadsheet control's help file (opened by clicking the Help button on the spreadsheet) under the topic, "Number formats in a spreadsheet." You can also build a custom format string just like you can in Excel, and the symbols used in custom number formats are documented in the Excel 2000 help files starting with the overview topic titled, "Create a custom number format."

For both value and category axes, you can choose to drop some of the labels—for example, showing only every fifth label. This is useful only for axes that have too many labels to show without overlapping and for which the dropped labels can be inferred from visible labels such as a series of dates. Category axes will not drop labels by default. But suppose you have a large set of dates on a category axis. You can choose to drop some of them by setting the TickLabelSpacing property of the appropriate WCAxis object, either by selecting the axis and using the Property Toolbox at design time or in code at runtime. The setting for this property determines how many labels to skip between the labels that appear. Note that this property affects only labels, meaning the axis will still show tick marks where each label would have appeared. However, you can drop some of these tick marks as well by adjusting the TickMarkSpacing property to the same value as TickLabelSpacing.

The Chart Legend

By default, newly created charts do not have a legend. If you plan on showing more than one series of data points, you might want to add a legend to your chart to explain which color maps to which series. To add a legend, either select the chart and use the Property Toolbox at design time, or set the HasLegend property of the WCChart object to True in code at runtime.

Initially, the legend will contain one entry per series by default, but you can adjust that. To hide a legend entry, use the LegendEntries collection of the WCLegend object to retrieve the legend entry you want to hide, and then set the returned object's Visible property to False. At design time, you can simply select the legend entry itself and press the Delete key.

If you have more than one chart in the Chart control, you can create a legend for each individual chart or you can create one for the control as a whole. If you are showing multiple Pie charts, for example, it probably makes the most sense to show only one legend for all the pies since the color/category mapping will be same for each chart. Other chart types that show the series in the legend can benefit from individual legends if the set of series displayed differs among charts.

One rather annoying aspect of using a single legend for multiple charts that show series in the legend (charts other than Pie, Stacked Pie, and Doughnut) is that the Chart control will initially add an entry to the legend for each occurrence of each series in each chart. If you are showing two series on five charts, the chart space legend will contain ten legend entries, the same two entries repeated five times. To eliminate the extra legend entries, select and delete them at design time or use the WCLegendEntry object's Visible property in code as described earlier.