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.

30 comments:

  1. Thanks for posting this, Jason. I'm always forgetting that not everyone's operating with R2 yet, and the next time we're dealing client who is, this may come in handy!

    ReplyDelete
  2. Thanks!! I have been searching for this solution a long time.

    ReplyDelete
  3. Hi, thanks for the 2005 workaround. However, when you the report in Excel the headers are empty... any ideas/more workaround :) Thanks again!

    ReplyDelete
  4. I just exported it into excel and I am able to see the headers, although in my case, it has become a little smaller. I am sure this can be adjusted by playing around with the width of the columns and the image. Can you check once again if the headers are being exported?

    ReplyDelete
  5. Hmmm... I'm not seeing it. The header row is there but there's no text or image in the cell. Are you seeing text or image? Please remember my SSRS server is 2005. Thanks for looking, I really appreciate your help!

    ReplyDelete
  6. Would you be able to send me the rdl file to jason143@gmail.com? Let me have a look at it.

    ReplyDelete
  7. nice post - but I have a visual studio 2008 and a RDLC and there I can't get further than point 3 - because I don't have all these options!

    HALP PLEASE!

    ReplyDelete
  8. Hi Joe,
    Sorry to say that I have absolutely no idea on the Visual Studio + rdlc part. Maybe you could try asking this in the reporting services forum - http://social.msdn.microsoft.com/Forums/en/sqlreportingservices

    ReplyDelete
  9. Many thanks to Jason for resolving my issue with using image in report headers to rotate text at 270! My report already uses image for the same reason but I used Background Image property to set it up. The issue with this is it does not get exported/rendered in Excel. Headers in Excel appear empty. Also, for those of you using VS 2005, follow Jason's steps outlined here BUT don't use the Image wizard. After dragging an image object to the textbox, exit out of the wizard and manually fill the image properties in the Properties pane (F4). Great job, Jason!

    ReplyDelete
  10. On a side note, if you are experiencing blurry text in method number two (the images generated on-the-fly), use the SetResolution method on the image objects to increase the resolution to e.g. 300 dpi:
    bmpImage.SetResolution(300.0, 300.0)

    Remember to do this for both images created and just after the constructors.

    ReplyDelete
  11. hi jason,
    thanks for the post. i am a newbee...can u pls extend ur help for rotating the entire table instead of a row/col in sql server 2008 r2?

    thanks in advance | bindu

    ReplyDelete
  12. Could you let me know why you would like to rotate an entire table? Wouldn't it make the contents hard to read?

    ReplyDelete
  13. how to rotate textbox in reportviewer (vs2010) to ccw?

    ReplyDelete
  14. Hi Meng,
    Sorry to say that I have absolutely no idea on the Visual Studio + reportviewer part. Maybe you could try asking this in the reporting services forum - http://social.msdn.microsoft.com/Forums/en/sqlreportingservices

    ReplyDelete
  15. AMAZING POST!
    It works very well!
    Very useful!

    ReplyDelete
  16. Thanks for this, saved me a bunch of time.

    ReplyDelete
  17. Thank you.
    You saved me a lot of time.

    ReplyDelete
  18. When I create project for C# .
    I have to do.

    HALP PLEASE!

    ReplyDelete
  19. I am not sure what you mean by project for c#, this workaround has to be done inside the rdl for the reports.

    ReplyDelete
  20. Thanks for the post, this helped me out a lot!

    ReplyDelete
  21. I have a strange problem with rotate270, the text it self is rotated however the font or the display of the font is changed. And this only occur when I have deployed the report and it's rendered i html(report manager). The report looks fine in bids preview. Anyone else have this problem in ssrs 2008R2

    ReplyDelete
    Replies
    1. There is a comment above which asks to set the resolution if you are encountering blurry text. Can you make sure you have done that?

      Delete
    2. That is for the 2005/2008 method. The text looks fine with that method, but my text is quite different when I'm using the writingMode: rotate270 in R2. And I guess that has something to do with the html renderer in RS. It's looking just fine in preview in bids which is using RGDI to render, if I'm not mistaken. You can see some images here http://social.technet.microsoft.com/Forums/en/sqlreportingservices/thread/25978773-b4bf-4081-9d65-23355723a3ef

      Delete
    3. can you try changing the background color of the textbox to white from NoColor/Transparent?

      Delete
  22. Made a few tweaks to allow HTML color codes to be passed in for image background and text colors...

    Function LoadImage(ByVal sImageText as String,ByVal sImageTextMax as String, ByVal sImageFont as String, ByVal sImageColor as String, sFontColor 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(sImageFont, 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)
    Dim MyBGColor as Drawing.Color = Drawing.ColorTranslator.FromHtml(sImageColor)
    '// 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(MyBGColor)
    MyGraphics.TextRenderingHint = Drawing.Text.TextRenderingHint.AntiAlias
    MyGraphics.TranslateTransform(0, iWidth)
    MyGraphics.RotateTransform(270)

    Dim MyFontColor as Drawing.Color = Drawing.ColorTranslator.FromHtml(sFontColor)
    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

    ReplyDelete
    Replies
    1. Thanks Melanie, I love it when posts become more of a discussion rather than a one way communication :) I am pretty sure your code is going to help a lot of people and please do keep posting here if you have got more improvements.

      Delete
    2. its working fine, but headers are not coming fine while exporting to EXCEL.

      Thanks

      Delete
  23. It's Working Bt can't modify the font of image.

    ReplyDelete
  24. Great article!
    also; if you experience troubles with alignment in HTML with Rotate270.
    Set the properties CanShrink ans CanGrow to FALSE.

    ReplyDelete