Developing And Implementing SSRS Reports In Dynamics CRM

Introduction 

 
Reporting is an essential part of any tool across the globe, as it gives you the summarized information as per the business requirement. Every tool comes up with an inbuilt module for developing reports and so does Dynamics CRM, but the inbuilt tools have their own limitations and they lack certain features, which makes it tough for a developer to implement their business requirements.
 
Developing and Implementing SSRS Reports in Dynamics CRM
 
And so developers need to go for implementing these business requirements through reporting specific tools and one of the recommended solutions is SSRS.
 
To develop and implement the report, the following is needed:
  • Data Source – The data set which will be used to develop the logic and prepare a report
  • Data Connector – Now since we have a data source, and to connect the same Data Connector is needed, which empowers us to pull the data
  • User Credentials – Since initially report is developed outside the tool, so we need to have valid credentials which can be used to connect to the data source through the data connector
  • Business Requirement – The logic which needs to be implemented
Tools
  • Visual Studio 2015 or Lower – Data Connector for Dynamics CRM is supported in Visual Studio 2012. I tried using it with higher versions, but the connector will give you some random errors.
  • Visual Studio should have SSDT services installed on it.
Steps to create your first report:
  • Open Visual Studio 2012 and create your project:

    Developing and Implementing SSRS Reports in Dynamics CRM
  • Connector was chosen for Microsoft Dynamics 365 Fetch connector, as here we will be entering the required information of User Credentials.

    This will be create a valid Data Source for you, which we will be utilized in the report to fetch the data.

    Developing and Implementing SSRS Reports in Dynamics CRM
  • In Visual Studio, ensure that the Solution Explorer Window is enabled and in there click on “Add Reports” to add a new report to the project

    Developing and Implementing SSRS Reports in Dynamics CRM
  • Once you click on the new report, it will give you a user-driven pop-up window where you need to select the Data Source, which we have already cleared in the previous set.
  • Post selection of Data Source, we need to query by which we wish to extract the data from Dynamics CRM, so in case of Online instance of Dynamics CRM – only the fetchXML based query is supported which you may create either through the tools in XrmServiceToolkit or you may export the same through Advance find.
  • Once the Query is inserted, the user-driven pop-up window will assist you to create the report, placing the data in the required format and now you will be able to see the report in a format.
  • Using your skills of SSRS or by taking references, you can definitely groom it and make it more user friendly, including adding of filters: logo and features.
  • Once the report has been created, you need to upload the same in Dynamics CRM and while creating the same, select the exiting report. This way you will be able to upload the rdl file that has been created in SSRS
Issues faced while creating SSRS Reports:
  • Unable to connect to Dynamics CRM through Visual Studio.

    • Many users report, that they get an error even after adding the correct credentials to the connector, and this reason for the same is TSL1.2 which has been introduced in Dynamics CRM. The connector still by default uses TSL1.0 to connect to Dynamics CRM so we need to update the settings from TSL1.0 to TSL1.2 using the fiddler tool.

      Developing and Implementing SSRS Reports in Dynamics CRM

  • User is enabled for multiple organizations

    • Usually, we have multiple organizations of Dynamics CRM configured for the user, as we need to develop and test, then we put the report over the live instance of Dynamics CRM and so since no specific organization is mentioned so during development of report we usually get popup multiple time stating to select the organization
    • A simple trick to escape from the pop up is to append the connection string with the name of the instance with a separator as a semicolon

      Developing and Implementing SSRS Reports in Dynamics CRM

      In the above screenshot, I have appended the name of an organization
  • Issue faced while uploading the report to Dynamics CRM

    • Reports which are developed and implemented for Dynamics CRM does not support shared data source, and so the developer needs to create a separate data source for every report which needs to be developed and implemented.
Link to download the Connector.