Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Showing Missing Images


Report Showing Missing Images

Author
Message
dedeod
dedeod
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 61
I have a report that shows badge photo images for staff that is working using an external image source for the badge photos. I would now like to make a report that shows anyone who is missing a badge (i.e. the image on the other report shows a red X instead of their badge photo). How can look for missing images? The external path to the image uses their id number with a jpg extension.
mysorian
mysorian
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 101
Keep images on a site and give the site reference. For example I can keep images on a SharePoint server and plug them into a report with a URL in the SQL Server database. I am not sure how you are doing now.
dedeod
dedeod
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 61
Thanks for the reply! The images are stored on a network drive that the badge system automatically stores them to. I am not having any problems getting the images to show on my report. My question is how can I filter my report to show me the images that are not showing (i.e. the staff that have missing badge photos.) Right now if they do not have a photo to show it shows a red X in place of the photo so basically I want a report showing me everyone that has a red X photo.
eric.muller
eric.muller
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 1306
Well, without knowing how you actually implemented your report, and what you're ULTIMATELY trying to achieve, it seems to me this doesn't need to be a visual SSRS report.

I'm guessing you probably just want a list of names that don't have a picture.

It seems that doing something like this (roughly sketched out) would get you to the same place:

1) Do a query of your system for all the employees with their employee IDs
2) Use Powershell/Command line/SSIS to get a list of all the pictures from the directory they live, and split out the ID number from the file extension.
3) compare the full list versus the picture list in SQL/Excel to find the employee IDs without pictures.
4) You win?

If that's not right, maybe you could post a little more info and get a better response.

Cheers,
EJM
dedeod
dedeod
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 61
I was hoping to have a report that our security department could run from Report Manager to show which employees do not have a security badge. Basically this report would list print everyone from the other report that has a Red X displaying for their badge image.
eric.muller
eric.muller
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 1306
Okay, that being the case I think my recommendation still stands. If you don't know SSIS, this would be a great first project to get you started: read the file names from a directory and write them to a table, which you can compare to your main system's "all employees" data to find the missing folks. The SSIS package could be run periodically to keep the data in the "existing pictures" table fresh, and the output of this query could easily go to a SSRS report.

THIS might be helpful, but I'm sure some Google-fu with "SSIS read filenames in directory" type queries will also get you where you need to go.

Good luck!

~EJM
mysorian
mysorian
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 101
When the image is missing what value is stored in its place?
mysorian
mysorian
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 101
The following will show images if they are present and show empty spaces if they are not:

1. Insert an image control from main menu into the data cell

2. Set its value by using the expression:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),105))

3. Set the images visibility property as:
Hidden= Fields!Picture.Value is Nothing

2 assumes that the images originate from a Northwind database where images are stored as Pictures with datatype image
dedeod
dedeod
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 61
Thank you for the response! When I try the solution above using the expression example given, none of my pictures are showing up. I currently have the following in the value field and the pictures will show that are available:
="file:\ocvm-webext\Pictures\"&Fields!STF_ID.Value & ".jpg"

I tried using your example and changed it to:
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String("file:\ocvm-webext\Pictures\"&Fields!STF_ID.Value & ".jpg"),105))

With this example none of my pictures show up.
eric.muller
eric.muller
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 1306
Mysorian's suggestions is basically just another way of showing the picture files, which your report is already correctly doing. I'd really recommend that you some how export the list of your directory to a file (using a fully automated SSIS solution, or just a one-shot text import), import that into a database, and use some SQL to compare against your complete employee directory.

If you want a report that is a list of names of people without pictures, don't worry about working with your current "show the pictures" report. That picture file in that directory you're reading from could be a picture, or it could be a resume file, or a mandatory employee poem, or any other file with the employee ID on it.

Cheers,
EJM
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search