Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Text Box Orientation


By:   |   Read Comments (5)   |   Related Tips: > Reporting Services Formatting


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


Problem

If a SQL Server Reporting Services report has many columns with text descriptions in a horizontal direction then those headings will need significant space in the report. There are situations where report data will not fit in a single page and the users will have to scroll horizontally to see the data. Unfortunately, this is very irritating and users may ask you to modify the report in such a way that report data can fit in a single page. How can we modify the report and what approach you should follow in this case?

Solution

Proper orientation of a Textbox saves a lot of space in a report layout; if a report has 10 columns with text descriptions in a horizontal direction then it will take more space in the report as compared to the same report having those columns in a vertical direction.

This tip assumes that you have previous real world work experience building a simple SSRS Report. In this tip I will describes how to set the orientation of Textbox in a report. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

In SQL Server Reporting Services 2008R2 and above, we have out of the box functionality to change the Textbox orientation, but this functionality was not available in previous version of SSRS (2005 and 2008). When we change the Textbox orientation we also need to adjust the height and width of the Textbox, so that rotated text can be accommodated. To change the Textbox orientation we are going to use the WritingMode property of the Textbox. A Textbox can be oriented as follows.

  • Horizontal - Default text orientation.
  • Vertical - Rotate the text from top to bottom.
  • Rotate270 - Rotate the text from bottom to top.

Step 1: Create a New SQL Server Reporting Services Report

Let's create a sample report for demo purposes. Please change the report name to Change_TextBox_Orientation. You can refer to the below image.

Adding New Report

Step 2: Create SSRS Data Source and Dataset

I have already created data source connection to the AdventureworksDW2008R2 sample database and let's create the below dataset for this sample report.

Main Dataset: This dataset will be used for the report body and it returns 9 fields.

SELECT 
FirstName, LastName, MaritalStatus, Gender, EmailAddress, YearlyIncome, 
TotalChildren, NumberChildrenAtHome, HouseOwnerFlag
FROM  DimCustomer

After creating the Main dataset Report, the Data Pane looks like the below image.

Report Data Pane After Adding Dataset

Step 3: Add Tablix to the SSRS Report

Let's add a Tablix for data viewing purposes. I will pull all 9 data fields from Main dataset into the Tablix. After adding the Tablix, the sample report looks like the below image.

Adding Tablix

This sample report is showing all columns with horizontal text orientation. As you can see from the below image, users have to scroll horizontally to view the entire report data. Generally, users do not like to have to scroll horizontally in reports. User always want to see the entire report data in a single page.

Now our challenge is to display the entire report data in a single page. To achieve this we will change the Textbox orientation, but which Textbox orientation should be changed? Actually we have to look at the report and find out which columns have white space after displaying the data. If you will look below, you will see the Marital Status column has white space after displaying the data. This is because the Marital Status column is only one character (i.e. M and S), but the Marital Status column header Textbox is using more space to display the Marital Status. Due to this, we are wasting space in the report layout. We can save some report space if we rotate the Marital Status header column Textbox either vertically or by 270 degrees. In the below image I have used blue rectangles to highlight all columns which have white space after displaying the data. In the next step we will change the Textbox orientation to reduce the report width.

Report Preview Before Text Rotation

Step 4: SQL Server Reporting Services Text Box Orientation

Let's change the Textbox orientation of the column header for the Marital Status. To change the orientation of the Textbox, select the column header for the Marital Status data field as shown below.

Maritial Status Textbox Selection

Press the F4 key to get the property window.

Maritial Status Textbox Properties Window

Go to the WritingMode Property and select the Rotate270 option, you can refer to the below image.

Maritial Status Textbox WritingMode Property

After the above changes, you can see the column header for Marital Status is shown with the text from bottom to top. Please adjust the width and height of the Textbox to accommodate the rotated text.

Maritial Status Textbox After Rotation

Now we will also see how text looks in a vertical orientation. To change the Textbox orientation to vertical, select the Gender column header Textbox and press the F4 key. In the properties window set the WritingMode property to Vertical. Please adjust the width and height of the Textbox to accommodate the rotated text. You can see from the below image the column header for Gender is showing text from top to bottom, but it is difficult to read the text in the vertical direction so the Rotate270 orientation is preferable.

Gender Textbox After Rotation

Let's change the Textbox orientation for Marital Status, Gender, Total Children, Children At Home and House Owner Flag column header to Rotate270. You can also change the text alignment for the TextAlign property. After the changes my report looks like the below image.

Report After Textbox Proper Orientation

Step 5: Report Preview

We have completed all the necessary changes, now let's preview the report. As you can see from the below image, all 9 columns fit into a single page and the users do not need to scroll the report horizontally.

Report Preview

Conclusion

This approach is useful in the following conditions:

  • When a report has numerous columns and due to the number of columns, we need to scroll horizontally while previewing the report.
  • When multiple Tablixes are needed in the same report. The textbox orientation can be changed to manage the space in the report layout.
  • When you have a requirement to show data for few columns with the vertical or 270 degree orientation in the report.
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, November 04, 2015 - 10:49:36 AM - Michael Back To Top

A very good tip.  Your selection of Rotate270 over Vertical is an interesting one we have struggled with.  What we have found is that Vertical works better for us and I think it has to do with being American.  I know that sounds odd, but it seems there is a difference in the way vertical text is written on the spine of books depending on your location.  The spine tilting section of this wiki explains it pretty well.  The English write top to bottom and the other European countries write bottom to top.  It seems to be a personal preference here and it may be a regional preference as well, or at least a good way to break ties.

 


Friday, July 03, 2015 - 4:14:19 AM - Kiran Back To Top

Hi Ghanesh,

Thank you so much for this post. Really useful.

I followed the steps but the vertical allignment seems to be working fine in IE. Is there any way to get this working in other browsers also like Chrome, Firefox and Safari?


Regards,

Kiran


Thursday, July 02, 2015 - 11:04:41 AM - Melinda Back To Top

I am using VS2013 and your tip regarding rotating the box is brilliant and worked find in VS2013 preview. However, when I deploy to the reporting server, the rotated column headings disappear. 

Whats up with that? Have I done something wrong or is sqlserver I am running with? 

Thanks..follow ALL your tutorials. Well written. precise, clear..great job.


Thursday, July 02, 2015 - 7:04:57 AM - Ghanesh Back To Top

Gourisankar,

Thanks :)

Keep learning. 


Thursday, July 02, 2015 - 2:16:49 AM - Gourisankar Back To Top

Wooow, nice article. Please keep posting such nice articles.


Learn more about SQL Server tools