다음을 통해 공유

Dynamics CRM 2011 Fetch XML 集計クエリ

  • 아티클

みなさん、こんにちは。

今回も引き続き Fetch XML のトピックで、集計クエリを紹介します。

カスタムレポートの準備

1. SQL Server Business Intelligence Development Studio (BIDS) を
起動して、新規のプロジェクトを作成します。

2. ビジネス インテリジェンス プロジェクトより、レポート サーバー プロジェクト
テンプレートを選択して、任意の名前で作成します。

image

3. 空のレポート サーバー プロジェクトが作成されますので、ソリューション
エクスプローラーよりレポートフォルダーを右クリックして、「新しいレポートの
追加」 をクリックします。

image

4. レポートウィザードが起動しますので、次へをクリックします。

5. データソースの型に Microsoft Dynamics CRM Fetch が存在するか確認します。
表示されない場合には、Microsoft Dynamics CRM 2011 Report Authoring 拡張が
インストールできていないので、インストールをしてください。

image

6. 接続文字列は ServerURL;OrganizationName;HomeRealmURL を指定します。
今回はオンラインの環境なので以下の文字列を指定しました。

https://crm5org93eae.crm5.dynamics.com;crm5org93eae

7. 資格情報をクリックして、接続に利用できるユーザー情報を入力します。

8. 次へをクリックします。

9. クエリのデザイン画面が表示されるので、「クエリ ビルダ」 をクリックします。

10. 以下の Fetch XML を入力して、｢! 」 ボタンをクリックします。以下の Fetch XML は
営業案件一覧を取得するクエリです。

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="estimatedvalue" />
    <attribute name="estimatedclosedate" />
    <attribute name="statuscode" />
    <order attribute="estimatedclosedate" descending="false" />
  </entity>
</fetch>

11. 結果が正しければ準備完了です。

集計クエリの作成

引き続き、上記の Fetch XML を集計クエリに変えていきます。

1. トップノードの fetch を以下のように書き換えます。

旧) <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

新) <fetch version="1.0" mapping="logical" aggregate="true">

2. 各列を以下のように集計に書き換えます。今回はサンプルのため、estimatedvalue を
利用して、Sum, Avg, Max, Min を指定しています。

旧)
<attribute name="name" />
<attribute name="estimatedvalue" />

新)
<attribute name="name" aggregate="count" alias="namecount"/>
<attribute name="estimatedvalue" aggregate="sum" alias="estimatesum"/>
<attribute name="estimatedvalue" aggregate="avg" alias="estimateavg"/>
<attribute name="estimatedvalue" aggregate="max" alias="estimatemax"/>
<attribute name="estimatedvalue" aggregate="min" alias="estimatemin"/>

3. statuscode でグルーピングを行います。

旧) <attribute name="statuscode" />

新) <attribute name="statuscode" alias='statusgrouping' groupby='true' />

4. actualclosedate もグルーピングにしますが、日付単位ではなく、年と月
単位にします。

旧) <attribute name="estimatedclosedate" />

新)
<attribute name="actualclosedate" groupby="true" dategrouping="year" alias="year" />
<attribute name="actualclosedate" groupby="true" dategrouping="month" alias="month" />

5. 並べ替えを年、月で行います。年、月は actualcloseddate から作成しているので、
列名ではなく、Alias を利用して order に指定します。

旧) <order attribute="estimatedclosedate" descending="false" />

新)
<order alias="year" descending="false" />
<order alias="month" descending="false" />

6. 以下が完成したクエリです。クエリ ビルダに上書きして、「!」 をクリックします。

<fetch version="1.0"  mapping="logical" aggregate="true">
  <entity name="opportunity">
    <attribute name="name" aggregate="count" alias="namecount"/>
    <attribute name="estimatedvalue" aggregate="sum" alias="estimatesum"/>
    <attribute name="estimatedvalue" aggregate="avg" alias="estimateavg"/>
    <attribute name="estimatedvalue" aggregate="max" alias="estimatemax"/>
    <attribute name="estimatedvalue" aggregate="min" alias="estimatemin"/>
    <attribute name="statuscode" alias='statusgrouping' groupby='true' />
    <attribute name="actualclosedate" groupby="true" dategrouping="year" alias="year" />
    <attribute name="actualclosedate" groupby="true" dategrouping="month" alias="month" />
    <order alias="year" descending="false" />
    <order alias="month" descending="false" />
  </entity>
