sql server reporting services dynamics 365

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Configure SQL Server Reporting Services for on-premises deployments

• 7 contributors

Use the steps in this article to configure SQL Server Reporting Services (SSRS) for your Microsoft Dynamics 365 Finance + Operations (on-premises) deployment.

Only follow this guide if your environment was deployed using a base deployment older than Application version 10.0.17 (with Platform update 41).

Open the Reporting Services Configuration Manager application.

Leave the default Server name , which should be the name of the current machine, and the Report Server Instance , MSSQLSERVER .

Click Connect .

Click the Service Account tab and verify that the settings match the following graphic.

SQL Server Reporting Services 2019 no longer allows choosing the Local System account. Instead you need to use NETWORK SERVICE account.

Click the Web Service URL tab and verify that the settings match the following graphic.

Click the Database tab and verify that the Database Name and Credential settings match the following graphic.

You will need to create a new database. To do this, click Change Database , and then verify that the new database name is: DynamicsAxReportServer .

Click the Web Portal URL tab and verify that the settings match the following graphic.

You must click Apply to create and properly configure the Portal.

After the Portal is configured, the Web Portal tab will match the following graphic.

Click the reports URL to view the SQL Server Reporting Services web portal.

When you are in the portal, create a new folder named Dynamics .

In the Reporting Services Configuration Manager , click the E-mail Settings tab and verify that the settings match the following graphic.

Click the Execution Account tab and verify that the settings match the following graphic.

Don't change the default settings on the Encryption Keys tab.

Click the Subscription Settings tab, and verify that the settings match the following graphic.

Don't change the default settings on the Scale-out Deployment tab.

Don't change the default settings on the Power BI Integration tab.

Click Exit to close the Reporting Services Configuration Manager .

For SQL Server 2019 only, you need to manually grant NETWORK SERVICE the permissions to read the private keys of these certificates.

• DataEncryption
• DataSigning
• ReportingService

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

In this course, we will learn to implement and build a SSRS Report in Dynamics CRM. The Microsoft’s Dynamics 365 CRM and Model Driven PowerApps offers various number of advanced reporting tools. These advanced reports are often necessary when client requirements demand complex calculations, retrieving data from different data sources, grouping large sets of data based on rules, and returning multiple data sets.  The Dynamics CRM out-the-box reporting system is not fully capable of driving such complex logics, hence we use SSRS Reports to suffice the above requirements. Lets start with building your own SSRS Report from scratch.

SSDT for Visual Studio (VS) 2015

Click here to download SSDT for Visual Studio (VS) 2015 .

When the download is complete, run the SSDTSetup.exe (as administrator) program to install the tool.  When prompted, choose to install SQL Server Reporting Services and click on next as shown below .

Dynamics 365 Report Authoring Extension

The Dynamics 365 Report Authoring Extension is an addon for SQL Server Data Tools that will allow you to write reports using the FetchXML .  Currently, this is the only way to write SSRS reports that can access Dynamics 365 Online data directly.

Click here to download the Dynamics 365 Report Authoring Extension. Extract the package and install it in your system.

Create a new SSRS Report

We will create a new SSRS report from scratch using the above downloaded tools.

Create a Reporting project.

Launch the SQL Server Data Tool (SSDT) 2015 as shown below.

Click on Files >> New >> Project and create a new Project with the Projecting Services as “Report Server Project”, framework as “.Net Framework 4.5.2” or above as shown below.

Add a report in the project file.

In the project, right click on the folder called “Reports” >> Add >> New. Select the Report Project type as “Report” and click on Add.

Now we have added a new blank report to our reporting project file. Here you will observe the reporting components such as Data Sources and Data Sets are populated in the Report Data section as shown below.

Add Data Source to your report.

We have to give a data source to your SSRS report. In our case, the Dynamics 365 environment will act as a data source. Right click on the Data Sources >> Add Data Source. In data source, select the Embedded Connection type as Microsoft Dynamics 365 Fetch and enter the CRM Dynamics URL in connection string. (Note : – This is the above downloaded Add-On). Click on Ok.

Add a Data Set to your report.

Once the data source is added, now its the time to add the data set. The data sets are the tables which are used to store the data from the data source. As our data source in Dynamics CRM, you will use the fetch XMl as your data set.

