- ピボットの設定ダイアログにある行列という翻訳を変更する。
- ピボットの設定によって実行されるSELECT文を確認する。
以前の記事「カレンダとレポートを連携させる」にてサンプル・データのプロジェクトとタスクから作成したAPEXアプリケーションを、今回の説明に使用します。
サンプル・データから作成した表の名前は元記事と同様にSAMP_PROJECTSとします。アプリケーションの名前はプロジェクト管理としました。
作成されたAPEXアプリケーションのSamp Projectsレポートのページに作成されている、対話モード・レポートを使ってピボット処理を実施します。
Samp Projectsレポートのページを開き、対話モード・レポートのアクション・メニューよりピボットを呼び出します。
設定ダイアログに行列の設定があります。少し考えると行となる列のことだとは分かりますが、翻訳として適切とは言い難いです。
英語表記を確認します。
アプリケーション定義のグローバリゼーションを開き、アプリケーションのプライマリ言語を英語(en)に切り替えます。
変更の適用をクリックします。
対話モード・レポートのメッセージはプライマリ言語の選択によって切り替わります。
ページのタイトル、ナビゲーション・メニュー、リージョンのタイトルなどアプリケーションのデータとして日本語のテキストが設定されている部分は、プライマリ言語を変更しても設定済みの日本語のテキストが表示されます。これらを他の言語に切り替えるには、APEXアプリケーションの翻訳を行なう必要があります。APEXアプリケーションの翻訳については、こちらの記事で紹介しています。
ActionsメニューよりPivotを実行します。
元となる英語表記を確認したので、アプリケーションのプライマリ言語を日本語に戻しておきます。
Oracle APEXの製品に含まれる翻訳メッセージは、共有コンポーネントのテキスト・メッセージに、メッセージを作成することで置き換えることができます。
作成済みのテキスト・メッセージが一覧されます。
テキスト・メッセージの作成をクリックします。
名前としてAPEXIR_ROW_COLUMNS、言語として日本語(ja)を選択し、テキストとして行となる列を指定します。JavaScriptで使用はオフです。
テキスト・メッセージの作成をクリックします。
テキスト・メッセージが作成されます。
対話モード・レポートのピボットを開くと、行列と表記されていたところが行となる列に置き換わっていることが確認できます。
以前にOracle APEXのアプリケーションの翻訳を支援するアプリケーションの作成方法を紹介する記事を書いています。今回はこのアプリケーション（demo/demo）を使用して、置き換えるメッセージを特定しています。
Messagesのページを開き、行列という文字を含むメッセージを検索しています。
ひとつひとつテキスト・メッセージを作成して、翻訳文字列を置き換えることは大変です。
Oracle APEXではテキスト・メッセージを、APEX_LANG.CREATE_MESSAGE（更新はUPDATE_MESSAGE）を呼び出して作成できます。
以下のコードを実行します。C_APP_IDには、更新対象のアプリケーションIDを指定します。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| declare | |
| C_APP_ID constant number := 274; | |
| /*+ | |
| * 翻訳メッセージの追加または編集を行なう。 | |
| */ | |
| procedure create_or_update_message( | |
| p_application_id in number | |
| ,p_translatable_message in varchar2 | |
| ,p_message_text in varchar2 | |
| ,p_language_code in varchar2 default 'ja' | |
| ,p_is_js_message in varchar2 default 'Y' | |
| ) | |
| as | |
| l_translation_entry_id number; | |
| p_used_in_javascript boolean; | |
| begin | |
| /* メッセージをJavaScriptで使うかどうかの条件をBoolean型に変換する */ | |
| p_used_in_javascript := p_is_js_message = 'Y'; | |
| /* すでに翻訳メッセージが登録済みであれば更新する。 */ | |
| select translation_entry_id into l_translation_entry_id | |
| from apex_application_translations | |
| where 1=1 | |
| and application_id = p_application_id | |
| and translatable_message = p_translatable_message | |
| and language_code = p_language_code; | |
| /* 翻訳メッセージの更新 */ | |
| apex_lang.update_message( | |
| p_id => l_translation_entry_id | |
| ,p_message_text => p_message_text | |
| ); | |
| exception | |
| when no_data_found then | |
| /* 翻訳メッセージが未登録なので追加する。 */ | |
| apex_lang.create_message( | |
| p_application_id => p_application_id | |
| ,p_name => p_translatable_message | |
| ,p_language => p_language_code | |
| ,p_message_text => p_message_text | |
| ,p_used_in_javascript => p_used_in_javascript | |
| ); | |
| end create_or_update_message; | |
| begin | |
| create_or_update_message(C_APP_ID,'APEXIR_PIVOT_ROW_COLUMN_INVALID','別の行となる列を選択してください。行となる列のHTML式またはリンクには、ピボット列または集計列として定義された列が含まれます。'); | |
| create_or_update_message(C_APP_ID,'APEXIR_ADD_ROW_COLUMN','行となる列の追加'); | |
| create_or_update_message(C_APP_ID,'APEXIR_PIVOT_AGG_NOT_ON_ROW_COL','行となる列として選択された列で集計できません。'); | |
| create_or_update_message(C_APP_ID,'APEXIR_ROW_COLUMNS','行となる列'); | |
| create_or_update_message(C_APP_ID,'APEXIR_ROW_COL_DIFF_FROM_PIVOT_COL','行となる列はピボット列と異なる必要があります。'); | |
| create_or_update_message(C_APP_ID,'APEXIR_ROW_COLUMN_N','行となる列%0'); | |
| create_or_update_message(C_APP_ID,'APEXIR_ROW_COLUMN_NOT_NULL','行となる列が指定される必要があります。'); | |
| create_or_update_message(C_APP_ID,'APEXIR_SELECT_ROW_COLUMN','- 行となる列の選択 -'); | |
| end; |
共有コンポーネントのテキスト・メッセージを開くと、テキスト・メッセージが８つ作成されていることが確認できます。すべてのメッセージで、行列という表記を行となる列に置き換えています。
アクション・メニューよりピボットを開きます。行列の表記が行となる列に置き換えられていることが確認できます。
実際にピボット処理を行ってみます。
ピボット列としてAssigned ToおよびStatusを追加します。行となる列にはProjectとTask Nameを追加します。ファンクションにカウントを選び、カウントの対象として列Statusを選択します。合計をオンにします。
以上で適用をクリックします。
列ProjectとTask Nameが縦方向（行となる列）、列Assigned ToとStatusが横方向（ピボット列）に配置され、値として列Statusのカウントが表示されます。
データ量が多いため、ページ送りやスクロールをしないとレポートの全体が見えません。フィルタを設定し、表示されるデータを制限します。
対話モード・レポートの表示はピボットされて列と行が置き換わっていますが、フィルタの設定はピボットの設定に影響されません。列としてProject、演算子は=、式としてMigrate Desktop Applicationを指定します。
開発者ツール・バーのデバッグより、デバッグ・レベルを情報に変更します。Oracle APEXの標準コンポーネント（レポートやチャート）が発行するSQLは、デバッグ・レベルが情報であればログに出力されます。SQLの実行計画まで確認したい場合は、デバッグ・レベルを完全トレースまで上げる必要がありますが、アプリケーションの実行速度は大幅に低下します。
デバッグ・レベルを情報に切り替えた時点で、ページが再ロードされます。そのため、対話モード・レポートによるSELECT文は実行済みで、ログに実行されたSELECT文が出力されています。
開発者ツール・バーのデバッグより、デバッグの表示を実行します。
対話モード・レポートのページで発生した直近のshowの処理のログを開きます。
出力されているログに、Generated Component SQL Queryとして、対話モード・レポートが発行しているSELECT文が記載されています。
一番最初のSELECT文はピボット列として横並びになる列名（集約条件）を取り出しています。
select distinct "ASSIGNED_TO","STATUS"
from
(
(
select i.*
from
(
select "PROJECT","TASK_NAME","START_DATE","END_DATE","STATUS","ASSIGNED_TO","COST","BUDGET","ID"
from
(
select /*+ qb_name(apex$inner) */ d."PROJECT",d."TASK_NAME",d."START_DATE",d."END_DATE",d."STATUS",d."ASSIGNED_TO",d."COST",d."BUDGET",d."ID"
from
(
select x.* from "SAMP_PROJECTS" x
) d
) i
) i
where 1=1
and "PROJECT"='Migrate Desktop Application'
)
)
order by "ASSIGNED_TO" asc nulls last, "STATUS" asc nulls last
ピボット処理を行なうSELECT文は以下になります。
select
"PROJECT",
"TASK_NAME",
"APXWS_PV1_PFC1",
"APXWS_PV2_PFC1",
"APXWS_PV3_PFC1",
SUM("APXWS_PV1_PFC1") over () "APXWS_PV1_PFC1_SUM",
SUM("APXWS_PV2_PFC1") over () "APXWS_PV2_PFC1_SUM",
SUM("APXWS_PV3_PFC1") over () "APXWS_PV3_PFC1_SUM",
"APEX$TOTAL_ROW_COUNT" "AGGREGATE_ROW_COUNT",
count(*) over() "APEX$TOTAL_ROW_COUNT"
from
(
select "ASSIGNED_TO", "STATUS", "PROJECT", "TASK_NAME","APEX$TOTAL_ROW_COUNT" "AGGREGATE_ROW_COUNT","APEX$TOTAL_ROW_COUNT"
from
(
select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
from
(
select "APEX_ROW_PK","PROJECT","TASK_NAME","START_DATE","END_DATE","STATUS","ASSIGNED_TO","COST","BUDGET","ID"
from
(
select /*+ qb_name(apex$inner) */d."APEX_ROW_PK",d."PROJECT",d."TASK_NAME",d."START_DATE",d."END_DATE",d."STATUS",d."ASSIGNED_TO",d."COST",d."BUDGET",d."ID"
from
(
select ('')"APEX_ROW_PK","PROJECT","TASK_NAME","START_DATE","END_DATE","STATUS","ASSIGNED_TO","COST","BUDGET","ID"
from
(
select x.* from "SAMP_PROJECTS" x
) d
) d
) i
) i
where 1=1
and "PROJECT"='Migrate Desktop Application'
order by "PROJECT" asc nulls last
)
)
PIVOT
(
COUNT( "STATUS") "PFC1" FOR ("ASSIGNED_TO", "STATUS")
IN (
('John Watson', 'Open') "APXWS_PV1",
('Mark Nile', 'Closed') "APXWS_PV2",
('Mark Nile', 'Open') "APXWS_PV3"
)
)
合計（合計がオンであるため）やAPEXが内部的に使用するページングのための値の計算を含んでいるため、SELECT文が複雑になっています。単純化すると以下のようなSELECT文が実行されています。最初のSELECT文によって取り出された結果が、ピボット関数の集約条件として指定されています。
select * from
(
select project, task_name, assigned_to, status
from samp_projects
where project = 'Migrate Desktop Application'
)
pivot
(
count(status) for (assigned_to, status)
in
(
('John Watson', 'Open') "John Watson - Open",
('Mark Nile', 'Closed') "Mark Nile - Closed",
('Mark Nile', 'Open') "Mark Nile - Open"
)
)
ソースとなるSELECT文がどのような処理を含んでいたとしても（ピボットや分析関数など）SELECT文の結果は集合（つまり表）であるため、レポートのソースとして記述すると表形式で表示されます。
上記のSELECT文をデータ・ソースとしてクラシック・レポートを作成してみます。
https://github.com/ujnak/apexapps/blob/master/exports/sample-interactive-report-pivot.zip
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
