This is how I built SSRS RDL Reports for Microsoft Dynamics 365…

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!

Let’s Parameterize our SSRS RDL Reports!

Let me show you, how to add parameters to SSRS RDL Reports, while populating the reporting data based on those parameters dynamically!

You can add any number and any type of parameters into your Reports, so that during the execution users can easy load different sets of data into the Reports for analysis.

Backstory…

Since my last few posts I’ve been revealing to you guys that I’ve been working with Microsoft Dynamics 365, where I built those Reports using SQL Server Reporting Services RDL Reports, where those RDL files are uploaded to the Dynamics 365, allowing use to execute them directly from there.

In those Reports, I’ve added a bunch of Parameters where they can load different sets of data into the Reports through those reports for different analytics of the data. Yeah sounds pretty cool eh! 😉 So I thought of sharing this experience with you all, how I added the feature of passing Parameters into my RDL Reports for Microsoft Dynamics 365!

the Set up!

So my set up was on Visual Studio 2015 with SSRS in order to build Reports using fetchXML based data sources that pulls data from Microsoft Dynamics 365 cloud, which was a helpless requirement specifically with the Report Authoring Extension. Let’s set up Microsoft Dynamics 365 Fetch Extension in VS2015!

Although I believe the process of adding parameters any SSRS RDL Report is the same, so you should be able to follow along despite your targeted deployment source. 🙂

Parameters for Reports!

So you need to add Parameters for your RDL Report eh, which means you’ll be passing a bunch of data values into the filtering pipeline of the Report and repopulate its results based on it.

So how do we add those Parameters? We need to build each one of those Parameters in our SSRS project. This is where you configure what is the type of the parameter, whether its Text, Date, Boolean, and so on, you can even define your own set of data values available for this parameter, which the user will choose from during execution. It also facilitates you to define the data source in which those values will be loaded from.

Then you add those Parameters into the Report, by adding a dependency between them. Once they are set you need to determine the refresh cycle for re-loading the data based on the new parameters given. 😉

Now as you can see there’s all kinds of cool stuff you can do with it, even though the set up seem a bit complicated, its actually quite straight forward! 😀

We need to get started by defining the Parameters in our Report! So go ahead and open up your RDL Report with Design View…

Let’s get Started!

In order to add any Parameter, you need to first define it in your project, so first make sure you have the “Report Data” View is opened up.

To open up the 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! 😉

Let’s dive right into it by, Right click on the Parameters -> Add Parameters

This will open up the dialog to create a new Parameter to be added to your Report…

Now this let’s you define your Parameter with the properties you want to set for, starting from the values available in it, to the refresh action up on the user’s selection. As you can see I have here “ReportParameter1” which is labeled as “Report Parameter 1 :” for the display fields in the Report. You can choose the type of data this parameter represents and the type of inputs it accepts from the user.

So let’s go over each section you need to consider when you’re creating a Parameter.

Parameter: Available Values!

This allows you to determine the set of values available for your Parameter, that is presented to the user during execution. You can also leave this in the “None” option which will let the user type in the value they prefer.

If you want to load a specific list of values in to this parameter, then you can select “Specify values”, which will allow you to build the list of data values with their representing label value as shown before. This will provide the user with a drop down list of data using this source.

If you’d like to populate the data for this Parameter using a data query source that you can build, then feel free to select the “Get values from a query” option, in which case the list of data will be loaded from this dataset source based on the query defined in it, and be presented to the user accordingly with the Labeled values.

I will get in to this query based data loading into the Parameter, later in this article 😉

Then the next section you need to consider is the Default Values…

Parameter: Default Values!

This is where you define the default value that’s loaded into your Parameter during the execution, in which case you could also leave it with the “No default value” option selection if you don’t want to have any pre-selected values for loading of the data.

If you select “Specify values” option here, it will allow you to choose the default value to be loaded from the list of data that you defined in the “Available Values” section as I’ve shown before.

Now the same way you could populate your default values based on Expressions as well, in a situation like setting the default value for a Date type parameter can easily be done as below…

But if you’ve defined a data source based on a query, then you can select the data from that source to be loaded here as well from the option “Get values from a query“. You need to make sure to select the same dataset that you selected in “Available Values” section here as well.

Next the final section you need to consider when building your Parameter, “Advanced” section!

Parameter: Advanced!

This is where you define the refresh action based on the selection of a new value for the Parameter from the user during execution. Simply put let report refresh its data every time a user selects a new parameter value, or let the user refresh manually to see the results once change the selection.

Now that’s quite straight forward eh! So those are key sections you need to focus on when building any Parameter for your RDL Report.

Next let’s deep a little more into loading values from query into your Parameter… 😀

Let’s get Values from a Query!

As I’ve shown earlier you can load data into your Parameter from a dataset source based on a query. Let me show you how easy it is to set this up! 😉

