How to Create Your First Dynamics 365 SSRS Report

Summary

  • SQL Server Reporting Services reports are a viable way to generate information from your Dynamics 365 system.
  • The Dynamics 365 Report Wizard has limitations in terms of the reports that can be created.
  • Creating SSRS Reports does not necessarily require deep development skills.

This post outlines the steps to create a simple SSRS report integrated and connected to Dynamics 365 without using the Dynamics 365 Report Wizard.

Dynamics 365 SQL Server Reports

Dynamics 365 Sales and Service Apps come with a set of reports out of the box.  In my experience, these reports are not often used and in most cases, there is a need for custom reports.

The Dynamics 365 report wizard can create simple list based reports based on one or two related entities, has some basic charting abilities, but has limits on flexibility.

With some basic knowledge, you can create custom SSRS reports for Dynamics 365 very quickly.

wizard_lego
Become your own Report Wizard!

SSRS reports are created using SQL Server Data Tools, which is a version of Visual Studio specific for creating database related projects.  The fact that Visual Studio is used does NOT mean that the report author needs to be a developer or coder to create powerful reports.

Before starting, please review my post on setting up your workstation to create SQL Server Reports for Dynamics 365.

Set up your workstation to create Dynamics 365 SSRS Reports

Once you have your workstation ready, you will also need some level of access to your Dynamics 365 system.  Note that these steps could also be used to create custom reports for a Common Data Service Model-driven PowerApp.

Creating your First SSRS Report

To create reports, you first need to launch SQL Server Data Tools

ssdtdl8

This will launch Visual Studio.  Again, you don’t need to be a developer to use this tool!

assdt16
Visual Studio SQL Server Data Tools

Click on File -> New -> Project

assdt17

The New Project Dialog will appear.

From the Templates, choose Business Intelligence -> Reporting Services and select Report Server Project.

assdt18

Once that is loaded, from the Solution Explorer on the right-hand side, right click on the Reports folder, choose Add -> New Item

assdt19

Choose to create a Report file and give it a name.  The “RDL” extension stands for “Report Definition Language” which is an XML interpretation of the report you will create.

assdt20

The report design surface will appear.

assdt21
Report Designer

The first step is to create a link to your Dynamics 365 system.  On the left of the report designer window, in the Report Data section, right click on the Data Sources folder and click “Add Data Source”.

assdt22

If the Report Authoring extensions were installed correctly, you should see “Microsoft Dynamics 365 Fetch” as a type.  For Dynamics 365 On-Premise, you do have the option to create T-SQL based reports, but to future proof your reports, you should consider writing in FetchXML where you can.

Provide the Data Source with a name and enter in your URL for your Dynamics 365 system.

assdt23

Click on credentials and enter in your Dynamics 365 login and password.  Note that these will not be “carried” with the report, but just used in the designer.  When we eventually load the report in Dynamics 365 it will run in the context of the logged in user (and applicable security roles will apply)

assdt24

We need to add a Dataset to pull data from Dynamics 365.  We will be using a query language called “FetchXML”.

While you could construct a FetchXML by hand, it is easier to generate using a tool.

One quick way to get a FetchXML query is from Dynamics 365 Advanced Find.

Directly in Dynamics 365, simply build an Advanced Find query, and then choose the “Download FetchXML” button.  An XML file containing the query will be downloaded.

assdt26

Looking at the FetchXML file, you can see the structure identifying the entity, the fields and filters.

assdt27

If you want to create advanced FetchXML queries, I suggest using the XrmToolBox  plug-in “FetchXML Builder” by MVP Jonas Rapp.

fetchxml
Must Have Tool for Dynamics 365 Report Development

Again on the Report Data section, right click on the Datasets folder and choose “Add Dataset”

assdt25

Provide a name, choose to use the embedded dataset that we created earlier and paste in the FetchXML statement.

assdt28

You should now see a dataset in the Report Data section.

assdt29

For the first report, we will just drag a “table” component onto the design surface.

assdt30

The table will automatically bind itself to the dataset we created earlier, from here we can define what fields we want to appear on our report.

assdt31

Once we have defined our layout (pretty simple at this point) we can click the “Preview” button to see what our report will look like.

assdt32

Now that we have our “fancy” report created, lets load it to Dynamics 365!

Loading the Report to Dynamics 365

Login to Dynamics 365 and choose Sales or Service and click on the Reports icon.

Note: If you using a CDS environment with no Dynamics 365 First Party Apps installed, you can find the reports via the Advanced Find or add the Report Button to a Site Map.

assdt33
Dynamics 365

You will see the list of out of the box reports.  Click on the +NEW button to add your new report.

assdt34

Change the Report Type to “Existing File”.

