ReadyXRM

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?

Yes.

Are reports solution aware?

Yes. You should add them to solutions and have them a part of your ALM processes.

Summary

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