10 Common SQL Server Reporting Services Challenges and Solutions

Challenges/Solutions

Original Article at : SSRS
Thanks to Mr. Ryan Duclos

SSRS offers a range of different reporting techniques and technologies, to cater for the reporting needs of all levels of users, from the chief executives, to business analysts, to operational staff. Their reporting needs range from simple, tabular ad-hoc reports, to parameterized, linked or snapshot reports, to complex drill-down and drill-through multi-level reports.

Following is the list of some of the challenges I have encountered while developing such reports using Reporting Services 2000/2005. In the sections that follow, I will cover each challenge individually, providing insight into what may cause the difficulty, alongside a possible solution.

1. Horizontal Tables: Calendar Reports
   
2. Select "ALL" Query Parameter option
   
3. Multiple Sheets in Excel
   
4. Excel Merged Cell Issues
   
5. Blank Pages
   
6. Vertical Text
   
7. Report Data in Header/Footer
   
8. Are you missing XML/CSV data on your exports?
   
9. Template Reports
   
10. Using the Reporting Services database

A ZIP file containing samples of the reports detailed in this article is available to download , try out and amend to suit your own needs.

Convert lead to contact, account and/or opportunity programmatically

public void Convert(Guid leadId, string entityName)
{
SetStateLeadRequest qualifier = new SetStateLeadRequest();
qualifier.EntityId = leadId;
qualifier.LeadState = LeadState.Qualified;
qualifier.LeadStatus = -1;
Service.Execute(qualifier);
//lead is qualified, but not converted to contact yet, if you check the leads from user interface, you will not be able to see this lead in open leads, but you will not see it in contacts.

InitializeFromRequest req = new InitializeFromRequest();
req.EntityMoniker = new Moniker(); // this is the very thing that does the job.
req.EntityMoniker.Id = leadId;
req.EntityMoniker.Name = "lead";
req.TargetEntityName = entityName; //contact for our example req.
req.TargetFieldType = TargetFieldType.All;
InitializeFromResponse rps = (InitializeFromResponse)Service.Execute(req);
//now the lead is converted to a contact, and you can see it in contacts.
Guid entityId = service.Create(rps.Entity);
}

Displaying inactive records in associated view

Below customization is taken from http://ronaldlemmen.blogspot.com/search/label/customization
Thanks to Mr. Ronald.




In some situations you do want to show the inactive records in an associated view as well. My fellow MVP George Doubinski wrote an article about how to solve this by using a plugin:
http://crm.georged.id.au/post/2008/03/07/Displaying-inactive-records-in-associated-view.aspx.

In this post I will show that there are more routes to Rome. I've looked into the option of editing the query which is being executed against CRM. This seemed to be more easy then expected. What I did is:
- Export Customizations for the specific entity
- Open the XML file with a text editor
- Look for the section ""
- Look for the saved query with the localized name of the associated view
- In that saved query look for the filter that is specified in the columnset
- Remove the filter
- Save the Customizations file
- Import the Customizations file
- Publish the Customizations

Here's an example:







....




fullname
createdon
statuscode
subject
leadid
createdon







....


....


....



Keep in mind that you do add the attribute StateCode or StatusCode to the associated view as well, so that end users can see which records are current and which are inactive.

One of the locations that this is useful, is when you are looking into relationships. The out of the box entity does allow you to select party 1, party 2, role 1 and role 2, but no real extensibility options. You could recreate that entity and add useful attributes like startdate and enddate. As soon as the end date has passed, then the record can be deactivated by using workflow. The fact that a person had a professional relationship with a company is information that you do want to keep visible, especially in the associated view.

CRM Javascript: Loop through all elements

var iLen = crmForm.all.length;
for (i = 0; i < iLen; i++)
{
o = crmForm.all[i];
switch (o.tagName)
{
case "INPUT":
case "SELECT":
case "TEXTAREA":
case "IMG":
case "IFRAME":
if (o.id != "leadqualitycode")
{
o.disabled = true;
}
if (o.IsDirty)
{
alert("The value of " + o.name + " has changed.");
}

break;
default:
break;
}
}


