MS CRM 2011: General approaches to generation of reports

Following article describes general steps to use web services of Reporting Services.

Preparation of report

The easiest way is to develop and test report for CRM and then make it available for external usage (or use in-build report of CRM). In following samples I will use standard Quote report.

To publish report for external use Open CRM, navigate to report, choose Report and click Edit:

Once report is opened click Actions – ‘Publish report for External Use’:

To recheck that reports were published open your reports url – http://[Reporting Server]:[Port]/Reports/Pages/Folder.aspx (in my case it is http://crm/Reports/Pages/Folder.aspx):

Click on folder called YourCRMOrganization_MSCRM (in my case it is Contoso_MSCRM):

In case your report has subreport report will be published for external use as well but main report would be broken. How to fix it you can check here.

In case your report has to receive some parameters you should get names of those parameters. To do it click on dropdown near the name of the report and choose Manage, then select Parameters:

Usage of Reporting Services web services

For development of extension for CRM I use developer toolkit. So I created deployment package and Workflow Project. Next step is adding of reference to Reporting Services web service. Proceed following steps to add reference of Reporting Services service to your project:

Once you’ve done it you should get something similar in your project explorer:

Development – helper class that allows generation of report

Add class to your project and put following code into it:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ReportGenerationWorkflow.Reporting;
using System.Net;
using System.Web.Services.Protocols;

namespace ReportGenerationWorkflow
{
    internal class ReportGenerator
    {
        #region Privates

        ReportExecutionService _reportexecutionservice = null;

        #endregion Privates

        #region CTOR

        internal ReportGenerator(string ServiceUrl, ICredentials credentials)
        {
            if (string.IsNullOrEmpty(ServiceUrl))
                throw new Exception("Parameter ServiceUrl has to contain value");

            if (credentials == null)
                throw new Exception("Parameter Credentials has to contain value");

            _reportexecutionservice = new ReportExecutionService()
            {
                Credentials = credentials,
                Url = ServiceUrl
            };
        }

        #endregion CTOR

        #region Methods

        internal byte[] Render(string Report, FormatType formattype)
        {
            return this.Render(Report, formattype, new ParameterValue[] { });
        }

        internal byte[] Render(string Report, FormatType formattype, ParameterValue[] parameters)
        {
            byte[] result = null;
            string format = GetFormatType(formattype);
            string historyID = null;
            string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
            string encoding;
            string mimeType;
            string extension;
            Warning[] warnings = null;
            string[] streamIDs = null;

            try
            {
                ExecutionInfo execInfo = new ExecutionInfo();
                ExecutionHeader execHeader = new ExecutionHeader();
                _reportexecutionservice.ExecutionHeaderValue = execHeader;
                execInfo = _reportexecutionservice.LoadReport(Report, historyID);
                _reportexecutionservice.SetExecutionParameters(parameters, "en-us");
                result = _reportexecutionservice.Render(format, devInfo, out extension, 
                    out mimeType, out encoding, out warnings, out streamIDs);
            }
            catch (Exception ex)
            {
                if (ex is SoapException)
                {
                    SoapException sexc = ex as SoapException;
                    throw new Exception(string.Format("Error generating report - {0}", sexc.Detail.InnerText));
                }
                else
                {
                    throw new Exception(string.Format("Error generating report - {0}", ex.Message));
                }
            }

            return result;
        }

        private string GetFormatType(FormatType formattype)
        {
            switch (formattype)
            {
                case FormatType.XML:
                case FormatType.CSV:
                case FormatType.IMAGE:
                case FormatType.PDF:
                case FormatType.MHTML:
                case FormatType.EXCEL:
                case FormatType.Word:
                    return formattype.ToString();
                case FormatType.HTML40:
                    return "HTML4.0";
                case FormatType.HTML32:
                    return "HTML3.2";
                default:
                    throw new Exception(string.Format("Rendering type {0} is not available", formattype));
            }
        }

        #endregion Methods
    }

    internal enum FormatType
    {
        XML, CSV, IMAGE, PDF, HTML40, HTML32, MHTML, EXCEL, Word
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using ReportGenerationWorkflow.Reporting;

using System.Net;

using System.Web.Services.Protocols;

namespace ReportGenerationWorkflow

{

internal class ReportGenerator

{

#region Privates

ReportExecutionService _reportexecutionservice = null;

#endregion Privates

#region CTOR

internal ReportGenerator(string ServiceUrl, ICredentials credentials)

{

if (string.IsNullOrEmpty(ServiceUrl))

throw new Exception("Parameter ServiceUrl has to contain value");

if (credentials == null)

throw new Exception("Parameter Credentials has to contain value");

_reportexecutionservice = new ReportExecutionService()

{

Credentials = credentials,

Url = ServiceUrl

};

}

#endregion CTOR

#region Methods

internal byte[] Render(string Report, FormatType formattype)

{

return this.Render(Report, formattype, new ParameterValue[] { });

}

internal byte[] Render(string Report, FormatType formattype, ParameterValue[] parameters)

{

byte[] result = null;

string format = GetFormatType(formattype);

string historyID = null;

string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

string encoding;

string mimeType;

string extension;

Warning[] warnings = null;

string[] streamIDs = null;

try

{

ExecutionInfo execInfo = new ExecutionInfo();

ExecutionHeader execHeader = new ExecutionHeader();

_reportexecutionservice.ExecutionHeaderValue = execHeader;

execInfo = _reportexecutionservice.LoadReport(Report, historyID);

_reportexecutionservice.SetExecutionParameters(parameters, "en-us");

result = _reportexecutionservice.Render(format, devInfo, out extension,

out mimeType, out encoding, out warnings, out streamIDs);

}

catch (Exception ex)

{

if (ex is SoapException)

{

SoapException sexc = ex as SoapException;

throw new Exception(string.Format("Error generating report - {0}", sexc.Detail.InnerText));

}

else

{

throw new Exception(string.Format("Error generating report - {0}", ex.Message));

}

return result;

}

private string GetFormatType(FormatType formattype)

{

switch (formattype)

{

case FormatType.XML:

case FormatType.CSV:

case FormatType.IMAGE:

case FormatType.PDF:

case FormatType.MHTML:

case FormatType.EXCEL:

case FormatType.Word:

return formattype.ToString();

case FormatType.HTML40:

return "HTML4.0";

case FormatType.HTML32:

return "HTML3.2";

default:

throw new Exception(string.Format("Rendering type {0} is not available", formattype));

}

#endregion Methods

}

internal enum FormatType

{

XML, CSV, IMAGE, PDF, HTML40, HTML32, MHTML, EXCEL, Word

}

Development – usage of helper class

As a sample that shows how to use this helper class I took following scenario – CRM user prepared Quote for customer and decided to send it using email. Using out of box features it would require a lot of steps – open prepared quote, run report, save it somewhere, create new email, attach saved file to email and send. Provided custom workflow activity makes this process easier. As input parameter it receives reference to an email to which file has to be attached:

namespace ReportGenerationWorkflow
{
    using System;
    using System.Activities;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Workflow;
    using System.Net;
    using ReportGenerationWorkflow.Reporting;
    using Microsoft.Crm.Sdk.Messages;

    public sealed class RenderQuoteReport : CodeActivity
    {

        [Input("E-Mail")]
        [ReferenceTarget("email")]
        public InArgument<EntityReference> Email { get; set; } 

        protected override void Execute(CodeActivityContext executionContext)
        {
            IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
            IOrganizationServiceFactory serviceFactory = 
                executionContext.GetExtension<IOrganizationServiceFactory>();
            IOrganizationService service = serviceFactory.CreateOrganizationService(null);

            ReportGenerator rg = new ReportGenerator("http://crm/Reportserver/ReportExecution2005.asmx",
                new NetworkCredential("administrator", "Password", "contoso"));

            ParameterValue[] parameters = new ParameterValue[1];
            parameters[0] = new ParameterValue();
            parameters[0].Name = "P1";
            parameters[0].Value = string.Format("Select * From FilteredQuote Where QuoteId = '{0}'", 
                context.PrimaryEntityId);

            byte[] reportresult = rg.Render("/contoso_mscrm/quote", FormatType.PDF, parameters);

            Entity attachment = new Entity("activitymimeattachment");
            attachment["objectid"] = Email.Get<EntityReference>(executionContext);
            attachment["objecttypecode"] = "email";
            attachment["filename"] =
            attachment["subject"] = "Quote.pdf";
            attachment["body"] = System.Convert.ToBase64String(reportresult);

            try
            {
                service.Create(attachment);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Error creating attachment - {0}", ex.Message));
            }

            try
            {
                service.Execute(new SendEmailRequest()
                {
                    EmailId = Email.Get<EntityReference>(executionContext).Id,
                    IssueSend = true,
                    TrackingToken = string.Empty
                });
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Error sending email - {0}", ex.Message));
            }

        }
    }
}

namespace ReportGenerationWorkflow

{

using System;

using System.Activities;

using System.ServiceModel;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Workflow;

using System.Net;

using ReportGenerationWorkflow.Reporting;

using Microsoft.Crm.Sdk.Messages;

public sealed class RenderQuoteReport : CodeActivity

{

[Input("E-Mail")]

[ReferenceTarget("email")]

public InArgument<EntityReference> Email { get; set; }

protected override void Execute(CodeActivityContext executionContext)

{

IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();

IOrganizationServiceFactory serviceFactory =

executionContext.GetExtension<IOrganizationServiceFactory>();

IOrganizationService service = serviceFactory.CreateOrganizationService(null);

ReportGenerator rg = new ReportGenerator("http://crm/Reportserver/ReportExecution2005.asmx",

new NetworkCredential("administrator", "Password", "contoso"));

ParameterValue[] parameters = new ParameterValue[1];

parameters[0] = new ParameterValue();

parameters[0].Name = "P1";

parameters[0].Value = string.Format("Select * From FilteredQuote Where QuoteId = '{0}'",

context.PrimaryEntityId);

byte[] reportresult = rg.Render("/contoso_mscrm/quote", FormatType.PDF, parameters);

Entity attachment = new Entity("activitymimeattachment");

attachment["objectid"] = Email.Get<EntityReference>(executionContext);

attachment["objecttypecode"] = "email";

attachment["filename"] =

attachment["subject"] = "Quote.pdf";

attachment["body"] = System.Convert.ToBase64String(reportresult);

try

{

service.Create(attachment);

}

catch (Exception ex)

{

throw new Exception(string.Format("Error creating attachment - {0}", ex.Message));

}

try

{

service.Execute(new SendEmailRequest()

{

EmailId = Email.Get<EntityReference>(executionContext).Id,

IssueSend = true,

TrackingToken = string.Empty

});

}

catch (Exception ex)

{

throw new Exception(string.Format("Error sending email - {0}", ex.Message));

}

Build project and register it in CRM using Developer Toolking or Plugin Registration Tool.

In provided sample I hardcoded credentials and Url of webservice. You can use it as it is but from point of flexibility and security it is better to store this settings somewhere – it could be config file on server, registry keys or some configuration entity in CRM.

Design of workflow
Following screenshots show how to create workflow that uses created custom workflow activity:

Demonstration

Following screenshots demonstrate usage of workflow action:

Dear Andrew,
I created OnDemand Workflow and I select 10 Accounts to Send Email Report
I got the below Error, Any Idea About that, it is not Generating Report

Error generating report – rsProcessingAborted400An error has occurred during report processing.http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsProcessingAborted&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=12.0.4100.1Microsoft SQL Server Reporting Services12.0.4100.11033OsIndependent1033Microsoft.ReportingServices.ProcessingCoreAn error has occurred during report processing.Microsoft.ReportingServices.ProcessingCoreQuery execution failed for dataset ‘DSXYZ’.Microsoft.Crm.Reporting.DataExtension.Common.Fetch
Microsoft.Crm.CrmException: An unexpected error occurred.
System.ServiceModel.CommunicationObjectFaultedException: The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.An unexpected error occurred.The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state

Unhandled Exception: System.Exception: Error generating report – rsProcessingAborted400An error has occurred during report processing.http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsProcessingAborted&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=12.0.4100.1Microsoft SQL Server Reporting Services12.0.4100.11033OsIndependent1033Microsoft.ReportingServices.ProcessingCoreAn error has occurred during report processing.Microsoft.ReportingServices.ProcessingCoreQuery execution failed for dataset ‘DSXYZ’.Microsoft.Crm.Reporting.DataExtension.Common.Fetch
Microsoft.Crm.CrmException: An unexpected error occurred

20 Comments

ChrisS says:

08/11/2012 at 4:14 pm

Thanks for sharing. One question, what "account" actually runs your report (in the SSRS shared data source)? We tried to enable kerberos delegation to solve a double-hop (separate CRM database), but ended up using a "CRM System" account.

Andrii Butenko says:

09/11/2012 at 4:12 pm

Hello,
In my test system I have SQL and RS installed on the same server so I didn't have any chance to test separated scenario. I believe that usage of special AD Account is fast and effective workaround.

ChrisS says:

09/11/2012 at 4:24 pm

Sounds good to me. Thanks again.

Andrii Butenko says:

09/11/2012 at 4:32 pm

Welcome!

sermoc says:

23/01/2013 at 1:42 pm

This comment has been removed by the author.

sermoc says:

23/01/2013 at 1:44 pm

Hello Andrii,
I have followed your recommendations to attach a report to an email. The report attached ok, but when I open it (I render the report as PDF) I can only see the first title label. I have a Tabflix defined in my report. Could it be a problem to render it?

Thanks for your post. It's a proposal very interesting and useful.

sermoc says:

23/01/2013 at 3:07 pm

I answered myself. The problem was the credentials. I had used the CredentialCache.DefaultCredentials. When I change this value to one AD crm user I can see the report results.

Andrii Butenko says:

23/01/2013 at 3:13 pm

Yes =) I wanted to answer in the same way. Glad that my article helped you.

Роман Копаев says:

15/04/2013 at 1:00 pm

This works with IFD?

Andrii Butenko says:

15/04/2013 at 7:08 pm

Yes, this would work with IFD.

Antoniu says:

31/08/2017 at 12:09 am

Hi Andrii,
Getting an error for the last parameter although I ended up hardcoding the value:
Exception: Unhandled Exception: System.Exception: Error generating report – rsReportParameterProcessingError400Error during processing of ‘IsKroger’ report parameter.

ReportGenerator reportGenerator = new ReportGenerator(reportUrl, CredentialCache.DefaultCredentials);
ParameterValue[] parameters = new ParameterValue[3];
parameters[0] = new ParameterValue();
parameters[0].Name = “SlotId”;
parameters[0].Value = “25408F61-24B7-E611-8112-005056A850EB”;

parameters[1] = new ParameterValue();
parameters[1].Name = “RetailMarketId”;
parameters[1].Value = @”Select Superf_RetailMarket As RetailMarketId
From FilteredSuperf_Slot (Nolock) As S
Where Superf_SlotId = ‘2458F61-24B7-E611-8112-005056A850EB'”;

parameters[2] = new ParameterValue();
parameters[2].Name = “IsKroger”;
parameters[2].Value = “0”;

1. Andrii Butenko says:
  
  31/08/2017 at 1:49 pm
  
  Hello,
  Based on the name I can assume that IsKroger parameter is boolean. Try to assign false to value of parameter like
  parameters[2].Value = false;
  Thanks,
  Andrii
  
Mohammad says:

21/12/2020 at 4:05 am

this is working fine for On demand workflow with one record , if I select Many records it is giving error .

How we use it for bulk Records ?

1. Andrew Butenko says:
  
  21/12/2020 at 12:13 pm
  
  Mohammad,
  Can you please be more specific on what error do you get?
  Thanks,
  Andrew
  
Mohammad says:

12/01/2021 at 3:59 am

Hi Andrew Butenko

Did you Test The Above For Batch Processing.
I need to Use the above to Send Bulk Email Reports in One Click .
I am using CRM 2011.

1. Andrew Butenko says:
  
  12/01/2021 at 9:25 am
  
  Hello,
  I had no scenario where I needed to send emails in batch so I haven’t tested it.
  Andrew
  
Mohammad says:

12/01/2021 at 11:29 am

Dear Andrew,
I created OnDemand Workflow and I select 10 Accounts to Send Email Report
I got the below Error, Any Idea About that, it is not Generating Report

Error generating report – rsProcessingAborted400An error has occurred during report processing.http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsProcessingAborted&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=12.0.4100.1Microsoft SQL Server Reporting Services12.0.4100.11033OsIndependent1033Microsoft.ReportingServices.ProcessingCoreAn error has occurred during report processing.Microsoft.ReportingServices.ProcessingCoreQuery execution failed for dataset ‘DSXYZ’.Microsoft.Crm.Reporting.DataExtension.Common.Fetch
Microsoft.Crm.CrmException: An unexpected error occurred.
System.ServiceModel.CommunicationObjectFaultedException: The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.An unexpected error occurred.The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state

Unhandled Exception: System.Exception: Error generating report – rsProcessingAborted400An error has occurred during report processing.http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsProcessingAborted&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=12.0.4100.1Microsoft SQL Server Reporting Services12.0.4100.11033OsIndependent1033Microsoft.ReportingServices.ProcessingCoreAn error has occurred during report processing.Microsoft.ReportingServices.ProcessingCoreQuery execution failed for dataset ‘DSXYZ’.Microsoft.Crm.Reporting.DataExtension.Common.Fetch
Microsoft.Crm.CrmException: An unexpected error occurred

1. Andrew Butenko says:
  
  12/01/2021 at 11:36 am
  
  Mohammad,
  Does it work when you select 1 record?
  Andrew
  
Mohammad says:

12/01/2021 at 11:51 am

Yes , For 1 Record Everything Fine.

1. Andrew Butenko says:
  
  12/01/2021 at 11:57 am
  
  Mohammad,
  I have never experienced similar issues so I don’t know how I can help you. The issue is related to “Reporting Service” so I would recommend adding logging around it and look for an answer to why it fails.
  Andrew

Andrew Butenko's Blog

MS CRM 2011: General approaches to generation of reports

Like this:

Related

20 Comments

Leave a Reply Cancel reply

Share this:

Like this:

Related

20 Comments

Leave a Reply Cancel reply

#Stand­With­Ukraine

#StandWithUkraine