Pages

Thursday, January 27, 2011

Rotate Text in SSRS

Sometimes we get pampered so much by modern technology that we tend to raise the bar of necessity towards the fringes of luxury. 10 years back, a mobile phone was a luxury in my native place. Today, even school children carry one along with them and most of them take it as granted. Similarly, I had been so used to the rich features of SSRS 2008 R2 that I often forget that this feature never existed in the previous versions. Take this case where one of the forum users had asked me how to rotate text in the matrix column header of a SSRS report as shown below.

Rotate text report

Being used to SSRS 2008 R2, I thought rotating text to 270° was a pretty easy solution but I conveniently forgot that this feature was not available directly in SSRS 2008 and below versions. So I decided to blog down the solution for both SSRS 2008 R2 and other versions. For detailing the solution, I have already created a dataset which will give me the greenhouse emissions of the states in the USA by sector. The requirement is to display all the states in the column header and the optimal way would be to rotate it and display vertically.

(I) SSRS 2008 R2

1) Select the column whose text you want to rotate and press F4 to bring up the properties panel.

Select column to rotate text and press F4 to open properties panel

2) Select the WritingMode property and select the value as Rotate270. (The Vertical selection will rotate the text by 90° instead of 270°)

SSRS WritingMode property

3) Now you can preview the report and the header text should be rotated by 270°.

Report with rotated text header in SSRS 2008 R2

You can also change the horizontal and vertical alignment as needed by selecting the appropriate values in the TextAlign and VerticalAlign properties.

(II) SSRS 2008 and 2005

Since this option is not available out of the box for older versions, we will have to use custom code in our report.

1) Open the report properties and paste the following custom code in the Code tab of report properties.

(Note : I am in no way a VB.net coding expert and the following piece of code was collected by me quite some years ago and have lost track of it’s origins; hence could not attribute it to the original author. If you are or know the author, please let me know so that I can link it to you/the original post. I have also made some modifications and if any of you guys feel that it can be improved, please paste the improved code in the comments sections so that it can be used by others)


Function LoadImage(ByVal sImageText as String,ByVal sImageTextMax as String)
sImageTextMax= sImageTextMax.PadRight(40)
Dim iFontSize As Integer = 10 ‘//Change this as needed
Dim bmpImage As New Drawing.Bitmap(1, 1)
Dim iWidth As Integer = 0
Dim iHeight As Integer = 0


'// Create the Font object for the image text drawing.
Dim MyFont As New Drawing.Font("Arial", iFontSize, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point)
'// Create a graphics object to measure the text's width and height.
'Graphics(MyGraphics = Graphics.FromImage(bmpImage))
Dim MyGraphics As Drawing.Graphics = Drawing.Graphics.FromImage(bmpImage)
'// This is where the bitmap size is determined.
iWidth = MyGraphics.MeasureString(sImageTextMax, MyFont).Width
iHeight = MyGraphics.MeasureString(sImageTextMax, MyFont).Height
'// Create the bmpImage again with the correct size for the text and font.
'bmpImage = New Drawing.Bitmap(bmpImage, New Drawing.Size(iWidth, iHeight))
bmpImage = New Drawing.Bitmap(bmpImage, New Drawing.Size(iHeight, iWidth))
'// Add the colors to the new bitmap.
MyGraphics = Drawing.Graphics.FromImage(bmpImage)
MyGraphics.Clear(Drawing.Color.Green)
MyGraphics.TextRenderingHint = Drawing.Text.TextRenderingHint.AntiAlias
MyGraphics.TranslateTransform(0, iWidth)
MyGraphics.RotateTransform(270)

MyGraphics.DrawString(sImageText, MyFont, New Drawing.SolidBrush(Drawing.Color.White), 0, 0)
MyGraphics.Flush()
Dim stream As IO.MemoryStream = New IO.MemoryStream
Dim bitmapBytes As Byte()
'Create bitmap
bmpImage.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg)
bitmapBytes = stream.ToArray
stream.Close()
bmpImage.Dispose()
Return bitmapBytes
End Function

The above code converts Now your code window should look like this

Custom code


2) Go to the References tab and add a reference to the System.Drawing assembly.


Adding reference to Drawing assembly


