Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

SQL Server Reporting Services Formatting and Placeholders


By:   |   Last Updated: 2015-11-05   |   Comments (2)   |   Related Tips: > Reporting Services Formatting

Problem

In SQL Server Reporting Services, how can I format a partial section of a text box or separate a text box in parts or placeholders?

Solution

Over the past few years, I have had this particular question surface every so often with most SSRS report developers never realizing they can customize the formats of individual items within a textbox. This customization requires the use of several not very well known or used formatting functions. The functions cover dates, numbers, currency, and percent. Also included are general formatting expressions that covers any available VB formatting function. In addition to these formatting functions, there is also the availability of an even lesser known object called placeholders. A placeholder allows you to segment out the parts and pieces of a textbox and actually format them individually. In either case, you need to understand which pieces and parts you would like to highlight, change, or adjust.

We will use the Adventure Works databases as the basis for our SSRS reports. The 2014 versions of the regular and data warehouse databases are available on Codeplex: https://msftdbprodsamples.codeplex.com/releases/view/125550. Once you download and install the SQL Server databases, we will subsequently use SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio to develop reports. You can download SSDT-BI from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

Using SSRS Format Functions

When using a format function, we need to surround the characters or fields with the format function. So for instance, if we have a textbox that contains text / characters and then a date time field that we want to display a particular way, our function would look something like = "For transactions through: " & FormatDateTime(NOW(), DateFormat.ShortDate). As you can see the basic syntax of the expression function ("value to be format", style or syntax to be used in the formatting). In the below screen print, the FormatDateTime syntax follows this method. The results of this setup are posted in the column to the right of the display column.


format Date

The results of this setup are posted in the column to the right of the display column.


format date results

In the next screen prints, you can see how to apply formats for time, number, and currency parts of a textbox. After the value to be formatted is the arguments to be used to specify the how the format should be applied. For the DateTime version, we are specifying to use the Short Time format.

For the FormatNumber, FormatCurrency, and FormatPercent, we actually can specify the following arguments (in order of being specified, but all are optional):

  • Number of digits after the decimal to display
  • Include leading digits (True/False)
  • Use parentheses for negative numbers (True/False)
  • Group digits by comma (True/False)

The only difference is the FormatCurrency adds a leading currency symbol where as the FormatPercent adds a trailing % sign and multiplies the results by 100. You will notice in the below screen prints, we request 4 places to the right of the decimal for the FormatNumber function and no places for the FormatCurrency.


FormatTime

Format Number

format currency


The formats all provide us with the following results.

Format Results

In addition to the format for specific functions, SSRS also provides us with what I call a general or generic format function that can be used to display any of the specific formats while also allowing you to display formats that may not be available in the any of the above noted format functions. As long as the format specifications are available in the VB library, SSRS can use them. I provided a link to some of the formatting options in the next steps area at the end of this tip. As shown below, the Format function is applied to the TotalDue field with special formatting of "-numbers and "$$" at the end. I know this formatting example is very odd, but it shows you the flexibility that can be achieved if needed for a special formatting display.


Format Only

As illustrated below, that format produces some interesting results.


format only results

As you can see it is actually pretty easy to customize some of formatting option for just pieces and parts of a textbox. I do find using this option to be quickly helpful when dealing with dates and currency fields. However beyond these quick uses, the coding can become more cumbersome and that is where placeholders come in.

Using Placeholders in SSRS Reports

Placeholders, in my opinion, provide a superior method to customize the formatting and display of individual pieces of a textbox. Placeholders act very similar to a textbox within a textbox (something you can literally do, but that is a topic for another tip).

In order to add a placeholder, we need to right mouse click on a textbox and select the Create Placeholder option, as shown below.

Ceate Placeholder

Now we can edit the placeholder and add a placeholder Label or name which will appear in the textbox body once saved as illustrated next. Also notice on the left side of the below window, we have many tabs which allow us to customize a place holder. We will discuss several of these below.

Placeholder Name

Now when we look at the text box, the placeholder is labeled with [TimeDetails]; notice in the below illustration that a placeholder label is surrounded by square brackets, "[ ]".


label

We next need to add the actual value to appear in the placeholder as shown in the next screen print. For our example, you can see we are adding the Now() function. The value settings are exactly the same that you would use when adding a value to a text box.


place holder properties value

Using the Now functions actually returns the current date and time. For this example we only want to return the time. Thus we now can customize the format via the formatting window which is very similar to a text box formatting option. As displayed in the next screen print, we can easily set our desired format without having to know any VB Code.


placeholder time format

As shown next, we can see the results of using both the FormatDateTime and formatting the placeholder.


placeholder results

Additionally, we can adjust various other components of the placeholder properties including alignment, font, and actions. All these items are shown below; we could use the action option, for instance, to have a link to a URL that the report user would only need to click on that one placeholder item to open the URL instead of the entire box.

alignment

font
action

Conclusion

When working with SSRS formatting individual textboxes, many report developers think your formatting options are an all or nothing proposition. However, that is not the case, you can easily use the Format functions of FormatDateTime, FormatNumber, and Format Currency to set a field or other character (text, numbers, dates, etc.). Furthermore, SSRS also provides a more comprehensive method to customize a specific portion of a textbox through the use of placeholders. These placeholders provide a way to segment out a set of characters into its own object which in turn can be named, formatted and set to a specific value.

Next Steps


Last Updated: 2015-11-05


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, November 10, 2015 - 3:14:54 PM - Darren Stanger Back To Top

Thanks Scott! This is one of those things that will make reports design much more meaningful. It's weird how the topic of Placeholders wasn't covered in RS training that I attended in the past.


Thursday, November 05, 2015 - 4:30:33 AM - leandro sardinha Back To Top

thanks for this tip, very helpful

keep posting


Learn more about SQL Server tools