BLOG

MY PERSONAL JOURNEY

Dynamic picture alignment in SQL Reporting Services

alignment

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 :).

Will not get stuck in date formatting again

Date formattingYou’ve all been there, you’ve all banged your head against the monitor just because you could not remember that code for the specific date formatting you needed in SQL. I know I have.

I SQL 2012 this is no longer such a headache to remember all those codes – JAY!

Ex. from SQL 2008 R2 and older:

SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] --Italian
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] --German
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] --USA

And with the codes specific for each country format needed.

Now in SQL 2012 we are all free to try to memorize those codes for each country – all you need now is the countrys letters and language. For those of you who are familiar with Reporting Services ‘locale’ setup, this is easy-peasy.

There the same formats in SQL 2012:

SELECT FORMAT(getdate(), N'd', N'lt-lt'), --Italian
SELECT FORMAT(getdate(), N'd', N'de-de'), --German
SELECT FORMAT(getdate(), N'd', N'en-us') --USA

I know that my everyday coding just got a whole lot smoother and easier.

The complete documentation from Microsoft is here.

en_USEnglish
da_DKDanish en_USEnglish