3) Click OK to save and close the report properties. Then drag and drop an image item from the toolbox to the column header which will popup the Image Properties window as shown below.


Adding image to column header


Make sure that the


(a) image source is set as Database,


(b) MIME type is set as image/jpeg


(c) Use this field has the following expression


=Code.LoadImage(Fields!State.Value,"District of Columbia")


In the above expression, Fields!State.Value is the field value whose text should be rotated (in this case - State). It can be substituted with any string value also. The second parameter is ideally for the longest string in the values for Fields!State.Value and is just used so that all the text values start at the same position and for general aligning purposes. In the above example, I have directly hardcoded it as “District of Columbia” but you can change it such that the field with the maximum length is passed.


4) Click on OK and then preview the report. Now you should be able to see the report as shown below


Rotated text with SSRS 2008, 2005 and below versions


In case you are not getting the report properly, make sure that


(a) Image properties should be set as Original Size


Image property set as Original size


(b) there should be a grouping on the column where the image is placed by the Field that you want to group by, else only one value would be displayed.


Group by State field


I am sure there is more scope for improvement in the VB.NET code like adding a code such that the text can be left, right or centre aligned, reducing the padding in the left hand side, etc. Feel free to modify and improve the codeNerd smile.

Thursday, January 13, 2011

How to Choose the Right Business Intelligence Technology to Suit Your Style

In my roles as a technical lead and a consultant, I have been often posed with this question – which is the best BI tool for this set of requirements and what is the best mode of presentation for this? I learned to answer this question from the various experiences I had in my projects since the past few years. Well, I am glad to say that answering this question should not be much of an issue even for a less experienced person with the latest whitepaper that has been released by Microsoft - Choose the right business intelligence technology to suit your style (white paper)

How to Choose the Right Business Intelligence Technology to Suit Your Style

The title might be a bit deceptive as it talks only about the Microsoft BI suite, but hey, did you really expect something else to come out of Microsoft? Overall, it is a good read for anyone who wants to understand which MSBI tool to use where and when. Read and get enlightened.

Tuesday, January 11, 2011

Extracting Images from SSRS reports

What do you do when you want to re-use an image in one of those old forgotten reports that was developed by that weird guy who left your company an year ago? No matter how much you hate or abuse that guy now, it is not going to do you any good as he didn’t save those images in a source or version control mechanism. And you are left tearing your hair apart thinking on how to get those same images in your new reports. This was exactly the question that was asked by one of my blog readers and I thought of blogging down the solution for the benefit of others who might be in the same situation.

I have created a sample report using a beautiful image from Riez.Info and some statistics from the Peace Alliance.

Sample Peace Report

Suppose you just have this report in your hand and no access to the original source images. There might be two scenarios that I can think of which are given below along with their solutions on how you would want to use the images :-

1) Re-use the image in a new report

For External and Database images, you can reuse the code (which is already working in the old report) and that should be fine (read How to: Add an Image (Reporting Services) on how to add images). However, for Embedded images, we will have to follow the steps below:-

1) Open the report in Business Intelligence Development Studio (BIDS). Then right click on the report in the solution explorer of BIDS and select view code.

View Code in solution explorer

2) Search for <EmbeddedImages> and copy all the code between <EmbeddedImages> and </EmbeddedImages>

Find embedded images

3) Now click on the new report in the solution explorer and view it’s code. Paste the code copied from the step above in a relatively same position (or above </Report> if it is a new report).

Paste copied code

4) Save it and close it. Now open the report once again and you should be able to see the images embedded in your new report and ready to use.

Images in new report

2) Save the images from a report to the hard drive

I had always thought the easiest way to do this would be to view the deployed report in Internet Explorer, right click on the image and select ‘Save image as’ option. Somehow, it didn’t work when I tried it out now. So the next easiest solution is given below:-

1) View the deployed report in Internet Explorer and select the Export as Excel option.

Export to excel

2) Save and open the Excel file and then copy the image that you need into an image editing tool like Microsoft Paint. Save it and you have the image with you Smile

Excel report extract

Note

I found this blog link by Adam Pooler where he has coded an utility which does the job of extracting embedded images from SSRS reports. This would definitely make your life easy if you have lots of images and also, the image would not suffer any changes which can occur due to improper copying and pasting techniques which can happen in the technique mentioned above. Recommended.