Wednesday 4 April 2012

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 !!


Field Security Profile - Based on Owner

 Recently received requirement related to Field security profile. Expectation : - 1.       Set to users need access of secure attributes. 2....

Test