SQL Server Report Builder expressions are a powerful tool for creating smarter and more interactive reports. In this article, we will explore some common fields and functions used in SQL Server Report Builder.
Prerequisites
Before we begin, make sure you have SQL Server Report Builder installed on your machine. Additionally, you will need a report with some values to test the expressions. For this example, we will be using the Adventureworks database, specifically the SalesOrderDetails table. This table contains numeric values and multiple rows, allowing us to test various functions and expressions. However, you can use any table with numeric values for testing purposes.
Here is a sample report we will be working with in the examples:
Built-in Fields
SQL Server Report Builder provides several built-in fields that contain useful information about the report. These fields include the user, execution time, page number, and more. Let’s take a look at some of these fields:
| Expression | Result | Explanation |
|---|---|---|
| =Globals!ExecutionTime | 5/19/2022 12:20:48 PM | This field shows the date and time when the report started execution. |
| =User!Language | es-Es | This field displays the language of the user running the report. |
| =Globals!OverallPageNumber | 34 | This field shows the page number of the entire report. |
| =Globals!PageNumber | 5 | This field shows the current page number relative to page breaks that reset the page number. |
| =Globals!RenderFormat.IsInteractive | True | This field indicates if the render uses an interactive format. |
| =Globals!RenderFormat.Name | RPL | This field shows the format used for rendering the report. RPL stands for Report Page Layout. |
| =Globals!ReportFolder | Sales | This field displays the path of the folder where the report is stored. |
| =Globals!ReportName | TestingReport | This field shows the name of the report. |
| =Globals!TotalPages | 2478 | This field displays the total number of pages in the report. |
| =User!UserID | mydomain\Administrator | This field shows the Windows user account running the report. |
Aggregate Functions
In addition to built-in fields, SQL Server Report Builder also provides various aggregate functions that can be used to perform calculations on data. These functions include Average, Count, Sum, and more. Let’s take a look at some examples:
| Expression | Result | Explanation |
|---|---|---|
| =Max(Fields!OrderQty.Value) | 44 | This function returns the maximum value of the OrderQty field. |
| =Min(Fields!OrderQty.Value) | 1 | This function returns the minimum value of the OrderQty field. |
| =Avg(Fields!OrderQty.Value) | 2.266 | This function calculates the average value of the OrderQty field. |
| =COUNT(Fields!OrderQty.Value) as Computed | 121317 | This function counts the number of non-null values in the OrderQty field. |
| =CountDistinct(Fields!OrderQty.Value) | 41 | This function counts the number of distinct non-null values in the OrderQty field. |
| =First(Fields!OrderQty.Value) | 1 | This function returns the first value of the OrderQty field. |
| =Last(Fields!OrderQty.Value) | 1 | This function returns the last value of the OrderQty field. |
| =Stdev(Fields!OrderQty.Value) | 7.05981685604 | This function calculates the standard deviation of the OrderQty field. |
| =StDevP(Fields!OrderQty.Value) | 7.05981685604 | This function calculates the population standard deviation of the OrderQty field. |
| =Sum(Fields!OrderQty.Value) | 274914 | This function returns the sum of all values in the OrderQty field. |
| =Var(Fields!OrderQty.Value) | 49.841014 | This function calculates the variance of non-null values in the OrderQty field. |
| =VarP(Fields!OrderQty.Value) | 49.83771515 | This function calculates the population variance of the OrderQty field. |
Conditional Expressions
In SQL Server Report Builder, you can use conditional expressions to display different values based on certain conditions. Let’s look at an example using the “if” clause:
First, add a column to the report and provide a header column name. Then, create an expression for the data column. In this expression, we will display the value “High” if the OrderQty is greater than or equal to 5, otherwise we will display “Low”.
=IIf(Fields!OrderQty.Value >= 5, “High”, “Low”)
By using this expression, the report will display “High” for OrderQty values greater than or equal to 5, and “Low” for values less than 5.
Using the Choose Function
The Choose function in SQL Server Report Builder allows you to select a value based on a numeric index. Let’s see an example:
First, create a new column and write the following expression:
=Choose(Fields!OrderQty.Value, “one”, “two”, “three”, “four”, “five”, “six”, “seven”, “eight”, “nine”, “ten”, “eleven”)
This expression will display the literal number corresponding to the OrderQty value. For example, if the OrderQty is 1, the expression will display “one”.
Using the Switch Function
The Switch function in SQL Server Report Builder is similar to the CASE operator in T-SQL or other programming languages. It allows you to choose a value based on multiple conditions. Here’s an example:
=Switch(Fields!OrderQty.Value = 1, “One”, Fields!OrderQty.Value = 2, “Two”, Fields!OrderQty.Value = 3, “Three”, Fields!OrderQty.Value = 4, “Four”, Fields!OrderQty.Value = 5, “Five”, Fields!OrderQty.Value = 6, “Six”, Fields!OrderQty.Value = 7, “Seven”)
This expression will display the literal value corresponding to the numeric value of OrderQty. For example, if the OrderQty is 1, the expression will display “One”.
Conclusion
In this article, we have explored various SQL Server Report Builder expressions, including built-in fields, aggregate functions, and conditional expressions. These expressions allow you to create more dynamic and interactive reports. By leveraging the power of SQL Server Report Builder, you can enhance the functionality and usability of your reports.
Appendix
Here is a list of some common languages used in SQL Server Report Builder:
- Arabic (ar-SA)
- Bulgarian (bg-BG)
- Chinese (Simplified) (zh-CN)
- Chinese (zh-TW)
- Croatian (hr-HR)
- Czech (cs-CZ)
- Danish (da-DK)
- Dutch (nl-NL)
- English (en-US)
- Estonian (et-EE)
- Finnish (fi-FI)
- French (fr-FR)
- German (de-DE)
- Greek (el-GR)
- Hebrew (he-IL)
- Hindi (hi-IN)
- Hungarian (hu-HU)
- Indonesian (id-ID)
- Italian (it-IT)
- Japanese (ja-JP)
- Kazakh (kk-KZ)
- Korean (ko-KR)
- Latvian (lv-LV)
- Lithuanian (lt-LT)
- Malay (ms-MY)
- Norwegian (Bokmål) (nb-NO)
- Polish (pl-PL)
- Portuguese (pt-BR)
- Portuguese (pt-PT)
- Romanian (ro-RO)
- Russian (ru-RU)
- Serbian (Latin) (sr-latn-RS)
- Slovak (sk-SK)
- Slovenian (sl-SI)
- Spanish (es-ES)
- Swedish (sv-SE)
- Thai (th-TH)
- Turkish (tr-TR)
- Ukrainian (uk-UA)
- Vietnamese (vi-VN)