Need help to convert my SQL query to Fetchxml

(0) Share

Report

Posted on by Bipin D365

971

Hello Experts,

I need your help and guidance to convert my SQL query to fetchxml which I need in my Report.

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

Here's what I am trying to achieve - Fetch All Accounts which has more than 100 openend/active employees created during last 1 year.

I did tried to use SQL4CDS plugin from xrmtoolbox but it does not give single fetchxml.

Thanks,

Bipin

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (3)

Verified answer

meelamri 468 on at

Like (1)

Report

Hi,

There is a tool to convert SQL to FetchXml: http://sql2fetchxml.com/
Unfortunately, your query cannot be translated into fetchXml.

I suggest you to fetch all active employees created during the last year, then group these employees according to the parent account and display the grouping if it contains more than 100 rows.

Was this reply helpful? Yes No
Verified answer

Bipin D365 971 on at

Like (0)

Report

Hi @melamri ,

I can easily do this from c# code but I need to create report based on fetchxml. So if you can prrovide any solution with respect to Reporting then it will be very helpful.

Thanks,
Bipin

Was this reply helpful? Yes No
Verified answer

a33ik 3,306 Most Valuable Professional on at

Like (1)

Report

As I suggested in on of the other threads you created yesterday - https://powerusers.microsoft.com/t5/Microsoft-Dataverse/List-of-Accounts-which-has-more-than-100-open-cases-during-last/m-p/1422982#M17013 - create the field, implement recalculation of that field, use the field in the report. As @melamri mentioned - this query can't be directly converted to FetchXml.

Was this reply helpful? Yes No