Let’s get started by creating the dataset that will handle loading the data from the data source that we target. Right click on the “Datasets” section -> Add Dataset… option.

Create a new dataset using an existing shared dataset, or using an existing data source by accessing the data through the preferred query type.

As you can see below here in my example, I’m using a fetchXML query to be execute through my TestDataSource which is a MS Dynamics 365 data source. So you need to build your own query based on your data source here.

Once the dataset is defined, you need to make sure the Fields are properly loaded as expected. You can check this in the dataset property window itself and even after click “OK” and save it under the dataset you just created as shown below.

Now that we have set up our Dataset Source, then we can map this into our Parameter to load these data during the execution.

First thing, you need to make sure to set up the type of the Parameter, accordingly to the type of values that you’re loading from the dataset. In this example, “Text” would preferred.

So now, you can set up the Available Values with the “Get values from a query” option. You just have to select the dataset that you created as shown below.

Here you got Value field, which refers to the actual value that’ll be passed into the Report up on selection from the User. And then the Label field, that represents the display value of your dataset items, as you may have noticed in the previous steps.

Finally you set up the Default Values with the same set up, which will load the first item provided by the dataset query during execution into the Parameter, which will then trigger the loading of the Report data based on that.

Here make sure to set the same Value field selection as you set up on Available Value sections. 😉

Once the set up is done, you may see your Parameters now visible in the Design View of your RDL Report as shown below… 😀

You still need to set up the those Parameters as dependencies for your Report, which we will discuss next…

Let’s tie up Parameters with Report!

Here’s the final step fellas! 😀 Let’s open up the properties dialog of the dataset that you’re using to populate your Report,

Go to the Parameters section, and add the Parameters you just created in the previous step!

Yep it’s that simple! 😀 as you can see I have added the ReportParameter1 that I have created here, where as Parameter Name is set up as it is, and then the Parameter Value field is set up as [@ReportParameter1], which is how you address the Parameters you have defined in the Report. Subsequently you can add any number of Parameters following the same.

Notice how each Parameter set up has the Expression button next to it? that is to allow you to modify the value coming in from the Parameter using Expressions, if you wish to! 😉

Next you have to set up the Filter based on the Parameters that you just registered, so go to the Filters section, and add the filtering logic you require based on those parameter value for the repopulating of the Report data.

Right here you need to pay extra attention to details, where you need to consider which field in your data set you want to set up the parameters against for filtering, and what type of operator you need for this logic. You need to make sure both values you’re comparing are of the same type, and also you can modify their values based on Expressions before they’re applied for the filter.

As you can see above I’m using the DataField1 text field’s value to check whether its equal to the value given in the ReportParameter1 for each data row.

If I’m to show another example, take a look at below, I have added a second parameter which is type of Date/Time, which then compares itself with the value of DataField2 date/time field in the report dataset.

Now both those filters will act as an AND junction and filter out the data based on the combined logic. Save it up, you’re all set! 😀

Well… that’s how you tie up your Parameters with the Report’s dataset! 😉

You can now run this Report and see the beauty in action! 😉

Conclusion

Microsoft SQL Server Reporting Services – RDL Reports allows you to add Parameters to them, so that you could allow Users to filter the data according to different business logic for ease of analysis. You can add many different types of Parameters which could be used to filter the report data in kinds of different ways, using Expressions.

• Define your Parameters in the RDL Report
• Set up the available values for your Parameters
  • load data into Parameters based on specific list of values, or
  • load data into Parameters from a query based data set
• Set up the Refresh action event
• Add them as dependencies for your Report
• Set up Report data filtering based on Parameters

This will allowing the Report data to be filtered dynamically based on the Parameter values that are passed in by the selections from the User. Well it is quite straight forward as you can see… 😉

Hope this little step by step guide helped someone, somewhere in the world… happy building my fellow devs! 😀

Share the love! 😀 Cheers!

Data formatting in SSRS RDL Reports with Field Expressions…

Let me share some exciting ways of formatting data with Field Expressions in SSRS RDL based Reports that I discovered during my Microsoft Dynamics 365 adventures! 😉

Using the Expression Builder in Visual Studio Report Designer, you can add all kinds of interesting ways of formatting your data for RDL Report projects. You can set custom expressions to these data fields to format and manipulate them easily.

This is where the magic happens, as you can see there’s a whole collection of library for formatting data with Expressions…

Backstory…

So I happened to be embarked on a Microsoft Dynamics 365 CRM data Reporting project, where I was tasked built a bunch of SSRS RDL based reports, that pulls data from CRM and format it based on the custom business logic before displaying to the user.

This involved writing a whole bunch of custom expressions on each field in those reports, where I came across all these interesting ways of formatting and manipulating data.

