Oracle ApexでCRUD画面を爆速開発 - その7（積み上げグラフを作成する）

シリーズ

Oracle ApexでCRUD画面を爆速開発 - その1（構成周り、一覧表示）
Oracle ApexでCRUD画面を爆速開発 - その2（詳細表示）
Oracle ApexでCRUD画面を爆速開発 - その3（登録/更新処理,Validation,アクセス制限など）
Oracle ApexでCRUD画面を爆速開発 - その4（選択リストの内容で一覧表示内容を更新させる）
Oracle ApexでCRUD画面を爆速開発 - その5（Classic Reportのヘッダの上にグループヘッダ行を作る）
[Oracle ApexでCRUD画面を爆速開発 - その6（Classic Reportのレコード条件に応じて行の背景色を変える）]
(https://qiita.com/harukb/items/b976f3fad9d58bdb4587)

今回の主な内容

• 積み上げグラフを作る

やっぱり欲しくなっちゃいますよね。分かります、その気持ち。

積み上げグラフを作る

※　まずお断りしておくと積み上げグラフをOracle Apexで作成する場合には以下の知識が恐らく必要になるため、知識がない＆時間がない場合は別の方法（Oracle Analytics Cloud）などで実現されるのがよろしいかと思います。

• グラフのX軸、Y軸に対応するKeyによる集計
• 集計結果に対しKeyの欠損値を埋めるSQLを書く

完成品

DNAME(部署)がX軸、Y軸にJOB毎に積み上げられたSALの合計を表示しています。

集計表の作成フロー

各EMPLOYEEが1件となるEMP表から積み上げグラフのデータマートを作成するために今回は以下のステップでViewを作成します。

※ 実運用での件数を考慮した場合、Viewでなく集計済データを保持したテーブルをご用意されるのが良い場合もあると思いますが、ここではViewにて進めます。

View作成 前半） EMP表、DEPT表からV_EMP,V_EMP_CHART_ORGの作成

1 V_EMPを作成する

CREATE OR REPLACE FORCE VIEW "V_EMP" ("EMPNO", "DEPTNO", "DNAME", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM") AS
SELECT
E.EMPNO,E.DEPTNO,D.DNAME,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM
FROM
EMP E LEFT OUTER JOIN DEPT D
ON
E.DEPTNO = D.DEPTNO
ORDER BY
E.EMPNO
/

2 集計View:V_EMP_CHART_ORGを作成する

CREATE OR REPLACE FORCE VIEW "V_EMP_CHART_ORG" ("LINK", "LABEL", "SERIES", "VALUE") AS
SELECT
null LINK,D.DNAME LABEL,E.JOB SERIES,SUM(E.SAL) VALUE
FROM
EMP E LEFT OUTER JOIN DEPT D
ON
E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME,E.JOB
ORDER BY DNAME,JOB
/

※ LINK列（追加）,DNAMEが"LABEL"にJOBが"SERIES"という列名に変えているのは
ApexのPage Designer画面でのチャート設定時の表現に合わせているためです。そのままでも画面でマッピングができるので問題ないと思われます。（またLINK列は結局利用しなかったので不要のようです。）

View作成 後半）V_EMP_CHART_ORGを元に欠損値を動的に埋めたV_EMP_CHART1の作成

※ 全KEYの組み合わせ集合を定義してV_EMP_CHART_ORGを左辺外部結合し、左辺がないレコードには右辺のVALUEを0で補います。
（補足）全KEYの組み合わせ集合、、定義が大変だしKEYが増えたらどうするの？という問題があるので、より良き取得方法があればアドバイス頂けると嬉しいです。

CREATE OR REPLACE FORCE VIEW "V_EMP_CHART1" ("LINK", "LABEL", "SERIES", "VALUE") AS
SELECT null LINK,MST.LABEL,MST.SERIES,NVL(VE.VALUE,0) VALUE
FROM
(
SELECT 'ALPHA' LABEL,'ANALYST' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'ALPHA' LABEL,'SALESMAN' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'ALPHA' LABEL,'CLERK' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'ALPHA' LABEL,'PRESIDENT' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'BETA' LABEL,'ANALYST' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'BETA' LABEL,'SALESMAN' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'BETA' LABEL,'CLERK' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'BETA' LABEL,'PRESIDENT' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'DELTA' LABEL,'ANALYST' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'DELTA' LABEL,'SALESMAN' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'DELTA' LABEL,'CLERK' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'DELTA' LABEL,'PRESIDENT' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'GAMMA' LABEL,'ANALYST' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'GAMMA' LABEL,'SALESMAN' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'GAMMA' LABEL,'CLERK' SERIES,0 VALUE FROM DUAL
UNION ALL
SELECT 'GAMMA' LABEL,'PRESIDENT' SERIES,0 VALUE FROM DUAL
) MST LEFT OUTER JOIN
V_EMP_CHART_ORG VE
ON MST.LABEL = VE.LABEL AND MST.SERIES = VE.SERIES
ORDER BY LABEL,SERIES
/

ここまででやっとデータの準備ができました。

V_EMP_CHART1を利用して積み上げグラフの作成

Regionを追加する

Page DesignerでRegion追加を行います。Type="Chart"を選択します。
Sourceは指定せず空のままとしています。

Legend（凡例）を表示する

Attributeで設定する

Stack=ONにする

引き続きこちらもAttributeで設定する

※ 上のOrientationでグラフを水平か垂直タイプかも選べます

Seriesを追加する

※ Seriesとは積み上げられる要素です。今回はDNAME列由来のSERIES列がそれになります。
Seriesを明示的に個別設定していきます。（補足：自動認識もあるようですがうまく働かなかったのでこの方法を取っています(2020/10/17現在)）

1.左ペインの対象のRegionのSeriesで右クリックして"Create Series"を選択する。

2."SERIES > Identification"に名前を設定（下の図ではSALES）
3."Source-SQL Query"にて以下のSQLを設定する。

SELECT LINK,LABEL,SERIES,VALUE FROM V_EMP_CHART1 WHERE SERIES='SALESMAN'

4.同様に他のSeriesも作成する（下の図ではそれぞれCLERK,ANALYST,PRESIDENTを設定）

• "Source-SQL Query"はそれぞれ以下のように設定する。

CLERK:
SELECT LINK,LABEL,SERIES,VALUE FROM V_EMP_CHART1 WHERE SERIES='CLERK'
ANALYST:
SELECT LINK,LABEL,SERIES,VALUE FROM V_EMP_CHART1 WHERE SERIES='ANALYST'
PRESIDENT:
SELECT LINK,LABEL,SERIES,VALUE FROM V_EMP_CHART1 WHERE SERIES='PRESIDENT'

• Column Mappingはそれぞれ自動設定されているであろうLabel<-"LABEL",Value<-"VALUE"であることを確認する。

5.Saveして実行
※ X軸はDNAME,Y軸はJOB毎にSALが積みあがりました。以下のように、LEGEND（凡例）マウスオーバーで対象のJOBを強調表示にすることも可能です。