Pages

Sunday, April 18, 2010

SSRS reports using embedded images

Ok, this post is dedicated to all those zillion questioners asking how to use embedded images on your report based on a condition. Yet again, I will be making use of my favourite dataset from AdventureWorks – Order Count by Product (people who are following me would understand that this is the same dataset that has been featuring in many of my blogs. I admit it guys, I am too lazy to make a new dataset. On second thoughts, this doesn't look  that bad, considering I have created a re-usable component in the form of my dataset. Woah, high fives!)

For making this report, I am going to make use of three images mainly:-

a) Home                  b) Thumbs Up        c) Thumbs Down

Home                 thumbsup               thumbs_down

The home image would just be used in the top left of the report, which could be used for hyper linking to the home page. The other 2 images would be used in a matrix, and would be displayed based on a condition.

(P.S. : This report is made in SSRS 2008, although the steps are quite similar in SSRS 2005 also)

To make the report, follow the steps below:-

1) On the Report Data panel, go to Images and right click on it. Click on Add Image and select the images you need to embed by giving the source path.

Add Image

2) Once you have uploaded the 3 images, drag and drop Home image from the report data to the body of the report and press ok on the popup that appears (This is one method to bring embedded images in your layout)

3) Make a matrix underneath the Home image like shown in the figure below

Tablix

4) Now the matrix will be having Products on it’s rows and order counts on it’s columns. Now let us add the Thumbs up image for the row which has the highest value of Order Count and the Thumb down image for the row which has the least value for Order Count. For this, go to the toolbox and drag and drop the Image tool to the vacant column in the right end of the matrix (This is the second method to bring embedded images in your layout). You would be getting the Image Properties popup on doing this. Click on the expression symbol in the image name.

Image Properties

5) Once you get the Expression editor open, enter the following expression:-

=iif(Fields!Order_Count.Value=max(Fields!Order_Count.Value,"DataSet1"),"thumbsup",iif(Fields!Order_Count.Value=min(Fields!Order_Count.Value, "DataSet1"),"thumbs_down",""))

where thumbsup and thumbs_down are the names of the images embedded in the report. Notice that they are enclosed in double quotes. Also notice that if none of the conditions are true, empty string would be displayed.

6) Save the report and deploy it. Now you should be able to preview the report in internet explorer.

Image Visibility report

This is all it takes to embed images in your reports and use them. You can use them for a wide variety of purposes, though I usually use them for showing indicators or for using them as hyperlinks. However, if you want to store images in your database and use them in your reports, you might want to check out this post.