</fetch>

7. 結果を確認します。

特殊なグルーピング

上記の例でもあるように、年や月の情報を日付列より取得してグルーピングする等、
特殊なグルーピングが可能です。詳細は以下を参照してください。

Group By With Linked Entity

Group By Year

Group By Quarter

Group By Month

Group By Week

Group By Day

Multiple Group By

まとめ

集計クエリは主にグラフで利用されますが、通常のレポートでも活用可能です。 高度な検索からは
取得できませんが、すこし変更するだけで集計クエリになりますので、ご活用ください。

参考: FetchXML 集計の使用
https://msdn.microsoft.com/ja-jp/library/gg309565.aspx#groupby

- Dynamics CRM サポート 中村 憲一郎

Comments

  • Anonymous
    January 27, 2015
    オプションセットフィールドの値を集計クエリで合計したいのですが、エラーとなってしまいます。 キャストも出来ない様なのですが、何か方法あればご教授頂けると幸いです。

  • Anonymous
    January 27, 2015
    コメントありがとうございます。 こちらの環境でも何パターンか試してみましたが、残念ながら意図する値を取ることはできませんでした。最終的な出力がレポートであれば、FetchXMLのクエリで集計するのではなく、レポート側で集計を行える可能性があると考えます。どのような用途でご利用される予定でしょうか。

  • 中村 憲一郎

  • Anonymous
    January 27, 2015
    早速の返信ありがとうございます。 集計項目はテキストフィールドも含め複数あるのですが、可能であれば、全ての項目の集計をFetchXMLのクエリで実施できればと思い質問させて頂きました。 ご回答を踏まえますと、煩雑さを低減させる（メンテナンスし易くする）意味でも、全ての項目の集計をレポート側で行う様に変更する必要があると考えております...

  • Anonymous
    January 27, 2015
    ご返信いただき、ありがとうございます。 こちらで確認した限りでは残念ながら FetchXML のクエリ結果自体にオプションセットの値を明示的に指定することができませんでした。実行結果としては <列名>Value にて返りますが操作が行いない模様です。 確かにデータソース側である程度の結果を持てる穂が、管理が容易ではありますが、今回の要望については別途レポート側での管理が必要になりそうです。ご了承ください。 ちなみに、エンティティ側でのカスタマイズを行っても構わない場合であれば、例えば別の列にオプションセットの値を同時に埋め込むことで集計はできるのではないかと考えています。

  • 中村 憲一郎

  • Anonymous
    January 27, 2015
    オプションセットの値について、操作ができないという事で承知致しました。 別のフィールドに値を埋め込む案もご提示頂きましたが、今回は レポート側での管理に統一し、実現しようと思います。 ありがとうございました。

  • Anonymous
    March 24, 2015
    FetchXMLで、見積り・受注・営業案件・取引先企業を結合して、見積りごとに集計したデータを取得したいと思っています。 見積りエンティティのGUIDでgroupbyはできないのでしょうか。 また、FetchXMLで、SQLの入れ子のようなことはできるのでしょうか。

  • Anonymous
    March 24, 2015
    コメントありがとうございます。 まず Guid で集計する場合、例えば顧客ごとのサポート案件数を出したい場合などは以下のように FetchXML をかけます。 <fetch version="1.0" aggregate="true" mapping="logical">  <entity name="incident">    <attribute name="title" aggregate="count" alias="casecount"/>    <attribute name="customerid" alias="customer" groupby="true"/>  </entity> </fetch> しかし SQL の入れ子に相当する機能は FetchXML にはございません。あくまでテーブルとして一覧が取得できるものに対しての集計が可能です。 中村 憲一郎

  • Anonymous
    March 24, 2015
    ご回答ありがとうございます。 FetchXMLでSQLの入れ子のようなことができないこと、承知いたしました。 また、Guidでの集計が、複数エンティティを結合した際にできなくなることがわかったので、 クエリを2回行うことで対応いたしました。