MS CRM

https://powerdynamixx.blogspot.com/2023/05/ssrs-report-from-scratch-for-dynamics.html

SQL Server Reporting Services (SSRS) is a powerful reporting tool developed by Microsoft. It is a server-based solution that allows users to create, deploy, and manage reports. SSRS can be used to create reports from various data sources, including SQL Server, Oracle, and Excel.

Pre-Requisites for creating SSRS report:

Visual Studio 2015 or higher. Download from here
Net Framework 4.5.2 and above.
Report Authoring Extension. Download from here
Microsoft SQL Server Reporting Services. Download from here

To create an SSRS report, follow these steps.

Launch SQL Server Data Tools (SSDT) and create a new SQL Server Reporting Services project.
Create a new report using the Report Wizard, or design the report from scratch using the Report Designer.
Define the data source for the report by specifying the connection string and the credentials.
Define the dataset for the report by writing a SQL query or selecting an existing stored procedure.
Design the report layout by adding report items such as tables, matrices, charts, and images.
Customize the report properties such as page size, orientation, margins, and header/footer.
Apply formatting to the report items such as font, color, alignment, and borders.
Group and sort the data in the report by adding groupings and sort expressions.
Add calculated fields to the report by writing expressions using the report functions.
Preview the report to check for errors and formatting issues.
Deploy the report to the report server by specifying the target folder and settings.
Configure the report server security by setting up roles and permissions.
Schedule the report for automatic delivery by creating a subscription and specifying the recipients.

By following these steps, you can create an SSRS report that meets your specific requirements and provides valuable insights into your data.

Environment Setup: I used Visual Studio 2019 for creating the report. However, you can use the 2015/17/19 versions as well. Make sure that the SQL Server Data Tools are installed. We also need to install the Dynamics 365 Report Authoring Extension. You can download it from below link. The environment for which I am creating the report is 9+

Download SQL Server Data Tools (SSDT) from here

Download Report Authoring Extension from here

Scroll down to see how to install SQL Server Data Tools (SSDT) and Report Authoring Extension

Step 1. Open SQL Server Data Tools

Step 2. Click on Files, go to New, and then Project and create a new Project.

Step 3. Click on "Reporting Services" on the left pane and select "Report Server Project". Give the name and click on the Ok button to create the report.

Note- .Net Framework should be 4.5.2 or above as shown below.

Step 4. SSRS report files should have been created and you can see them under solution explorer as shown below.

Right Click on the Report folder and click on Add and New Item

Step 5. Click on Report Project on the left pane and select Report from the options and give the report name. Click on Add button

Step 6. Now a blank report is created and you can see the components like Data Sources, Datasets, etc under Report Data on the left pane. We need to add data sources and datasets in our blank report.

Step 7. Right-click on Data Source and click on Add Data Source

Step 8. We need to give a connection string in the data source so go to the CRM and copy the URL as shown below.

Step 9. In the data source, give the name, Type should be "Microsoft Dynamics 365 Fetch" in the embedded connection, and paste the link in the connection string which we copied in the above step. Click on the Ok button

Step 10. We have added the data source to our report and now we need to add the dataset to our blank report.

Right-click on Datasets and click on Add Dataset

Step 11. Do the followings for dataset details

Give the Dataset name

and select the "use a dataset embedded in my report" option

Select the data source which we created in Step 9

Query type - Text

Query- Here we need to provide the fetch XML code from CRM for the report. Follow steps 12 -14 to get the query.

Step 12. Go to CRM, click on Accounts and click on Advance Find, select your entity (Account), and click on the Continue button

Note - I want to create a report for the Account so I'll get a query for the Account entity. You can select any entity according to your requirement.

Step 13. Click on the "Download FetchXML" button to download the query. Save the file on your system.

Note- We can add conditions here like if you want to show only active accounts in the report or want to have any particular owners then you can do that by clicking on Add button.

Step 14. Open the FetchXML file and copy the query.

Step 15. Paste the copied query in the Query box and click on the Ok button.

Step 16. 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.

Note- If you have more environments then click on the "Display list of available organizations" checkbox and select your env in the next step

Step 17. Select your environment and click on Login

Step 18. Now, you can see the data set is successfully created in the form of tables as shown below.

We need to create the table for the report and insert data. Click on Toolbox from the left pane.

Step 19. Select “Table” and drag it into the designer part as highlighted below.

Step 20. You can add multiple rows and columns by right-clicking on the table and selecting Insert Column. Drag the fields from the dataset and put them inside the table.

Step 21. You can add the data to the table by right-clicking on the row and selecting “Expressions” as done in the above step for inserting the column. Here, you can give your field name.

Note- When you drag and drop the field from dataset to table then by default the field value will be mapped as shown in step 20.

Step 22. Right-click on the Table and click on Tablix Properties

Step 23. Five names and select the data set which was created above then click on Ok.

Step 24. You can see the preview of your report in the preview section near Design, just click on Preview and you should be able to see the report.

Note - You may be required to give login credentials again here.

Step 25. Our report is created, let's build the report. Click on Build and then build the solution

Step 26. If everything is correct in the report then the build should be successful like below.

Step 27. We need to get the file location of our report. Right-click on the solution and slick on Open Folder in File Explorer as shown below.

Step 28. Open the SSRS report which is created.

Step 29. Open the bin folder and then open the debug folder.

Step 30. Copy the path.

Step 31. Now we need to create the report.

Step 32. Select "Existing File" from the report type, give the report name, and click on choose the file to upload the report which we have created and copied the path in step 30.

Step 33. After clicking on Choose file, paste the copied path (step 30) and select the report and click Open.

Step 34. Scroll down and give Categorization details like the below. Check step 35 to know how to insert data in the Categorization fields.

Click on the Save button to save the report

Step 35. Click on 3 dots of the categorization fields >>select the field from the left side>>click on the arrow button and click ok to insert the value

Step 36. The report is created and saved in CRM. Click on Run Report to view the report.

Step 37. Your report will be shown once you’ve clicked on Run Report like below

Step 38. You can download the report by clicking on the Save button. We can download the report in different formats like- pdf, word, PowerPoint, excel, etc. Your SSRS report creation part is done