I'm working on setting up a report in SSRS. Our report is somewhat flexible, and I'm looking to dynamically select an image for a cover page based on a parameter.
Here's what I have:
In our Reports db I have an image table set up. To keep things simple, lets just say I have three columns:
ImageName, ImageType, and Image.
Name and type are both varchar while ImageType is varbinary. I've uploaded the images we plan on using as well as their names and associated types to the table.
In my report I have a Data Source and a Dataset for bringing in the above table and columns.
I also have a parameter called "ImageName." I've set this parameter up to use "Image" (from the table described above) as its value field and the label field is set up for "ImageName."
On the report itself I have an image ... control? Report element? Not sure what to call it, but I've set up an image. The image source is set to "Database" and the MIME type uses the lookup function to pull the appropriate image type from the Dataset based on the Parameters!ImageName.Label argument - and that part works a treat - I'm returning the proper MIME type each time.
What doesn't work, and I would expect should work, is setting the image itself. For the "Use this field" field under "Select the image source:" I've tried both =Parameters!ImageName.Value with no success as well as a lookup based on the ImageName.Label from the images Dataset. Neither seem to work.
When I try using the parameter value, I'm getting the error
[rsInvalidDatabaseImageProperty] The value of the ImageData property for the image 'ImageControlName' is "=Parameters!ImageName.Value", which is not a valid ImageData.
I get essentially the same error message from the lookup method, it just swaps the lookup code for the Parameters!ImageName code above.
If I attempt to check the type returned by the lookup above I simply get an error. For the parameter if I check the type I get System.String so I decided to print that out and apparently the value of Parameters!ImageName.Value is System.Byte[] for some reason.
I can get an image to display using the First() function, but I need to be able to select the image. I'm guessing I may have to filter. First() has a return type of System.Byte[], which seems reasonable. I just don't understand why my parameter is returning the type of the image data column as a string rather than the data from the image column itself.
So how do I get this working? Is filtering my Dataset my only option? Or is there a way to get the parameter working or to get some function that will allow me to select the appropriate image?
Reason for your error is because of the data types returned by Parameters vs First.
For the Parameters data type returned is of type text and which is not valid image data type hence the error.
For First function it returns the data type based on type of expression and it returns the right type of data type for image and displays the image.
Per Microsoft Parameter Data Type:
A report parameter must be one of the following data types:
- Boolean. The user selects True or False from a radio button.
- DateTime. The user selects a date from a calendar control.
- Integer. The user types values in a text box.
- Float. The user types values in a text box.
- Text. The user types values in a text box.
When available values are defined for a parameter, the user chooses values from a drop-down list, even when the data type is DateTime.
Per Microsoft First function data type:
Return Type
Determined by the type of expression.
You are correct to get the right image you need to filter the dataset.
Now use the following expression to get the correct image data.
=Fields!Image.Value