Let me share my journey of building SSRS RDL Reports for Microsoft Dynamics 365 CRM using Visual Studio 2015, with the Report Authoring Extension to pull data through FetchXML queries!
Backstory…
So recently I found myself working on a Dynamics 365 Reporting project module, where I had to build a set of Reports that are to be executed in the cloud on demand. The data for those reports had to be pulled from the Dynamics 365 cloud, using FetchXML queries, process it through a set of business logic and then display to the user in a nice looking Report Layout.
This can be nicely done using a Microsoft SQL Server Reporting Services Project where it will allow you to build the RDL Report files that you need to upload to the Dynamics 365 environment, allowing the reports to be executed directly from there.
So let me share my journey step by step… 😉
The Set up!
In order to pull data from Microsoft Dynamics 365 using FetchXML queries, we need to have Visual Studio 2015 installed with Dynamics 365 Report Authoring Extension installed. This is only set up that’s properly functioning. which I released after a series of trial and error try out!
So here is the set up I used during my journey,
- Install Visual Studio 2015 Community Edition
- Install SQL Sever Data Tools (version 17.4)
- Install Microsoft Dynamics 365 Report Authoring Extension (version 9.0)
I have posted a blog article of the step by step installation of this whole set up previously in detail, so you might want to give it a read before proceeding…
Let's set up Microsoft Dynamics 365 Fetch Extension in VS2015!
https://theconfuzedsourcecode.wordpress.com/2020/02/11/lets-set-up-microsoft-dynamics-365-fetch-extension-in-vs2015/
Now that we have set up the dev tools and environment required, let’s look into the best practices of maintain our SSRS Report Project solution.
Project Structure…
It’s best to maintain a single Project Solution for all your Reports, even if you’ve got multiple types of Reports under different categories or modules, you could maintain them in a tree folder structure.
Each Report must maintain its own Data Source, which all the Datasets will use to execute their Queries to pull data from the Dynamics 365 CRM. I found having a shared Data Sources or a Data Sets some times troublesome between different Reports.
Alright then lets get started by creating the Project Solution for our Reports.
Create the Report Project!
Let’s open up Visual Studio 2015, Create New Project -> Templates -> Business Intelligence -> Reporting Services and select Reporting Server Project as follows…
Once the Project is created then its time to create the Reports…
Create the Report!
In the Report Data view, right click on the Reports folder -> Add New Item -> select Report -> give an appropriate name to your new Report and click Add..
Once the new Report is created, it will open up Report Data View, if not visible on your Visual Studio 2015 solution, go to View -> Report Data or hit the short cut keys, Ctrl + Alt + D on your keyboard! 😉
Add the Data Source!
This is where we define the connectivity to our Microsoft Dynamics 365 CRM, allowing our Datasets to pull data from, to be used in the Reports.
In the Report Data view, Right click on Data Sources -> Add New Data Source -> Set up your Data Source with your Dynamics 365 hosted link with the Credentials as shown below…
Once that’s done, next we need to set up our Datasets…
Adding the Dataset!
You could add multiple Datasets as you wish according to your requirement, which will all be pulling data through the Data Source that you created above.
In the Report Data view, Right click on Datasets -> Add New Dataset
Give an appropriate name to your Dataset, and select the “Use a dataset embedded in my report” option, which will allow you to select the Data Source that you created above.
Insert the FetchXML query that you intend to be executing though this dataset to pull data from Dynamics 365, which will populate the data that you require to populate in the Report at the end. 😀
FetchXML Queries, everywhere!
This is something worth the notice, regarding the FetchXML queries, you need to make sure you build your queries properly, error free, as well as performance in mind. The most complex your Report gets, the more FetchXML queries and datasets that you will have to set up.
One of my colleagues, Lohit introduced the team to this awesome tool called XrmToolBox https://www.xrmtoolbox.com/, that you can use to build your queries. This tool allows you to install plugins in it, specially one specific one, FetchXML Builder plugin. This will allows you to easily build, configure and debug your FetchXML queries, against a Dynamics 365 data source.
It’s got a nice FetchXML query editor with all the functionality available in each keyword, allowing you to execute those queries that you build in real time and see the actual data results, experiment, making it easier to debug, and tweaking performance. So you might wanna So you might wanna give it a try and save time! 😉
Dataset Data Fields!
Once you add the FetchXML Query to your Dataset properties, you need to make sure the query is populating the data accordingly. You can check this by clicking on the Fields tab on the left pane in the window.
This will most likely prompt you to login to Dynamics 365 data source that you created before, once that’s done, and if the FetchXML properly written, then you should see all your Data Fields in this Dataset populated as below…
Or you can even see the dataset fields populated once you save the new dataset, in the Report Data view as follows…
Now we’re ready to design our Report…. 😉
Design the Report Layout!
Make sure you have selected the Design mode of your Report, the top region of the Designer represents the Parameters display area, and the bottom white box is the Report Layout area that we can build our Report UI.
So open up the Toolbox Pane in your Visual Studio, and you will see all the UI Elements available for you to design your RDL Report.
Let’s drag and drop a Textfield element, that will represent the Title of the Report and a Data Table element that will populate the data of the Report as shown below…
Now we can populate data on that Report Layout
Data on Report Layout!
How to attach our Data Fields from the Dataset we created? it’s actually quite easy, just drag and drop the Dataset Fields on to each column of the Data Table element on the designer that we added earlier…
You wanna add more columns, please go ahead right click on the last column -> Insert Column -> Right will do it! 😀
It’s really easy to design the Report Layout and set up the data fields that we want to display!
Report Data Formatting!
One of the most fun things that I encountered was implementing custom data formatting in the Reports that I built. You can easily add all kinds formatting for the data fields with the use of Expressions that’s cooked into SSRS Reports. I’ve written a comprehensive blog post about this, which you can take a look for further learning.
Data formatting in SSRS RDL Reports with Field Expressions…
https://theconfuzedsourcecode.wordpress.com/2020/02/15/data-formatting-in-ssrs-rdl-reports-with-field-expressions/
This will help you format and manipulate the data you get from your datasets to display on the report according to your business logic requirement.
Report Parameters!
You can add data Parameters to your Reports, which will allow you to re-populate the data on the go, for easy analysis of Report results. Check out my comprehensive blog post about this, which will walk you through a step by step guide how to add Parameters to your Reports..
Let's Parameterize our SSRS RDL Reports!
https://theconfuzedsourcecode.wordpress.com/2020/02/17/lets-parameterize-our-ssrs-rdl-reports/
Adding Parameters will include you having to create multiple datasets to populate the data for the Parameters themselves, then based on those Parameter values User selects during execution, Report data will be automatically filtered and repopulated.
Test and Repeat!
You can use the Preview tab in the Report Design view to test your Report, how they would render in Dynamics 365. Although for myself I had trouble testing these reports locally, so each time I had to upload to Dynamics 365 and test it directly from there.
You can also Build the VS project solution locally, to see if everything is set up properly, and no errors are popping out. So make sure to test the Reports locally if possible.
Once you’ve done building your Report in Visual Studio, grab your RDL files from the project solution, and then its time to elevate them up to the Microsoft Dynamics 365 cloud! 😀
Upload Report to Dynamics 365!
Now it’s time to upload our Report to Microsoft Dynamics 365, which is actually quite easy, click and click you’re done! 😉 Just open up your Dynamics 365 endpoint -> go to the Reports section -> click on the New button at the top…
This will bring up the New Report window, where you will have all the options you need to set up your new Report in Dynamics 365. Since we’re uploading a file, in the Report Type, use the Existing File option in the. Then you can click on the Choose file button and attach your RDL Report file.
Make sure to give a proper Name and Description for your Report, although one thing you need to keep in mind is that, you need to make sure to set up the Related Record Types field, where you need to set up all the Entities that you’re pulling in data from for your Report.
This is governed by the FetchXML query that you built to pull data from Dynamics, whatever the linking Entities that you use, you need to mention them in this field as follows…
Once you’ve set up everything in the wizard, click on Save button at the top left corner, and you will see your new Report appeared in the Reports section of your Dynamics 365!
Just click on that Report, and it will open itself up on a new browser tab and execute itself, populating the data like a charm! 😉
Updating Reports in Dynamics 365!
This is actually quite easy and similar to uploading your Report, simply select your Report in the list of Reports available and click Edit button.
This will open up the Report Edit window, where you can upload your New RDL Report file, or edit any other data in your Report set up.
Once you’re done, just Save and exit, your changes will reflect in the Dynamics 365!
Conclusion
You can easily build powerful Reports for Microsoft Dynamics 365 using SQL Server Reporting Tools. You need to use Visual Studio 2015 installed with Dynamics 365 Report Authoring Extension, on top of SQL Server Data tools set up as your dev environment.
Simply put, building your Reports follows these steps…
- Create the Report
- Add Dynamics 365 Data Source
- Add FetchXML Query based Dataset
- Design Report Layout
- Add Data Formatting with Expressions
- Add Parameters for the Report
- Test your Reports before Publishing
Your FetchXML queries plays a huge role in pulling the data from Dynamics 365, so you need to make sure your queries are properly set up, error free, with perfomance impact in mind. Use a tool like XrmToolbox with FetchXML Builder plugin could make it easier.
Once you’re done with building your Reports, you can directly upload them to Dynamics 365, also make sure to set up details and the data dependency entities of that Report. Editing and Updating those reports are quite the same process as well.
Hope this little step by step guide helped someone, somewhere out there… happy building my fellow devs! 😀
Share the love! 😀 Cheers!
ÇøŋfuzëÐ SøurcëÇødë 