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.
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.
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.
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.
Parameter
Description
CRM_FilterText
Contains the value of the filter text that a report user interactively creates in the Report Viewer when the user runs a report. The parameter is in a filter summary text box that is located in the report header. The initial value is set to the default filter.
CRM_URL
Set to the URL of the app. Use this parameter when drilling through.
CRM_Filtered[Entity]
Use in a query expression to enable data pre-filtering (through Advanced Find). You should replace [Entity] with your Actual entity name for which the report is being developed
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.
From Report Data Toolbar expands Data Sources and right-click to add new data sources. Specify the following at Data Source Properties window.
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.
Click OK
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.
Use Word templates to create standardized documents
After you create and import Office Word templates into customer engagement apps (Dynamics 365 Sales, Dynamics 365 Customer Service, Dynamics 365 Field Service, Dynamics 365 Marketing, and Dynamics 365 Project Service Automation), with one click users can generate standardized documents automatically populated with data. This feature has some special considerations you should know about to successfully create Word templates.
Warning
There is a known issue when creating templates in Word. This topic contains information on how to prevent interactions that could potentially destabilize Word. See: Important! A known issue and how to avoid it
The following are the supported versions of Word.
Area
Word Version
Creating a Word template
2013, 2016
Using a Word document generated in customer engagement apps
2010, 2013, 2016
Note
Macro-enabled Word documents (.docm) are not supported.
Follow the steps in this topic to successfully create and use Word templates in customer engagement apps.
Step 1: Create a Word template
Where you can create a template
There are three places in customer engagement apps where you can create a Word template:
From the Settings page. Go to Settings > Templates > Document Templates > New(). You'll need sufficient permissions to access to the Settings page, such as System Administrator or System Customizer.
From a record. Open a record such as an account in Sales. Go to Sales > Client_Accounts > My Active Accounts. Click an account to open it, and then click More (…) > Word Templates > Create Word Template. Templates created here are personal and available only to the user creating the template.
From a list of records. For example, go to Sales > Client_Accounts > My Active Accounts. Select a single account, and then click More (…) > Word Templates > Create Word Template.
Tip
To delete personal document templates, do the following:
Click Advanced Find ().
For Look for, select Personal Document Templates.
Click Results (!).
Select the personal document template to delete and then click Delete ().
To update templates, delete the template and then upload an updated version of that template.
After clicking Create Word Template, select an entity to filter with, and then click Word Template > Select Entity.
The relationship selection page appears.
What are 1:N, N:1, and N:N relationships?
This screen requires an understanding of your customer engagement apps data structure. Your administrator or customizer can provide information about entity relationships. For admin content, see: Entity relationships overview.
Here are some example relationships for the Account entity.
Relationship
Description
An account can have multiple contacts.
A lead, account, or contact can have multiple accounts.
An account can have multiple marketing lists.
A marketing list can have multiple accounts.
The relationships you select on this screen determine what entities and fields are available later when you define the Word template. Only select relationships you need to add data to the Word template.
Note
To ensure documents download in a timely matter, there is an upper limit of 100 for the number of related records returned for each relationship. For example, if you're exporting a template for an account, and you want to include a list of its contacts, the document will return at most 100 of the account's contacts.
Download the template
Click Download Template on the Select Entity page to create a Word file on your local computer with the exported entity included as XML data.
Important
Document template downloaded from one environment can only be used within that environment. environment to environment migration for Word or Excel templates isn't currently supported.
Step 2: Enable the Developer tab
Open the Word template file. At this point, the document appears to be blank.
To see and add customer engagement apps XML data, you need to enable the Word Developer tab.
Go to File > Options > Customize Ribbon, and then enable Developer.
Click OK.
Developer now appears in the Word ribbon.
Important! A known issue and how to avoid it
There's a known issue with customer engagement apps apps-generated Word templates and Office Word. In the next section, you'll be adding XML content control fields to the Word template.
Warning
A few things can cause Word to freeze, requiring you to use Task Manager to stop Word:
You insert a content control other than Picture or Plain Text.
You make a textual change, such as changing the capitalization or adding text, to a content control. These changes can occur through AutoCorrect as well as user edits. By default, Microsoft Word AutoCorrect capitalizes sentences. When you add a content control field, Word sees it as a new sentence and will capitalize it when focus shifts away from the field.
To prevent issues with control fields, do the following:
Only add fields as Plain Text or Picture
You use the XML Mapping Pane to add entity fields to your Word template. Be sure to only add fields as Plain Text or Picture.
Do not make any textual changes to the added content control
You can make formatting changes to content control fields, such as bolding the text, but no other textual changes, including capitalization changes.
If you experience Word freezing or performance degradation, try turning off AutoCorrect.
Turn off AutoCorrect
With the template file open in Word, go to File > Options > Proofing > AutoCorrect Options.
Deselect Capitalize first letter of sentences and Automatically use suggestions from the spelling checker.
Deselect Hyphens (--) with dash (-) on the AutoFormat and AutoFormat as You Type tabs.
Click OK.
If you followed the above recommendations, you're ready to define the Word template.
Step 3: Define the Word template
Use the XML Mapping Pane to define the Word template with entity fields.
In your Word template, click Developer > XML Mapping Pane.
The default XML schema is selected.
Select the XML schema. It will begin with "urn:microsoft-crm/document-template/".
Important
If you have frequent accidental edits that cause Word to freeze or have performance degradation, be sure to turn off the AutoCorrect options according to the section: "A known issue and how to avoid it".
Expand the entity, right-click the entity field, and then click Insert Content Control > Plain Text.
The entity field is added to the Word template.
Add additional entity fields, add descriptive labels and text, and format the document.
A completed template might look like this:
Some content control fields you entered likely have multiple lines of data. For example, accounts have more than one contact. To include all the data in your Word template, set the content control field to repeat.
Set content control fields to repeat
Put fields with repeating data in a table row.
Select the entire table row in the template.
In the XML Mapping Pane, right-click the relationship containing the content control fields, and then click Repeating.
When you use the Word template in customer engagement apps to create a document, the table will populate with multiple rows of data.
When the template has the fields and formatting you want, save it and upload it into customer engagement apps.
Step 4: Upload the Word template back into customer engagement apps
When you have your Word template built the way you want, save it so you can upload it into customer engagement apps.
Administrators can use the Settings page to upload the Word template into customer engagement apps. A template uploaded in Settings is available to all users in your organization.
For admins: Upload the Word template into customer engagement apps
Go to Settings > Templates > Document Templates.
Click Upload Template.
Drag the Word file in the dialog box or browse to the file.
Click Upload.
Non-admin users can upload a template for their own use from a list of records.
For non-admins or admins wanting to create a personal template: Upload the Word template into customer engagement apps
Open a page with a list of records, for example, the list of customer accounts in Sales.
Select a single item such as an account, click More (…) > Word Templates > Create Word Template.
Click Word Template > Upload.
Drag the Word file in the dialog box or browse to the file.
Click Upload.
Step 5: Generate a document from the Word template
To use the Word template you've created, do the following:
Open a record with information you want to create a document. For example, open a customer account record in Sales.
Click More (…) > Word Templates, and then under Word Templates select the template you created.
If the template you created is not visible, there are two possibilities:
Only templates built for the selected record type (entity) will be displayed. For example, if you open an opportunity record, you will not see a template you created with the Account entity.
You need to refresh customer engagement apps to see the template. Either refresh your browser or close and reopen customer engagement apps.
After you select your Word template, customer engagement apps create a Word document from the record you selected.
Try out the sample Word templates
There are five Word templates included with customer engagement apps.
The sample Word templates were created with a particular record type (entity). You'll only be able to apply the template to records of the same record type.
Name
Entity
Opportunity Summary
Opportunity (Sales area)
Campaign Summary
Campaign (Marketing area)
Case Summary
Case (Service area)
Invoice
Invoice (Sales area)
Account Summary
Client_Account (Sales, Service, and Marketing areas)
To apply a sample Word template
Open a record with information with the entity type that matches the sample template. For example, open a customer account record in Sales to apply the Account Summary template.
Click More (…) > Word Templates, and then under Word Templates select the sample template.
Open the newly-created Word template and give it a look.
Note
You can review but not edit templates that are included in customer engagement apps.
Additional considerations
Use Security Roles to control access to templates
Administrators can control access to Word templates with some granularity. For example, you can give salespeople Read but not Write access to a Word template.
Click Settings > Security > Security Roles.
Select a role, and then click the Business Management tab.
Select Document Template to set access for templates available to the entire organization. Select Personal Document Template for templates shared to individual users.
Click the circles to adjust the level of access.
Lists in created documents are not in the same order as records
Lists of records created from a custom template may not appear in the same order in Word documents as the order in customer engagement apps. Records are listed in the order of the time and date they were created.
). You'll need sufficient permissions to access to the Settings page, such as System Administrator or System Customizer.
).
).
