Reports in CRM 2011

Run the Report Builder 3.0,

Download "Microsoft SQL Server 2008 R2 Report Builder 3.0"  from http://www.microsoft.com/download/en/details.aspx?id=6116

Connect to Report Server "http://<computername>/reportserver"

Create a Data Source ".;db_mscrm"

Create a new Dataset -> select the above mentioned data source and use the following query

"select column1,column2 from filterednew_customentity" to show records user has access to,

OR

"select column1,column2 from filterednew_customentity as CRMAF_Filterednew_customentity" to show records user has access to and also context sensitive i.e. for the records user selected/or currently viewing entity record.

Drag and drop columns on report, Preview report and then save report

In CRM go to workplace and select reports and click on new 

Report Type: Existing File

File Location: "browse the file created and saved earlier"

Name: Basic Custom Report

Description: Report description

Categories: <<blank>>

Related Records: new_customentity "this is the entity for which the report is created for"

Display In: Forms for related record types;Lists for related record types;Reports area

Save,

Open the form for custom entity and click on Reports button and browse the report.

http://ronaldlemmen.blogspot.com/2006/09/reporting-prefilter.html

------------------------------

When creating a custom report for CRM 2011 and using explicit filtering. Create a parameter with the name of CRM_FilteredEntity and type of text, 

In this example, I am trying to create a very basic report where I show statuscode of Cases (Incidents). For that I followed following steps.

1. Created a Parameter of type text with name of CRM_FilteredEntity

2. Created a DataSet with the following query

declare @sql nvarchar(max)

set @sql = 'select statuscode from ('+@CRM_FilteredIncident+') as incident';

exec(@sql) 

and when clicked okay, 

When prompted, I entered "select statuscode from FilteredIncident" to populate dataset with fileds.

3. Draged and drop field on report.

Explicit reports do not include context sensitive records i.e selected records can not be shown in report, however this can be achieved by custom methods e.g creation of ribbon button and then passing selected values to report server url with selected parameters.

http://www.njevity.com/blog/creating-dynamics-crm-2011-reports-sql-reporting-services-pre-filtering-and-default-filters
http://gtcrm.wordpress.com/2011/03/24/fetch-xml-reports-for-crm-2011-online/

----------

When defining data source for the report use embedded option, when report is uploaded to crm or different server, CRM is clever enough to ignore the data source details embedded into report.