Multiple Group-BY in a Chart

(0) Share

Report

Posted on by JO-30091535-0

238

Hi,

I know its possible to include multiple entities in one chart, as described here, but that's primarily for adding multiple series to count. What are the supported groupings a chart will display if supplying a FetxhXML with multiple Grouping. In the UI, its either multiple series with a single Grouping, or a single series and two Group-By.

Here is an example of where it might be useful. Say i wish to create an "Activity by User" chart listing all system users (regardless if they do or dont have an activity) and grouped by the date (scheduleddate). This example would require two group-by from two different entities. I would need to start with a chart in the User entity (to get all users), and then link the activity entity to count the number of activities (or "0" if none are found). Thats all good and simple. But, i now want to add a second grouping to show the date, as below. Is this possible?

 <attribute name="scheduledstart" alias="StartDate" groupby="true" dategrouping="day" />

Something like this:

<fetch mapping="logical" aggregate="true" >
  <entity name="systemuser" >
    <link-entity name="new_customactivity1" from="owninguser" to="systemuserid" link-type="outer" >
      <attribute name="subject" alias="CustomActivity1" aggregate="countcolumn" distinct="true" />
      <attribute name="scheduledstart" alias="StartActiv1" groupby="true" dategrouping="day" />
    </link-entity>
    <link-entity name="new_CustomActivity2" from="owninguser" to="systemuserid" link-type="outer" >
      <attribute name="subject" alias="CustomActivity2" aggregate="countcolumn" distinct="true" />
      <attribute name="scheduledstart" alias="StartActive2" groupby="true" dategrouping="day" />
    </link-entity>
    <attribute name="systemuserid" groupby="true" alias="User" />
  </entity>
</fetch>

*This post is locked for comments

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Kokulan 18,054 on at

Like (0)

Report

Hi

A Chart in Dynamics 365 can only be based on one main entity, in your example above, the main entity is Activity. A chart cannot have different sets of data from multiple primary entities. The View we select is what provides FETCH query for the chart the data Chart and this fetch query as you mentioned above includes the grouping. In the example I posted below, the All Activities view returns the data and this data can then be grouped by User (Owner User of the Activity) and Date Created for example Monthly.

Please see the following links for more information or details on creating charts

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/basics/create-edit-chart

https://crmbook.powerobjects.com/basics/analytics/charts-4/

When you design the chart from UI, the fetch query will be generated by the system. You can edit the query but most of the time it's not necessary as the UI supports most of the chart requirements. But if you would like to see the Fetch query, please see the example below to see how two grouping can be added

<fetch mapping="logical" aggregate="true">
<entity name="activitypointer">
<attribute groupby="true" alias="groupby_column" name="ownerid" />
<attribute alias="aggregate_column" name="subject" aggregate="count" />
<attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_13" name="scheduledstart" dategrouping="month" />
</entity>
</fetch>

Was this reply helpful? Yes No
JO-30091535-0 238 on at

Like (0)

Report
Hi Kokulan . Thanks for the info.

Im not sure im following you. First of all, in my example above, the main entity is "Users". I then linked two additional activity entities, which is very possible. All the links and info you provided refer to creating charts using the UI. Im referring to configuring the XML beyond to what the UI offers, which again is very possible by providing a custom fetch. The chart im looking for is a breakdown of ALL users, and a count of activities per user. If a user has no activities, then it would still show the user, but with no activities. For example, see the chart below, and the fetch behind this Chart. This chart fulfills the need. The Chart below links 3 entities. However, i also wish to add another grouping (if possible). In fact, i have another chart in which i got it working, including the two grouping (from two entities), but i wasnt able to get it working when i linked a 3rd entity.

<fetch mapping="logical" aggregate="true"> <entity name="systemuser"> <link-entity name="new_Activity1" from="owninguser" to="systemuserid" link-type="outer"> <attribute alias="Activity1" name="scheduledend" aggregate="countcolumn" distinct="true" /> </link-entity> <link-entity name="new_Activity2" from="owninguser" to="systemuserid" link-type="outer"> <attribute alias="Activity2" name="scheduledend" aggregate="countcolumn" distinct="true" /> </link-entity> <attribute name="systemuserid" groupby="true" alias="User" /> </entity> </fetch>

Was this reply helpful? Yes No