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 2014 version as the basis for our SSRS reports. 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.
The results of this setup are posted in the column to the right of the display column.
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.
The formats all provide us with the following 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.
As illustrated below, that format produces some interesting 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.
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.
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, “[ ]”.
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.
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.
As shown next, we can see the results of using both the FormatDateTime and formatting the placeholder.
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.
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
- Numeric Function Formats Pre-defined – https://msdn.microsoft.com/en-us/library/y006s0cz(v=vs.90).aspx
- Numeric Function Formats user defined – https://msdn.microsoft.com/en-us/library/4fb56f4y(v=vs.90).aspx
- Date Time Functions user defined – https://msdn.microsoft.com/en-us/library/73ctwf33(v=vs.90).aspx
