SQL Server Reporting Services Formatting and Placeholders

By: Scott Murray   |   Comments (2)   |   Related: > Reporting Services Formatting

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?

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

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

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.

  • 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

sql server categories

About the author

MSSQLTips author Scott Murray

Comments For This Article

Related articles.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Tutorial: Format text (Report Builder)

  • 14 contributors

In this tutorial, you practice formatting text in various ways in a Reporting Services paginated report. You can experiment with different formats.

After you set up the blank report with the data source and dataset, you can pick the formats you want to explore. The following illustration shows a report similar to the one you create in this tutorial.

Screenshot of a formatted Report Builder report.

In one step, you make a mistake on purpose so you can see why it's a mistake. Then you correct the mistake to achieve the desired effect.

Estimated time to complete this tutorial: 20 minutes.

Requirements

For information about requirements, see Prerequisites for tutorials (Report Builder) .

Create a blank report with a data source and dataset

Create a blank report.

Start Report Builder either from your computer, the Reporting Services web portal, or SharePoint integrated mode.

The New Report or Dataset dialog box opens.

If you don't see the New Report or Dataset dialog box, on the File menu > New .

In the left pane of the Getting Started dialog box, verify that New Report is selected.

In the right pane, select Blank Report .

Create a data source

In the Report Data pane, select New > Data Source .

If you don't see the Report Data pane on the View tab, check Report Data .

In the Name box, enter: TextDataSource

Select Use a connection embedded in my report .

Verify that the connection type is Microsoft SQL Server, and then in the Connection string box enter: Data Source = <servername>

The expression <servername> , for example Report001, specifies a computer on which an instance of the SQL Server Database Engine is installed. This tutorial does not need specific data; it just needs a connection to a SQL Server database. If you already have a data source connection listed under Data Source Connections , you can select it and go to the next procedure, "To create a dataset." For more information, see Alternative ways to get a data connection (Report Builder) .

Select OK .

Create a dataset

In the Report Data pane, select New > Dataset .

Verify that the data source is TextDataSource .

In the Name box, enter: TextDataset.

Verify that the Text query type is selected, and then choose Query Designer .

Select Edit as Text .

Paste the following query into the query pane:

In this tutorial, the query already contains the data values, so that it does not need an external data source. This makes the query quite long. In a business environment, a query would not contain the data. This is for learning purposes only.

Select Run ( ! ) to run the query.

The query results are the data available to display in your report.

Add a field to the report design surface

If you want a field from your dataset to appear in a report, your first impulse might be to drag it directly to the design surface. This exercise points out why that doesn't work and what to do instead.

Add a field to the report (and get the wrong result)

Drag the FullName field from the Report Data pane to the design surface.

Report Builder creates a text box with an expression in it, represented as <Expr> .

Select Run .

You only see one record, Fernando Ross , which is alphabetically the first record in the query. The field doesn't repeat to show the other records in that field.

Select Design to return to design view.

Select the expression <Expr> in the text box.