Consider the scenario where you have to create a SSRS Report to display data of Account entity. To do so, navigate to CRM >> Advance Find >> Query the Account entity (Add the columns as per your need) and click on Download Fetch XML as shown below.

Copy the downloaded Fetch XML, navigate back to report project, right click on the Datasets >> Add Dataset. Select the above created Data Source, set Query Type as Text and paste the above downloaded Fetch XML query in the Query text area as shown below. Click on Ok.

Once you click on Ok, the SSDT data set will get connected with your Dynamics CRM. Enter the credentials and help SSDT will establishing the connection.

Now, you can see the data set is successfully created in form of tables as shown below.

Create a table and insert data.

In the same project, open the tool box, select component “Table” and drag it in the designer panel as shown below.

You can add multiple rows and columns by right clicking on table >> Add new Row / Column. Below is the table created for demo purpose.

Right click on the Table/ Tablix >> Tablix Properties and select the data set which was created above.

Lets add the data to our table, right click on the below row component and select “Expressions”.

We can perform complex operational logics and calculation with of the Expressions. In Expressions, navigate to the section called “Fields”. Here you can find all the fields or columns available in the data set. Add the expressions (Fields mapping) in each of the rows as per your need.

Now our table is ready with the necessary data. Lets deploy this table in the Dynamics CRM.

Deploy the SSRS Report in Dynamics 365 CRM

Rebuild the above created report and go the project’s local folder and copy the .rdl file (Report Defination File) as shown below.

Now, navigate back to the Dynamics CRM. Open the Advance Find >> Reports >> New Report. Select report type as “Existing File”, upload the above obtained .rdl file in this section. We have done the categorization in order to display the report in Account entity’s report section as shown below. Click on Save.

Now, the report is deployed in Dynamics CRM.

Test the deployed SSRS Report

In CRM, navigate to the Account record >> Ribbon Bar >> Run a Report. In the section “Run a Report”, you will find the report which we have created above.

View our latest blogs

Integrate Copilot control into a Canvas App

Optimizing data query practices in Canvas Apps

Using Upsert & UpsertMultiple request in Microsoft Power Apps

Congratulations, well done, welcome to crm crate.

Stay tuned with us and get all latest updates and learning in Microsoft CRM and related techonologes.

• Dynamics 365 CRM

How to Create SSRS Reports in Dynamics 365

Marketing Department

• December 19, 2022

SSRS stands for SQL Server Reporting Service report. You can use SSRS reports to dynamically present your data from Microsoft Dynamics in graphs, charts and bars to help with smooth Dynamics implementation .

Additionally, you can use SSRS on structured data. These SSRS reports help you reduce complexity within other integrated apps such as business central and FinOp/SCM .

SSRS provides user-friendly facilities with batch processing, email support, and print archive functionality

It is a beneficial way to produce packing slips for tracking orders from manufacturing to sales warehousing. Furthermore, it provides a beautiful way to extract data to articulate business health. It also provides tools to support different basic reporting requirements. These SSRS reports work best when you integrate them with Excel , Reporting Services , Power BI Data Flow , and Management Reporter .

Basic Components of SSRS Report

  DP (Data Provider) class: A DP (Data Provider) class  renders a job to provide a data

Data Contract class : In the contract class ,  you define several variables and report parameters in the data contract class. There can be some hidden parameters in the data contract class. Here is a scenario we have one hidden parameter, and that is the sale’s agreement RecId.

Three temporary tables: We create three temporary tables to define and carry the report data. They will be filled, and the date these tables are holding will be exposed to the SSRS report, and this job will be rendered by the Data Provider class.

Controller class: In the Controller class , you handle the report dialogue form, the SSRS report’s design, and the hidden parameters’ values. In this scenario, we will take the value of the sale agreement RecId as our concealed parameter.

Menu item: There should be something that opens the SSRS report so you can view the SSRS report in printable form. The menu item usually renders this job. It also tells you that the menu item triggers the report dialogue form.

Now let’s create temporary tables for our SSRS report.

Creating Temporary Tables:

Navigation to create temporary tables for your SSRS report is given below

Create temporary tables by going to  Add  >  New Items  >  Dynamics 365 Items  >  Data Model  >  Table

By following the same procedure, you can create three temporary tables

DocAgreementHeader  –used to store a sales agreement’s header data.