So here I am sharing some interesting field expression formatting that I discovered during that adventure… 😉

Formatting Fields with Expressions!

Opening up the Expression editor dialog you can see the long list of available syntaxes with their respective functionality description. It gives a good head start for whatever the function you’re looking to implement to format your data.

Alright then let me share some awesome stuff you can do with these data formatting functions available through expressions… 😀

– Lower case and Upper case…

=UCase(Fields!mytestfield.Value))

or for lower case,

=LCase(Fields!mytestfield.Value))

– Multiple fields into a Single field…

= Fields!mytestfield1.Value + " - " + Fields!mytestfield2.Value

You can pull data from multiple fields into a single field, and concatenate those field values using “+” operator, even add string chars in the middle as you wish.

– Check for null…

ISNOTHING(Fields!mytestfield.Value)

This will return a True or False boolean value based the value in the field.

– If conditions…

=IIf(ISNOTHING(Fields!mytestfield.Value),
" - ",
"DC-" + UCase(Fields!mytestfield.Value)))

As shown above this will check if “mytestfield” no value, then return ” – “, otherwise return the Upper-cased, custom value of “mytestfield” as “DC-XXXXX”…

– Switch statements…

=Switch(
Fields!mytestfield.Value = "1", "Monkey",
Fields!mytestfield.Value = "2", "Rabbit",
Fields!mytestfield.Value = "3", "Penguin")

Based on the value present in “mytestfield”, we return the response value.

– Text Trimming…

=RTrim(UCase(Fields!mytestfield.Value))

No trailing spaces (RTrim) at the end or similarly no leading spaces (LTrim) in the beginning of the value, or for both function in the same time use (Trim).

– To Date object…

=CDate(Fields!mytestfield.Value)

This will return a date object from the value present in “mytestfield”…

– Formatting a value…

=Format(CDate(Fields!mytestfield.Value), "dd/MM/yyyy")

This formats the value accordingly to the given date format, similarly this applies to any type..

– Format and Concat multiple fields into a single value…

= Format(Fields!mytestfield1.Value, "yy") + 
Format(Fields!mytestfield2.Value, "MM") + 
Format(Fields!mytestfield3.Value, "dd")

This format those different field values and merge them into a single field…

– Yesterday, Today, or Tomorrow…

=DateAdd("d",-1,Today())
=Today()
=DateAdd("d",1,Today())

First expression, gets the Yesterday’s date, second get the Today’s date, third expression gets the Tomorrow’s date…

You can also format is similarly,

=Format((DateAdd("d",-1,Today())), "dd/MM/yyyy")

Since it returns a date object no need to convert the value before formatting…

– Convert UTC date time object to local time…

=Format(
System.TimeZone.CurrentTimeZone.
ToLocalTime(Fields!mytesttimefield.Value), "hh:mm tt")

Here “mytesttimefield” carries a date time object with UTC time, and we’re now converting that to the local system time, whichever the timezone set up in the client environment…

– Extract Date object segments…

=Year(CDate(Fields!mytestfield.Value))
=Month(CDate(Fields!mytestfield.Value))
=Day(CDate(Fields!mytestfield.Value))

Three options, extract Year, Month and Date values from a given Date object…

– Build a Date object…

=DateSerial("2020", "06", "18")

Now that builds a date object by year, month, and date segments. If I’m to spice it up…

=Format(
   DateSerial(
   Year(CDate(Fields!mytestfield.Value)),
   Month(CDate(Fields!mytestfield.Value)),
   "1").AddMonths(1).AddDays(-1),
"dd/MM/yyyy")

You can also add extra values after build the date object as shown there.

Well that’s it for now… 😀

Conclusion

It’s actually quite fun to work with formatting or manipulating data using Expressions in SSRS RDL Reports, while the Expression editor window follows a great editing tool with descriptions of each function available.

There’s many awesome functions available in the Expression editor to format our data fields before presentation to the user during execution. You could build all kinds of custom formatted data values even by merging multiple fields. All this is quite straight forward and easy to use.

Share the love! 😀 Cheers!

Let’s set up Microsoft Dynamics 365 Fetch Extension in VS2015!

Let’s install Microsoft Dynamics 365 Fetch extension in Visual Studio 2015 for SQL Server Reporting Services, on Windows 10, out here in the year of 2020!

This is also known as Microsoft Dynamics 365 Report Authoring Extension, which is used for building data sources, for Reporting Projects, where it helps you establish the connection to the Dynamics 365 instance you require.

Backstory…

So recently I found myself working on a Dynamics 365 Reporting project module, where I had to pull data from the Dynamics 365 cloud, using a FetchXML query, process it through a set of business logic and then display to the user in a nice looking Report Layout.