In the Properties pane, for the Value property, you see the following (if you don't see the Properties pane on the View tab, check Properties ):

The First function is designed to retrieve only the first value in a field.

Dragging the field directly to the design surface created a text box. Text boxes by themselves aren't data regions, so they don't display data from a report dataset. Text boxes in data regions, such as tables, matrices, and lists, do display data.

Select the text box (if you have the expression selected, press ESC to select the text box), and press the DELETE key.

Add a field to the report (and get the right result)

On the Insert tab of the ribbon, in the Data Regions area, select List . Choose the design surface, and then drag to create a box that about 2 inches wide and 1 inch tall.

Drag the FullName field from the Report Data pane to the list box.

This time Report Builder creates a text box with the expression [FullName] in it.

This time the box repeats to show all the records in the query.

Select the expression in the text box.

In the Properties pane, for the Value property, you see the following:

By dragging the text box to the list data region, you display the data that is in that field in the dataset.

Select the list box and press the DELETE key.

Add a table to the report design surface

Create this table so you have a place to put hyperlinks and rotated text.

On the Insert tab, go to Table > Table Wizard .

On the Choose a dataset page of the New Table or Matrix wizard, select Choose an existing dataset in this report or a shared dataset > TextDataset (in this Report) > Next .

On the Arrange fields page, drag the Territory , LinkText , and Product fields to Row groups , drag the Sales field to Values , then select Next .

Screenshot that shows how to arrange text fields in Report Builder.

On the Choose the layout page, clear the Expand/collapse groups check box so you can see the whole table, then select Next .

Select Finish .

The table looks OK, but it has two Total rows. The LinkText column doesn't need a Total row.

Screenshot that shows two totals in the Report Builder report.

Select the Total cell in the LinkText column, then hold down the SHIFT key and choose the two cells to its right: and the empty cell in the Product column and the [Sum(Sales)] cell in the Sales column.

With those three cells selected, right-click one of those cells and choose Delete Rows .

Screenshot that shows how to delete rows from a Report Builder report.

Now it has only one Total row.

Screenshot that shows one total in the Report Builder report.

Add a hyperlink to the report

In this section, you add a hyperlink to text in the table from the previous section.

Right-click in the cell containing [LinkText] , and select Text Box Properties .

On the Action tab, select Go to URL .

In the Select URL box, select [URL] , then choose OK .

The text doesn't look any different. You need to make it look like link text.

Select [LinkText] .

On the Home tab > Font , select Underline , and change Color to Blue .

The text now looks like a link.

Screenshot that shows hyperlinked text in the Report Builder report.

Select a link. If the computer is connected to the Internet, a browser opens to a Report Builder Help article.

Rotate text in the report

In this section, you rotate some of the text in the table from the previous sections.

Select in the cell containing [Territory].

On the Home tab in the Font section, select the Bold button.

If the Properties pane isn't open, on the View tab, select the Properties check box.

Locate the WritingMode property in the Properties pane, and change it from Default to Rotate270 .

When the properties in the Properties pane are organized into categories, WritingMode is in the Localization category. Be sure you have selected the cell and not the text. WritingMode is a property of the text box, not of the text.

Screenshot of the Territory field in the Report Builder report.

On the Home tab > Paragraph section, select Middle and Center to locate the text in the center of the cell both vertically and horizontally.

Select Run ( ! ).

Now the text in the [Territory] cell runs vertically from the bottom to the top of the cells.

Screenshot that shows the Territory values rotated 270 degrees.

Format currency

Select Design to switch to design view.

Select the top table cell that contains [Sum(Sales)] , hold down the SHIFT key, and choose the bottom table cell that contains [Sum(Sales)] .

On the Home tab, go to Number group > Currency button.

(Optional) If your regional setting is English (United States), the default sample text is [ $12,345.00 ]. If you don't see an example currency value in the Numbers group, select Placeholder Styles > Sample Values .

Screenshot of the Sample Values option in the Report Builder.

(Optional) On the Home tab in the Number group, select the Decrease Decimals button twice to display dollar figures with no cents.

Select Run ( ! ) to preview the report.

The report now displays formatted data and is easier to read.

Screenshot that shows the reformatted report build format report.

Display text with HTML formatting

On the Insert tab, select Text Box , and then on the design surface, choose and drag to create a text box under the table, about 4 inches wide and 3 inches tall.

Copy this text and paste it into the text box:

Drag the lower edge of the text box so all the text fits. You notice the design surface gets larger as you drag.

Select all of the text in the text box.

Right-click all of the selected text and choose Text Properties .

This property is of the text, not the text box, so in one text box you could have a mixture of plain text and text that uses HTML tags as styles.

On the General tab, under Markup type , select HTML - Interpret HTML tags as styles .

The text in the text box is displayed as a heading, paragraph, and bulleted list.

Screenshot that shows formatted text in a Report Builder report.

Save the report

You can save reports to a report server, SharePoint library, or your computer.

In this tutorial, save the report to a report server. If you don't have access to a report server, save the report to your computer.

Save the report on a report server

From the Report Builder button, select Save As .

Select Recent Sites and Servers .

Select or enter the name of the report server where you have permission to save reports.

The message "Connecting to report server" appears. When the connection is complete, you see the contents of the report folder that the report server administrator specified as the default location for reports.

In Name , replace the default name with a name of your choosing.

Select Save .

The report is saved to the report server. The name of the report server that you're connected to appears in the status bar at the bottom of the window.

Save the report on your computer

Select Desktop , My Documents , or My computer , and then browse to the folder where you want to save the report.

There are many ways to format text in Report Builder. Tutorial: Create a free form report (Report Builder) contains more examples.

Related content

  • Report Builder tutorials
  • Format paginated report items (Report Builder)
  • Microsoft Report Builder in SQL Server

More questions? Try asking the Reporting Services forum .

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

reporting services formatdatetime

FormatDateTime Function

Returns an expression formatted as a date or time.

FormatDateTime( Date [ , NamedFormat ] )

The FormatDateTime function syntax has these arguments:

The NamedFormat argument has the following settings:

Facebook

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

reporting services formatdatetime

Microsoft 365 subscription benefits

reporting services formatdatetime

Microsoft 365 training

reporting services formatdatetime

Microsoft security

reporting services formatdatetime

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

reporting services formatdatetime

Ask the Microsoft Community

reporting services formatdatetime

Microsoft Tech Community

reporting services formatdatetime

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Master of None

Unfortunate ending of the saying … jack of all trades, ssrs – format() and formatedatetime().

Found this reference … JUST IN TIME!

Thanks Thavash This is by far my most visited page.

http://thavash.spaces.live.com/blog/cns!CF6232111374DFD2!155.entry

Working with Dates in Reporting Services

…but the better way to do it would be to use …

2) The Format command and specify the exact format you require. For example…