21 comments:

  1. Hi,

    Thanks for your post. I have an image and on that image i have some text boxes. This is image is visible on a condition. When the image is hidden, the report looks good. but when the image is visible, all the text boxes are moving down to next page. The main thing is this is happening only in report server 2008 and not in 2005. Please reply me @ srikanthreddyn143@yahoo.com

    Thanks
    Sri

    ReplyDelete
  2. Hi Sree,

    Overlapping objects are not supported when SSRS reports are rendered in formats like html. So if you are placing textboxes on top of an image, it will either move down or to the right of the image and this behaviour is the same in both SSRS 2005 and 2008.

    ReplyDelete
  3. jason,

    Thanks for your reply. I have 2 instances which are using same rdl file. one instance has report viewer pulling report from SSRS 2005 and other instance pulls from SSRS 2008. report that is pulled from SSRS 2005 looks good as how it should be. For the report pulled from 2008, text boxes and lables moved below the image. But when we print the report, it prints fine.

    Thanks
    Sri

    ReplyDelete
  4. Hi Sree,

    I had sent a mail to your id asking for the rdl, hope you received it. Meanwhile, why dont you put the image as a background image and then use the text? I think this approach should work always

    ReplyDelete
  5. Jason,

    I had sent an email to your gmail acnt u specifiedin the email.

    Sri

    ReplyDelete
  6. Hi Sri,

    I saw your report and the correct way of designing this should be having the VOID image as the background image of the report. If you would notice, you would get a warning when you preview this report in both SSRS 2005 and 2008 that overlapping report items are not supported. This is the reason why it gets moved.

    It would be having the same functionality if the image is used as the background image of the report.

    ReplyDelete
  7. Jason,
    Just wondering if you are still actively viewing this topic - I have an issue with a background image and multiple columns on a report.

    ReplyDelete
  8. Sure I am, would love to have your name and issues here

    ReplyDelete
  9. Hi Jason, I am writing a SSRS blog as well. Please visit my blog and maybe we can link our blog together. http://ssrsdeveloper.blogspot.com/

    Thanks,
    Chan

    ReplyDelete
  10. Does anyone know how to drilldown on a ssrs2008 Map, to diplay an image? (i.e. to display a .jpg file of a person)

    thanks for any help!

    ReplyDelete
  11. 1) You need to make a report which will have a parameter for the persons name, and the report will display the photo based on the name parameter (something like given in this link - http://road-blogs.blogspot.com/2010/04/ssrs-reports-using-database-images.html)
    2) Then you need to have the map report, which will have a relation between the region name and the person name. Go to action, Go to Report and give the name of the report you created in step 1. Also pass the persons name as the parameter.
    (the steps are given in this link - http://road-blogs.blogspot.com/2010/12/drill-down-in-ssrs-map-reports.html)
    3) Now you should be having a drill down on ssrs 2008 map which will display the image of a person when clicked. You can use javasvript if you want the image report to be opened in a new window

    ReplyDelete
  12. Hello, thanks a lot for a detailed post. I did everything you mentioned and still couldn't get the image to display. The difference between your example and mine is that I have 2 datasets and only 1 dataset is attached to the tablix. However, the field based on which the image should display is in the other dataset. PID is the common field in both datasets and i get the value using on Lookup function. Can you please help? THanks!

    ReplyDelete
  13. It is not possible to display an image using Lookup function (because it is not the field name that is returned, but the evaluated result which might be varbinary. This would throw up an error). Best way to go forward is to
    1) Join your 2 datasets into 1 using PID as the join condition
    2) If you can't do the above, make the dataset with the image as the primary one, and lookup all the other fields (which would not be an image). This way you would not want to lookup an image.

    ReplyDelete
  14. Hi Jason,
    I am using the image in my report which sends an email to my cilents ,when i click on image.I have used 'mailto:' for the same.
    The problem, i face is,i want to include a hyperlink in the message text which should change based on the parameters that report use to filter the results. How can i do this? I tried using javascript:window.navigtaion but in vain.

    ReplyDelete
  15. Jason,
    I learnt how to add image on report page. Yours only blog useful.
    -Vinod Andani

    ReplyDelete
  16. I read your article and very confident to use

    Hemant

    ReplyDelete
  17. Hi Jason, I need help with custom markers.
    I have a Gantt chart that displays different marker styles based on the condition.
    My report needs 3 marker types: Bar, up triangle and down triangle .
    The first 2 are supported in BIDS, downtriangle marker is not avliable in BIDS. so I have embeded a image in to my report for the down triangle.
    At a time I will be displaying only one type of marker on the report, when I embed a image the report is always displaying the image, I want to show either image or bar or up triangle based on the condition
    For ex: If Type is null, Bar Marker
    If Type = 1, Uptraingle Marker
    If Type = 2, embeded image down triangle

    how do I conditionally hide the image? Please help me out.

    ReplyDelete
    Replies
    1. Easy way out is to embed three images (get image for the other 2 also) and use the expression, should work. I don't think you can mix default markers with images.

      Delete
    2. Thanks Jason. I have tried with 2 embeded images, the third marker style I wanted on my report is the bar and it has to expand based on the task start and end dates(Gantt chart). I used as follows in the image expression:
      IIF(type=1, "uptriangle embeded image", IIF(type=2, "down traingle embeded image "," "))
      In the back ground style of the series I have selected bar, so when the type is null it is showing the bar otherwise embeded images.

      Delete
  18. Uhmmmmmmm... I just realized that it is throwing a warning message when I have " " (null) in the image expression, how to fix this?

    ReplyDelete
  19. I figured it out, I have tried with Nothing keyword instead of " " . Not throwing any warning messages now.

    ReplyDelete