CODEWITHSUNDEEP
ssrs-2012
Eric
Eric

Reputation: 1212

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

Upvotes: 0

Views: 1217

Answers (2)

ketla
ketla

Reputation: 31

It is possible if you store the logos in the database as varbinary (e.g. 0x424C216E4400....). Then set the Logo Field in the code based on which database value you have. I don't know what language you are using, but for C# you can do this. An example of how the tablix cell XML could look like:

<TablixCell>
  <CellContents>
    <Image Name="Logo2">
      <Source>Database</Source>
      <Value>=Fields!Logo.Value</Value>
      <MIMEType>image/png</MIMEType>
      <Sizing>FitProportional</Sizing>
      <Visibility>
        <Hidden>=IsNothing(Fields!Logo.Value)</Hidden>
      </Visibility>
      <Style>
        <Border>
          <Style>None</Style>
        </Border>
      </Style>
    </Image>
    <ColSpan>4</ColSpan>
  </CellContents>
</TablixCell>

And then declaring the logo as:

public byte[] Logo { get; set; }

And simply set Logo to the database value.

var company = ctx.GetCompany(...);
data.Logo = company.Logo;

Upvotes: 0

Alan Schofield
Alan Schofield

Reputation: 21683

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.

Upvotes: 1

Related Questions