Dynamics CRM Using SQL Server Reporting Services

Dynamics CRM uses the Reporting Services reports that is the Report Definition Language (RDL)
Report entity contains standard security settings like other entities.

2 Report permissions - Publish Reports, Add Reporting Services Reports.

Run reports in one of 3 areas - Reports View (Workplace -> My Work -> Reports subarea), Entity Ribbon, Entity Form



In default, Dynamics CRM includes approximately 26 Reporting Services reports, and those reports include an additional 28 sub-reports

Performance

• Limit a report to display information from a specified time period, instead of displaying all records in the Dynamics CRM database
• Pre-filter a report so that the dataset is limited.
• Calculate aggregate totals by using SQL code or aggregations in FetchXML query, instead of passing raw data to Reporting Services and grouping.
• Limit the number of datasets used, if possible.
• Use SQL stored procedures instead of inline SQL.
• Do not select all columns from a Microsoft Dynamics CRM filtered view.
• Do not create a report that uses a large dataset or a complex SQL query available on-demand to all users.
• Use an Existing Report in Dynamics CRM instead of Create new Custom Reports
• Use Microsoft Dynamics CRM Formats for Currency, Number, Date and Time, Calendar ( fn_GetFormatStrings database function)
• Dynamics CRM reports were designed for 8.25 x 11 (portrait) or 11 x 8.25 (landscape) page sizes that work for both US letter and A4 paper.
• Use images with Reporting Services is to put the images into a database.
• If the images are not in a database, you can use embedded images in .png, .gif, or .jpg formats in a report.
• The image files that are used by Microsoft Dynamics CRM image files are located in the C:\Program Files\Microsoft Dynamics CRM\CRMWeb\_imgs\ico folder.

Copy a report between organizations or deployments is to include the report and any custom entities the report uses in a solution ( mapped automatically). If you choose to manually copy a reportt, you must change the entity type codes for custom entities.

Schedule Report - Running complex reports can drastically reduce the performance of your reporting server. Click Schedule Report to edit the scheduled snapshot report

Managing report Categories -> Settings -> Administration -> System Settings  -> Reporting tab

Modifying the Existing report

1. Go to the Workplace -> My Work -> Reports -> Select 'All Reports, Including Sub-Reports' view
2. Select the Report (Campaign Performance Sub-Report) -> click Edit -> click Actions, Download Report.
3. Use VS 2008/2010 (SQL Data Server Tools), to load and open the saved report.
4. In the Report Data -> Data source -> Select CRM.
5. Some times Connectin String: "data source=localhost;initial catalog=Adventure_Works_Cycle_MSCRM"
6.    Change to your organizationname_MSCRM database.
7.    Click OK to close the Data Source dialog box.
8. Report contains multiple datasets, before modify the report, you must to know which dataset to edit.
9.    To edit the query, select the dataset. Use the Query Designer to edit.
10. Click the red exclamation point in the Query Designer toolbar to test the query.
11. Save your report by clicking Save All on the File menu.
12. In Dynamics CRM Reports, navigate to the Reports view. Select the 'Campaign Performance Sub-Report', and click Edit Report.
13. Browse the modfied RDL file and then click Save and Close.
14.    A warning message reminding you that you will overwrite an existing report appears.
15.    Click OK to update the existing report.
16. Click Run Report

Fetch XML Limitation
You cannot specify left outer joins – e.g. The following is not supported: “Select all Contact who do not have a related Completed Activites record”
You cannot specify group by / sum queries
Fetch XML queries return a maximum of 5000 records.
Cannot use Shared Data Source

Creating a Reporting Services Report Using Fetch XML
1. Using Advanced Find query to get Fetch XM statements.
2. First, install the Microsoft Dynamics CRM Report Authoring Extension.
http://www.microsoft.com/en-us/download/details.aspx?id=27823
3. Open the Business Intelligence Development Studio and create a new project using the Business Intelligence Project Type and the Report Server Project Template.

4. Right Click Reports -> Click Add -> select New Item.

5. In the Report Data toolbar, click New and select Data Source. Create a new data source see the image
    a. In the Name box, type CRM.
    b. In the Type list, select Microsoft Dynamics CRM Fetch.
    c. In the Connection String, type the server URL followed by a semicolon and then the organization unique name
    https://test.crm.dns.com;food
    d. Click the Credentials tab, select Use this user name and password, enter user name and password. Click Ok.
  
6. Righ click Datasets, add new Dataset. On the Dataset Properties dialog box, enter the following FetchXML statement (Advanced Find Query) into the Query window. Click Refresh Fields and then click OK.

 

Add the enableprefiltering="1" - this will enable Pre-Filtering and Default Filters on the report. If you do not add this parameter, you can still run the report in Dynamics CRM 2011, but you will not be able to apply filters to it in Dynamics CRM 2011.

On the Report Parameter Properties window, select the radio button labeled 'Internal'. avoid the prompted value, because this paramater value directly from CRM.


7. Desing, Preview and Save the report.

8. Goto Dynamics CRM,  Select Workplace -> My Work -> Reports -> Click New button
9. Select Existing File for the Report Type, and select your new report.rdl in the File Location field.
   Enter a description and choose Contacts in the Related Records Type field.
   For Display In, select all three options: Forms for related record types; Lists for related record types; Reports area.
10. Click Save, and then click Run your new report.

11. For filter
      <condition attribute="dns_empstatus" operator="eq" value="@EmpStatus" />
      Create new Parameter
    
Area         SQL-based Report             Fetch-based report

Data Provider    <DataProvider>SQL</DataProvider>    <DataProvider>MSCRMFETCH</DataProvider>

Report query    <CommandText>SELECT name FROM FilteredAccount;</CommandText>    <CommandText>&lt;fetch version="1.0" output-format="xml-platform" mapping="logical"&gt;
    &lt;entity name="account"&gt;
        &lt;attribute name="name" /&gt;
    &lt;/entity&gt;
&lt;/fetch&gt;</CommandText>

     

    
Create Dashboard
Select 'Customization' -> 'Solutions' -> Double Click Solution -> 'Dashboards' -> Create or Add Existing.