May 30, 2013 at 12:32 pm
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.
June 10, 2013 at 5:49 pm
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.
June 11, 2013 at 7:01 am
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.
June 13, 2013 at 2:52 pm
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
June 13, 2013 at 3:15 pm
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.
June 13, 2013 at 3:59 pm
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
June 13, 2013 at 10:11 pm
When the image is missing what value is stored in its place?
June 14, 2013 at 12:49 am
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
June 17, 2013 at 7:21 am
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.
June 17, 2013 at 10:17 am
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[/url]), 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
June 17, 2013 at 3:01 pm
You need to use only this in your report:
Hidden= Fields!Picture.Value is Nothing
Have you tried to set the visibility property of the image?
June 17, 2013 at 3:04 pm
Mysorian, I think you may have misunderstood the OP's actual question. It's not that he wants to hide the image if it doesn't exist, but he wants to write a report that tells him who has an image and who doesn't, which he currently can only see by looking at the report with the pictures and seeing who has a red "x".
You're taking a good stab at answering the wrong question 😉
June 17, 2013 at 3:17 pm
Can you access the source of pictures on the file system?
June 17, 2013 at 3:41 pm
Yes, I can access the files and create a file of employee id number to compare outside of SQL. I was hoping for a solution that Report Manager based so our security staff could run the report at any time and see who does not have a badge.
June 17, 2013 at 8:10 pm
If you can access the file system why don't you write a program to access the FileSystemObject and find files which are empty for the badge image field? This way you can get a report independant of SSRS.
Report Manager uses Report Builder to modify the report.
This is just an hypothetical method: Create a linked report with Photo(badge) as the parameter and create two folders Badge and No badge. Using this parameter you may be able to populate the nobadge folder. Report Manager can create linked reports.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply