SQL Server Reporting Services Expressions Tips and Tricks

By:   |   Comments (27)   |   Related: > Reporting Services Development


Expressions are a very powerful feature of SQL Server Reporting Services (SSRS). They enable us to handle various things dynamically at run-time. In this tip, we will take a look at few Tips and Tricks on SSRS Expressions.


In this tip, we will take a look at the following Tips and Tricks on SSRS Expressions:

  • Adding Comments in Expressions
  • Displaying Columns Dynamically
  • Referring to Textbox Contents
  • Referring to Contents within Textboxes
  • Adding a Line Break in Expressions
  • Using Visual Basic (VB) Functions

Note: This tip assumes that you have experience building a simple to medium complexity SSRS Report.

Adding Comments in SSRS Expressions

Comments are a very useful feature in any kind of coding/programming as they have various benefits like they act as a self-documentation, make it easy to understand and maintain the code. Fortunately, SSRS also allows us to add comments as part of the expressions.

For the purpose of this tip, add a textbox to report header with the following expression in it and preview the report.

="Report Executed By: " + User!UserID

The report output should contain the following text: "Report Executed By: <<User Running the Report>>"

Now let's add a comment towards the end of the expression and also on the next line as shown below.

Comments in SSRS Expressions

Again preview the report and the output should contain the same text as before: "Report Executed By: <<User Running the Report>>"

Following are the highlights of the Comments in SSRS Expressions:

  • Comments in SSRS Expressions are implemented with a single quote.
  • Comments can be added either on the same line as the expression or on the next line.
  • Comments cannot be added in the beginning or middle of the expression.
  • There is no concept of multi-line comments. All comments are single line comments. However, when a comment is added on the same line or next line of an expression, any text added on the subsequent lines is ignored by SSRS and hence is treated as a part of the comment.

Displaying Columns Dynamically in SQL Server Reporting Services Expressions

For the purpose of this demonstration, let's build a simple report. Create a new project, add a new report, and create a data source pointing to AdventureWorksDW database. Then create a dataset with the following query.

    DPC.EnglishProductCategoryName AS Category
    , DPS.EnglishProductSubcategoryName AS Subcategory
    , DP.EnglishProductName AS Product
    , FIS.SalesAmount
    , FIS.Freight AS FreightAmount
    , FIS.TaxAmt AS TaxAmount
    dbo.FactInternetSales AS FIS
    INNER JOIN dbo.DimProduct AS DP
        ON FIS.ProductKey = DP.ProductKey
    INNER JOIN dbo.DimProductSubcategory AS DPS
        ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey
    INNER JOIN dbo.DimProductCategory AS DPC
        ON DPS.ProductCategoryKey = DPC.ProductCategoryKey

Next, add a Table report item to the designer with four columns as Category, Subcategory, Product, and SalesAmount in the same sequence. The completed report, with some basic formatting, should look as shown below.

Report with Static Columns

Next let's add a single-select parameter "DisplayField", of "Text" type, with the following set of values.

Available values in DisplayField parameter

Add the following expression in the "Sales Amount" column header.


Add the following expression in the "Sales Amount" column value/detail.


Next render the report with parameter set to "Sales Amount", followed by setting it to "Freight Amount" and finally setting it to "Tax Amount". The output with each of the 3 parameter selections will be as shown below respectively.

DisplayField Parameter set to Sales Amount

DisplayField Parameter set to Freight Amount

DisplayField Parameter set to Tax Amount

Notice that both the value and the label of the last column changed based on the value selected in the "DisplayField" parameter, thereby making the field display dynamicly. Also, note that we have not used any IIF(), SWITCH() or any other conditional statements in this expression. The same technique can be applied for other features within SSRS like the following:

  • Dynamic Parameters
  • Dynamic Grouping

Referring to Textbox Contents in SSRS Expressions

SSRS has a collection called "ReportItems" which allows us to reference values from a Textbox in other places within the report. For the purpose of this demonstration, add two textboxes to report and name them as "tbxSource" and "tbxTarget" respectively.

Add the sample text "This is the source textbox. The contents of this textbox can be referred anywhere within the same scope using ReportItems collection." in the first textbox "tbxSource". And add the following expression in the second textbox "tbxTarget".


The report designer should look like the image shown below.

SSRS ReportItems Collection Demo - Designer

Now preview the report and see that the contents displayed in second textbox "tbxTarget" are exactly the same as that of the contents of the first textbox "tbxSource".

SSRS ReportItems Collection Demo - Preview

SSRS ReportItems Collection is a great feature and can be used in different scenarios. However, the Textbox being referred to should be in the same scope as that of the item from which it is being referred.

Referring to Contents within Textboxes with SSRS Expressions

