I'm trying to get a report from customer filtered on related records for example, all customers which opened more then 100 cases during the last year, how can I achieve this, I tried with fetch-xml using aggregation since I wasen't able to filter on the aggregation, also note: both the customer and the related entity contains more than 5000 records.
Hello,
I'm glad that it worked.
Could you please mark my answer verified by clicking Yes under 'Did this answer your question?'
Please mark my answer verified if this is helpful!
Regards,
Bipin Kumar
Follow my Blog: xrmdynamicscrm.wordpress.com/
Hi,
Have you tried creating a rollup field on Customer which will show count of Opened cases duing last year?
Then use this filter in your fetchxml.
Ohhh....We don't have option to filter Case records created during last year...
I have tried writting below SQL code based on Account (OOB Entity) and Employee (Custom entity)-
SELECT a.name FROM msft_employee INNER JOIN account AS a ON msft_employee.msft_company = a.accountid WHERE msft_employee.statecode = 0 AND msft_employee.createdon = lastxyears(1) group by a.name having COUNT(a.name)>=100
I did try to see fetchxml with the help of SQL4CDS plugin in xrmtoolbox but it shows 3 fetchxml -
Please mark my answer verified if this is helpful!
Regards,
Bipin Kumar
Follow my Blog: xrmdynamicscrm.wordpress.com/
Thanks for your response, I checked the link you posted, but I can't find how to filter on the amount of the retrieved records from the related records, as I wrote I only want to return the customers which had opened more than 100 cases in last year.
Hi,
Please find information to get large records using paging as mentioned in https://docs.microsoft.com/en-us/powerapps/developer/data-platform/org-service/page-large-result-sets-with-fetchxml.
Best Regards,
Mobeen
#1
André Arnaud de Cal...
291,820
Super User 2024 Season 2
#2
Martin Dráb
230,514
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
