none
Programatically Render SSRS report from CRM 4.0

    Question

  • I cannot find a working example of programmatically rendering a CRM 4.0 report.


    Here are a few blogs that try to explain it but the code does not seem to work for me:
    With the first scenario I receive an error about the data source not being found.
    With the second scenario I receive an error about the UserId Parameter.

    Untitled Page"Error during processing of ‘UserName’ report parameter."

    Here is my current code, it is a hybrid between both of them:


    ////////////////////////////////////////////////////////////////////////////////////
    public void Render2() 
            { 
                ReportExecutionService rs = new ReportExecutionService();            
                rs.Credentials = System.Net.CredentialCache.DefaultCredentials; 
     
                byte[] result = null
                string reportPath = "/ForethoughtCapitalFunding_MSCRM/4.0/{89215301-40B6-DD11-978E-0003FF378118}"
                string format = "MHTML"
                string historyID = null
                string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"
     
                string showHideToggle = null
                string encoding; 
                string mimeType; 
                string extension; 
     
                Warning[] warnings = null
                ParameterValue[] reportHistoryParameters = null
             
                string[] streamIDs = null
     
                DataSourceCredentials dsc = new DataSourceCredentials(); 
                dsc.DataSourceName = "MSCRM_DataSource"
                dsc.Password = "{E84024B2-C82D-DD11-AB3B-0003FF378118}"
                dsc.UserName = "{0B21E346-C92D-DD11-AB3B-0003FF378118}"
                DataSourceCredentials[] credentials = new DataSourceCredentials[] { dsc }; 
                
                ParameterValue[] parameters = new ParameterValue[1]; 
                parameters[0] = new ParameterValue(); 
                parameters[0].Name = "UserName"
                parameters[0].Value = "{0B21E346-C92D-DD11-AB3B-0003FF378118}";            
     
                try 
                {                
                    ExecutionInfo execInfo = new ExecutionInfo(); 
                    ExecutionHeader execHeader = new ExecutionHeader(); 
                    rs.ExecutionHeaderValue = execHeader; 
                    execInfo = rs.LoadReport(reportPath, historyID); 
                    String SessionId = rs.ExecutionHeaderValue.ExecutionID; 
     
                    //rs.SetExecutionCredentials(credentials); 
                    //rs.SetExecutionParameters(parameters, "en-us"); 
                    result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs); 
                    execInfo = rs.GetExecutionInfo(); 
     
                    Response.ContentType = "application/vnd.ms-word"
                    Response.AddHeader("content-disposition""attachment; filename=download.doc"); 
                    Response.BufferOutput = true
                    Response.BinaryWrite(result); 
                } 
     
                catch (SoapException err) 
                { 
                    Response.Write(err.Detail.OuterXml); 
                } 
            } 

    ////////////////////////////////////////////////////////////////////////////////////
    Wednesday, February 11, 2009 4:53 AM

Answers

  • I figured it out partially... the DataSource was case sensitive!

    MSCRM_DataSource did not work, but MSCRM_Datasource did...  Wierd since it has the S capitalized in my reportservice manager.

    Next though, I need to figure out how to pass in CRM_FilterText parameters, etc.
    Wednesday, February 11, 2009 5:22 AM