More help can be taken from :
http://blogs.infinite-x.net/2007/11/21/disabling-all-fields-on-a-crm-form/

CRM Javascript: Refreshing parent Crm Form

Sometime, it is required to refresh a parent CRM Form from an ASP.Net web page, which is being used as an Iframe application:

private void ReloadWindow()
{
StringBuilder builder = new StringBuilder();
builder.Append(“<script language="JavaScript">”);
builder.Append(“\r\n”);
builder.Append(“if (“);
builder.Append(“(window.opener != null) &&”);
builder.Append(“(window.opener.parent != null) &&”);
builder.Append(“(window.opener.parent.document != null) &&”);
builder.Append(“(window.opener.parent.document.crmForm != null)) {“);
builder.Append(“\r\n”);
builder.Append(“var parentForm = window.opener.parent.document.crmForm;”);
builder.Append(“\r\n”);
builder.Append(“parentForm.Save();”);
builder.Append(“\r\n”);
builder.Append(“window.opener.document.location.replace(window.opener.location);”);
builder.Append(“\r\n”);
builder.Append(“}”);
builder.Append(“\r\n”);
builder.Append(“self.focus();”);
builder.Append(“</script>”);
this.Response.Write(builder.ToString());
}

CRM SDK : Upload/Download an Attachment to Notes

Upload an Attachment
The following code example demonstrates how to upload an attachment to a note programmatically.

// Now create the annotation object.
annotation note = new annotation();
note.notetext = "This is a sample note";
note.subject = "Test Subject";
note.objectid = new Lookup();
note.objectid.type = EntityName.account.ToString();

// Sets the note's parent to the newly created account.
note.objectid.Value = createdAccountId;
note.objecttypecode = new EntityNameReference();
note.objecttypecode.Value = EntityName.account.ToString();

// Create the note.
Guid createdNoteId = service.Create(note);

#region Setup Additional Data Required for this Sample

// Now convert the attachment to be uploaded to Base64 string.
// This will create a doc file in the current folder of executable.
string currentPath = System.Environment.CurrentDirectory.ToString();
TextWriter tw = new StreamWriter(currentPath + "\\Crm" + createdNoteId.ToString() + ".doc");
// Write a line of text to the file.
tw.WriteLine("This document is for testing an attachment upload feature of CRM 4.0.");
tw.Close();

#endregion

// Open a file and read the contents into a byte array.
FileStream stream = File.OpenRead(currentPath + "\\Crm" + createdNoteId.ToString() + ".doc");
byte[] byteData = new byte[stream.Length];
stream.Read(byteData, 0, byteData.Length);
stream.Close();

// Encode the data using base64.
string encodedData = System.Convert.ToBase64String(byteData);

// Now update the note.
annotation updateNote = new annotation();
updateNote.annotationid = new Key();
// Set the Note ID that is being attached to.
updateNote.annotationid.Value = createdNoteId;
updateNote.documentbody = encodedData;
updateNote.filename = "Crm" + createdNoteId.ToString() + ".doc";
updateNote.mimetype = @"application\ms-word";
service.Update(updateNote);



Download an Attachment
The following code example demonstrates how to download an attachment programmatically:


string attachid = "{61BB601D-C43F-4738-BD1F-AD22DC8E7F0E}";
string objecttypecode = "1070"; //SaleLiteratureItem
string url = "http://mycrm/Activities/Attachment/download.aspx?AttachmentType=" + objecttypecode + "&AttachmentId=" + attachid;
System.Net.WebClient myWebClient = new System.Net.WebClient();
myWebClient.Credentials = System.Net.CredentialCache.DefaultCredentials;
myWebClient.DownloadFile(url,"C:\\myfile.txt");