Despite all the cool stuff you can do with the Power Platform, one of my most popular blog posts is Set Up Your Workstation to Create SQL Server Reporting Services Reports for Dynamics 365.
The post was likely popular because there were different components in the setup process and Microsoft never did a great job explaining all the pieces and steps.
Another challenge installing the tools was that it required SQL Server Data Tools 2015, even after there were newer versions, and many started the process assuming the latest version would work (it didn’t).
Microsoft has finally updated the tools that can run on the latest versions of Visual Studio (2019) so here is an all-new, all-fresh version of how to install the tools you need to create SQL Server Reporting Services Reports that will run on model-driven Power Apps and Dynamics 365 (Customer Engagement Apps).
As a bonus, at the end of this post we will create a simple report and upload it to a model-driven Power App.
WANT SSRS REPORTS TRAINING? Check out Megan V. Walker’s SSRS REPORTS FOR CDS & D365 online course.
WHY SSRS REPORTS?
The ability to generate formatted, printable documents with information from Dataverse is still a very important business requirement.
Microsoft Word templates can provide some of this functionality, but has issues with pulling multiple tables, sorting and layout.
Power BI (especially paginated reports) is an option but requires some very expensive licensing and also requires additional integration steps into model-driven apps.
SSRS (SQL Server Reporting Services) is a viable option as it is included as a feature of Dataverse and provides a lot of flexibility and options in creating reports.
The Required Tools
You will need the following tools;
- Visual Studio 2019 (The free community edition will work perfectly well)
- Microsoft Reporting Services Projects Visual Studio Extension
- Dynamics 365, version 9.0 Report Authoring Extension*
*If you are building model-driven Power Apps and NOT using Dynamics 365, don’t get hung up on the name, you can create SSRS reports pointing to a Dataverse that you can launch from non-Dynamics 365 model-driven Power Apps.
Installing Visual Studio 2019
If you already have Visual Studio 2019 installed (Community, Professional or Enterprise Edition) you can skip ahead to Microsoft Report Services Projects installation steps further below.
If you are not a developer, don’t get stressed out about installing/using Visual Studio. Designing reports is very similar (or even easier) to creating canvas Power Apps. (maybe some folks will disagree with me?)
Since many folks tasked with Report Writing likely aren’t using Visual Studio for anything else, I will walk through installing the community edition but the other edition setups will be similar.
Go to the Visual Studio Community web page and click Download Visual Studio.
The process will download an executable to your local machine called “vs_community_xxxx.exe“. When it is finished downloading, double-click to run the file. You may be prompted that the program will make changes to your machine.
To create reports, you won’t need to select any Workloads (unless you want to). Click install to begin the process.
You will get prompted about workloads, just click continue.
The installation process will take a few minutes, and eventually present you with a choice of color themes. Pick your theme and start Visual Studio.
You will be presented with the ability to create a project, at this point, you can shut down Visual Studio because we need to load some additional components.
Installing Microsoft Reporting Services Projects
The next part we need is a Visual Studio extension found on the Visual Studio Marketplace called Microsoft Reporting Services Projects. This is the component that will allow us to build and edit Report Definition Language (RDL) files that describe SSRS reports.
Browse to the Visual Studio Marketplace and search for Reports. Choose the Microsoft Reporting Services Projects.
Choose to Download the package. This will download a VSIX package (Visual Studio extension)
Click on the VSIX package to being the installation process. The process will also install additional prerequisites such as SQL Server Data Tools (if they are not already installed).
The process will install the extension.
Eventually the Reporting Services Projects will be installed.
Dynamics 365, version 9.0 Report Authoring Extension
The final piece to install is the Report Authoring Extensions. This is the piece that has been recently updated to allow Dataverse reports to be built using modern (Visual Studio 2019) tools.
NOTE: If you have a previous version, you will need to uninstall it first!
What it does is allows reports to be written by reading data directly from Dataverse using FetchXML statements. Perhaps this means that the Dataverse SQL TDS endpoint will eventually be supported as well? (currently it is not)
Again, if you are building non-Dynamics 365 Dataverse applications (e.g. model-driven Power Apps), these steps still apply.
Download the Report Authoring Extensions from this link here.
This will download an executable with is a zipped file containing the installation files. Run the executable and extract the files.
When the files are extracted, run the installation process. I would recommend getting the updates if any are available, but its up to you.
Chances are there will be no updates, click next to continue.
Accept the license agreement and continue to download and install the required components.
Agree to download components and continue with the process.
When the required components are installed, continue with the process.
I also would recommend that the Microsoft Update is enabled to install required updates regularily.
Select an installation location, the default works fine, at very least, the location should be on your local workstation.
Once the system checks are complete, click Next to continue the installation process.
Finally, click Install to complete the process.
After a few moments the installation should be complete.
Create a Dataverse SSRS Report
Once the tools are installed, you are ready to start building your reports! I highly recommend Megan V. Walker’s SSRS Course if you are new to report writing. The following steps are a very simple primer to build a report that pulls data from Dataverse.
The first step is to fire up Visual Studio 2019. There is something satisfying using up to date tools to write reports after years of using SSDT 2015.
You will need to create a new project for your report(s).
Next choose Report Server Project (this is what was installed with the Microsoft Reporting Services Projects extension.
Give the project a name.
The Visual Studio “canvas” will appear. Now, normally you would add a new item (a blank report) by right-clicking the Reports folder, then Add, then New Item… (BUT)
As of the date of this post (January 23, 2021) this will generate an error, which is supposedly going to be fixed in the next Visual Studio update.
For the time being, you can use the Report Wizard to build a simple report and use that as your launching point for creating new reports, or load an existing report.
The Report Wizard will start.
The next step should specify a data source type of Microsoft Dynamics 365 Fetch. You will need to specify a connection string. This is the Environment URL to your Dataverse instance.
You can find the URL in the Power Platform Admin Center of your tenant;
Back in Visual Studio, You will need to supply your credentials to connect to Dataverse.
You will then need to design the query using FetchXML. You can spend time learning the FetchXML syntax, or do what I do and use FetchXML Builder in the XrmToolBox.
In this example, I used a simple query to retrieve Accounts. In more advanced reports you can link multiple tables or have multiple queries.
You may be prompted for cached credentials a few times in the following steps, click Yes to proceed. The wizard will ask you what kind of report you want to create, at this point you can pick either depending on requirements or if you plan to remove it and build out your own report using the file created to circumvent the error encountered earlier.
For now I created a table and added the fields to the details section. The wizard allows you to group, etc.
Finish off the report wizard process by giving the report a name.
When the wizard completes you will see the table on the canvas. At this point you can begin to modify the table, or delete it and add your own. You can get real fancy with sub-reports, filters, headers, footers, etc. Reports can be designed to the pixel. For now, lets keep it simple.
Select Preview to see the report (note you may be prompted for credentials again)
The report will run and display data from the Account Dataverse table.
Note where the RDL file is saved as you will need that to upload to your model-driven app.
Run the Report from a model-driven Power App
Creating a Dataverse report is great, but chances are you want your users to run it when they need it. If you are using Dynamics 365, you can add the report from the Reports option in app.
For a custom model-driven app, you either need to add the Reports link to the sitemap, add the report to a solution in the maker portal (recommend way) or add the report via the Advanced Find (quick and easy way that I will show you now).
When the Advanced Find window appears, run a search on Reports. When the list of existing reports appears, choose New Report.
On the new report screen, choose Existing File for Report Type, and upload the RDL file that was created in Visual Studio.
Choose the related record type (in our case, Accounts) and the Display In (lists) to specify that you want a link to the report to appear in the Accounts view.
IMPORTANT! Before saving the report, choose the Administration tab and specify the report to be viewable to the organization in order for all users to see it.
Save the report. Now, in your model-driven Power App, you can choose the Run Report menu item and select and run the report you uploaded.
You will see your Dataverse report and be able to download, print, etc.
Some Quick Questions regarding SSRS Reports and Dataverse
Does this require extra licensing?
No, this is a standard feature of Dataverse.
What about security?
Dataverse Security Roles are respected. If a user runs a report, they will only see data that is readable by the security roles they belong to.
Can I build a report on my dev system and move it to production, do I need to update connections (like flows)
Yes, you can build a report on any Dataverse environment and move it over to another, and it will automatically connect to that environment (no adjustment on your part). Note that if you have custom tables, they need to exist in the new environment as well.
Can you report on custom tables?
Are reports solution aware?
Yes. You should add them to solutions and have them a part of your ALM processes.
I feel SSRS Reports are not a well known feature of the Power Platform outside of Dynamics 365. There are many requirements that I have encountered over the years that were met using SSRS reports.
Cover Photo by Scott Graham on Unsplash
Nick Doelman is a Microsoft MVP, a Microsoft Certified Trainer. Nick started writing reports using Crystal Reports in Dynamics CRM 1.2 (the reporting engine for CRM at the time) and began writing SSRS reports for Dynamics CRM 3.0 and never looked back. Nick speaks at various community events and works with other MVPs delivering online training at https://365.Training Follow Nick on Twitter @ReadyXRM
21 thoughts on “Set Up Your Workstation to Create SSRS Reports for Dataverse, Model-Driven Power Apps and Dynamics 365”
Hi Nick, I’m wondering if we could simplify the above by using the Power BI Report Builder. It supposted to be a superset of SSRS. There is also a datasource available from the dropdonw called ‘common data service (preview)’.
The output that is created is a .rdl file. Do you think we could build a report in the PowerBI report builder and upload this directly to D365 (online !)
LikeLiked by 2 people
I haven’t tried that yet, but will definitely look into it.
LikeLiked by 2 people
Have you tried it?
I am facing rsprocessingaborted error while accessing report. I am system admin. Its custom report on CRM online
The rsprocessingaborted error can be tricky to troubleshoot. Does the report run in Visual Studio? If not, chances are that its the report itself. You could also create a simple report using the Report Wizard (Solutions -> New Report -> (Report Type = Report Wizard) and if it runs, then you know that your SSRS is working properly, and the issue is with your report. (if a report wizard report fails, then definitely open a support ticket with MS, as it is backend) I would then start to rebuild the report, piece by piece, to zero in on the issue (could be datasource, could be some function, etc). You need to be methodical in the troubleshooting. Failing all that, the other option is to open a support ticket with MS, as they might have better visibility in the diagnostic logs and can zero in on what the issue is. Hope that helps! Cheers, Nick
I installed Power BI desktop and now the ‘Microsoft Dynamics 365 Fetch’ has disappeared from my list of data source options.
That’s strange, I have both installed with no issues. I would suggest re-installing the Dynamics 365 Report Authoring Extensions to see if that restores the data source option.
I hope you are doing well.
I tried to follow this tutorial, but I’m facing multiple errors during installation. I already installed VS 2019 Pro and the reporting extension that needs to be installed too, but when I try to installed the Dynamic 365 extension it tells me that “Some other components are missing” and also show this link: https://go.microsoft.com/fwlink/?linkid=2151248
Which recommends to install the Microsfot Dynamic Customer Engagement (On-Promise), but this installer commes with 3 differents installers at the same time, each of them mentioning that the OS is not the right version, event when the Dynamic 365 extensions mentions that Windows 10 is supported for this deployment.
What would be the minimum requirements for a developer machine in order to install the Dynamics 365 extension, the vs 2019 and the Microsoft Reporting Services Project and start deploying reports with SSRS?
Thanks for your help.
Sorry to hear you are having trouble. First off, make sure you don’t have any previous version of the Dynamics 365 Report Authoring extensions installed. Then make sure you have all the Windows updates (I am running Windows 10 Professional). After VS2019, plus Microsoft Reporting Services project extension to VS, then install the latest (version 18.104.22.168) of the Dynamics 365 Report Authoring extensions (link here: https://www.microsoft.com/en-us/download/details.aspx?id=56973) There are various versions of the report authoring extension available, but the latest should install without prompting for any of those other components. I hope that puts you on the right path.
Reblogged this on Nishant Rana's Weblog.
This was really helpful in getting things set up for me, thank you! I was going along just fine creating reports and then I updated VS. For some reason I now cannot connect to my data source when trying to preview the report. No matter what approach I take, I get the same error: “You don’t have permission to access any of the organizations in the Microsoft Common Data Service region that you specified. If you’re not sure which region your organization resides in, choose “Don’t know” for the CDS region and try again. Otherwise check with your CDS administrator.”
Not sure if others are having the same issue or if you are but would love to know of any tips you have.
Microsoft recently deprecated the regional discovery service. Try to fix by downloading the latest should be 22.214.171.124. The link is here: https://www.microsoft.com/download/details.aspx?id=56973
Thank you so much Nick! This worked. I appreciate the response.