Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Report Showing Missing Images Expand / Collapse
Author
Message
Posted Thursday, May 30, 2013 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 3:09 PM
Points: 18, Visits: 60
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.
Post #1458354
Posted Monday, June 10, 2013 5:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:39 PM
Points: 40, Visits: 79
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.
Post #1461821
Posted Tuesday, June 11, 2013 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 3:09 PM
Points: 18, Visits: 60
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.
Post #1462074
Posted Thursday, June 13, 2013 2:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 262, Visits: 778
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
Post #1463319
Posted Thursday, June 13, 2013 3:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 3:09 PM
Points: 18, Visits: 60
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.
Post #1463327
Posted Thursday, June 13, 2013 3:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 262, Visits: 778
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

Post #1463337
Posted Thursday, June 13, 2013 10:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:39 PM
Points: 40, Visits: 79
When the image is missing what value is stored in its place?
Post #1463377
Posted Friday, June 14, 2013 12:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 5:39 PM
Points: 40, Visits: 79
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
Post #1463402
Posted Monday, June 17, 2013 7:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 3:09 PM
Points: 18, Visits: 60
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.
Post #1464135
Posted Monday, June 17, 2013 10:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 262, Visits: 778
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
Post #1464257
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse