Microsoft Dynamics CRM (Archived)

MS Dynamics CRM FetchXMLReports Group by Month & Year

(0) Share

Report

Posted on by Jayakumar

390

Hi,

I am using FetchXML to generate SSRS Report in MS Dynamics CRM Online 365.

If I can use SQL I could easily achieve this report, but since there is limitations in using FetchXML I am not able to generate a report for appointments grouping by Year,Month and Account Name (Regarding Id).

This is how I want the report, Please help.

35185.Capture.JPG

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (2)

Suggested answer

Arun Vinoth 11,615 Moderator on at

Like (1)

Report

RE: MS Dynamics CRM FetchXMLReports Group by Month & Year

Due to fetchxml limitations & cumbersome to generate reports, we moved to PowerBI.

Some of the benefits:

PowerBI can connect to CRMOL service directly, then adding calculated columns/metrics for flexibility, nice visualizations, slice & dice options, can be embedded inside CRM, etc

Was this reply helpful? Yes No
Verified answer

Thomas David Dayman 11,323 on at

Like (1)

Report

RE: MS Dynamics CRM FetchXMLReports Group by Month & Year

You have to add a table in your ssrs report and add a column group =Month(DateField). This should group the columns by month.

Was this reply helpful? Yes No
LeoAlt 16,331 Moderator on at

Like (1)

Report

RE: MS Dynamics CRM FetchXMLReports Group by Month & Year

Hi Jayakumar,

You could sort your data by using FetchXML with some rules.

Here are some similar requirements.

https://www.inogic.com/blog/2011/04/group-by-using-fetchxml/

https://community.dynamics.com/crm/f/117/t/248052

Hope it helps.

Best Regards

Leo

Was this reply helpful? Yes No
Suggested answer

Muhammad Asimuddin on at

Like (1)

Report

RE: MS Dynamics CRM FetchXMLReports Group by Month & Year

I did something similar, look at this URL. It may help you achieve similar thing

dynamicscrmcoe.com/complex-activity-report-fetchxml-ssrs

Was this reply helpful? Yes No
Verified answer

Piyush Paliwal 817 on at

Like (1)

Report
RE: MS Dynamics CRM FetchXMLReports Group by Month & Year

This can easily be achieved by creating calculated fields your report. Take a look at https://docs.microsoft.com/en-us/sql/reporting-services/report-data/add-edit-refresh-fields-in-the-report-data-pane-report-builder-and-ssrs?view=sql-server-2017#to-add-a-calculated-field

Create 2 calculated fields for Month, put the expression as Month(DateField) and Year, expression as Year(DateField)

In your Tablix group settings, put the grouping based on the two calculated fields you created above.

Should be fairly straightforward. Let me know in case you are seeing some issues.

Was this reply helpful? Yes No
Jayakumar 390 on at

Like (0)

Report

RE: MS Dynamics CRM FetchXMLReports Group by Month & Year

Thank you everyone for your response. The issue seems to be in fetchxml where it only returns 500 records for SSRS Tablix. But when I published to crm all the records are displayed.

Was this reply helpful? Yes No