SSRS has interesting syntax to refer to values within the textbox without actually using the expression, field, or parameter present within the textbox. For the purpose of this demonstration, let's add a textbox in the report and put a value 5 in it. Select the properties window and set the "BackgroundColor" property to the following expression.

    Me.Value >= 0 AND Me.Value <= 3, "Red"
    , Me.Value >= 4 AND Me.Value <= 7, "Yellow"
    , Me.Value >= 8 AND Me.Value <= 10, "Green"

Your designer window should look as shown below.

Referring to Contents Within SSRS Textboxes - Designer

Now preview the report and notice that the textbox background color is "Yellow". Next set the value to "2" and preview it, and notice that the background color is "Red". Finally, set the value to "8" and preview it, and notice that the background color is "Green". The output in each of the scenarios looks like the image as shown below.

Referring to Contents Within SSRS Textboxes - Preview

In the above textbox, we can put any valid expression instead of a hardcoded number. This feature can be really handy especially at design time when the value in the textbox is a result of an expression.

Adding a Line Break in SQL Server Reporting Services Expressions

Often developers use multiple textboxes to display data, which otherwise can be combined into a single textbox by just adding a Line Break. Visual Basic Carriage Return Line Feed (VbCrLf) can be used to add a Line Break in expressions. For the purpose of this demonstration, add a textbox to the designer and then enter the following expression in the textbox.

="Executed By: " & User!UserID & VbCrLf & "Execution Time: " & Globals!ExecutionTime

Preview the report and notice that the "Report Executed By" and "Report Execution Time" are on two different lines as shown below.

Adding a Line Break in SSRS Expressions

Line Breaks can be used to combine contents of multiple textboxes placed one after the other and hence make it easy to manage and reduce the effort required for formatting, alignment, etc.

Using Visual Basic (VB) Functions in SQL Server Reporting Services

Another interesting feature of SSRS is the ability to use Visual Basic Functions in expressions. There are various categories of functions available in SSRS as shown in the this screenshot.

Categories of Functions in SSRS Expressions

Basically, any valid VB function can be used in SSRS Expressions.

As we can see in this tip, expressions are a very powerful feature of SSRS. Expressions can be used to address many complex problems and reporting requirements.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Dattatrey Sindol Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, November 11, 2021 - 1:38:14 PM - Oscar Back To Top (89434)
Is there any way to access to the function Miscellaneous Lookup via VB Embedded Code?

I am trying to find any documentation related to it, but i have no luck

Monday, July 23, 2018 - 12:02:12 PM - john Back To Top (76792)

I have an SSRS invoice report. When user run for an invoice date between certain date like 1/1/2018 and 7/23/2018, it needs to show in report footer like invoice date between 1/1/2018 and 7/23/2018. Can you help me on this expression. Thank you.

Tuesday, December 12, 2017 - 5:53:20 AM - Rahul sinha Back To Top (73922)


 An error occured during local report processing.

The definition of report/Report1 is invalid

The report parameter A has a DefaultValue that depends on the report parameter"A". Forward dependencies are not valid.


Saturday, June 24, 2017 - 6:36:12 AM - Prabhat Giri Back To Top (58068)

 Hi I want to view date and Time on same coloumn . Thus while writing Expression i am Getting error on it.


Monday, November 14, 2016 - 6:54:34 AM - Asad Amjad Back To Top (43757)

 Hi Priyanka you can you datadd funcation




Wednesday, April 13, 2016 - 12:38:05 PM - Mohammed Zafar Back To Top (41211)


Great thing you explane  

Wednesday, October 14, 2015 - 3:07:38 PM - Gabe Back To Top (38895)

Can you please add example of switch for more than 3 conditions. Just add 1 or 2 more condition to below code that works.

    Me.Value >= 0 AND Me.Value <= 3, "Red"
    , Me.Value >= 4 AND Me.Value <= 7, "Yellow"
    , Me.Value >= 8 AND Me.Value <= 10, "Green"





Wednesday, March 11, 2015 - 10:33:25 AM - Lyna Back To Top (36488)

I am relatively new to SQL and SSRS.  I am doing a lot of things the hard way because I do not know that there is an easy way.  The tips you posted here are going to save me a lot of time.  Thank you!

Thursday, February 26, 2015 - 6:05:27 PM - Greg Back To Top (36368)

The dynamic column is excellent, thank you!  However, when I use it, it reallllllly slows down the rendering of my report.  Instead of about five seconds, it's now 30.  I've only added three column options - any feedback for how to help get the loading time back down?



Friday, January 9, 2015 - 4:25:33 PM - Greg Robidoux Back To Top (35889)

Hi Lance,

does this tip help: http://www.mssqltips.com/sqlservertip/2612/data-driven-colored-text-for-reporting-services-reports/

Friday, January 9, 2015 - 4:00:50 PM - Lance Back To Top (35888)


