対話モード・レポートのピボットを使用する

対話モード・レポートのピボットの機能を使っていて、気のついたことを記述します。主に以下の２点です。

1. ピボットの設定ダイアログにある行列という翻訳を変更する。
2. ピボットの設定によって実行されるSELECT文を確認する。

以前の記事「カレンダとレポートを連携させる」にてサンプル・データのプロジェクトとタスクから作成したAPEXアプリケーションを、今回の説明に使用します。

サンプル・データから作成した表の名前は元記事と同様にSAMP_PROJECTSとします。アプリケーションの名前はプロジェクト管理としました。

作成されたAPEXアプリケーションのSamp Projectsレポートのページに作成されている、対話モード・レポートを使ってピボット処理を実施します。

Samp Projectsレポートのページを開き、対話モード・レポートのアクション・メニューよりピボットを呼び出します。

設定ダイアログに行列の設定があります。少し考えると行となる列のことだとは分かりますが、翻訳として適切とは言い難いです。

英語表記を確認します。

アプリケーション定義のグローバリゼーションを開き、アプリケーションのプライマリ言語を英語(en)に切り替えます。

変更の適用をクリックします。

対話モード・レポートのメッセージはプライマリ言語の選択によって切り替わります。

ページのタイトル、ナビゲーション・メニュー、リージョンのタイトルなどアプリケーションのデータとして日本語のテキストが設定されている部分は、プライマリ言語を変更しても設定済みの日本語のテキストが表示されます。これらを他の言語に切り替えるには、APEXアプリケーションの翻訳を行なう必要があります。APEXアプリケーションの翻訳については、こちらの記事で紹介しています。

ActionsメニューよりPivotを実行します。

Pivotの設定ダイアログが開きます。Row Columnsが行列と翻訳されていることが分かります。間違いではないかもしれませんが、一般的には行列はMatrixのことだと認識されると思います。

元となる英語表記を確認したので、アプリケーションのプライマリ言語を日本語に戻しておきます。

Oracle APEXの製品に含まれる翻訳メッセージは、共有コンポーネントのテキスト・メッセージに、メッセージを作成することで置き換えることができます。

作成済みのテキスト・メッセージが一覧されます。

テキスト・メッセージの作成をクリックします。

名前としてAPEXIR_ROW_COLUMNS、言語として日本語(ja)を選択し、テキストとして行となる列を指定します。JavaScriptで使用はオフです。

テキスト・メッセージの作成をクリックします。

テキスト・メッセージが作成されます。

対話モード・レポートのピボットを開くと、行列と表記されていたところが行となる列に置き換わっていることが確認できます。

作成するテキスト・メッセージAPEXIR_ROW_COLUMNSは、Oracle APEXがインストールされているスキーマに含まれている表WWV_FLOW_MESSAGES$に保存されています。

以前にOracle APEXのアプリケーションの翻訳を支援するアプリケーションの作成方法を紹介する記事を書いています。今回はこのアプリケーション（demo/demo）を使用して、置き換えるメッセージを特定しています。

Messagesのページを開き、行列という文字を含むメッセージを検索しています。

ひとつひとつテキスト・メッセージを作成して、翻訳文字列を置き換えることは大変です。

Oracle APEXではテキスト・メッセージを、APEX_LANG.CREATE_MESSAGE（更新はUPDATE_MESSAGE）を呼び出して作成できます。

以下のコードを実行します。C_APP_IDには、更新対象のアプリケーションIDを指定します。

共有コンポーネントのテキスト・メッセージを開くと、テキスト・メッセージが８つ作成されていることが確認できます。すべてのメッセージで、行列という表記を行となる列に置き換えています。

アクション・メニューよりピボットを開きます。行列の表記が行となる列に置き換えられていることが確認できます。

実際にピボット処理を行ってみます。

ピボット列としてAssigned ToおよびStatusを追加します。行となる列にはProjectとTask Nameを追加します。ファンクションにカウントを選び、カウントの対象として列Statusを選択します。合計をオンにします。

以上で適用をクリックします。

列ProjectとTask Nameが縦方向（行となる列）、列Assigned ToとStatusが横方向（ピボット列）に配置され、値として列Statusのカウントが表示されます。

データ量が多いため、ページ送りやスクロールをしないとレポートの全体が見えません。フィルタを設定し、表示されるデータを制限します。

ProjectをMigrate Desktop Applicationに限定します。アクション・メニューのフィルタを呼び出します。

対話モード・レポートの表示はピボットされて列と行が置き換わっていますが、フィルタの設定はピボットの設定に影響されません。列としてProject、演算子は=、式としてMigrate Desktop Applicationを指定します。

表示されるデータが少なくなり、レポートの最後に合計が表示されていることが確認できます。

対話モード・レポートにピボットの設定を行ったときに発行されているSELECT文を確認します。

開発者ツール・バーのデバッグより、デバッグ・レベルを情報に変更します。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文をデータ・ソースとしてクラシック・レポートを作成してみます。

ページを実行すると、ピボットされた結果がクラシック・レポートに表示されることが確認できます。

今回作成したAPEXアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/sample-interactive-report-pivot.zip

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完