SSRS で新規陽性者数の推移グラフを作ってみる【第４回】

2021年9月29日

第３回からの続きです。現在できてるレポートを確認してみます。

目標となるレポートは以下の通りで、今回は指標値（新規陽性者数、１週間平均、前週平均）を表示してみます。

記事中で利用しているファイルは以下から取得してください。こちらが本家です。

newly_confirmed_cases_daily ダウンロード

1. フィルターの修正
2. 指標値の出力方針を決定
3. データセットへの項目追加
4. 「表」を使ったデータセットのテスト出力
5. 「一覧」の動作確認
- 5.1. 関連

フィルターの修正

まず、このレポート全体の数値は「都道府県＝ALL」のデータを対象にしていることは第２回でも記載しました。なので「フィルター」という機能でWHERE句にあたる条件を設定しているのですが「グラフデータ」に対して設定していました。今回追加する指標値も当然ながら「都道府県＝ALL」ですが、また同じような条件を設定する必要がでてきます。

ということで、フィルターは「グラフデータ」で行うのではなく「データセット」で行うように修正します。これにより、このデータセットを利用するコントロールはすべて「当道府県」を意識する必要がなくなります。まず、グラフのプロパティのフィルターより、都道府県の条件を選択状態にして「削除」ボタンで削除します。

次にデータセットのプロパティを選択します。

フィルターに「都道府県＝ALL」の条件を追加します。

「実行」ボタンにより結果が変わらないことを確認しておいてください。

指標値の出力方針を決定

今回追加する指標値をどのように出力するかをあらかじめ決定しておきます。

項目名	内容	出力方針
新規陽性者数	「情報更新日」の「新規陽性者数」	データセットの最初の行の「新規陽性者数」を出力
前日比陽性者数	「新規陽性者数」（情報更新日）－「新規陽性者数」（情報更新日ー１日）	データセットに「前日新規陽性者数」、「新規感染者数前日比」を追加し「新規感染者数前日比」の絶対値を出力
陽性者数傾向	「前日比陽性者数」＞０の場合「↑」、＝０の場合「→」、＜０の場合「↓」	インジケーターを利用し「新規感染者数前日比」の値によってアイコンを変化させるように設定
一週間平均	「情報更新日」を含む直近７日間の「新規陽性者数」の平均値	データセットに「連番」「相対日数」「週開始日」を追加し「週開始日」単位で集計した場合の「連番」＝０の「新規陽性者数」の平均値を出力
前週平均	「一週間平均」の前７日間の「新規陽性者数」の平均値	「一週間平均」と同様だが「連番」＝７の「新規陽性者数」の平均値を出力

指標値の出力方針

データセットに新規追加する項目について説明します。
データセットに項目追加する方法には２通りあります。「データセットの元になるクエリ（SQL）そのものに項目を追加する方法」と「クエリ（SQL）の取得項目とは別に計算列として項目を追加する方法」です。今回はどちらも使います。

尚、「一週間平均」「前週平均」でいうところの「週」ですが、目標とするレポートの実際の動作を確認すると、日曜始まりの週ではなく「情報更新日」から遡って７日間単位で区切った単位を週として算出していました。これは、別の記事である「週別集計をSQLで行う」の「最終日から数えて７日単位の集計」に該当します。今回もそのテクニックを利用しますので参照しておいてください。

「前日新規陽性者数」

クエリ（SQL）そのものに列追加します。LEAD 関数を利用して、日付の降順で次のレコードにある「新規陽性者数」を取得するようにします。

「新規感染者数前日比」

クエリで取得した項目を利用した計算列で定義します。「新規陽性者数」－「前日新規陽性者数」です。

「連番」

クエリ（SQL）そのものに列追加します。ROW_NUMBER 関数を利用して、日付の降順で０からの連番を振ります。目的は７日間単位で集計するための項目「週開始日」を定義するためです。

「週開始日」

クエリで取得した項目を利用した計算列で定義します。「連番」を７で割った余りを設定します。連番は日付の降順で０から振るので、０～７が繰り返されることになります。これは週の開始日からの相対日数ということになります。

「相対日数」

クエリで取得した項目を利用した計算列で定義します。「日付」に「相対日数」を加算（連番が日付の降順なので加算）した日付を設定します。つまり週の始まりの日ということになります。

データセットへの項目追加

前項の方針に従い、まずはデータセットへ項目追加してみます。
データセット（DataSet1）を選択し、右クリック→「データセットのプロパティ」を開きます。
まず、クエリ（SQL）そのものに列追加するため、以下のように SQL を修正します。

SELECT T.*
     , LEAD(新規感染者数,1)
         OVER (PARTITION BY 都道府県
                   ORDER BY 日付 DESC ) AS 前日新規感染者数
     , ROW_NUMBER()
         OVER (PARTITION BY 都道府県
                   ORDER BY 日付 DESC) - 1 AS 連番
  FROM CSVFILES...newly_confirmed_cases_daily#csv AS T

SQLが正しいかどうかは「クエリデザイナー」で実行してみて確認してください。
問題なければ「フィールド」を選択すると、追加項目が新たに表示されていると思います。

次に計算列を追加します。「追加」ボタンを押下して「相対日数」「週開始日」「新規感染者数前日比」を追加します。

順番に計算式を定義していきます。まず「相対日数」の「式」ボタン(fx)から以下の式を設定します。

=Fields!連番.Value Mod 7

同様に「週開始日」には以下の式を設定します。

=DateAdd(DateInterval.Day,Fields!相対日数.Value,Fields!日付.Value)

最後に「新規感染者数前日比」には以下の式を設定します。

=Fields!新規感染者数.Value-Fields!前日新規感染者数.Value

これでデータセットへの項目追加は完了です。

「表」を使ったデータセットのテスト出力

クエリ（SQL）の変更はクエリデザイナーでその場で確認できますが、計算列として追加した場合は出力されません。少し脱線しますが、これを簡単に確認する方法をご紹介します。
まず、少し余白を設けてそこに「表」を挿入します。

「表」「マトリックス」「一覧」というのはデータセットと紐づけて集約や集計をしながら出力する機能を持つオブジェクトです。「表」は縦（行）が可変、「マトリックス」は縦（行）と横（列）が可変、「一覧」は自由配置という使い分けになります。単純なデータセットの中身の確認は一番直感的に分かりやすい「表」を使います。

デフォルトで３列が表示されているので、今回テストしたいデータセットの列数８まで「列を挿入」→「右揃え」で追加していきます。（「右揃え」っていう日本語は直感的ではないですが翻訳の問題でしょうね、、、）