データベースからデータを再インポートしてグラフを更新する

概要

このチュートリアルは説明のみを目的としています。チュートリアルに示されている接続文字列は、インストールされたデータベースには接続しません。チュートリアルで説明したプロセスを実行できるように、独自のサーバーに AdventureWorks データベースをセットアップする場合は、このGitHubページを参照してください。

このチュートリアルでは、データベースからのデータインポートおよび分析とグラフ作成を行います。その後、OriginのLabTalkスクリプトを使用したデータベースクエリーの更新とデータ再インポートを行うボタンをグラフに追加します。

学習する項目

このチュートリアルでは、以下の項目について解説します。

クエリーのデータ範囲を定義するためにSQLクエリーのLabTalk変数を使用して、データベースからデータをインポートする
ピボットテーブルとグラフを作成し、データを分析する
クエリーのデータ範囲を編集するボタンをグラフに追加して、データベースからの再インポートを行う

ステップ

サーバマシンnoho上にSQLサーバAdventureWorks2008を予めセットアップ済であることを想定しています。

データベースからデータをインポートする

新しいプロジェクトを開始します。データベースアクセスツールバーのSQLエディタを開くボタンをクリックしてSQLエディタを起動します。

メニューからファイル：接続文字列の編集を選択してテキストボックスに以下の接続文字列を入力します。

Provider=SQLOLEDB.1;
Password=labtalk2015;
Persist Security Info=TRUE;
USER ID=CONNECT;
Initial Catalog=AdventureWorks2008;
DATA SOURCE=noho

テストボタンをクリックして接続を確認します。問題なければ、OKをクリックしてデータベースにアクセスします。

SQLエディタでクエリー：LabTalk...と操作し、LabTalkサポート設定ダイアログを開きます。ダイアログで、LabTalk置換(%,$)をするのチェックボックスを付けて、以下のスクリプトをテキストボックスに入力します。

string pastFrom$ = ""; string pastTo$ = ""; string curFrom$ = ""; string curTo$ = ""; if(exist(strPast1$, 18) == 18)  // strPast1$に値があるか確認 { // strPast1$に値がある場合 pastFrom$ = strPast1$;  // strPast1 $をpastFrom$に割り当てる } else  // strPast1$が定義されていない場合 { pastFrom$ = "1/1/2003";  // 定数文字列をpastFrom$に割り当てる } if(exist(strPast2$, 18) == 18) { pastTo$ = strPast2$; } else { pastTo$ = "3/31/2003"; } if(exist(strCurrent1$, 18) == 18) { curFrom$ = strCurrent1$; } else { curFrom$ = "4/1/2003"; } if(exist(strCurrent2$, 18) == 18) { curTo$ = strCurrent2$; } else { curTo$ = "6/30/2003"; }

OK をクリックして、SQLエディタに戻ります。右側のテキストボックスに以下のSQLステートメントを入力します。

SELECT OrderDateTotalDueAndProductSubcategoryID.OrderDate AS 'Order Date', 'Date Range' = 
	CASE 
		WHEN OrderDateTotalDueAndProductSubcategoryID.OrderDate < '%(curFrom$)' THEN 'Past'
		ELSE 'Current'
	END,
	Production.ProductSubcategory.Name AS 'Subcategory Name', 
	OrderDateTotalDueAndProductSubcategoryID.TotalDue AS 'Total Due'
FROM 
	(SELECT OrderDateAndTotalDue.OrderDate, OrderDateAndTotalDue.TotalDue, Production.Product.ProductSubcategoryID
	FROM
		(SELECT Sales.SalesOrderheader.OrderDate, Sales.SalesOrderDetail.ProductID, Sales.SalesOrderheader.TotalDue
		FROM Sales.SalesOrderheader
		INNER JOIN Sales.SalesOrderDetail
		ON Sales.SalesOrderheader.SalesOrderID=Sales.SalesOrderDetail.SalesOrderID
		WHERE (Sales.SalesOrderheader.OrderDate BETWEEN '%(pastFrom$)' AND '%(pastTo$)'
				OR Sales.SalesOrderheader.OrderDate BETWEEN '%(curFrom$)' AND '%(curTo$)')) AS OrderDateAndTotalDue
	INNER JOIN Production.Product
	ON OrderDateAndTotalDue.ProductID=Production.Product.ProductID) AS OrderDateTotalDueAndProductSubcategoryID
INNER JOIN Production.ProductSubcategory
ON OrderDateTotalDueAndProductSubcategoryID.ProductSubcategoryID=Production.ProductSubcategory.ProductSubcategoryID

上の図で確認できるように、合計で3つのLabTalk変数がステートメントの中で使用されています。

ツールバーの最後のボタンをクリックすると、置換結果を確認出来ます。
メニューから、ファイル：アクティブワークシートに保存を選択し、この設定をワークシートに保存します。そして、ワークシートにデータをインポートボタンをクリックしてインポートを行い、SQLエディタ閉じます。次の画像で、インポートされたデータを確認出来ます。ワークシートの右上に表示される黄色いアイコンは、ワークシートにデータベース接続が含まれていることを示します。

ピボットテーブルの作成と列演算の実行

インポートしたデータで操作を開始します。列Cを選択し、ワークシート：ピボットテーブルと操作してData Manipulation\Worksheet: wpivot ダイアログを開きます。
再計算を自動に設定します。
ピボットテーブルの列ソースで右向きの三角形をクリックし、列Bを設定します。
サマリー出力タイプで合計を選択します。ピボットテーブルのデータソースの行が追加されます。列Dを右向きの三角形をクリックして追加します。
オプションノードを開き、出力列のソートをなしに設定します。
OKボタンをクリックして、設定した方法でピボットテーブルを作図します。
列の追加ボタンを2回クリックして、新しい列を2列追加します。
それぞれのロングネームをPercent Change と Gain/Loss にします。
列DのF(x)=セルにおいて、

