Performance issue using formatting in MDX

ActivityMonitor

To make a long story short – Do not use formatting in MDX when using results for Reporting Services. You’ll only get performance issue using formatting in MDX.

Here’s a little tip for all who works with MDX and gets stuck on the performance.

Sometimes when you work on MDX queries the performance is very slow. This even though the resultset is only a few rows. (I know that the dataintegrity also is a role player here…).

But but, I’ve come up with a little finding.

If you have used formatting directly in your MDX query in order to get the whole thing done from the SSAS servers power, then it’s actually a lot faster to get Reporting Services to do the formatting for you.

I didn’t now this, it’s possible that many of you allready did. I know that I’ve learned something new today.

And yes, this is also a matter of hardware, network, setup, configuration etc. etc. But the bottom line is Reporting Services are a whole lot faster to render the formatting than compared with Analysis Services and MDX.

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

en_USEnglish
da_DKDanish en_USEnglish