locked
How get Count of Stores against Brand in CRM Report RRS feed

  • Question

  • I have Entities

    1)Brand (Primary Entity "new_brand")

    2)Store(Related Entity "new_store"), Look up "new_active_brand"
    A Brand have many Stores
    I want to Build a Report as Count of Stores against the Brand Name and show data as below

    Brand Name and Store Count

    For example

    Maxi’s = 54 stores
    Scooters = 112 stores
    St Elmo’s = 27 stores
    Domino’s = 6 stores
    FCC = 312 stores
    Total stores = 511 stores
    so how make query for this plz help


    Muhammad Sohail



    • Edited by sohail450 Wednesday, January 28, 2015 8:33 AM
    Wednesday, January 28, 2015 8:31 AM

Answers

  • As I already wrote - recheck article about aggregations. I've modified your query based on article:

    <fetch version="1.0" mapping="logical" aggregate="true">
      <entity name="new_store">
        <attribute name="new_storeid" aggregate="count" alias="storenumber" />
        <attribute name="new_active_brand" alias="brand" groupby="true" />
      </entity>
    </fetch>



    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    • Marked as answer by sohail450 Wednesday, January 28, 2015 1:11 PM
    Wednesday, January 28, 2015 10:37 AM
    Moderator

All replies

  • Hello,

    I would suggest to build query using XrmToolbox - it contains plugin that allows to build FetchXml queries.

    For better understanding how that query could be built you should recheck article regarding aggregates in FetchXml.


    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Wednesday, January 28, 2015 9:19 AM
    Moderator
  • Hi Andrii,

    I am not using this query in .net Code i want query for Report

    actually I am able to run a query for Single brand and this result the count by making 2 data Set

    1.For Brand that get all Brand

    2.For Store and pass user selected Brand Name parameter to Store query and getting store count

    but how can change it to get brand and their Stores count in Single run on a report???

    Maxi’s = 54 stores
    Scooters = 112 stores
    St Elmo’s = 27 stores
    Domino’s = 6 stores
    FCC = 312 stores
    Total stores = 511 stores

    the query i am now using is

    <fetch version="1.0" mapping="logical">
      <entity name="new_store">
        <attribute name="new_storeid" />
        <attribute name="new_store" />
        <attribute name="createdon" />
        <attribute name="statecode" />
        <attribute name="new_active_brand" />
        <order attribute="new_store" descending="false" />
        <filter type="and">
          <condition attribute="new_active_brand" operator="eq" uiname="Scooters" uitype="new_brand" value="@BrandName" />
        </filter>
      </entity>
    </fetch>


    Muhammad Sohail

    Wednesday, January 28, 2015 10:25 AM
  • As I already wrote - recheck article about aggregations. I've modified your query based on article:

    <fetch version="1.0" mapping="logical" aggregate="true">
      <entity name="new_store">
        <attribute name="new_storeid" aggregate="count" alias="storenumber" />
        <attribute name="new_active_brand" alias="brand" groupby="true" />
      </entity>
    </fetch>



    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    • Marked as answer by sohail450 Wednesday, January 28, 2015 1:11 PM
    Wednesday, January 28, 2015 10:37 AM
    Moderator