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

Readers Feedback:

Did you like this article? If Yes, then please share it: | More Options

Did you like my blog? If Yes, then please help spread the word by sharing this blog with your colleagues, friends, & anyone else in the MSBI Space: Like us on Facebook , Follow us on Twitter .