Creating a Table report for SQL Reporting Services with no wizards

Zahara Hirani, MCAD
Crowe Chizek and Company LLC
July 31^st, 2007
http://www.crowecrm.com

Summary: Zahara shows the steps needed to build a table report for Microsoft Dynamics CRM without using any wizards.

Creating a report using Visual Studio 2005

1. Open Visual Studio 2005.
2. Click on the File menu on the menu bar, point to Open, and then click Project/Solution.
3. In the New Project pop-up,
   1. Under Project Types, select the Business Intelligence Projects. Under Templates, select Report Server Project Wizard.
   2. Give the project a new name (e.g. AdventureWorksReports). Browse to a location where you wish to create the project.
   3. The Solution Name will be automatically populated with the Project Name that you specified earlier.
   4. Click OK.
4. Click the View menu bar, and select Solution Explorer.
5. This should open the solution Explorer on the right.
6. Right click Shared Data Sources
7. Enter the name of the shared Data source (e.g. AdventureWork_MSCRM)
8. Select Type as Microsoft SQL Server
9. Enter the Connection string as Data Source=Servername;Initial Catalog=Databasename

   

10. Click OK.
11. Right click the folder named Report
12. Select Add
13. Select New Item
14. This should open a pop-up window to specify what type of item to add
    1. Select Report
    2. Enter the name of the report (e.g. OpportunityByOwner.rdl)
    3. Click Add
15. This should create a new report and place it under the Report folder in the Solution Explorer.
16. This should also open the report and select the Data tab.
17. Click the Dataset dropdown list and select <New Dataset...>

    

18. Enter the name of the Dataset (e.g. OpportunityDS)
19. Select the Data Source to use for the Data Set (e.g. AdventureWork_MSCRM)
20. Select the Command Type (e.g. Text)
21. Enter the query to get the data.

    

22. Click Ok.
23. Execute the query by clicking the () Run button.
24. The records should display under the query statement.

    

25. Click on the 'Layout' tab.
26. Click on 'View' from the top menu bar and select 'Toolbox'

    

27. This should give you a list of all tools that can be used to setup the report layout.
28. Drag and Drop the 'Table' tool from the Toolbox to your Layout tab body section.
29. Right click the table in the Layout tab and select Properties.
30. Select the Dataset that will be used to populate the table.
31. Select other header and footer properties for the report.

    

32. Navigate thru the tabs and select other properties for the report.
    1. Sorting:

       

    2. Grouping:
       1. Click on Add to add a group by field.(e.g. Group by Opportunity owner)
          
          
       2. Click OK.
       3. Add a second group to group by customer

          

       4. Click OK.
          
       5. Click OK.
33. Click on 'View' from the top menu bar and select 'Datasets'
34. This should give you a tree view of all datasets contained in the report and the fields each data set returns.

    

35. Enter the display name for each column in the Header section of the table.
36. Drag and drop the fields to display in each column as desired in the Detail section of the table.
37. If you have fields for which total needs to be calculated, add the field to the footer row. (SRS will automatically add the SUM() function to the column).
38. Edit the style information based on what you would like to display.
39. Click on the 'Preview' tab to see the report.

    

    

The report should now open in preview mode with the results.

• You will notice the report shows the opportunities grouped first by the Owner (i.e. Jeremy Cox owns 7 opportunities for 4 customers and Zahara Hirani owns 2 opportunities for 2 customers).
• The second grouping is by customer (i.e. Nancy Hirota has 2 opportunities open).
• The detail shows which stage each opportunity for a customer is at with the probability of closing and the rating.
• The total per customer is summed and displayed in the customer group footer.
• The total per owner is summed and displayed in the owner group footer.
• The total for all opportunities is displayed in the table footer.

Zahara Hirani, MCAD is a Microsoft CRM developer at Crowe Chizek (www.crowecrm.com) Zahara currently focuses on .NET / SQL / SRS development on the Microsoft Dynamics CRM 3.0 platform.