DocAgreementLine  – used to store a sales agreement’s lines data.

DocAgreementContact  – used to store a sales agreement’s customer contact data

As u can see below

What makes your table behave as a temporary table is a TableType property. Set this property to InMemory for all tables. Selecting this property for your tables allows you to convert a standard table to a temporary one.

Create the Controller Class

First of all, we have to extend our contract class with SRSReportRunController.

After that, we have to define DataContractAttribute attributes

A Data Provider class extends SRSReportDataProviderBase and should also implement at least the process report () method.

To add a new class, toggle to Add  >  New Items  >  Dynamics 365 Items  >  Code  >  Class .

By following this navigation, we will create three classes as given below

DocAgreementDocumentController  –   Controller  class.

DocAgreementDocumentContract  –   Data Contract  class.

DocAgreementDocumentDP  – Data Provider  class

For every report, each controller class should extend  SrsReportRunController

Create the Data Contract Class

The data contract class basically stores all the data members or parameters of the report.

Code for Data Contract Class

Create the Data Provider Class

Remember, your Data Provider  class should expand SRSReportDataProviderBase for pre-processed reports. Also, it should at least implement the ProcessReport() method.

The ProcessReport() method is called by Reporting Services to fetch the report data and fill the temporary tables. In our scenario, we have three temporary tables.

Code for Data Provider Class

Create the SSRS Report

Now we will create our SSRS report by using the navigation that is given below

To add a new SSRS report into Dynamics 365.

Go to  Add  >  New Items  >  Dynamics 365 Items  >  Reports  >  Report

Set the Source Data of the Report to DP Class

Now add a dataset to the report. Also, set its  Data Source Type  property as  Report Data Provider . Once done with selecting the source type, click the small button available on the  Query property.

In your SSRS report’s dataset, add a new dataset and change the property of Data Source Type to Report Data Provider, then check the small button available on the Query Property. As shown below, follow the same procedure.

Replicate the same steps to add more than one dataset.

Now we’ll create the design of our report

Create the Menu Item for our SSRS Report:

To create a menu item, create a new extension for the  SalesAgreement  form.

The first (Preview document) will open the report on screen, and the second ( Print document ) should open the report dialogue form.

As in our case, we will create a different extension for the SalesAgreement  form for adding two menu items.

The first one is to preview the document on the screen.

The second one will open the report dialogue form.

Execution Of  SSRS Report:

The report that we’ve just created is going to be executed.

So now open your Microsoft dynamics 365 finance and operation.

Follow the navigation that is given below.

Goto Accounts receivable  >  Orders  >  Sales agreements.

Here, open any sales agreement. Then, click the Preview document  to see the result.

Your report will display on the screen with the same design that is given below

Conclusion:

In this article, we’ve covered how to create dynamics CRM SSRS reports. SSRS can assist you with making direct, graphical, and freestyle reports from social, multi-layered, and XML-based information sources. The messages can likewise be distributed and gotten to on request. SSRS again has an implicit planning instrument to perform fundamental report organizations through email, record share, or SharePoint.

Did we mention that building SSRS reports can help you construct a healthy business management system? If you are looking to integrate SSRS reports within your Microsoft Dynamics with expert assistance, do not hesitate to contact us .

• Reports in Dynamics 365

Recent Posts

Advantages of Integrating Power BI with Dynamics Business Central

Top 7 Innovative Approaches to Canvas App Development

How Can AI Improve Customer Service in Dynamics 365?

Improving your Data Experience: The Importance of Staff Augmentation in Data and AI

Stallions Solutions delivers innovative business management solutions as a Microsoft Certified Partner. Our experienced consultants design customized solutions using Dynamics 365, Power Platform, Azure, Machine Learning, and SharePoint to optimize processes and improve bottom lines.

Our Technologies

• Business Central
• Dynamics 365 FinOps/SCM
• Dynamics 365 CE
• Dynamics 365 HR
• Power Platform
• Machine Learning
• Share Point

Our Services

• D365 Consulting
• D365 Implementation
• D365 Migration
• D365 Integration
• Power Platform Development
• Business Intelligence Services
• Microsoft Azure O365
• .Net Development

+44 (0) 203 129 7000

[email protected]

Fergusson House, 124 City Road, London, EC1V 2NX

