Setting up your workstation to write SQL Server Reporting Services Reports for Dynamics 365

Over the years, I have found that there seems to be a lot of confusion around the setup and tools required to create and modify Dynamics 365/CRM SQL Server Reporting Services (SSRS) reports.

There are a lot of options for analyzing and visualizing data from Dynamics 365 such as built in views, charts and dashboards, as well as Excel and PowerBI.

Since Dynamics CRM 3.0, SSRS has been one of the core options for generating reports out of the system.

A number of reports come “out of the box” and simple table and chart reports can be created using the Report Wizard tool within Dynamics 365/CRM.

Most projects still have many specific and unique reporting needs that require custom reports to be developed that are beyond the capabilities of the Report Wizard.

It is my hope that this blog posting will provide clear instructions to anyone needing to install the necessary tools to get started writing/modifying Dynamics 365 Reports!

bothans

Part of the confusion is that the documentation from Microsoft is not fully clear (surprise!).

Another contributing factor to the confusion is that you actually need a couple of different components, one of which is Visual Studio.  There are many different versions of Visual Studio available, and many get easily confused as to which version is required.  Note: Its NOT Visual Studio 2015 Professional/Enterprise!

SQL Server Data Tools

The actual version you will need is this (install FIRST):

Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2012

This is a free, lightweight (not full) Visual Studio tool from Microsoft where one can author and modify SSRS reports.  If you are using Dynamics CRM on-premise, and only want to ever write T-SQL based reports, this will be the only component you will need.

Even if you have a paid version Visual Studio installed with all the latest updates and service packs, you still need to install the SSDT 2012 edition to develop SSRS reports specific for Dynamics 365/CRM.

If you happen to be writing reports for older versions of Dynamics CRM (2013 or older), then you need to install “Business Intelligence Design Studio”.  This can be installed from SQL Server installation media.

Report Authoring Extensions

Ideally you want to write reports using FetchXML (your main option for Dynamics 365 Online, but also encouraged for on-premise).  T-SQL might be more powerful and flexible, but this is your only option for online.  Even if you are on-premise, in the future you may need to make the jump to online and your reports will migrate much smoother if they are already written in FetchXML.

Install this AFTER SSDT is installed:

Microsoft Dynamics CRM 2016 Report Authoring Extension (with SQL Server Data Tools support)

This is the add-on for SSDT that allows you to connect to Dynamics 365 Online and use FetchXML to access data.

There are versions for each Dynamics 365/CRM version, but most will work for roughly a CRM version ahead and behind.  (e.g Report Authoring for CRM 2016 will work for CRM 2015 and 365)

Many will try to install this component without installing SSDT first and run into problems, confusion and delay.

crying
Why won’t this just work?

Once you have successfully installed both components, you are ready to write your first report!

Since I am a nice guy, I will provide a few more steps to get you started.

You may start to search for “Visual Studio” or “Report Writer” on your workstations.  You actually need to launch “SQL Server Data Tools for Visual Studio 2012”

crmssrs1
SQL Server Data Tools for Visual Studio 2012

Once SSDT starts up, create a new “Report Server Project” under “Business Intelligence Templates”.  Don’t use the wizard.

crmssrs2
Report Project

The project will add a few items to the solution.  In the “Report” folder, right click and choose “Add” and then “New Item”

crmssrs3

Then choose “Report”.  Give the report a more intelligent name than “Report1.rdl” (Do as I say, not as I do)

crmssrs4

Now you have a clean palette to work with.  First you need get some raw data to start putting your report together.  On the left side, right click on “Data Sources” and choose “Add Data Source”

crmssrs5

If you have successfully installed the CRM Report Authoring Extensions, you should see the type set to “Microsoft Dynamics CRM Fetch”

Put your Dynamics 365/CRM URL in the connection string box, and put in your Office 365 credentials in the designated area.  Note that these credentials are only used to design and build the report.  Eventually when you load it into CRM, the report will be run under the credentials (and security context) of the end user.   This means that a user won’t be able to write a report to retrieve private or sensitive data.

crmssrs6

Now right click on “Datasets” to create a dataset from Dynamics CRM.

Choose to use a “dataset embedded in my report”, choose the datasource name your created above, and enter in the FetchXML query you want to base your report on.  FetchXML can be generated from Advanced Find within CRM or using various tools like the XrmToolBox.

crmssrs7
FetchXML

Assuming you entered your credentials correctly, you should see the fields from your dataset on the left.  You can then drag and drop the fields into tables, sections, tablixes, etc. and manipulate/filter layouts/fonts/formulas and whatever other tools SSRS provides. General Report writing is beyond the scope of this posting, but with a little practice, one can build and design very complex and useful reports.

crmssrs8

The report itself can be previewed within the SSDT designer.  Once you are satisfied, the actual RDL file can be directly uploaded into Dynamics 365/CRM.

crmssrs9
My Dynamics 365 Report! Yay!

You now should have the steps to successfully setup your workstation for CRM Report Development and have the basics to get started.  Soon you will victorious in creating exciting and useful reports!

celebration
SSRS Report Hero!

I hope you found this helpful!

Cheers
Nick

3 thoughts on “Setting up your workstation to write SQL Server Reporting Services Reports for Dynamics 365

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s