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

 

SQL Server Reporting Services Expressions Tips and Tricks


By:   |   Last Updated: 2013-08-08   |   Comments (26)   |   Related Tips: > Reporting Services Development

Problem

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.

Solution

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.

SELECT
    DPC.EnglishProductCategoryName AS Category
    , DPS.EnglishProductSubcategoryName AS Subcategory
    , DP.EnglishProductName AS Product
    , FIS.SalesAmount
    , FIS.Freight AS FreightAmount
    , FIS.TaxAmt AS TaxAmount
FROM
    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.

=Parameters!DisplayField.Label

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

=Fields(Parameters!DisplayField.Value).Value

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

=ReportItems!tbxSource.Value

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.

=SWITCH(
    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


Last Updated: 2013-08-08


next webcast button


next tip button



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

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.



    



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

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

 

 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

 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

 Hi Priyanka you can you datadd funcation

 

=DateAdd("d",-7,Today())

 


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

 

Great thing you explane  


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

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

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

 

Thanks

 

 


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

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

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?

Thanks,

Greg


Friday, January 09, 2015 - 4:25:33 PM - Greg Robidoux Back To Top

Hi Lance,

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


Friday, January 09, 2015 - 4:00:50 PM - Lance Back To Top

 

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

Hi,
 
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

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

Hi,

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

 

Regards

Goutham


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

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 08, 2014 - 3:09:00 AM - Suleman Back To Top

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

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.

http://www.mssqltips.com/sqlservertutorial/2111/report-builder-table-or-matrix-wizard/

Best Regards,

Dattatrey Sindol (Datta)


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

Datta,

 

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

 

Puanani


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

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

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

Datta,

 

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.

=Parameters!DisplayField.Label

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

=Fields(Parameters!DisplayField.Value).Value
 
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

Thanks for sharing


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

Thank you Datta !

Those were really nice tips.


Thank you again

Satish 



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

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 09, 2013 - 1:36:32 PM - Jeremy Kadlec Back To Top

Datta,

Congrats on your 20th tip!

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, August 09, 2013 - 9:36:11 AM - Kris Back To Top

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 08, 2013 - 2:19:53 PM - SSRSSmurf Back To Top

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


Learn more about SQL Server tools