Click the Choose File and navigate to the directory where you created the report project in Visual Studio, locate and choose the “rdl” file.  To have the report appear on the menu within the context of certain entities, you can set them in the “Related Record Types” and indicate for them to show up on the report area, forms and lists.  In a future post I will expand upon running reports agains specific Dynamics 365 records (e.g. running a quote report against a specific quote record).

assdt35

The report should now appear on the list of available reports.  Double click the report to run.

assdt36

You should now see your report rendered within the context of Dynamics 365.  You can now print or download to a variety of formats.

assdt37
Super fancy SSRS Report generated directly in Dynamics 365

Next Steps

The report we created was pretty simple and frankly, quite ugly.  In SQL Server Data Tools, we can clean up the report and have specific formatting, totals, logos, sections, etc.  and create a pixel perfect rendering of our data.

Creating SSRS reports for Dynamics 365 is not difficult once you understand the basics.  I hope you find this post useful and I look forward to hearing about the amazing reports you have created.

legoffice
Stan gets ready to build some reports!

Nick Doelman is a Microsoft Business Applications MVP and isn’t scared of using Visual Studio and nor, should you!

9 thoughts on “How to Create Your First Dynamics 365 SSRS Report

  1. Hi Nick,

    Thanks for publishing these instructions. It saved me from having to bug my MVP contact for help. 😉

    I’d like to share an issue I encountered and how I resolved it, just in case someone else faces the same challenge.

    I had no issue downloading the SQL Server Data Tools 2015 (https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017#ssdt-for-vs-2015-standalone-installer), but had to stop once I got to setting up the report’s DataSource, as “Microsoft Dynamics 365 Fetch” was not part of the available options. Based on this line from your post, I concluded that I was missing the Report Authoring extensions:

    “If the Report Authoring extensions were installed correctly, you should see “Microsoft Dynamics 365 Fetch” as a type.”

    I successfully downloaded the “Microsoft Dynamics 365 Report Authoring Extension” (https://www.microsoft.com/en-us/download/confirmation.aspx?id=50375), but while extracting the files, I received the following [fatal] error message:

    “Action Microsoft.Crm.Setup.Common.Analyzer+CollectAction failed.”

    A bit of research indicated that this happens when the workstation already has another version of the Microsoft Dynamics CRM Report Authoring Extension installed. If you’ve written FetchXML reports for CRM before, it’s quite likely that you already have another version installed. The resolution provided by Microsoft (https://support.microsoft.com/en-us/help/2988630/fatal-error-during-installation-of-microsoft-dynamics-crm-2013-report) is to edit the registry to remove these prior installs’ entry. That did the trick for me and I was then able to install the Dynamics 365 Report Authoring Extension and, after that, successfully select “Microsoft Dynamics 365 Fetch” for my DataSource connection type.

    Cheers!

    Like

  2. Hi, Thanks for this article. Its clear on how to integrate SSRS / Dynamics. I am a newbie in dynamics world. I have question now. If we need to use the fetchXML from the advanced find, then why not build it directly in CRM. Its the same XML that we are using in SSRS fetch right?

    Thanks
    Jay

    Like

    1. Hi Jay

      Welcome to the Dynamics world! I hope you have a rewarding and prosperous career. Thanks for visiting my blog.

      Can you elaborate on your question a bit more? Are you asking of why to build an SSRS report if building an advanced find will give you the same results?

      If you just need to show a list of data to an end user of Dynamics 365, then building an Advanced Find (or a Model-Driven View) and showing that for the particular entity or on a dashboard totally makes sense.

      What SSRS will give is the ability (still using FetchXML) is to build more advanced data queries (joins, etc) and also show parent-child relationships. SSRS will also provide the ability to build a nicely formatted report that can reflect an organization’s branding (think a quote or an invoice) that can be exported to a PDF file.

      A lot of what we traditionally used SSRS for can now be done with PowerBI, however, SSRS still holds a place for specific workloads.

      I hope this answers your question or at least sheds some light.

      Cheers
      Nick

      Like

  3. Thank you for this article. I find it increasingly frustrating trying to understand the vision of Microsoft in the BI space these days. SSRS using SQL seemed logical. Having a SQL based source for Power BI seemed reasonable as well – even if in Azure SQL DB or DW. But then throwing SSAS on top and needing to understand DAX to build reports on a tabular model and the move from SQL to leverage FetchXML for Dynamics 365. Seems most BI platforms standardize on a query method – Microsoft seems to be doing the opposite – with no plan to standardize.

    Like

  4. Thank you for posting this. It’s helpful.
    On a somewhat related note (more whining than anything else) – I’m not sure of Microsoft’s vision when it comes to their Business Intelligence tool set. It used to be SQL-based for both SSRS and Power BI – though with SSAS it is DAX and now SSRS to access Dynamics 365 it is FetchXML. While most BI vendors are standardizing on their query language/method – it seems Microsoft is doing the opposite – making the landscape a tad more convoluted. Just my $0.02.

    Like

Leave a Reply to Brigitte Maheu Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s