How to use functions in SQL Server Reporting Services (SSRS)

 
SQL Server Reporting Services (SSRS) is a great BI tool offering lot of powerful features. SSRS 2008 has lot more features and is much more powerful than SSRS 2005. Irrespective of whether its SSRS 2005 or SSRS 2008, there are many features common between the two versions. Functions are one such powerful options/capabilities in SSRS.
 
In this article I will present detailed steps for the use of Functions in SSRS.
 
Often in reporting/BI projects using SSRS we use a lot of calculations/expressions in many fields in the reports. There are scenarios where in same formula/calculations is used across many fields in the report. Functions come in handy especially in this kind of scenarios.
 
Let us take a look at this sample report with simple calculations for finding Sum, Difference, Product & Percentage.
 
Create an SSRS report with the following query in the dataset:
 
SELECT 1 AS ColumnA, 2 AS ColumnB
UNION ALL
SELECT 3 AS ColumnA, 4 AS ColumnB
UNION ALL
SELECT 5 AS ColumnA, 6 AS ColumnB
UNION ALL
SELECT 7 AS ColumnA, 8 AS ColumnB
UNION ALL
SELECT 9 AS ColumnA, 10 AS ColumnB
UNION ALL
SELECT 11 AS ColumnA, 12 AS ColumnB
UNION ALL
SELECT 13 AS ColumnA, 14 AS ColumnB
UNION ALL
SELECT 15 AS ColumnA, 16 AS ColumnB
UNION ALL
SELECT 17 AS ColumnA, 18 AS ColumnB
UNION ALL
SELECT 19 AS ColumnA, 20 AS ColumnB
 
Now lets drop a Table control on to the report designer with the following six fields as shown in the below screenshot.
  • Column A
  • Column B
  • Sum of A & B
  • Difference of A & B
  • A Multiplied by B
  • A as a % of B
 
Now go to “Report -> Report Properties“. Report Properties dialog box will open and in this window click on “Code” in the left pane. Enter the following code with four different functions for calculating Addition, Difference, Product & Percentage in the Code window as shown in the below screenshot.
 
‘ Function for Addition
Function GetSum(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarSum AS Integer
VarSum = A + B
Return VarSum
End Function
 
‘ Function for Difference
Function GetDiff(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarDiff AS Integer
IF (A>=B) THEN
VarDiff = A – B
ELSE
VarDiff = B – A
END IF
Return VarDiff
End Function
 
‘ Function for Multiplication/Product
Function GetProduct(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarProduct AS Integer
VarProduct = A * B
Return VarProduct
End Function
 
‘ Function for Percentage
Function GetPercent(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarPercent AS Integer
VarPercent = (A/B)*100
Return VarPercent
End Function
 
 
Enter the following expressions in the detailed row of the report for the six fields as shown in the below screenshot.
 
 
Expr1: “=Fields!ColumnA.Value”
Expr2: “=Fields!ColumnB.Value”
Expr3: “=Code.GetSum(Fields!ColumnA.Value,Fields!ColumnB.Value)”
Expr4: “=Code.GetDiff(Fields!ColumnA.Value,Fields!ColumnB.Value)”
Expr5: “=Code.GetProduct(Fields!ColumnA.Value,Fields!ColumnB.Value)”
Expr6: “=CStr(Code.GetPercent(Fields!ColumnA.Value,Fields!ColumnB.Value)) + ” %””
 

Now go to the Preview tab and check out the results as shown in the below screenshot.

 
And the results are amazing. Having functions for scenarios where the same calculation is used in many places in a report is really helpful and makes the code more modular & neat.
 
Note: This has been tested & presented as per SSRS 2008.
 
If you find this post helpful then feel free to leave a comment below 🙂
Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on January 3, 2010, in Code Snippets, SQL Server, SSRS, SSRS Snippets, SSRS Tips, Tips 'N' Tricks and tagged , , , , . Bookmark the permalink. Leave a comment.

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: