April 2020 Update
I know this is a popular post, and the information is still very relevant today and will work both with Dynamics 365 and Model-driven Power Apps projects. If you want more in-depth information and become a SSRS wizard, then take a look at Megan Walker’s SSRS REPORTS FOR CDS & D365 online class.
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.
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
This will launch Visual Studio. Again, you don’t need to be a developer to use this tool!
Click on File -> New -> Project
The New Project Dialog will appear.
From the Templates, choose Business Intelligence -> Reporting Services and select Report Server Project.
Once that is loaded, from the Solution Explorer on the right-hand side, right click on the Reports folder, choose Add -> New Item
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.
The report design surface will appear.
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”.
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.
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)
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.
Looking at the FetchXML file, you can see the structure identifying the entity, the fields and filters.
If you want to create advanced FetchXML queries, I suggest using the XrmToolBox plug-in “FetchXML Builder” by MVP Jonas Rapp.
Again on the Report Data section, right click on the Datasets folder and choose “Add Dataset”
Provide a name, choose to use the embedded dataset that we created earlier and paste in the FetchXML statement.
You should now see a dataset in the Report Data section.
For the first report, we will just drag a “table” component onto the design surface.
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.
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.
Now that we have our “fancy” report created, lets load it to Dynamics 365!
Loading the Report to Dynamics 365
May 2020 Update
The upload a report in the Power Apps maker portal, create a new solution, and add your report to the solution.
Old way:
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.
You will see the list of out of the box reports. Click on the +NEW button to add your new report.
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).
The report should now appear on the list of available reports. Double click the report to run.
You should now see your report rendered within the context of Dynamics 365. You can now print or download to a variety of formats.
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.
Nick Doelman is a Microsoft Business Applications MVP and isn’t scared of using Visual Studio and nor, should you!
ReadyXRM 
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!
LikeLike
Hi Brigitte, Thank you for sharing!
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
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.
LikeLike
How do I export the results of my new rdl file to sharepoint? and then schedule the report to be emailed to a list?
LikeLike
There is no out of the box way to do this, however, the open source solution at this link should allow you to accomplish what you need: https://github.com/bguidinger/Xrm.ReportScheduler
LikeLike
Hi
I want to create a custom report using these tools. My problem is that writing this report with FetchXML is really hard (if not impossible) and the T-SQL query is easier for me. I would like to know that if I write a SQL query in the report, what will happen to security roles?! The appropriate report is one that everyone can see his allowed records only…
Thanks
LikeLike
T-SQL reports are only available for on-premise. For online you will need to use FetchXML. No one really writes FetchXML from scratch, I use tools like FetchXML Builder (in the XrmToolBox) to build my report queries and with a little practice, it is not that hard. SSRS reports will respect Dynamics 365 security, so if a user runs a report, they will not see data that they don’t have permission to view. Hope that helps!
LikeLike
Hello Nick. Thanks for the wonderful post. I have a question – In CRM we are setup to use LastName, FirstName for fields but in the report I want to display that as FirstName LastName. How can I do that?
Thanks!
LikeLike
Hi NavRaj, I am glad you liked the post. To show firstname lastname, instead of adding a column with “Fullname” field you can add 2 columns with “firstname” and then “lastname” fields (these are what D365 uses to make the Fullname field). Another option would be to concatenate the 2 fields in a formula for the column. Something along the lines of =firsname.value & ” ” & lastname.value
LikeLike
Thanks Nick. So the thing is I am getting the value from the Lookup field and that brings in LastName, FirstName. Can that be changed to FirstName LastName?
LikeLike
If its a lookup field then you can modify your datasource FetchXML statement to include attributes from related entities. For example, if I were writing a report on cases, and I wanted to see the related case contact, then I use a tool (FetchXML builder) to build a query with the contact as a link entity;
See screenshot of fetchXML here (WordPress doesn’t like xml code)
References:
https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/use-fetchxml-construct-query
https://fxb.xrmtoolbox.com/
I hope that helps!
LikeLike
Yes thanks very much!
LikeLike
Hi mate,
This has made my year. I’ve been fighting with this for so long. I am still having problems but I can see light at the end of the tunnel.
I am at the step of “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.”
I am in some kind of loop. I have to reenter my Username and Password then select the organisation. After this, I need to reenter my password and hit login again and then choose the organisations again. This just loops. Pressing cancel obviously stops me from progressing.
I have three different organisations to choose from that my is associated with. (live version, pre-live version and sandbox)
After hitting cancel I get the following appear where the preview should be:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot create a connection to data source ‘DS1’.
The connection to server “https://####.####.dynamics.com/” could not be established. Make sure that the connection string and credentials are correct, and try again.
What I find confusing is that I can make the connection and download fields, setup data sources, datasets etc.
Any insights you might have??
LikeLiked by 1 person
Hi Russell, The first thing I would check to see if you have the latest version of the report authoring extensions installed, if its not v9 or higher, you might be running into TLS 1.2 issues. To check, go to Apps & Features in Windows and look for the Dynamics 365 Report Authoring Extension. If it still says v8.x, then you should upgrade to the latest version. If that is updated, are you able to connect to your instance using XrmToolBox? Cheers, Nick
LikeLike
Nick, great blog post and very easy to understand. One question I do have though (I actually have two), is when you add the report file (.rdl) in CRM, does it automatically get added to SSRS? Also, how would one handle a parameterized query using FetchXML? OK, a third question…what if you have to relate the same entity more than once? Can you use aliases in FetchXML?
Thanks again,
Mike
LikeLike
Hi Mike
2 or 3 Answers!
Yes, the RDL gets added to SSRS, if you are online, that SSRS server is not accessible so you can’t really see it.
FetchXML parameters – big topic, hopefully I can summarize. You can add the enableprefiltering=”true” in your FetchXML entity tag to use the context of the record you are launch the report from (e.g. Running a report from the specific contact record) If you want an actual parameter where a user needs to enter a value (e.g. date range) You add parameters in SSDT as you normally would and use the “@value” as a placeholder in your FetchXML query.
Relate same entity more than once? Yes, you can use aliases in FetchXML. FetchXML builder (https://fxb.xrmtoolbox.com/) is your best friend to build these queries.
I hope this helps!
Cheers
Nick
LikeLike
Nick,
Thanks for the quick reply. I created a new parameterized report where one dataset provides the values for the user to choose the parameter and the other dataset uses that parameter to get the requested data. Works great in preview mode in VS. I did the datasets using T-SQL, querying against the base tables. When I imported it into CRM, I’m getting an error saying the report cannot run. My guess is for authentication I’m using a SQL account in the CRM database.
So I’m guessing I need the CRM Authoring Extensions installed. I tried doing that but I’m getting an error during install saying I’m missing some components. I am on Win 10 64 bit, VS 2107 with SQL Data Tools already installed, and I have both SQL Server Management Studio 2014 and 2016 installed. The error doesn’t tell me what components I’m missing.
Would you have any thoughts on that?
Thanks again!
Mike
LikeLike
Hey Mike, Are you using online? You can’t load custom T-SQL reports for Dynamics 365/CDS online. You need to use FetchXML.
Dynamics 365 Report Authoring Extensions require Visual Studio Data Tools 2015. They are not yet supported on SSDT 2017 or higher (I know…) You can have multiple versions of SSDT on your machine, and the tools are a free download. Please check out my post on setting up SSRS workstation. Hope this helps!
Cheers
Nick
LikeLike
Hey Nick,
We are on-premise.
At any rate, I got everything working. I did have to install SQL Data Tools 2015 to get Dynamics 365 Report Authoring Extensions to install correctly. I then launched the data tools instead of VS2017 and everything worked as your blog says. I used FetchXMLBuilder to help with the queries, and I even figured out how to create a parameterized query, with the parameter values coming from another data set. It works!
One weird thing I get though is when I import the .rdl file into CRM, I get two entries of the report. In other words, the report is listed twice. Have you seen that before?
Thanks very much for the great blog post and the help!
Mike
LikeLike
Hey Mike, the fact you are seeing the report twice is weird. I am really not sure. If you are on-premise then in theory your T-SQL reports should work, but I haven’t touched on-premise in a few years so not sure I could really help much, might be a SQL permissions or access thing. However, if you build FetchXML reports now, they will transfer easily to the cloud when you migrate. (right?) Thanks for reading my post and have a great weekend!
Cheers
Nick
LikeLike
Hi Nick,
Great post!
i´ve been trying to follow it but i keep having the same issue. We are working on Dynamics 365 online and we already have VS 2019 with server data tools included. This is the problem we get is when we try to install Authoring Extension v9:
on System Checks:
– Enviroment checks: Some of the required components are missing. For more information, click Details.
the details button takes us to: https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/deployment-administrators-guide/hh699754(v=crm.8)?redirectedfrom=MSDN#report_auth_gen_req
where we couldnt find answers.
Hope you know the answer
thanks very much!
Ignacio
LikeLike
Hi Ignacio, In order to install the report authoring extension (v9), you need to install SQL Server Data Tools 2015 (not 2019) I recognize that it is an older version, but it works. Check out the companion post about setting up a workstation to write SSRS reports. I hope this helps! Cheers, Nick
LikeLike
Hi Nick, sorry for bothering you again. I´m trying to install previous versions of SSDT but the set up fails. The system cannot find the file specified in logs. Some registry keys are not found.
Thanks, Ignacio.
LikeLike
The only thing I could think of is you might need to be full admin on your machine? Also, the download links have moved a bit, you can get the “latest” version of SSDT 2015 from here: https://docs.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-ver15
LikeLike
Hi Nick,
i could finally make it work by downgrading VS to 2015. After that, i followed the guide and everything worked just fine. Thanks again, really useful post.
Cheers!
Ignacio.
LikeLike
Great news. I know its a bit weird to have to use an older version, but I imagine MS will provide an update of some sort soon. 😉
LikeLike
Hi Nick,
Thank you very much for sharing your knowledge …After having installed the necessary tools I used advanced find to build my query and then generate the Fetch file, then copy – paste but later an error appeared:
“The XML passed to the platform is not well-formed XML. Data at the root level is invalid. Line 3, position 1.”
FetchXML:
–
–
–
–
100000019
100000015
100000012
100000000
100000020
100000002
2
100000004
100000014
100000013
100000011
1
–
{C2820AA8-91F8-E411-8109-C4346BACCE18}
{7CA4C87A-ADE1-E811-8166-70106FAAD7C1}
{C0820AA8-91F8-E411-8109-C4346BACCE18}
{BE820AA8-91F8-E411-8109-C4346BACCE18}
Any ideas what’s gone wrong?
LikeLike
It looks like WordPress messed up posting your fetchxml. What I would suggest is trying your fetchxml first in the FetchXML builder tool in XrmToolBox, it might help you diagnose what your fetchxml issue is: https://fxb.xrmtoolbox.com/
LikeLike
Hi Nick,
Using Fetch XML builder I could easily open my view …(without error)….
KR
Patricia
LikeLike
Hi, thanks by the post, I’m trying to upload my report but it is not successed, I have windows server 2016, visual studio 2014, SSDT 2015, Dynamics 365 report authoring extension 8.2 (I tried to upgrade and it failed)
If you can help me, I’m new in dynamics 365 CRM reports and I don’t have a lot of experience.
Thank you very much, best regards
LikeLike
Is the problem uploading the report into Dynamics 365? Also, you should try and resolve getting the 9.0 extension installed as that will cause you issues down the road.
LikeLike
Hi,
i am tried to create SSRS reports but in data source at (embedded connection type) i am not seeing (MICROSOFT DYNAMICS 365 FETCH) what would be the reason can you please suggest me what can i do.
LikeLike
A couple of reasons that it might now show up; You would need to confirm that the SQL Server Data Tools is the 2015 version (2017, 2019 will not work). And also that the Dynamics 365 Report Authoring Extension appears in your list of “Apps and Features” in Windows (ideally version 9.0.0002.0426 or higher)
LikeLike
i am using visual studio 2017 and SSDT also 2017 so are these not support to SSRS reports.?
LikeLike
SSDT 2017 is not supported. You need SSDT 2015 (you can install it alongside other versions). I know, it seems weird that 5 year old software is the “latest” supported, but it is what it is.
LikeLike
great post, created report successfully, however, when saving the report to Dynamics 365 i’m receiving the following message: Invalid Data Source. This report type is not supported. Microsoft Dynamics 365 Online supports only reports that use Fetch XML data sources.
LikeLike
please disregard, found issue, thanks for post again
LikeLike
Hello Sir,
Thank you for stupendous article and enriching our knowledge base. I tried to download and install BIDSv9.0 reporting extention, but everytime it throws an error saying to restart my compter. I did 4 times but still same error. I already installed VS2015. I tried on my remote server but still same error. Hope u can shed some light.
Error is “Setup cannot continue because there is a pending restart required.Restart computer and try running setup again”.
LikeLike
Ouch, that could mean so many things but the approach I would try first is to make sure all the Windows updates are installed and also try to install the various components using admin mode.
LikeLike
Genius Sir. It worked and i am able to install it correctly.
We have one requirement where i developed word template in DYnamics CRM and users are able to run this template against single CRM record and could download the resulting document successfully. But now users want to run the same word template across multiple CRM records and download one single word document which has combination of all CRM records info. Is this possible using SSRS? If yes, plz shed some light.
LikeLike
Yes, you can create SSRS reports that will run against selected records (users choose a bunch of records in a view and then select run a report) You will need to use the prefiltering tag in the FetchXML; https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/analytics/improve-report-performance-by-using-filters Hope that helps!
LikeLiked by 1 person
Hi Nick,
This is a great blog piece. I did this for Visual Studio 2015, and it worked well. but have since upgraded my program to VS 2019. Problem is, when I add Data Source, I don’t see: Microsoft Dynamics 365 Fetch. I’ve tried separately downloading Microsoft Dynamics 365 Report Authoring Extension (installed), Workloads (Data Storage and Processing, which includes SQL.) What else can I do to make this available.
LikeLike
For now, you will need to run SQL Server Data Tools 2015 (Visual Studio 2015 version) separately from VS 2019, you should be able to have both installed. The 2015 version should show the FetchXML data source if you have report authoring extensions installed.
LikeLiked by 2 people
Hello, Nick!
Thank you for this great article! Could you please help me understand how to make a report, that I have created and deployed to Report Server, accessible in D365 environment? I’m was hoping to see this report in AOT, so that I could reference it to a menu item, but that is not the case. I haven’t tried the solution proposed at the end of the article, because it seems that I might be lacking access rights to power platform. Hence the question. Thank you!
LikeLike
Hi Ash, I am not sure I can help as this article is written specifically for Dynamics 365 CE (customer engagement, aka CRM). I believe (and correct me if I am wrong) that based on the fact you asked about AOT, you are looking for something that relates to Dynamics 365 SCM (aka F&O, AX) ? I have pretty much no experience on that side of Dynamics 365. Not even sure where to direct you to… Since Dynamics 365 CE and Dynamics 365 F&O are different platforms, I suspect that the reporting setup is also completely different.
LikeLike
Really helpful article Nick. thank you!
Next concern is how to convert the slightly more complex sql queries within old SSRS reports when on-premise, which was linking multiple tables, using temp tables etc. Guessing that’s not going to be possible if it all has to be through fetch xml.
LikeLike
Hi Nick
I’ve followed these instructions to the letter and I keep hitting a roadblock when autheniticating with Windows
Error : The authentication type must be specified.
Parameter name: CrmPassword
Any ideas?
LikeLike
Are you using the *latest* Report Authoring Extensions download? I wrote a blog recently on the latest version and the steps to get it running. You will need to uninstall the existing version first: https://readyxrm.blog/2021/01/23/ssrsdataverse2021/
LikeLike
Hi Nick
Thank you so much for getting back to me. I’ve managed to follow the steps in the new blog on my personal PC and it’s working fine. Do you know if the new extension will work with SSDT 2015? I think installing the community edition of VS2019 on my work PC may be violating the licensing.
LikeLike
According to the specs the new plug-in is supposed to work with SSDT 2015 but I haven’t tested it. The community edition of Visual Studio 2019 shouldn’t require licensing (e.g. free) where the professional and enterprise editions do require a license. I hope this helps!
Cheers, Nick
LikeLike
Hi Nick,
Common Table Expression(CTE) is not working in SQL CRM on cloud instance.
Could you please assist me an alternative way for CTE ? I was trying to migrate my Power BI report.
Temp tables are also not supporting in the CRM on cloud instance. Any suggestion from your end?
LikeLike