vrijdag 31 oktober 2008

Microsoft CRM 4: Show image on account report

Recently I ran into a quest to display an image on a report and thought it would not be that difficult. So I did some research and came to the following example.

First of all, we have to download the right report to edit. Because it is a subreport we first navigate to the reports on the reportserver http://localhost/Reports and press the "show details" button in order to display all hidden items as well.

In my particular case I needed to navigate to the subfolder 4.0 to se all Crm reports and opened the {7334011c-66c1-dc11-b67a-0003ffbb057d} file.

Next, when I chose "Properties", I downloaded the .rdl file to the system by pressing the "edit" link.

Next, I have opened visual studio, created a new "Report server project" and added the downloaded report.

For testing purposes, I needed to change the datasource database to the right database to test the report in visual studio. There are other ways to do this.

Next, I added a column to the ds_BasicProfile dataset by adding: (select top(1) documentbody as Image from filteredannotationwhere mimetype = 'image/pjpeg'and objectid = @AcctID ) as image, to the query.

The design needed to be changed as well, so I moved the left columns a bit to the bottom and added an image and modified the image's value to =System.Convert.FromBase64String(First(Fields!image.Value, "ds_BasicProfile")) to show the image representation of the stored text.

After checked the result, modified the database back to it's original value, I have uploaded the report and replaced the existing one, and ran a report and enjoyed the result!