Follow us at: 

© 2024 stallions solutions. all rights reserved..

Implementing Microsoft Dynamics 365 for Finance and Operations by Rahul Mohta, Yogesh Kasat, JJ Yadav

Get full access to Implementing Microsoft Dynamics 365 for Finance and Operations and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

SQL server reporting services

SSRS continues to be the platform for producing advanced operational and business document reports in Dynamics 365 for Finance and Operations, Enterprise edition. The process for developing a report in the current version of Dynamics 365 for Finance and Operations, Enterprise edition is easier than it is in AX 2012, because you can create and validate a reporting solution entirely in Visual Studio. In Finance and Operations, administration of SSRS is also simplified by hosting the services on Microsoft Azure compute service. SSRS framework also provides d ocument printing and distribution services for producing precision documents which are intended for email, printing, archive, and bulk distribution. If you have ...

Get Implementing Microsoft Dynamics 365 for Finance and Operations now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

Creating SSRS Report for Dynamics 365 Online and Model Driven Power Apps

By Subhankar Roy  |  Published on July 9, 2020

Microsoft Dynamics 365 online and Model Driven PowerApps offers several advanced reporting options. Advanced reports are often necessary when business requirements demand complex calculations, returning multiple data sets, grouping large sets of data based on rules, and retrieving data from different data sources.  Before creating an advanced report using SSRS, you may also consider using Advanced Find, Dashboards and Charts, or the Report Wizard in Model Driven PowerApps/D365 Online.

In my experience, the out of the box reports offered by Dynamics 365 are not often used and in most cases, there is a need for custom reports. In such a situation, SQL Server Reporting Service reports offer much flexibilities as compared to Dynamics 365 Report wizard and creating SSRS reports does not warrant any particular development skill.

Watch Video: Microsoft Dynamics 365 – Reporting

In this article we are going to show that with some basic knowledge you can create a report using SSRS (SQL Server Reporting Services) for Model Driven PowerApps very quickly.

Pre-Requisites:

• Visual Studio 2015 or higher. Download from here
• .Net Framework 4.5.2 and above.
• Microsoft Dynamics 365 Report Authoring Extension to author Fetch-based.
• Microsoft SQL Server Reporting Services .

Optional Requisites

• XrmToolBox to connect to the Common Data Service for Apps (CDS)
• FetchXML Builder Plugin for XrmToolBox, to create and execute queries against CDS and Dynamics 365/CRM data.

Once we have downloaded and installed above required, we are ready to start Creating SSRS Report for Dynamics 365 Online/Model Driven PowerApps.

SSRS Reports Using Fetch XML in Dynamics 365

• Open Visual Studio 2015 and start a New Project. Select Templates > Business Intelligence  >  Report Server Project
• Specify project  Name  and  Solution Name . Click  Ok.
• Now expand Solution Explorer > Right Click on Reports Folder > Add > New Item From the Add new Item Window Select Report > Provide a Name > Click on Add
• Once the report is added you should see the following screen.

Adding Parameters

• You can add parameters to a report to define a report’s individual parameters, pass information through a query, or provide access to user settings, such as CRM_CurrencySymbol and CRM_CurrencyPositivePattern parameters.
• From Report Data Toolbar expand Parameters and right click to add parameters.

Hidden parameters

• The Report Designer in Visual Studio has built-in support for hidden parameters. In addition, you can hide parameters by adding a CRM_ prefix to the parameter name in a report. By default, the parameters with a CRM_ prefix are hidden when the report is published. When you run the report, you aren’t prompted to enter parameter values for the hidden parameters.

Special parameters

The following table shows the special hidden parameters that you can use in your reports.

You must create all parameters in a report before you can refer to them. The values of these special parameters are filled in by Customer Engagement (on-premises) when you run the report.

Also read : Dynamics 365 Integration Service

Adding Data Source to The Report

• Provide a proper Name and select Embedded Connections
• Select the Type as Microsoft Dynamics 365 Fetch
• In the connection, string section put your D365 URL
• From the left navigation click on Credentials and Use your Username and Password or Select Prompt for credentials if you don’t want to store the credentials.

Adding Data Set to The Report

