Dynamics CRM Report - Generate SSRS report using CRM DB and Extension Database

1) Create 2 data sources
a)DNSCRM_EXT (connect ext database, not using filtered table)

b)MSCRM_DataSource (connect CRM database, using filtered table)

Dynamic CRM 2011/2013 Audit Report in SSRS

My client want the particular contact entity attributes audit history in the horizontal way SSRS report.
CRM will create one line record in the audit entity for each action (new, update or delete records).
Refer "Dynamics CRM - Audit Entity" - http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

1)Step created dynamics sql query

declare @contactid uniqueidentifier="00000000-0000-0000-0000-000000000000"> Declare @AuditTable table (id int identity(1,1), AuditId uniqueidentifier, ObjectTypeCode int, AttributeMask varchar(max) null,
        ChangeData varchar(max), CreatedOn datetime)
declare @AuditColumnResult table (id int identity(1,1), rid int null, AuditId uniqueidentifier, Increment int, ColumnKey int,
        ColumnName varchar(max), OldValue varchar(max), NewValue varchar(max), ObjectTypeCode int, createdon datetime)
declare @AuditHisoty table (AuditId uniqueidentifier, Increment int, ColumnKey int, ColumnName varchar(max), Value varchar(max))

Convert Dynamics CRM Entity GUID in SSRS

When i create hyperlink i passed  GUID (Fields!contactid.Value) in the query string, the link is not working. I tried to convert GUID to string, still the link is not working.

Finally i used CType function to concatenation with the querystring, it works.

 = Parameters!CRM_URL.Value + "/tools/audit/audit_details.aspx?_CreateFromId=&_CreateFromType=2&id={" & CType(Fields!contactid.Value, GUID).ToString  & "}"

MS Dynamics CRM - Attach SSRS Report in Email Attachments

This blog will explain to call SSRS report, convert SSRS report content to HTML and place it in the body of the email, same SSRS report will convert to PDF and attached to the email.

It contains 2 class, one is email and call EmailAttachment() function to generate email. Second class name is ReportServerCredentials, it extends IReportServerCredentials, code at the end of blog.

using System.ServiceModel.Description;
using System.Security.Principal;
using System.Net;
using Microsoft.Reporting.WebForms;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Client;

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

CRM Report Wizard

The following table compares the features of charts and dashboards with those of the SQL Server Reporting Services Report Wizard.

	Charts and Dashboards	SQL Server Reporting Services Report Wizard
Report output	Inline visualizations  presented within Microsoft Dynamics CRM grids and forms	Web-based reports that can be exported to additional formats, such as Microsoft Excel, PDF, and CS
Ability to schedule reports for email delivery	No	Yes
Ability to include data from multiple record types in results	No	Yes
Ability to prompt users to enter parameters before running reports	No	Yes

CRM includes 25 standard SQL Server Reporting Services reports in the base product.

SSRS - Page Break

Customize Page Break
1)Right Click the data row, choose Add Group -> Row Group - Parent Group.

SQL Server Reporting Service (SSRS)

SQL Server Reporting Service (SSRS)

1. SSRS is a bigger software application. SSRS is part of SQL server since SQL SERVER 2005. 
2. The SSRS installation doesn’t need to have SQL Database in one box, but SSRS still needs to connects to an existing SQL server database instance. 
3. All the SSRS configurations and reports definitions are eventually saved in the database, the configuration database for SSRS by default named as “ReportServer” and “ReportServerTempDB”. 
4. Good practice to keep SSRS Report engine / Report definitions & web pages are at web server, i.e., n-tier enterprise solution.
5. In SQL Server 2005, SSRS does need IIS to be together in one box, because it needs IIS to host its web service / portal interface. 
6. In SQL 2008, SSRS has its own embedded web server, and doesn’t need IIS anymore.
7. SSRS can be setup as totally independent server, no AD/Domain at all, although AD/Domain setup can bring it to normal enterprise security level.

SSRS web management portal is for admin works (such as setup data source, online folders, security, adjust reports parameters, schedules etc.).  More info SSRS Web Management Portal - User Roles and Permissions

Display SSRS Report in ASP.NET Web Page

1)Open VS 2010 and Create a ASP.NET Web Application project.

2)Add a ScriptManager (AJAX Externsions), ReportViewer (Reporting) and Button (Standard) control from the toolbox in the Default.aspx page.

3)Double Click on the button, to add the following code in the button event.

protected void Button1_Click(object sender, EventArgs e)
{
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.ReportServerUrl = new Uri("http://localhost/ReportServer");
ReportViewer1.ServerReport.ReportPath = "/Report Project2/Report4";
ReportViewer1.ServerReport.Refresh();
}

Deploying a SSRS report to the Server, SSRS Web Management Portal - User Roles and Permissions

1)Right click on the project. Select properties and there you will get to see the Deployment properties and you should see the TargetServerURL property as shown ihe below image. Set the TargetServerURL value (In the Reporting Services Configuration Tools dialog box, click Web Service URL in the left pane, the URLs below Report Server Web Service URLs just is the URL you should use as the TargetServerURL.)

2)Click on the ‘solution explorer project name’ and right click to select the Deploy option to publish the report to the server.

Configuring SQL Server Reporting Services (SSRS) in Windows Server 2008

To deploy/access the report on a server, need to have the Report Server up and running. For this purpose, do some configuration for the report server.

1.Go to All Programs -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> Reporting Services Configuration Manager(RSCM), RSCM will open as shown in the screen below. Select the appropriate Server and Instance Name and click on Connect as shown in the screen below.

2.Once connected successfully, you can see the Reporting Services Status as shown in the screen below.

SQL Server Reporting Services (SSRS) - Design Report

Designing a report using Report Wizard

1)Programs -> Microsoft SQL Server 2008/2008 R2 -> SQL Server Business Intelligence Development Studio.

2)Create a new Project
Open File -> New -> Project (a list of templates will be display) -> Report Server Project Wizard -> Press OK button to create the project.