Rotate Text in SSRS

How-to-Choose-the-Right-Business-Intelligence-Technology-to-Suit-Your-Style4
How to Choose the Right Business Intelligence Technology to Suit Your Style
January 13, 2011
Page-totals-of-current-SSRS-report_t
Display Current Page Total in Footer
February 7, 2011
Show all

Rotate Text in SSRS

Rotate-text-report_thumb2

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 FunctionThe 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.

32 Comments

  1. Claire says:

    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!

  2. Deepan says:

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

  3. Anonymous says:

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

  4. Jason Thomas says:

    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?

  5. Anonymous says:

    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!

  6. Jason Thomas says:

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

  7. Joe says:

    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!

  8. Jason Thomas says:

    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

  9. Anonymous says:

    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!

  10. Anonymous says:

    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.

  11. Anonymous says:

    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

  12. Jason Thomas says:

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

  13. Meng Vireak says:

    how to rotate textbox in reportviewer (vs2010) to ccw?

  14. Jason Thomas says:

    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

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

  16. Anonymous says:

    Thanks for this, saved me a bunch of time.

  17. Anonymous says:

    Thank you.
    You saved me a lot of time.

  18. Anonymous says:

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

    HALP PLEASE!

  19. SQL_Jason says:

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

  20. Anonymous says:

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

  21. Anonymous says:

    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

  22. Melanie says:

    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

    • SQL_Jason says:

      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.

    • teja j says:

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

      Thanks

  23. Girish Kumar says:

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

  24. STJ says:

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

  25. Unknown says:

    Hi Jason thanx for the good article,

    While exporting reports to excel clients are not able to do caliculations in excel as they are merging with headers…

    Any help on merging headers and cells of report result while exporting to excel ???

  26. Steve says:

    Just had to use this on a SQL2014 deployment. Rotated Text wasn’t showing up in IE11, was fine in Chrome (50.0.2661.102) , go figure. Of course rendering is always a bundle of laughs when trying to achieve cross browser compatibility.

    I am using Melanies variation so I can control the forecolor and backcolor.

    Anyway this worked on IE but didn’t show up in Chrome, so no further on.

    I fiddled with various settings to get it working. I added some padding around the image so the text centred better, amended the code to render Png not Jpeg and amended the MIME TYPE accordingly. None of which seemed to make much difference.

    I then altered the sizing property of the Image to Autosize. This fixed it straight away.

    I did have an Action on the image to link to another report, but this didn’t seem to make any difference when I removed it.

    Just some feedback in case anyone else hits these issues.

    And a huge thank you to both of you.

Leave a Reply

Your email address will not be published. Required fields are marked *