Origin 2017を使用している場合、以下を入力します。
```
B==0 ? 100 : 100*(C-B)/B
```
Origin 2017より前のバージョンを使用している場合、以下を入力します。
```
col(B)==0 ? 100 : 100*(col(C)-col(B))/col(B)
```
列EのF(x)=セルにおいて、

Origin 2017を使用している場合、以下を入力します。
```
D > 0 ? 12 : 2
```
Origin 2017より前のバージョンを使用している場合、以下を入力します。
```
col(D) > 0 ? 12 : 2
```
結果は次のようになります。

グラフの作成とグラフの編集

Pivot1ワークシートで、列CとDを選択して、メニューから作図: 複数区分: 積み上げ... を選択します。
ダイアログで、以下のように設定します。
OKをクリックします。2区分のグラフが作成されます。
左パネルの下軸をダブルクリックします。目盛ラベルタブで、割る値を1000000 にし、ラベル接尾語でMを入力します。スケールタブで、開始と終了を0と120に設定します。主目盛のタイプは増分とし、値は20にします。軸ダイアログの左パネルで垂直方向を選択します。再スケール方法を自動にします。OKをクリックします。
右パネルの下軸をダブルクリックします。スケールタブで、開始と終了を-100と400に設定します。主目盛のタイプは増分とし、値は100にします。軸ダイアログの左パネルで垂直方向を選択します。再スケール方法を自動にします。グリッド線タブを開き、左パネルで垂直方向が選択されています。下の方にあるY=0 にチェックをつけます。特別な軸刻みタブを開き、左パネルで下軸を選択します。軸の開始行の表示を隠すに設定します。これをしないと、左プロットの最後の目盛ラベルが、右プロットの最初の目盛ラベルと重なります。OKをクリックします。
凡例、右軸の目盛ラベルなどを削除すると、下図のようになります。
左の横棒グラフの塗り色を薄い空色にします。
右の横棒グラフの塗り色を列E(Gain/Loss)のインデックスにします。ラベルを有効にし、ラベル形式をカスタム、フォーマット指定に$(Y, .1)%を入力して、接尾語%付きの小数点以下1桁のY値を表示します。
必要に応じて上、右、下の軸を削除するなどの軸編集を行います。グリッド線を表示します。左軸の主目盛、副目盛などを非表示にすると、グラフは下図のようになります。

ボタンを追加してLabTalkスクリプトを実行する

グラフの右下で右クリックして、テキストの追加を選択します。テキストボックスにUpdateと入力します。
このテキストUpdateを右クリックして、プロパティを選択し、テキストオブジェクトダイアログを開きます。プログラミングタブを開きます。Note: Origin 2017より前のバージョンでは、メニューからオブジェクトのプログラミング制御を選択して開くダイアログを使用します。

のあとでスクリプトを実行の前にあるドロップダウンでボタンアップを選択し、下にあるテキストボックスに次のスクリプトを入力します。OKをクリックします。

double pastDate1 = date(1/1/2003);
double pastDate2 = date(3/31/2003);
double currentDate1 = date(4/1/2003);
double currentDate2 = date(6/30/2003);
// strPast1$, strPast2$, strCurrent1$, strCurrent2$が同時に存在するか確認
if((exist(strPast1$, 18) == 18) && (exist(strPast2$, 18) == 18) && (exist(strCurrent1$, 18) == 18) && (exist(strCurrent2$, 18) == 18))
{
	// ある場合、ダイアログに表示するようにdouble値に設定します
	pastDate1 = date(strPast1$);
	pastDate2 = date(strPast2$);
	currentDate1 = date(strCurrent1$);
	currentDate2 = date(strCurrent2$);
}
// 日付設定のダイアログ
GetN (Last Seaon) :@G
	(From) pastDate1:@FD0
	(To) pastDate2:@FD0 (-) :@G
     (Current Seaon) :@G
	(From) currentDate1:@FD0
	(To) currentDate2:@FD0 (-) :@G
(Set Date);
// 設定された日付を取得し文字列に変換
string strPast1$ = $(pastDate1, D0);
string strPast2$ = $(pastDate2, D0);
string strCurrent1$ = $(currentDate1, D0);
string strCurrent2$ = $(currentDate2, D0);
// 設定された日付に従ってデータベースからデータを再インポート
dbimport iw:=[book1]sheet1!;
range rPivot = [Book1]Pivot1!;  // ピボットテーブル範囲
layer.x.to = rPivot.maxRows+0.5;  // 垂直軸の値を設定
rPivot.runfilter();  // データフィルタを実行

テキストオブジェクトがボタンになり、これをクリックすると、入力されたスクリプトが実行されます。

このボタンをクリックすると、デフォルトの日付が入力された状態でダイアログが開きます。（初回起動時のみ、初回起動ではない場合は、最後に使用したときの値）
では期間を変更してみます。「Last Season」を2004/01/01から2004/03/31まで、「Current Season」を2004/04/01から2004/06/30までに設定します。
OKをクリックすると、指定した日付範囲のデータがワークシートに再インポートされ、Pivot1ワークシートが更新されます。グラフも同様に更新されます。Pivot1シートのGain/Loss列の値により、横棒の色が塗り分けられます。1つの製品にロスがあるということが簡単に伝えられます。