Showing posts with label SSRS report. Show all posts
Showing posts with label SSRS report. Show all posts

Thursday, December 26, 2013

Printing Entity Images on Reports in Dynamics CRM 2013


In our earlier post Now Store Images in CRM 2013 we discussed at length about the new Image attribute added to CRM 2013. It included steps to programmatically read/write to this field. The next step being the ability to include the image attribute in the reports.

This seemingly appears a simple task to include images just as the images were picked up earlier from the notes entity and displayed on the report. But when we tried to do the same with the image attribute, we found that the image attribute is actually not stored the same way as the attachments.

We could use the following code to read the image data using the CRM SDK

string binaryImageQuery =
                "<fetch mapping='logical'>
                <entity name='lead'>
                <attribute name='fullname' />
                <attribute name='entityimage' />
                </entity>
                </fetch>"

EntityCollection binaryImageResults = _serviceProxy.RetrieveMultiple(new FetchExpression(binaryImageQuery));

This would return the image data in binary format that we could then read in a byte array.

But when you execute the same fetch query using BIDS for report designing, you receive the following results

 
This appears all good but when you set the control source of the image control on the form to the entityimage attribute, it returns an error about the data in the field not being in correct format.

Upon further review of the results we found that the System.Byte[] did not mean the binary data but only the string “System.Byte[]”.

The Fetch query when executed using the RetrieveMultipe API as shown above returns binary data. But when the same query is executed using the ExecuteFetch API call, the results are returned in xml format and therefore the image attribute returns only a string “System.Byte[]” not the actual image binary data in Base64 string.

ExecuteFetchRequest fetch = new ExecuteFetchRequest();

fetch.FetchXml = binaryImageQuery;

_service.Execute(fetch);

As a result of this, reports designed using FetchXML (reports in CRM Online) would not be able to include images on the report.

On-Premise installs can still add the images in the report using SQL queries.

select con.FullName, con.Parentcustomeridname,con.entityimage  from FilteredContact con

Bind the image control in the report to entityimage attribute

The result would be

Hope this helps anyone trying to include images on reports.

Sunday, December 16, 2012

Display more than 2000 characters in SSRS report


We would like to share one of our experiences with SSRS reports. One of the reports we were building required displaying of notes that could hold more than 2000 characters.
 
 Including a text box worked fine for most of the records but we observed that the ones that had more than 2000 characters reported error. To show the entire content of the notes in SSRS reports you can try the following workaround:
 
Add a textbox with the following expression

 =IIF(Len(Fields!description.Value) >= 2000,Left(Fields!description.Value,2000) ,Left(Fields!description.Value,2000))
 
This will show the first 2000 characters.
 
For the next 2000 add a blank row and include a textbox with the following expression
 
=IIF(Len(Fields!description.Value) >= 2000, cstr(Fields!description.Value).Substring(2000), "")
 
If the content included is less than 2000 characters in the first place itself, then to hide the blank row add the following expression to the visible property
 
=IIF(Len(ReportItems!txtDescription.Value) >= 2000,false,true)
 
Note ReportItems!txtDescription refers to the same textbox that we are hiding
 
This way we were able to increase the display limit to 4000 characters to increase it further add another text by using the same logic as above.

 
Hope this helps!

 

Monday, September 10, 2012

Phone Formating in SSRS report


Many a times there had been requirements to format the CRM phone number in the SSRS report in following format.


We tried to achieve this by creating 12 text boxes and in each textbox we applied the below logic in the expression.

= IIF(Fields!telephone1.Value.toString().Length>=1, GetChar(Fields!telephone1.Value.toString(),1), “”)

It works successfully when the length of phone number is equal to 10. But it fails when the length of phone number is less than 10 and you will get an error while running this report.

To resolve this issue we found a work around for this.

First you need to format the data using the SQL query and PAD the spaces into the field. As given below.

LEFT(ISNULL(telephone1, '') + '          ', 10)

The above statement will format the telephone1 attribute. If the user enters errorneuos or junk data into the telephone1 field like “99”, then the above statement will return the value “99        ”, i.e. 99 + additional 8 spaces . And in the textboxes expressions you just need to write the following expressions without checking the IIF condition.

= GetChar(Fields!telephone1.Value.toString(),1)
= GetChar(Fields!telephone1.Value.toString(),2)
= GetChar(Fields!telephone1.Value.toString(),3)
= GetChar(Fields!telephone1.Value.toString(),4)


Hope this helps!