This can nicely be done in a Report Server project in Visual Studio, but you need to have both the SQL Server Data Tools and Microsoft Dynamics 365 Report Authoring Extension (Microsoft Dynamics 365 Fetch Extension) set up installed in your Visual Studio.

But this whole environment set up is very much tricky these days, due to incompatibilities with latest versions of Visual Studio. So I had to go through a long roller coaster ride to figure this out, trying out different versions of the extensions and Visual Studio to get it to work.

So to save others from this painful roller coaster ride in a year like 2020, I’m documenting this process step by step for my fellow devs… 😉

Dynamics 365 Fetch Extension!

As I mentioned before, this also known as Microsoft Dynamics 365 Report Authoring Extension, is required for creating Data Sources in Report Server Projects, which is absolutely required for building Report Layout (RDL layout report). Since you need to define a valid data source before hand creating any Reports in your project.

Only for Visual Studio 2015!

The unfortunate situation is that this extension is only supported in Visual Studio 2015, quite surprisingly also there’s no support for this extension in any VS version beyond that.

Although there are some indirect hack ways to install this on VS 2017, it still doesn’t justify having to downgrade your whole Visual Studio Setup. So we might as well do a clean installation of Visual Studio 2015, and install this extension on top of it.

The Set up?!

So assuming you’re currently on latest Visual Studio version probably VS 2019 or beyond, the best choice would be to create a Virtual Machine on your Windows, and do a clean VS 2015 installation there and proceed with the extension set up.

If you don’t have resources to create a separate VM on your Windows, you could also resort to doing a fresh installation of VS 2015 in your dev environment, side by side with VS 2019. I’ve personally tried this method, since I was running Windows on Parallels in an Apple iMac.

So either choice is up to you, then let me get into the step by step walk through of this installation set up…

Let’s begin with VS 2015!

So let’s first go ahead with downloading VS 2015 from Microsoft Visual Studio older versions archive from the following link: https://visualstudio.microsoft.com/vs/older-downloads/

Here you’ll probably be asked to login, and directed to VS Subscriptions page, where you should be able to search for “Visual Studio 2015”

Select “Visual Studio 2015 Update 3” option, and web installer download will begin. Once the download completes, run the set up…

Once the installer loads up, select the “Custom” option,

This is to select only the “Microsoft SQL Server Data Tools” option only, since that’s our primary requirement here…

Once you’re done with selection, proceed with the installation…

Once the installation finishes, we need to next download the SQL Server Data Tools version that we require for this set up…

Setting up SSDT 17.4!

So let’s go to this link: https://docs.microsoft.com/en-us/sql/ssdt/previous-releases-of-sql-server-data-tools-ssdt-and-ssdt-bi?view=sql-server-ver15 and download SSDT for VS2015 17.4

Launch the installer and make sure to select the following options…

And let the installation process complete…

Now the SSDT for VS2015 installation finishes, next we need to download the Microsoft Dynamics 365 Fetch Extension…

Setting up Dynamics 365 Report Authoring Extension – Version 9.0!

In this step we go to this link, https://www.microsoft.com/en-us/download/details.aspx?id=56973 and download Dynamics 365, version 9.0 Report Authoring Extension (with SQL Server Data Tools support)

This will download the CRM9.0-Bids-ENU-i386.exe file, up on running it will ask you to choose a folder to extract itself. So select the current folder path and create a new folder for the set up. Once the extraction completes, the set up will begin itself.

Select the recommended option and click Next…

The next step will check for the required components and prompt you to automatically install them as follows…

Click the “Install” button and wait for it to complete.

Then click next, which will run a quick system check.

Once that’s done, click Next, which will begin the installation as follows…

Let it complete, and you should see the following up on success! 😀

Now the final step is done, let’s try this out!

Let’s try it out!

So to begin, open up your Visual Studio 2015, and select Business Intelligence -> Reporting Services -> Report Server Project, create a new project here. Or if you already had a Reporting Project created before, then open that up…

Let’s add a new Report to our project…

The new Report will be added and it will open up the Data Source window as follows, where you can see the Microsoft Dynamics 365 Fetch option is now listed up.

So go ahead, add your Dynamics 365 source and set up your Report as you wish!

Yay Success! Well that wasn’t so bad was it eh! 😉

Conclusion!

Well I’m not sure this actually requires a conclusion though lol 😀 Although I still can’t figure out why Microsoft Dynamics 365 Report Authoring Extension, is not supported in any VS version beyond VS 2015.

So keep in mind the extension for Microsoft Dynamics 365 Fetch option in your Data Sources list, which added by Dynamics 365 Report Authoring Extension – Version 9.0 is only available via the set up of SQL Server Data Tools version 17.4 with Visual Studio 2015.

Anyhow I hope this step by step guide saved someone the time required to figure out this set up, by trial and error. 😉

Share the love! 😀 Cheers!