So 3 M’s give you “Apr” ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.

EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.

Tiny URL for this post:   Copy

Comments are closed.

Recent posts.

  • I am Only Here to Help – sys.xp_logininfo and sys.helplogins
  • SQL Agent Jobs Last Run
  • Database Statistics Health and Update Scripts
  • Find Object Execution
  • Identify Lead Blocker Script

Search by Tags!

  • January 2016
  • December 2015
  • February 2014
  • February 2012
  • January 2012
  • December 2011
  • February 2010
  • November 2009
  • February 2009
  • January 2009
  • December 2008
  • October 2008
  • August 2008
  • February 2008
  • All About Duncan
  • Check Out the On Demand MSDN Webcasts
  • MicroSoft Virtual Labs
  • Old JackDonnell.com Articles
  • SQL Junkies
  • SQL Server Central
  • Red Gate’s Simple Talk
  • Instructables!
  • Simple-Talk.com RedGate’s SQL Blog
  • SSWUG.ORG – SQl Server World User Group
  • Comer A. Donnell
  • MSSQLTips.com
  • ArtsNash – Nashille Arts and Review

Copyright © 2024 Master of None | Powered by zBench and WordPress

Formatting Dates in SSRS

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc.  Therefore I've found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me.  Previously I would have used FormatDateTime in the expression, as shown here:

=FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate)

Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.  When designing the report, I want to know exactly what the date will look like, no matter where it is deployed.  So I instead use the Format function to allow me to choose the format I require.  Some examples:

=Format(Fields!DateStamp.Value, "dd-MMM-yy")

returns 03-Dec-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy")

returns 03-Dec-2008

=Format(Fields!DateStamp.Value, "dd-MMMM-yy")

returns 03-December-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy H:mm")

returns 03-Dec-2008 16:44

This method removes a lot of the issues regarding date formatting, and means there are no shocks or questions when it comes to deployment time!

Edit: Setting the Language property of the report to your locale will also ensure that dates in your report parameters will have the correct format.  This is something that is often overlooked.

graphdates

IMAGES

  1. SSRS Formatting and Placeholders

    reporting services formatdatetime

  2. SSRS Formatting and Placeholders

    reporting services formatdatetime

  3. reporting services

    reporting services formatdatetime

  4. Reporting Services Basics: Creating Your First Report

    reporting services formatdatetime

  5. Overview of Reporting Services

    reporting services formatdatetime

  6. Reporting Services e informes interactivos y muy visuales

    reporting services formatdatetime

VIDEO

  1. SSRS Dashboard en Reporting services en Visual Studio 2022 desde 0

  2. Developing a Simple SQL Server 2008 Reporting Services Report

  3. PwC's Cloud Security Services

  4. Information server Part 4

  5. US Beneficial Ownership Reporting Now Open: How to File & Stay Compliant!

  6. Information Server Part 7

