Objective:

Create a report containing opportunities created by employees in a month, opportunities value, grouped on business units.

A. Create a specific view to identify employees and business units to which they belong.

1. Open SQL Server Management Studio
2. Identify your CRM database. In this demo: FEAA_CRM_MSCRM
3. Navigate to Views, right click New View
4. In Add table pop-up window select SystemUserBase table and press Add and after that close.
5. In SQL part of View builder, select all, delete and type:

SELECT TOP (100) PERCENT SU.SystemUserId, BU.Name AS BUName, SU.LastName, SU.FirstName, SU.DomainName
   FROM dbo.SystemUserBase AS SU INNER JOIN
        dbo.BusinessUnitBase AS BU ON SU.BusinessUnitId =         BU.BusinessUnitId
ORDER BY BUName, SU.LastName, SU.FirstName

6. Execute query. Expected columns in your view are:
SystemUserId, BUName, LastName, FirstName, DomainName
7. Save the view with a specific name. In this demo: wUseriBU

B. Create and test a query for all opportunities including the name of author and the name of business unit.

1. In SQL Server Management Studio create a new query and type:

SELECT [OpportunityId]
      ,[OpportunityRatingCode]
      ,[Name]
      ,[Description]
      ,[EstimatedValue]
      ,[SalesStageCode]
      ,[EstimatedCloseDate]
      ,[CreatedOn]
      ,[StatusCode]
      ,wSU.FirstName
      ,wSU.[LastName]
      ,wSU.DomainName
      ,wSU.BUName
  FROM [FEAA_CRM_MSCRM].[dbo].[OpportunityBase] OB
        ,[FEAA_CRM_MSCRM].[dbo].[wUseriBU] wSU
  WHERE  OB.CreatedBy = wSU.SystemUserId
  ORDER BY wSU.BUName, wSU.LastName, wSU.FirstName

2. Execute query and review your results.
Expected columns:
OpportunityId, OpportunityRatingCode, Name, Description,    EstimatedValue,    SalesStageCode,    EstimatedCloseDate, CreatedOn, StatusCode, FirstName, LastName, DomainName, BUName

C. Access your reporting server to create a new report

1. Into a new browser type your report server address. In this Demo:
http://10.10.1.3/Reports/Pages/Folder.aspx
Usually there are two folders:
– your organization name (in this demo: FEAA_CRM_MSCRM )
– SharedReports
2. Access SharedReports and after that 5.0.xxxx folder.
3. Switch on Details View    and go to the end of page and confirm that there are two data source files:
– MSCRM_DataSource
– MSCRM_FetchDataSource
4. Click on Report builder and after that Run on Application security pop-up Window.

D. Create and test your report.

1. In Getting started window choose Table or Matrix Wizard
2. In Choose a dataset window select Create a dataset and press Next
3. Choose a dataset or browse for one from your reporting server and press Test Connection.
4.a If you are asked for an username and password type your data and press OK. If you receive an error message: "System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)." read this article: http://support.microsoft.com/kb/2006869?wa=wsignin1.0
4.b. Another possible solution is to open a datasource from FEAA_CRM_MSCRM folder. Click on browse, navigate into your report server, in _CRM_MSCRM folder and choose MSCRM_DataSource. Test your connection and press Next.
5. In Design query window press edit as text and copy/paste your SQL statement defined into step B.1.
6. Press Run (!) button and Next.
7. In Arrange fields Window coose your grouping level (Sample: BUName, DomainName) and drag-and-drop fields into Row Groups. Select other fields and drag-and-drop all into Values section and press Next
8. In Choose the layout you can press Next
9. In Choose a style, choose what you want and press Finish.
10. Type a title for your report (in this Demo: Opportunities created by employees)
11. Test your report as is pressing Run.
12. Design your report and change some Grouping calculated fields from Sum tu Count, or delete it ([Sum(OpportunityRatingCode)])
13. Add common parameters: Start Date and End Date. In Report Designer right click on Parameters and Add parameter. Type parameter Name and Prompt (pStartDate). From Data type choose Date/Time and press Ok. Repeat steps for pEndDate parameter.
14. Add a filter for your Dataset. Navigate to Datasets, and right click on your dataset name (Dataset1) and choose: Dataset properties.  
15. On Dataset properties window click on Filters and Add. On Expression choose CreatedOn field, on Operator choose >=,  on Value type the name of your start date parameter: [@pStartDate]
16. Press Add to add a new parameter and On Expression choose CreatedOn field, on Operator choose <=,  on Value type the name of your end date parameter: [@pEndDate]
17. Run your report again to test your parameters.
18. Save the report (in this Demo: Opportunities by user and BU)

E. Publish your report into Dynamics CRM 2011

1. Open your report from Reporting service web page
2. Copy the URL address
3. Open your Dynamics CRM web page.
4. Go into Workplace, Reports and press New
5. On report type wizard choose: Link to web page
6. Paste your Report URL
7. Type de report name and description
8. Press Save and Close
9. Test your Report.

F. (optional) Report Security

If your users cannot access your report
1. Go into your Report server URL
2. Identify your Report
3. Click on contextual menu
4. Choose Security
5. From Security Page press Edit Item Security and add your users into the report.

Step-by-Step Video: http://youtu.be/dlrRLYeWLCY

Hope is useful!

Disclaimer: Microsoft, SQL Server, Dynamics CRM, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.