Search code examples
ssrs-2012

How to add multiple images to table cell based on db values in SQL Server Report Builder

We are using MS Sql Server Reporting Services to create a report. In a table cell, I am trying to show icons instead of the values.

enter image description here

This image shows the values for "SDG(s) Addressed." The cell can hold 0 to 17 values. We want to show the value's corresponding icon. I can't seem to figure out how to add multiple images into a cell and how to transform the values like "6 Clean Water and Sanitation" into "ID6_Clean_Water_and_Sanitation.jpg".

Can anybody help with this? Thx

Solution

  • I'm fairly certain you cannot add more than a single image to a tablix cell but you could add 17 small cells and then set each to show an image.

    In the following example, I added 5 images called beer, wine, soft, spirits and energy, these are embedded PNGs but you could easily adapt this to form a full file path to the PNG if required.

    I started by adding a dataset as follows.

    DECLARE @t TABLE(ID int, CSVs varchar(100))

    INSERT INTO @t VALUES
(1, 'beer,wine,soft,sports,energy'),
(2, 'beer,wine,energy,sports'),
(3, 'energy,sports,soft')

SELECT * FROM @t

    As you can see the dataset just returns records with an ID and a comma separated list of words, these words match the image file names for simplicity.

    I then added a tablix with 7 columns...

    1. ID
2. CSVs
3 - 7 for each image placeholder

    I inserted an image into each of the cells in columns 3 - 7

    The design looks like this

    enter image description here

    The image properties are set to an expression as follows

    =Split(Fields!CSVs.Value,",").GetValue(0) -- first image
=Split(Fields!CSVs.Value,",").GetValue(1) -- second image
etc..

    The expression above just splits the CSV list and selects the specified index.

    I set the image types to embedded but as I said you could adapt this to generate a file/path to your image if they are stored on a network.

    enter image description here

    The final output looks like this.

    enter image description here

    As you can see there are issues, when the CSVs column does not conain a full set of values the image lookup breaks and shows a cross but you get round this by testing the value and setting the hidden property accordingly, but hopefully this will give you enough to get going.