Picture alignment in SQL Reporting Services

Posted on Posted in Database, Development, Reporting Services

alignmentEver wondered how to dynamic align pictures in SQL Server Reporting Services? I have for sure needed it often. Untill now I’ve allways answered no to requests for this.

The solution can be used when creating reports in SQL Server Reporting Services with dynamic pictures in different sizes based on variables inside the report.
In this article the pictures will be aligned right and these are stored in a FileTable on a SQL 2012 instance – you can also do this with other types of picture storage in Reporting Services.

Well here it goes:

For starters you’ll need a parameter to hold the pictures variable as follows – the default values etc. you need to define to with your own data:

 

Next add  the relevant pictures either as a datasource (this example) or loaded directly to the reports image folder. Remember to map your parameter to the query.

 

Now add a reference in the report to the system.drawing object in Windows.
A note for this: If the report is going on a Sharepoint site for production, you should choose the ver 2.0 as Sharepoint (for now) only supports up to .NET 3.5.

 

Then – and there comes the geeky parts – first you need to find the maximum width in the collection of pictures. This is to be the width of the picture placeholder. In this case I’ve set it to 190 pixels.
Place the placeholder with the right border where you want the picture to be aligned.

The picture inside the placeholder is here defined by a server storage as descriped earlier. The variable is used directly in the datasource.

 

In the placeholder for the pictures properties you’ll find the padding area – in this example we’ll need to define an expression for the left padding. (we are moving the picure from left to right in order to align it right).

The expression calls the system.drawing object added to the report with this content:

=CSTR(round(190-(System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(First(Fields!file_stream.Value, "Kommunelogo"),Byte()))).Width*0.75),0)-1) + "pt"

It might look hairpulling, but do not worry – it’s not that hard to decode.

First of all we need to make sure that the expessions results is a string (CStr), next the number for the padding needs to be with zerro decimals (round).

Then I must find the missing pixels rom the maximum width of 6,2632cm or 190 pixels – therefore ‘190-‘

We call the system.drawing with this: System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(First(Fields!file_stream.Value, “Kommunelogo”),Byte()))).Width

The first bit of this should be out of the box for old .NET developers – for all us other people: accept the thing as it is or read the documentation. The interesting thing there is the bit ‘Byte().Witdh’ which tells the command to return the width of the picture. The fast readers allready now knows how to change this to return the height…

The output of the ‘Byte().Width’ is the pictures width in pixels. This gives us the result ‘190-picture_width’. This number then needs to be converted to points which is needed for the indent. 1px = 0,75pt.

Just to be sure that I do not push the pictures right border out of the placeholders right border I extract 1 point.

The end is just to be sure that I pass the indent in the right syntax ‘”pt”‘.

And that’s it!!

Now no matter how wide the picture is, the expression in the indent controls the picture to be aligned to the right in the placeholder.

You can also use this feature to center your images in the placeholder. That’s pure mathematical changes to the above – I’ll let you sort that one out for your selfes :).

 

 

 

8 thoughts on “Picture alignment in SQL Reporting Services

  1. Hi Brian!
    I’ve added the reference to the System.Drawing to the report properties.
    But I’ve not understood where I can put the string that you’ve used for the alignment. In the “espression value” of the image?
    Because I get an error: FromStream is invalid.

    Can you help me please?
    Thanks
    Martina

    1. Hi Martina,
      First, I’m sorry fo rmy late reply.

      The expression needs to be placed in the expression-field for the padding (left padding) property of the image.
      The expression calculates the width of the image and based on this number the padding from the left side in order to move the image to the right of the placeholder.

      Let me know if it works, or else just write me again. 🙂

      Best regards
      Brian

  2. Hi Brian,
    I’ve added the reference and created the parameter but since my logos aren’t stored in a table, currently they are external, I’m confused at this step: ” Next add the relevant pictures either as a datasource (this example) or loaded directly to the reports image folder. Remember to map your parameter to the query.”
    I have a folder in my project for client logos and that’s where I’m pulling my images from do I map my parameter to that?

    Currently I have the value of my placeholder image set to an Iif statement which is taking the value from a parameter and returning the image associated with that value.

    1. Hi Jacque,
      Images can as you allready know be loaded to the report in several ways. You are using a file-folder, I was using a file-table from SQL Server. Either way the image source needs to be configured. The way you are doing it is quite fine. The mentioned parameter is used because I dynamic asign the specific picture based on a report parameter.

      I hope this helps you further – or else you are welcome to write again.

      Best regards,
      Brian

      1. Hi Brian, Thanks for the response. BTW, how you came up with this is genius – so clever.

        My next question is about how to modify the expression (copied in below) to fit how I’m loading my image(s)? Client logos are all over the place size/dimension wise and to have them align cleanly every time would be awesome.

        =CSTR(round(190-(System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(First(Fields!file_stream.Value, “Kommunelogo”),Byte()))).Width*0.75),0)-1) + “pt”

        Thank you,
        Jacque

        1. Hi Jacque

          I was given the task to make sure that all pictures in a given report was allways algined right and in the same size – so google was my friend for a while i order to find and engineer this solution.
          Thanks for the feedback 🙂

          The only thing you should need to change is the name of your picuture source – in this case the text ‘Kommunelogo’ (it’s a danish word) needs to be changed to fit your pictures name in SSRS.
          If you need the picture to be of another size you can also change the number 190 to fit your needs in ref to width.
          Then if you set the properties on the picture-holder to scale the picture the pictures will always look the same in ratio no matter how wide or narrow you make them,

          I hope this helps – or else you are welcome to come back to me again 🙂
          Glad to help

          Best regards,
          Brian

  3. I tried this. It works when the report is previewed in report designer, but when deployed to the report server it does not align to the right as expected. Can you help please?

    1. Hi Nicholas,

      Sorry for my late reply. I’ll sure help you.

      Are the assemblies registered on the server providing the SSRS service?
      That is where I would look first.
      If you want to mail me – please do: brianbonk(at)gmail.com

      Thanks,
      Brian

Leave a Reply

Your email address will not be published. Required fields are marked *