• From Report Data Toolbar expand Datasets and right click to add new data set.
• Specify Name and select the Data source created on previous step.
• Select the Query type as Text.
• Use FetchXML Builder to create Fetch XML and paste the same here in the Query text.
• Apply prefilter and filters as per requirement.
• Make sure to remove page size attribute from fetch XML if present as these is not supported.

Designing the report

• Once all the above steps are completed, we are ready to design the report.

• Use the Toolbox and the Dataset to create the report as per your requirement.

Build and Publish the Report

Build the rdl file.

• In the Solution Explorer, right-click on the Project and click Properties.
• In the Solution Explorer, right-click on the Project and click Build or Rebuild.
• In the Solution Explorer, right-click on the Solution and click Open Folder in File Explorer.
• You will find the RDL file placed in the ..\ [Project Folder] \bin\Debug folder.

Import the RDL file in Dynamics 365:

• In Dynamics 365, open the Reports section and click New.
• In the New Reports form.
• Select Report Type as Existing File.
• Click Choose File to select the RDL file.
• Specify Name.
• Select Display In as per your requirement.
• Click Save and Close, this will import the report in the Available Report List, from where you can now run the report.

Open the Report

• Go to the entity form for which you have chosen the report.
• Click on the 3 dots symbol if Run Report button is not available in ribbon of the form.
• In Run Report section you should be able to view the report.
• Click on the report to run it.

The goal of this article was to demonstrate how quickly you can leverage SSRS reporting capability and apply it to your Dynamics 365 information set. It is really onto you to experiment and explore how you can exploit all the features of SSRS reports and we may talk about that in some later post. Meanwhile, enjoy creating reports and stay in touch if you need any further assistance.

Related Posts

• Application Modernization (1)
• Azure Development (5)
• Business Application (2)
• Cloud Infrastructure and Management (18)
• Cloud Infrastructure and Security (27)
• Cloud Infrastructure Management (36)
• Content and Collaboration (52)
• Custom Development (42)
• Data and Analytics (11)
• Data and Business Analytics (5)
• Data Engineering and Analytics (13)
• Digital Workspace (5)
• Documentum to Microsoft 365 Migration (2)
• Dynamics 365 (27)
• EFSS to Microsoft 365 Migration (3)
• Endpoint Management (5)
• Enterprise Search (5)
• eRoom to Microsoft 365 Migration (4)
• Govern 365 (3)
• Governance (1)
• Intelligent Communication (2)
• Managed Services (7)
• Microservices (1)
• Microsoft Power Platform (34)
• Microsoft Teams Rollout (13)
• Migrations (22)
• Miscellaneous (6)
• Modern Applications (17)
• Modern Enterprise Data Center (2)
• OneDrive for Business Enablement (4)
• Security and Compliance (3)
• SharePoint Custom Development (133)
• SharePoint On-Prem to SharePoint Online Migration (1)
• Tenant to Tenant Migration (4)

Excellent step by step article. Was able to solve my problem.

This was a great primer to get started with SSRS development for Dynamics 365.

I did have one issue. When attempting to install the “Microsoft Dynamics 365 Report Authoring Extension” on my Windows 10 machine with VS 2017 and VS 2019 installed, the installation would encounter an error and say that said there was an environment error and that not all required components were installed. After doing some digging, it appeared that VS 2015 is required.

I tried the install on another Windows 10 machine that had VS 2015 installed and it installed without issue.

Hi David, Thanks for reply. Yes, you are correct. Microsoft haven’t yet release the version for VS 2017/2019. According to this article: docs.microsoft.com/…/microsoft-dynamics-365-reporting-requirements

They updated on April 2019, but still the latest VS requirement is 2015.

Instead of importing the RDL into Dynamic 365, is there a way to load that RDL on my own SQL Report Server? I haven’t found a solution online…

We plan to move AX2012 to D365, Once we go that same SSRS report using is possible or not?

Yes, you can use SSRS with D365 but it will require some code migration. Here is a reference for more info on the upgrade tool. https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/lifecycle-services/configure-execute-code-upgrade

Leave a comment Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

Stay Connected

Subscribe and receive the latest insights

Get involved by tagging Netwoven experiences using our official hashtag #UnravelTheComplex

Megan V. Walker

Tips, tricks and ideas for non developer geeks

SSRS Reports For CDS & D365 Using FetchXML

*** note: all information is accurate at date of publishing ***, disclosure : this post may contain affiliate links. full details here.

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!

Stuff about the Microsoft Power Platform and Dynamics 365

Set Up Your Workstation to Create SSRS Reports for Dataverse, Model-Driven Power Apps and Dynamics 365

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

Share this:

Published by Nick Doelman

Nick Doelman is an independent Power Platform specialist, trainer and coach. Nick delivers presentations, workshops and collaborates on Power Platform and Dynamics 365 projects worldwide. From 2021 to 2023, Nick worked as a Senior Content Developer at Microsoft where he worked on skilling content for Power Pages, Power Automate, and other parts of the Power Platform. Nick has been awarded the Microsoft Most Valuable Professional (MVP) and is also a Microsoft Certified Trainer (MCT). Nick is a cohost on the Power Platform BOOST podcast as well as one of the organizers of the Canadian Power Platform Summit. Nick is also a competitive Powerlifter and is a member of Team Canada competing at various international events. View all posts by Nick Doelman

21 thoughts on “ Set Up Your Workstation to Create SSRS Reports for Dataverse, Model-Driven Power Apps and Dynamics 365 ”

• Pingback: Set Up Your Workstation to Create SQL Server Reporting Services Reports for Dynamics 365 Revisited – ReadyXRM
• Pingback: Setting up your workstation to write SQL Server Reporting Services Reports for Dynamics 365 – ReadyXRM
• Pingback: Power BI Paginated Reports vs SSRS reports - Microsoft Dynamics CRM Community
• Pingback: January 25, 2021 Weekly Update on Microsoft Integration Platform & Azure iPaaS - Hooking Stuff Together
• Pingback: January 25, 2021 Weekly Update on Microsoft Integration Platform & Azure iPaaS - BizTalkGurus

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 !)

Like Liked by 2 people

I haven’t tried that yet, but will definitely look into it.

Have you tried it?

Hi Nick, 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 9.0.26.6) 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.

• Pingback: Secrets of the Dataverse: The Report Wizard – ReadyXRM

Reblogged this on Nishant Rana's Weblog .

• Pingback: Set Up Your Workstation to Create SSRS Reports for Dataverse, Model-Driven Power Apps and Dynamics 365 ERP for Hong Kong SME

Hi Nick, 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 9.0.26.6. The link is here: https://www.microsoft.com/download/details.aspx?id=56973

Thank you so much Nick! This worked. I appreciate the response.

• Pingback: Reporting and Data Visualization in Dynamics 365 and Power Platform – Feridun's Knowledge Base

Leave a comment Cancel reply

• Power Platform BOOST! Podcast
• Training Services
• Microsoft Learn

• Already have a WordPress.com account? Log in now.
• Subscribe Subscribed
• Copy shortlink
• Report this content
• View post in Reader
• Manage subscriptions
• Collapse this bar

Build 2024: What’s new for Microsoft Graph

Jason Friedman

May 28th, 2024 0 1

In this blog, we’ll highlight how you can expand the knowledge of Copilot for Microsoft 365 with Microsoft Graph connectors and build custom solutions for your business leveraging the data and services in Microsoft Graph APIs.

In the world of building generative AI solutions, you can use Microsoft Graph APIs to interact with your collaboration data and perform productivity tasks. We’re excited to share our latest updates and for you to try them out!

Copilot extensions and Copilot connectors

At Build, we introduced Copilot extensions to expand the actions that Microsoft Copilot can take on the user’s behalf, customize grounding knowledge with relevant business data, and enable hand-off to other copilots.

We also introduced Copilot connectors in Copilot Studio to simplify how developers connect their business and collaboration data to their copilots. Copilot connectors include Microsoft Power Platform connectors, Microsoft Graph connectors, and Power Query connectors—with Microsoft Fabric integrations coming soon. Read more about Copilot connectors and other Copilot Studio announcements here .

Microsoft Graph connectors in Microsoft Copilot Studio

Let’s take a deeper look at Copilot connectors in  Microsoft Copilot Studio . Anyone with a valid Copilot license can create custom copilots, use Graph connectors as a source of knowledge, and publish them as copilots either within Microsoft Copilot or third-party applications. Microsoft Copilot Studio utilizes Graph connectors to enhance the capabilities of copilots by integrating knowledge from third-party systems like JIRA, Confluence, on-prem file share etc. This integration allows makers to add a variety of data sources within their enterprise to augment the generative AI capabilities of various copilots, to suit their specific needs and workflows. These capabilities can be further enhanced by utilizing additional Microsoft Copilot Studio features such as custom workflows, generative actions etc. This capability allows integration of Graph connectors as a knowledge source to Microsoft Copilot Studio.