Hello, In SSRS, How do you highlight rows based on codition if the condition is a string search like this. I clicked on details went to properties and went to FILL. in my background color i wrote this expression."=IIf(Fields!AllComments.Value Like "*tweet*","Blue","No Color")"

Thursday, November 13, 2014 - 9:41:44 AM - Priyanka Back To Top (35276)

I want to add a date filter parameter .But I get an error when I use this expression..
Can you please help?
I am trying to get the last sundays date from the current date:
="[Date].[Date].&[" & Format(CDate(DateAdd("d", - DatePart("w", Now(), 1)-6, Now()), "yyyyMMdd")) + "]






Thursday, November 13, 2014 - 7:05:41 AM - Dattatrey Sindol (Datta) Back To Top (35275)

Hi Goutham,


You can write your expression something like this:

=IIF(Fields!Name2.Value Is Nothing, "", Fields!title2.Value)

For more examples on SSRS expressions, please take a look at this: http://msdn.microsoft.com/en-us/library/ms157328.aspx


Hope that helps!


Best Regards,

Dattatrey Sindol (Datta)

Tuesday, November 11, 2014 - 5:17:31 AM - Goutham Back To Top (35249)


 I have a requirement in report like


 Name1   Name2   title1   title2

  A          B          Mr      Mrs

  C          D          Mrs     Mr


suppose  if Name2 is not there then it should not dislpay tittle2

eg:    Name1   Name2   title1   title2

         E                      Mr 


how write a expression for this...




Tuesday, September 30, 2014 - 4:10:54 AM - wasim Back To Top (34769)

what is the best way to get the data from three tables. where two have the same fields but the third one havent any common field.

Thursday, May 8, 2014 - 3:09:00 AM - Suleman Back To Top (30663)

When I sum my column its show results 3.12:33:44 But i want to show inform of 3days12:33:44.I have used =Timespan.FromTicks(Sum(Fields!TravelTime.Value, "DataSet1")) this expression.Thanks in advance

Tuesday, April 22, 2014 - 11:52:11 AM - Dattatrey Sindol (Datta) Back To Top (30474)

Hi There,

Please take a look at the following part of the SSRS Tutorial which talks about building a Matrix Report. This should give you some idea.


Best Regards,

Dattatrey Sindol (Datta)

Wednesday, September 25, 2013 - 7:08:43 PM - puanani Back To Top (26953)



Thank you, I got it to work.  The problem was the single select parameter setting. 



Wednesday, September 25, 2013 - 11:44:46 AM - Dattatrey Sindol Back To Top (26940)

Hi puanani,


Can you please check the following things in your report?

1. What are the values available in the "DisplayField" parameter

2. Do you have fields corresponding to all the values in the "DisplayField" parameter.

3. Whether your "DisplayField" parameter is single select or multi-select. Note that, this method requires that the parameter is single select.


Best Regards,

Dattatrey Sindol (Datta)

Wednesday, September 25, 2013 - 12:23:38 AM - puanani Back To Top (26925)

This is the error I am getting.


Warning 1 [rsInvalidExpressionDataType] The Value expression used in textrun ‘Textbox5.Paragraphs[0].TextRuns[0]’ returned a data type that is not valid. C:\Users\Roz\SSRS\Chapter06\SSRSExampleAdventureworkds\SSRSExampleAdventureworkds\AdventureWorksRpt.rdl 0 0 

Wednesday, September 25, 2013 - 12:13:44 AM - puanani Back To Top (26924)



I could not get this code to work  Where exactly do you install this code:


Add the following expression in the "Sales Amount" column header.


Add the following expression in the "Sales Amount" column value/detail.

I just get an error in the sale amount column header.  Could you walk me through where this code should be installed?

Wednesday, August 14, 2013 - 1:25:43 AM - Varun Gulati Back To Top (26293)

Thanks for sharing

Tuesday, August 13, 2013 - 4:29:04 AM - Satish Back To Top (26269)

Thank you Datta !

Those were really nice tips.

Thank you again


Saturday, August 10, 2013 - 1:52:37 AM - Dattatrey Sindol Back To Top (26205)

Hi Kris,


Thanks for the feedback.

This tip is based on SQL Server 2012. However, all of them work on 2008 and above.


Best Regards,

Dattatrey Sindol (Datta)

Friday, August 9, 2013 - 1:36:32 PM - Jeremy Kadlec Back To Top (26189)


Congrats on your 20th tip!

Thank you,
Jeremy Kadlec
Community Co-Leader

Friday, August 9, 2013 - 9:36:11 AM - Kris Back To Top (26181)

The tips are good.

I would suggest all users to mention the version of the SQL Server whenever a post is made, which helps the user from what version that this tip works

Thursday, August 8, 2013 - 2:19:53 PM - SSRSSmurf Back To Top (26153)

THX for the ideas.  A few of these seem pretty promising to use.

get free sql tips
agree to terms