SSRS Reports For CDS & D365 Using FetchXML

Each week, I take a look at my website statistics in Google Analytics, and I view the top 25 blog posts. Without fail, at least one or two of them relate to writing reports using FetchXML, a proprietary query language used in the Common Data Service. If you are a Dynamics 365 user, you might already be familiar with FetchXML, you use it every time you look at a view or create a new search using Advanced Find. If you have a model-driven Power App using the Common Data Service, you have this same option too. So what are SSRS Reports?

SQL Server Reporting Services

When I first started using CRM over a decade ago, our organisation had a need for reports that could be run by users from within the system. I went on an SSRS course that taught me how to set everything up, create queries, understand datasets and expressions, and write some pretty awesome reports. I then moved on to Dynamics 365 and realised that writing reports now had some slight quirks and subtle differences when using FetchXML. There were different tools needed, and expressions needed to be written in different ways. There were no courses I could take to learn, so just figured it out as I went along.

What about Power BI?

Yup, it’s awesome, and it’s the perfect solution for many requirements an organisation might have. It’s fantastic for dashboards, and a great tool for analysing data. However, it’s not always the RIGHT option. There is certainly a large cost consideration as sharing and collaborating on reports means a Pro license is required. You can embed your dashboards in to a Model-driven Power App, but it’s often a challenge to then get links to open up records from that dashboard. So, although it’s a great tool, sometimes you need something different.

But we have Document Templates!

Yes, that is very true also. You can use the Word and Excel Document Templates functionality that can be used in a Model-driven Power App to display data, and related records. As with Power BI, it’s the right solution for many requirements. Likewise, with Power BI, there are limitations. You can’t determine the sorting for the records you display in the templates. You can only get related records to your main entity, and it’s a royal pain if you add in new fields and need to then adjust the template file.

Writing SSRS Reports

I’ve written reports for a long time now. I have also trained people for a long time. I’ve also created online courses. So, I’ve combined all of that and come up with a new online course, SSRS Reports for CDS & D365. In it, I cover the following aspects:

• Before You Get Started
• Creating Your First Report
• Expanding On Your Report
• Adding Aggregates & Visualisations
• Using Parameters
• Report Management

As mentioned, this is NOT ‘just a Dynamics thing‘. This method of writing reports is for anyone using the Common Data Service and has a Model-driven Power App. You can go at your own pace on the course, and re-watch videos or go back over lessons as you need. You can also ask me questions along the way if you need.

So, if you want to start learning how to write reports, check out my new course. It would be great to see you in class!