Imagine you want to create a custom copilot as a student accommodation assistant on your website. Start by integrating it with your organization’s real-time data, enabling multi-turn chat on topics like product information. For critical functions like account management, design specific conversational flows using visual authoring or natural language before deploying generative AI.

Ground your copilot with 150+ available Graph connectors as knowledge, allowing the copilot to handle queries from multiple sources.

Publish your copilot across multiple platforms, such as websites, Microsoft Teams, and social apps, with escalation options to tools like Dynamics 365 for human assistance. Monitor performance via a built-in analytics dashboard and ensure data protection with governance features in the central admin center. Apart from these foundational steps, makers can utilize  many other features  in your custom copilot.

Building Copilot extensions using Microsoft Graph connectors

With our new capability, admins can now create Copilot extensions using one or more Graph connectors, directly from the Microsoft 365 Admin Center. This capability, currently in private preview, leverages the power of Microsoft Graph connectors to enrich Copilot’s organizational knowledge, enabling it to process and communicate in natural language more effectively.

The new feature allows for targeted searches, natural language queries, and custom communications in the form of predefined prompts, all within the familiar Copilot for Microsoft 365 interface on semantically indexed third party data in Microsoft Graph. With the ability to control deployment to specific users or groups, admins can ensure a personalized and secure experience. Stay tuned for the public release in July and reach out if you are interested in early access to this innovative way of enhancing Copilot functionality.

Simplified setup for Microsoft Graph connectors

We’re happy to share that we have made it easier than ever for admins to set up Graph connectors! The new simplified flow reduces the number of mandatory steps required to configure and publish a connection. We’ve also implemented intelligent defaults based on what works best for each Graph connector. Admins can easily review and adjust these defaults as needed. This simplified setup is now available for all  Microsoft-built Graph connectors .

Enhanced connection statistics for Microsoft Graph connector admins

Connection statistics is an essential tool for admins to gain precise insights into crawled and indexed items. This enhancement will help admins in comprehending the total number of items discovered, successfully indexed, or failed across all crawls. It will provide a cumulative perspective on the sync between the data source and Microsoft Graph connector index. With this enhancement, admins can swiftly identify discrepancies and ensure that their connection is up to date. Connection statistics is coming soon to general availability.

Microsoft Teams API updates

Developers leverage Microsoft Teams to build solutions for cross-industry, business-to-consumer (B2C) online meeting workflows in use cases such as clinician-led consultations (healthcare), banking and lending (financial services), and apparel purchases (retail). The new Microsoft Graph SMS  Notification  and  Reminder  APIs, now generally available, reduce no shows and facilitate a seamless customer experience that allow external attendees to receive SMS text notifications and reminders with an embedded join link.

Microsoft Teams app developers can send Microsoft Teams activity notifications to users without making manifest changes to their app. This is possible via the now generally available systemDefault property of Microsoft Teams  sendActivityNotification API .

Available in public preview, users are able to  subscribe to changes in any chat at user-level . This lets users receive Microsoft Graph API change notifications for all changes across all chats that a specific user is a part of.

A  new query parameter  “notifyOnUserSpecificProperties” lets users get properties for a chat, like “viewpoint”, which shows when the user read the last message and whether the chat is hidden. This is useful for developers who use  Microsoft Graph Toolkit  to make chat clients, for example. This query parameter is available in public preview, and users will be able to get these properties if they have a subscription to a specific chat or a subscription to all chats that a specific user is part of.

In addition, developers can now see how custom emojis appear in the Microsoft Teams message body and how custom emoji reactions are part of Microsoft Teams message reactions. This feature is available in a public preview release of  Chat and Channel Get Message APIs .

Approvals in Microsoft Teams APIs  are coming soon in public preview. These APIs will enable developers to programmatically create, manage, and delete approvals, making it easier to hook line of business apps into the  Microsoft Approvals Hub .

SharePoint API updates

