Using a LIKE clause within string.Concat()

user_17190524 0
Viewed 30

I am trying to display image files pulled from a server. In SSRS I have managed to do this before but recently to add security, the location was changed so each file is in its own folder which has a random string for a name.

Before I used this:

=string.Concat("/files/reports/images/",Fields!FormTutorInitials.Value, ".jpg")

Which pulled the file from its location. Each file is named after the tutors initials, so Tutor who's initials are ABC would pull their picture from /files/reports/images/ABC.jpeg. Now however, that file sits in a location like this:

/files/reports/images/rgdg5w-gtreh65-hts-56hehj-5rs333/ABC.jpeg

Is there a way to insert a LIKE clause into the code? I have tried =string.Concat("/files/reports/images/",LIKE *Fields!FormTutorInitials.Value, ".jpg") but it does not accept it.

visual-studio-2019 reporting-services visual-studio
1 Answers

I'm assuming you are using the expression as the image source here...

This answer assumes that the filename only exists once in the folder/subfolders. It uses xp_cmdshell so you must be aware of the security implications of enabling this if it is not already enabled. (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option?view=sql-server-ver15)

The way I would do this is to get a list of the files into a table variable then append this to your dataset query

As an example.

DECLARE @t TABLE(TutorName varchar(50), TutorInitials varchar(20))
INSERT INTO @t VALUES ('Dave Smith', 'DS'), ('Sally James', 'SJ'), ('Rob Jones', 'RJ'), ('Jane Bloggs', 'JB')

DECLARE @dir TABLE(filename varchar(1024))
INSERT INTO @dir 
    exec xp_cmdshell 'dir "\\myFileServer\ImgesRootFolder\*.png" /b /s'


SELECT DISTINCT 
        t.*,
        MIN(d.[filename]) OVER(PARTITION BY t.TutorName, t.TutorInitials) as ImageFileName
FROM @t t  
    JOIN @dir d on d.[filename] like '%' + t.TutorInitials + '.png'

The first table is the 'tutors' table with names and initials, the second table contains the results of a recursive DIR command against \\myFileServer\ImgesRootFolder\*.png

Then finally all we do is join then togther.

In this example, I get the MIN() filename value so be aware that is for exmaple, DS.PNG appeared in more than one folder, only the first (alphabetically) would be returned.

The result of the query is something like this.

TutorName   TutorInitials   ImageFileName
Dave Smith      DS          \\myFileServer\ImgesRootFolder\randomfolder12345\ds.png
Jane Bloggs     JB          \\myFileServer\ImgesRootFolder\randomfolder68548\jb.png
Rob Jones       RJ          \\myFileServer\ImgesRootFolder\randomfolder96325\rj.png
Sally James     SJ          \\myFileServer\ImgesRootFolder\randomfolder74125\sj.png

You can now just use the ImageFileName column as the imagesource value

user_17753890
Related