// Dynamic picture alignment in SQL Reporting Services

Ever wondered how to do dynamic picture alignment in SQL Reporting Services? I have for sure needed it often. Until now I’ve always 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 :).