本記事は「ローコード開発プラットフォーム(Oracle APEX)を使って家計簿?アプリを作る(その1)〜(その3)」(以降「その1」「その2」等と表記します)の続きです。
(想定所要時間:30分)
1. 「ファセット検索ページ」のカスタマイズ
「その1」でファセット検索ページも作成しましたが、それ以降変更していないので、現在ファセット検索画面は下のようになっているとおもいます。
これを下のように変更します。
・右に円グラフを表示させます。この円グラフは左のチェックボックスのチェックに連動して表示が変化します。
・左のチェックボックス「項目」「金額」「備考」に「年月」が選択できるよう追加します。
・後は細かいですが、「備考」の最大表示項目数(デフォルトでは5)を100に増やします。
1-1. ファセット検索のチェックボックスで「年月」が選択できるようにする
順番としては円グラフを表示させる前にこちらを済ませておく必要があります。
支出データが入っている「支出」という名のテーブルには「年月日」という列はありますが、「年月」という列はないので、「年月日」から「年月」項目を作成し、その項目でファセット検索ができるようにします。
1-1-1. 「年月」項目の作成
ファセット検索のページ編集画面を開き、
- 「支出」をクリックします。
- 画面右側の「ソース」ー>「タイプ」が「表/ビュー」になっていると思いますので、それを「SQL問合せ」に変更します。
- 「コード・エディタ」をクリックしてSQLを編集します。
「コード・エディタ-SQL問合せ」がポップアップしてきますので、下のSQLを入力し、「OK」をクリックします。
select ID,
"年月日",
"項目",
"金額",
"備考",
to_char("年月日",'YYMM') "年月"
from "支出"
order by 2
- 支出 ー> 列を開いて、「年月」が追加されていることを確認します(前に確認してなかったので、追加されたのかどうか分からないと思いますが、追加されているのです)。
- 「年月」をクリックします。
- 「年月」はファセット検索でデータを絞り込む際に利用するものであり、検索結果に表示する必要はないので、タイプ を「プレーン・テキスト」から「非表示列」に変更します。
1-1-2. 「年月」ファセットの作成
続いてファセット検索のページ編集画面の ファセット を右クリックし ファセットの作成 を選択します。
新たに"P3_NEW"というファセットが追加されると思いますので、それをクリックし、名前を「P3_年月」に、ラベルを「年月」に、LOVのタイプを「個別値」にします。
ファセット ペインをスクロールして、リスト・エントリ ー> 上位件数でソート を OFF にします(これを ON のままにすると、ファセットの年月の並び順が、データ件数の多いものからになります)。
ファセット ペインをさらにスクロールして、ソース ー> データベース列 が 「年月」になっていることを確認します。
これでファセットへの「年月」追加は完了です。一旦保存して、ファセットに「年月」が選択できるようになっていることを確認してみてください。
1-2. ファセット検索ページに円グラフを追加する
いよいよ円グラフの追加となりますが、こちらについては下の blog を全面的に参考にいたしました。というかここに書いてある通りにすればできました(スクリプトはほぼコピペ)。これだけでなく、こちらの blog には Oracle APEX に関する非常に貴重で重要な情報が沢山あります。
1-2-1. ファセット検索のレポート・リージョンに静的IDを追加
- ファセット検索のページ編集画面を開き、「支出」レポート・リージョンをクリック
- 右のプロパティの 詳細 ー> 静的ID に "FC_EXP" と設定(名前は任意ですがこの通りでお願いします)
- 一旦保存してください。
1-2-2. SQLスクリプトを記述
次に、画面上部メニューから 「SQLワークショップ」ー>「SQLスクリプト」を選択し、「作成」をクリックして SQLスクリプトを記述します。
下のスクリプトをコピーしてご利用ください。
「その1」から「その3」から一転、長いスクリプトを書かなければならず、「あれ APEXってローコード開発プラットフォームなんじゃなかったっけ?」と思われたかも知れませんが、こういう場合もあるということでご容赦ください。ただ、このスクリプトは非常に汎用性が高いので、どのファセット検索でも(列名とデータ型さえ変えれば)ほぼコピペして使えます。事実私も上の参考にしたblogからコピーして少し編集しただけです。
create or replace type t_exp_row as object(
年月日 date,
項目 varchar2(50),
金額 number,
備考 varchar2(250),
年月 varchar2(4));
/
create or replace type t_exp_table as table of t_exp_row
/
create or replace function get_faceted_search_data(
p_page_id in number,
p_region_static_id in varchar2 )
return t_exp_table pipelined
is
l_region_id number;
l_context apex_exec.t_context;
type t_col_index is table of pls_integer index by varchar2(255);
l_col_index t_col_index;
---------------------------------------------------------------------------
procedure get_column_indexes( p_columns wwv_flow_t_varchar2 ) is
begin
for i in 1 .. p_columns.count loop
l_col_index( p_columns( i ) ) := apex_exec.get_column_position(
p_context => l_context,
p_column_name => p_columns( i ) );
end loop;
end get_column_indexes;
begin
-- 1. get the region ID of the Faceted Search region
select region_id
into l_region_id
from apex_application_page_regions
where application_id = v('APP_ID')
and page_id = p_page_id
and static_id = p_region_static_id;
-- 2. Get a cursor (apex_exec.t_context) for the current region data
l_context := apex_region.open_query_context(
p_page_id => p_page_id,
p_region_id => l_region_id );
get_column_indexes( wwv_flow_t_varchar2( '年月日', '項目', '金額', '備考', '年月' ) );
while apex_exec.next_row( p_context => l_context ) loop
pipe row( t_exp_row(
apex_exec.get_date ( p_context => l_context, p_column_idx => l_col_index( '年月日' ) ),
apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( '項目' ) ),
apex_exec.get_number ( p_context => l_context, p_column_idx => l_col_index( '金額' ) ),
apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( '備考' ) ),
apex_exec.get_varchar2( p_context => l_context, p_column_idx => l_col_index( '年月' ) ) ) );
end loop;
apex_exec.close( l_context );
return;
exception
when no_data_needed then
apex_exec.close( l_context );
return;
when others then
apex_exec.close( l_context );
raise;
end get_faceted_search_data;
/
sho err
コピーしたら「実行」をクリックして実行してください。実行したらエラーがないことを確認してください(スクリプト名は facet_chart(任意 なくてもOK)にしています)。
1-2-3. チャート・リージョン(円グラフ)の追加
再びファセット検索の編集画面を開き、チャート・リージョンを追加します。"Content Body"を右クリックし、「リージョンの作成」を選択します。
リージョンのタイトルは「備考別支出割合」(任意)、タイプを「チャート」にします。
"Attributes"をクリックし、チャートのタイプを円にします。
"Attributes"部分を下に少しスクロールすると「値」のフィールドがありますので、そこの書式を「通貨」にしてください。
次に画面左側の赤いバツがついている「新規」をクリックします。すると画面右側が「シリーズ」と変化しますので、
- 名前を「備考別支出割合」(任意)に
- ソース ー> タイプ を「SQL問合せ」に
- コード・エディタ アイコンをクリックしてSQLを記述し、"OK"をクリックして保存します。
select sum("金額") "金額", "備考" from table(get_faceted_search_data(:APP_PAGE_ID, 'FC_EXP'))
group by "備考"
order by 1 desc;
SQL問合せ から下にスクロールし、列のマッピング の ラベル を「備考」に 値 を「金額」に設定します。
一旦この状態で保存し、実行してみます。・・・いかがでしょうか。円グラフは出ましたでしょうか。出るには出たけれども
・支出の表の下に表示される
・ファセット検索と連動しない
という状態かと思います。なので、
・支出の表の横に円グラフを表示させる
・ファセット検索と連動させる
を行いたいと思います。
1-2-4. 円グラフを支出表の横に表示させる
ファセット検索のページ編集画面を開き、真ん中の レイアウト を御覧ください。「支出」の下に「備考別支出割合」があると思います。この「備考別支出割合」を「支出」の隣にドラッグ&ドロップすればOKです。なかなか思うようにドラッグ&ドロップできなくてストレスが溜まるかも知れませんがご容赦ください。
(2022年6月9日追記)
laughterさんがコメントに書いていただいている通り、下画面のように レイアウトー>新規行の開始 を OFF にしていただければ、ドラッグ&ドロップしなくてもレイアウトを変更することができます。
laughterさん ありがとうございます!
ドラッグ&ドロップの場合の Before After 画面
保存、実行してみると 表の右に円グラフがでていることと思います。
1-2-5. ファセット検索と円グラフを連動させる
こちらも上でご紹介したblogの内容に従って行ったものとなります。
まず、ファセット検索のページ編集画面を開き、「検索」を右クリックし、「動的アクションの作成」を選択します。
作成した動的アクションに対し、以下設定します。
名前: ファセット変更(任意)
イベント: カスタム
カスタム・イベント: facetschange (スペルを間違わぬよう この通りでお願いします)
選択タイプ: リージョン
リージョン: 検索
「表示」をクリックし、右ペインに下の通り設定します。
アクション: リフレッシュ
選択タイプ: リージョン
リージョン: 備考別支出割合 (今回作成した円グラフのチャートのことです)
イベント: ファセット変更
以上により、ファセットが変更されたときに円グラフの表示もリフレッシュされるようになります。最後にリフレッシュの際にファセットの値が円グラフに渡るように設定します。
「備考別支出割合」のシリーズ「備考別支出割合」をクリックし、送信するページ・アイテムに
P3_項目
P3_金額
P3_備考
P3_年月
をカンマで区切って指定します。
保存して実行してみてください。チェックボックスのチェックと円グラフが連動していれば成功です。
1-3. ファセットで「備考」の表示項目数を増やす
「備考」の最大表示項目数(デフォルトでは5)を100に増やします。
P3_備考 をクリックし、最大表示エントリ を 100 にします。
「その4」は以上です。1-3. は無くても良いような気がしますね。
次回(最終回)は「チャート」ページのグラフをクリックすると「ファセット検索」ページに遷移するところの設定となります。