Dynamic CRM Custom Report

Requirements:

• Microsoft SQL Server 2008 or 2008 R2 Business Intelligence Development Studio (BIDS) installed

• Microsoft Dynamics CRM 2011 BIDS Fetch Extension installed 

Steps:-

• Start creating the report, Open the Microsoft SQL Server Business Intelligence Development    Studio (BIDS).

• Create a new project and select the Report Server Project from templates
• Right click on Reports folder within your project; and Add New Report, this will start the Report Wizard
• Select New data source . Type name:datasource1
•  Select Microsoft Dynamics CRM Fetch for type and fill in the connection string in the following format: ServerURL;OrganizationName;HomeRealmURL 

           Ex.crm.servername.com;myOrganizationName

• Enter in the credentials for connection:Select Use a specific user name and password.then Click Next.
• Click on Query Builder.
• Right Fetchxml query as:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="entityname" >
        <attribute name="att_name" />
        <attribute name="Att_surveyid" />
       </entity>
</fetch>

• Click on red Exclamation mark(!) .That will show result.

Limitation: Fetchxml  only give you the first 5000 records at a time 

Solution:
1. http://mscrmbi.blogspot.in/2011/04/fetch-xml-5000-records-limitation.html 
2. Use Parameter for Page no.


a). In Report data right click on Parameters :Add Parameter...
b). Report Parameter Property:
General:-Name:PageNo,Prompt :page ,Select parameter visibility: Hidden

 Default values:Specify Value,Click on Add,Type 1 in value
Advance:Always refresh,Uncheck report part notification.
c). Change fetchxml query:
<fetch version="1.0" output-format="xml-platform" count="5000" no-lock="false"  page="@PageNo" mapping="logical" distinct="false">
<entity name="entityname" >
</entity>
</fetch>
d).Insert two indicator in your page.one for back other for forward.

Back indicator property:-
General:-Name:Back,Tooltip:Back
Value and States:Value:[@PageNo],State measurementUnit:Numeric
Remove other indicator states except one:-Start:2,End:5000

 Action: select go to report.specify current report,Parameterto run the report.Click on add,Name:PageNo,Value:=(Parameters!PageNo.Value-1) Click on Ok

Forward indicator property:-
 General:-Name:Forward,Tooltip:Forward
Value and States:Value:[Count(MoreRecords)],State measurementUnit:Numeric
Remove other indicator states except one:-Start:5000,End:5000
Action: select go to report.specify current report,Parameterto run the report.Click on add,Name:PageNo,Value:==(Parameters!PageNo.Value+1) Click on Ok

Run report :Click on indicators it will show next 5000 record per page

Enjoy !!