CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
Tommen
Tommen

Reputation: 123

How can I generate dynamic images in SSRS 2008 based on the results set of a SQL query?

I am using the following SQL query in SSRS:

select s.name, s.studentid, x.section_number
from students s
inner join sections x
on s.studentid=x.studentid
where section_number = :Section

This query produces a list of 10-20 students depending on which 'section' (class) is entered into the :Section parameter. In addition to this, I also want to produce a picture of each student dynamically that matches up with the list.

I have every student's picture on a webserver, and if there's just one value for student then I could use a parameter for StudentID and set the image expression as follows:

="http://website.com/img/" & Parameters!StudentID.Value & ".jpg"

However, I need to have a picture for every student that is returned in the results. Is this possible in SSRS?

Upvotes: 0

Views: 217

Answers (1)

Stefan Steiger
Stefan Steiger

Reputation: 82246

Yes it is certainly possible. The problem is you need to have those pictures in the database, in order to return them in the result-set.

You can write a CLR stored procedure that goes and fetches the images, then inserts them into a table in the database. Or a stored procedure that calls a windows/web service that does that.

I have a art-report that does that:

SELECT 
         [...]
        ,
        ISNULL
        (
            T_File.Thumbnail 
            ,
            CAST
            (
                0xff[... default image byte-array too large for SO...]
                AS varbinary(MAX)
            ) 
        ) AS RPT_Thumbnail
    FROM T_Art

By the way, you can get the image mime-type from the file's extension in SQL

Upvotes: 0

Related Questions