You’re offline. This is a read only version of the page.
Hi,
Im trying to display a chart with different activities and the dates they were created. As of now, my FetchXML seems to return all QueueItems and display their dates on the Y-Axis, even though they dont have activities that match the filter-criteria.
What I want to achieve is either
A) Remove all results up to the first entry that has a value > 0 ( "1" in my figure below)
or
B) Remove all results where the value is 0 ("2" in my figure below)
Im new to charts in Dynamics, so im not sure how to approach this problem. Any suggestions are welcome!
Here is my XML:
Share
Report <datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="queueitem">
<attribute name="createdon" groupby="true" alias="CreatedOn" dategrouping="day" />
<filter type="and">
<condition attribute="createdon" operator="olderthan-x-days" value="7" />
</filter>
<link-entity name="email" from="activityid" to="objectid" link-type="outer">
<attribute alias="Email" name="activityid" aggregate="countcolumn" distinct="true" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>
<link-entity name="internetform" from="activityid" to="objectid" link-type="outer">
<attribute alias="InternetForm" name="activityid" aggregate="countcolumn" distinct="true" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="CreatedOn">
<measurecollection>
<measure alias="InternetForm" />
</measurecollection>
<measurecollection>
<measure alias="Email" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
*This post is locked for comments
Thank you, it works and I learned alot from this!
Hello,
You are quite right. I did not take into account that two inner-joins would always return zero results, because one queue item cannot be connected to two different activities. My apologies for this oversight.
Here is a revised XML definition, this time the joins are still outer and I added extra filters to check whether either the email activity or the internetform activity is not null:
All responses (
Answers (
<datadescription>
<datadefinition>
<fetchcollection>
<fetch mapping="logical" aggregate="true">
<entity name="queueitem">
<attribute name="createdon" groupby="true" alias="CreatedOn" dategrouping="day" />
<filter type="and">
<filter type="and">
<condition attribute="createdon" operator="olderthan-x-days" value="7" />
</filter>
<filter type="or">
<condition entityname="email" attribute="activityid" operator="not-null" />
<condition entityname="internetform" attribute="activityid" operator="not-null" />
</filter>
</filter>
<link-entity name="email" from="activityid" to="objectid" link-type="outer">
<attribute alias="Email" name="activityid" aggregate="countcolumn" distinct="true" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>
<link-entity name="internetform" from="activityid" to="objectid" link-type="outer">
<attribute alias="InternetForm" name="activityid" aggregate="countcolumn" distinct="true" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>
</entity>
</fetch>
</fetchcollection>
<categorycollection>
<category alias="CreatedOn">
<measurecollection>
<measure alias="InternetForm" />
</measurecollection>
<measurecollection>
<measure alias="Email" />
</measurecollection>
</category>
</categorycollection>
</datadefinition>
</datadescription>
Hope this query works better for you :-)
Thanks for your reply Michel.
This might be a step in the right direcetion, but if I set link-type="inner" for both link-entities, then i get 0 results (I guess because no QueueItem contains both an Email and an Internetform). Setting inner on just 1 of them only returns results from the one with "inner"specified. Is there some other way i can achieve this, or some way to accomplish what I mentioned in A) ?
EDIT: This is a bad suggestion, my apologies. Would never work with two joins.
Please take a look at my second post below.
-------------
Hello,
Note where in your query it says link-type="outer"
Change that to link-type="inner"
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156