SharePoint Pages API , now generally available, enables developers to manage SharePoint Pages programmatically, opening doors to incredible customization and automation opportunities. For example, developers can fetch page content programmatically for a third-party process flow or power a bot-like experience to help end users get answers from content in a page. Developers could also create pages and news posts programmatically on a schedule and at scale.

SharePoint Premium document processing APIs, coming soon in public preview, will enable developers to programmatically access  Syntex document processing models . For example, developers can use the APIs to apply a Syntex document processing model to document libraries and get a Syntex document processing model by name or id. Developers can also run document processing jobs to process files in these document libraries. Developers can also run document processing jobs to process files in these document libraries.

Other API updates

Custom Claims API, coming soon in public preview, addresses the disparity between our type 2 (claims issuance) and type 14 (claims mapping) policies for claims management in Microsoft Entra ID. It allows admins to use Microsoft Entra Portal and Microsoft Graph API interchangeably to configure and manage optional claims for their enterprise applications, enabling them to no longer be mutually exclusive. Additionally, any updates made to the optional claims through Custom Claims API are logged in audit log for review and audit requirements.

Additionally, we’ve launched the  Custom Claims Providers  capability, which enables developers to augment tokens with custom claims from external data sources. For example, developers could bring data from legacy on-prem data stores via an API into the authentication process and have Microsoft Entra issue tokens with the data as custom claims, thus providing this data to applications without the need to update application logic. This feature and the supporting Graph APIs to enable these custom authentication extensions are now generally available!

ExportJobs API , now generally available, can be used for reporting and data export scenarios. We continue to update available reports, and through exportJobs, you can improve throughput and minimize Microsoft Graph API calls to get Intune data for common scenarios.

For more Microsoft Graph API updates, see  What’s new in Microsoft Graph.

Microsoft Graph tooling updates

Microsoft Graph Toolkit v4.0 , now generally available, is packed with new capabilities, improvements to the developer experience, and bug fixes that will be appreciated by end-users. Users can now edit To Do items directly using the Microsoft Graph Toolkit components and see colleagues’ presence and cards when selecting them via our people picker. We also promoted our search components to their general availability, and they are now fully supported.

Microsoft Graph Java client library v6 , now generally available, is generated using Kiota, our code generator that produces more efficient and easier to use libraries. Kiota enables us to provide a consistent and fluent API across different platforms and new features to enhance your development experience. On top of that, this library also makes available new pagination support (PageIterator implementation), ODataCast support, and a backing store.

Microsoft Graph service capabilities updates

Bicep templates  for Microsoft Graph resources allow you to define the tenant infrastructure you want to deploy, such as groups or applications, in a file, then use the file throughout the development lifecycle to repeatedly deploy your infrastructure. The file uses the Bicep language, a domain-specific language (DSL) that uses declarative syntax to deploy resources, typically for your infrastructure-as-code solutions. This capability is now available in public preview.

Microsoft Graph activity logs , now generally available, give you visibility into HTTP requests made to the Microsoft Graph service in your tenant. With rapidly growing security threats and an increasing number of attacks, this log data source allows you to perform security analysis, threat hunting, and monitor application activity in your tenant. You can store and query the logs in an Azure Log Analytics Workspace and use the full set of Azure Monitor Logs features, such as a portal query experience, alerting, saved queries, and workbooks. You can also archive the logs in Azure Storage accounts or export to other security information and event management (SIEM) tools through Azure Event Hubs.

EventHub has always been an easy and powerful tool for receiving Microsoft Graph Notifications without having to set up your own web server endpoint. Now it’s even more secure as you can  use role-based access controls  to set up your EventHub to receive notifications from Microsoft Graph without having to share access to your KeyVault.

Closing thoughts

Thank you for taking the time to read through our announcements. We look forward to seeing what you build leveraging the data and services of Microsoft Graph. There are more possibilities than ever to create custom workflows, provide custom experiences, automate business processes, and build generative AI solutions on top of Microsoft Graph. Happy building!

Follow us on  X (Twitter) / @Microsoft365Dev ,  LinkedIn , and subscribe to our  YouTube channel  to stay up to date on the latest developer news and announcements.

Jason Friedman Product Manager, Microsoft Graph

Leave a comment cancel reply.

Log in to start the discussion.

Insert/edit link

Enter the destination URL

Or link to existing content