COMMENTS

  1. reporting services

    reporting services - SSRS FormatDateTime - Stack Overflow SSRS FormatDateTime Ask Question Asked 9 years, 4 months ago Modified 8 years, 9 months ago Viewed 9k times 2 Im using =FormatDateTime (Parameters!StartDate.Value,DateFormat.ShortDate) and its returning "mm/dd/yyyy" format. I want to return it in this format "dd/MM/yyyy".

  2. Lesson 5: Format a report (Reporting Services)

    In this lesson, you successfully formatted column headers and field expressions. Next, you're going to add grouping and totals to your report. Continue with Lesson 6: Add Grouping and Totals (Reporting Services). Related content. Format Numbers and Dates (Report Builder and SSRS) Rendering Behaviors (Report Builder and SSRS)

  3. SQL Server Reporting Services Formatting and Placeholders

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

  4. Format Date and Time in SSRS Report

    FROM [Employee] Data written by the above query is: Format Date and Time in SSRS Report To explain the list of Data format and Time format options, we are going to use the below-shown report.

  5. Formatting numbers and dates in paginated reports (Report Builder)

    In this article. Applies to: Microsoft Report Builder (SSRS) Power BI Report Builder Report Designer in SQL Server Data Tools You can format numbers and dates in data regions in a paginated report by selecting a format from the Number page of the corresponding data region's Properties dialog box.. To specify format strings within a text box report item, you need to select the item that you ...

  6. Tutorial: Format text (Report Builder)

    On the Insert tab of the ribbon, in the Data Regions area, select List. Choose the design surface, and then drag to create a box that about 2 inches wide and 1 inch tall. Drag the FullName field from the Report Data pane to the list box. This time Report Builder creates a text box with the expression [FullName] in it.

  7. FormatDateTime Function

    FormatDateTime Function - Microsoft Support FormatDateTime Function Access for Microsoft 365 Access 2021 Access 2019 Access 2016 More... Returns an expression formatted as a date or time. Syntax FormatDateTime ( Date [, NamedFormat ] ) The FormatDateTime function syntax has these arguments: Settings

  8. Format DateTime in Reporting Services

    Format DateTime in Reporting Services Archived Forums 381-400 > SQL Server Reporting Services, Power View Question 0 Sign in to vote Hi, I want to add the current date and time to the bottom of a report in Reporting Services but i'm not sure how to format it the way I want. I'm using =system.datetime.now () as the expression.

  9. SSRS Date Format

    You can use the FormatDateTime method to format the date column in the Expression box, or use the SSRS Format string function. The syntax is similar to that used in SQL Server queries. Following code is done by Right click to report Property

  10. SSRS

    Working with Dates in Reporting Services As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today , "How to I format dates when using the Date Picker and sending it through to a stored procedure" ? Let's have a look …. 1) The FormatDateTime command

  11. How do I format time in SSRS to HH:MM AM/PM format?

    In realizing that FormatDateTime was insufficient for what I was trying to do, I found the following did not work (just looking at the snippit that relates to the time fields), : ... SQL Server Reporting Services Format Hours as Hours:Minutes. 11. How to display a time span of seconds in hh:mm:ss format in Reporting Services ...

  12. Formatting Dates in SSRS

    In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc. Therefore I've found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me. Previously I would have used FormatDateTime in the expression, as shown here: =FormatDateTime ...

  13. sql server

    I've found this expression to work but it changes all the null values to '1/1/0001' =FormatDateTime(Fields! Stack Exchange Network. Stack Exchange network consists of 183 Q&A communities including Stack Overflow, ... SSRS - Data missing when running report from server. 3. SSRS: First report is slow: Reloading Appdomain. 2.

  14. Cannot format datetime in SQL Server Reporting Services report builder

    1 None of the previous questions/answers on this topic are working for me; I'm a bit baffled. I've inherited my first SQL Server Reporting Services Report Builder report and was given the seemingly simple task of changing what was a date to a datetime.

  15. SSRS : Changing how a date and time is displayed to UK

    However I can change the format so that it displays the field as dd/mm/yyyy (or any other format - see below for some details on that), as follows : To change the format select the textbox control in the report designer, click on F4 to display the properties window and enter dd/MM/yyyy for the format property (using an upper case M as lower ...

  16. sql server

    2 Answers Sorted by: 0 There are a few things you can do: You can try changing the Language on your report files (Properties - Location - Language) to match the same language on the database.

  17. sql server

    8 Answers Sorted by: 23 I would recommend using the format codes: Right click - properties on the cell, select format, click the ellipsis "...", and you can see the date formats from there. This will be converted into a date code when you OK the dialog. This is useful as it sets the date in the fomat the user wants to see it in.

  18. FormatDateTime gives #Error after report renders

    1 Sign in to vote Hi There Thanks for your posting. I have answered the similar question in another thread, Can you please try this expression =iif (ISDATE (Fields!LICEDDATE.value) = False, (Fields!LICEDDATE.Value), formatdatetime (iif (IsDate (Fields!LICEDDATE.value) ="1",Fields!LICEDDATE.Value,"01/01/1900"),dateformat.ShortDate ))