All replies

  • I figured it out partially... the DataSource was case sensitive!

    MSCRM_DataSource did not work, but MSCRM_Datasource did...  Wierd since it has the S capitalized in my reportservice manager.

    Next though, I need to figure out how to pass in CRM_FilterText parameters, etc.
    Wednesday, February 11, 2009 5:22 AM
  • Hello,

    I have bee trying to do something similar .. but generaring a pdf from a custom aspx under ISV folder, but got an error at the moment to write result.

    My question is if you done something similar

    Regards


    Yojan
    Monday, September 21, 2009 4:25 PM
  • i am able to generate report by AnswerProgramatically  from CRM 4.0 by following this article but how can i set CRM_FilterText  to get specific record for entityid plesae help me thanks in advance
    Friday, April 01, 2011 12:23 PM
  • Yohan & Jay,

    I think following plugin will help you:-

    using System;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Text;
    
    using SSR.ReportService2005;
    
    using Microsoft.Crm.Sdk;
    
    using Microsoft.Crm.SdkTypeProxy;
    
    using System.IO;
    
    using System.Web.Services.Protocols;
    
    using SSR.ReportExecution;
    
    using Microsoft.Crm.Sdk.Query;
    
    
    
    namespace SSR
    
    {
    
     public class HelloWorldPlugin : IPlugin
    
     {
    
      public void Execute(IPluginExecutionContext context)
    
      {
    
    
    
       CrmAuthenticationToken token = new CrmAuthenticationToken();
    
       token.AuthenticationType = 0;
    
       token.OrganizationName = "UAT";
    
    
    
       CrmService service = new CrmService();
    
       service.Url = "http://<ServeName>/mscrmservices/2007/crmservice.asmx";
    
       service.CrmAuthenticationTokenValue = token;
    
       service.Credentials = System.Net.CredentialCache.DefaultCredentials;
    
    
    
       WhoAmIRequest userRequest = new WhoAmIRequest();
    
       WhoAmIResponse user = (WhoAmIResponse)service.Execute(userRequest);
    
    
    
       // Get ID of Change Ticket
    
       Guid ChangeID = (Guid)((Microsoft.Crm.Sdk.Key)((Microsoft.Crm.Sdk.DynamicEntity)context.InputParameters.Properties["Target"]).Properties[context.PrimaryEntityName + "id"]).Value;
    
    
    
       TargetRetrieveDynamic target = new TargetRetrieveDynamic();
    
       target.EntityId = ChangeID;
    
       target.EntityName = "new_changeticket";
    
    
    
       // Define the entity attributes (database table columns) that are
    
       // to be retrieved.
    
       ColumnSet cols = new ColumnSet();
    
       cols.AddColumns(new string[] { "new_changeticketid", "new_changeticketnumber" });
    
    
    
       // Create a retrieve request object.
    
       RetrieveRequest retrieve = new RetrieveRequest();
    
       retrieve.Target = target;
    
       retrieve.ColumnSet = cols;
    
       retrieve.ReturnDynamicEntities = true;
    
    
    
       // Create a response reference and execute the retrieve request.
    
       RetrieveResponse response = (RetrieveResponse)service.Execute(retrieve);
    
       DynamicEntity retrievedEntity = (DynamicEntity)response.BusinessEntity;
    
    
    
       //if (retrievedEntity.Properties.Contains("new_changeticketid"))
    
       //{
    
       // retrievedEntity.Properties.Add(new StringProperty("new_requestdocument", ChangeID.ToString()));
    
       //}
    
       //TargetUpdateDynamic targetUpdate = new TargetUpdateDynamic();
    
       //targetUpdate.Entity = retrievedEntity;
    
       //UpdateRequest updateChange = new UpdateRequest();
    
       //updateChange.Target = targetUpdate;
    
    
    
       //UpdateResponse updatedChange = (UpdateResponse)service.Execute(updateChange);
    
       string oName = (String)retrievedEntity.Properties["new_changeticketnumber"];
    
    
    
       //Create new instance of Reporting service
    
       ReportExecutionService rs = new ReportExecutionService();
    
       // rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
    
       rs.Credentials = new System.Net.NetworkCredential("user", "password", "domain");
    
       rs.Url = "http://<ServeName>/reportserver/ReportExecution2005.asmx";
    
    
    
       // Render arguments
    
       byte[] result = null;
    
       // Make sure you use your correct org database name of the following line
    
       string reportPath = "/UAT_MSCRM/SnapshotReport1";
    
       string format = "PDF";
    
       string historyID = null;
    
       string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
    
    
    
       // Prepare report parameter.
    
       SSR.ReportExecution.ParameterValue[] parameters = new SSR.ReportExecution.ParameterValue[1];
    
       parameters[0] = new SSR.ReportExecution.ParameterValue();
    
       //in sql report put parameter like new_entityid = @CTID
    
       parameters[0].Name = "CTID";
    
       parameters[0].Value = ChangeID.ToString();
    
       //parameters[1] = new ParameterValue();
    
       //parameters[1].Name = "ReportMonth";
    
       //parameters[1].Value = "6"; // June
    
       //parameters[2] = new ParameterValue();
    
       //parameters[2].Name = "ReportYear";
    
       //parameters[2].Value = "2004";
    
    
    
       SSR.ReportExecution.DataSourceCredentials[] credentials = null;
    
       string showHideToggle = null;
    
       string encoding;
    
       string mimeType;
    
       string extension;
    
       SSR.ReportExecution.Warning[] warnings = null;
    
       SSR.ReportExecution.ParameterValue[] reportHistoryParameters = null;
    
       string[] streamIDs = null;
    
       ExecutionInfo execInfo = new ExecutionInfo();
    
       ExecutionHeader execHeader = new ExecutionHeader();
    
       rs.ExecutionHeaderValue = execHeader;
    
       execInfo = rs.LoadReport(reportPath, historyID);
    
    
    
       rs.SetExecutionParameters(parameters, "en-us");
    
    
    
       String SessionId = rs.ExecutionHeaderValue.ExecutionID;
    
       Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);
    
       try
    
       {
    
        result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
    
        execInfo = rs.GetExecutionInfo();
    
        // Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);
    
       }
    
    
    
       catch (SoapException E)
    
       {
    
        throw new InvalidPluginExecutionException(E.Detail.InnerText);
    
       }
    
    
    
    
    
    
    
       // Write the contents of the report to an PDF file.
    
    
    
       try
    
       {
    
        FileStream stream = File.Create(@"E:\Snapshot\" + oName + ".pdf", result.Length);
    
        Console.WriteLine("File created.");
    
        stream.Write(result, 0, result.Length);
    
        Console.WriteLine("Result written to the file.");
    
        stream.Close();
    
    
    
        FileInfo pointer = new FileInfo(@"E:\Snapshot\" + oName + ".pdf");
    
        FileStream fileStream = pointer.OpenRead();
    
        byte[] byteData = new byte[(int)fileStream.Length];
    
        fileStream.Read(byteData, 0, (int)fileStream.Length);
    
        string encodedData = System.Convert.ToBase64String(byteData);
    
    
    
        fileStream.Flush();
    
        fileStream.Close();
    
    
    
        //Save pdf report as an attachment to Notes entity
    
        annotation note = new annotation();
    
        note.subject = "Snapshot Report Created Successfully";
    
        note.filename = oName + ".pdf";
    
        note.mimetype = "application/pdf";
    
        note.documentbody = encodedData;
    
    
    
        note.objectid = new Lookup();
    
        note.objectid.type = "new_changeticket";
    
        note.objectid.Value = ChangeID;
    
        note.objecttypecode = new EntityNameReference();
    
        note.objecttypecode.Value = "new_changeticket";
    
    
    
        //Create Notes
    
        Guid annotationId = service.Create(note);
    
    
    
        string path = @"E:\Snapshot\" + oName + ".pdf";
    
        File.Delete(path);
    
    
    
       }
    
       catch (SoapException E)
    
       {
    
        throw new InvalidPluginExecutionException(E.Detail.InnerText);
    
       }
    
    
    
    
    
      }
    
     }
    
    }
    
    
    
    

     


    Regards Faisal
    Friday, July 15, 2011 3:06 PM