# Chapter-8-SSRS (Reporting Services) Working with Calculated fields

Calculated field is a field derived from another field.  Suppose we want to find out the total value of Items in the “Products” table of “Northwind” database then we need a calculated column which calculate total product value for each product. In such case we can color the entire row whose product value is > 1000. Final output will be as shown below.

Download Full Tutorial for Chapter-8-Working-with-Calculated-fieldsin PDF format

Download Sample Code for working with SSRS Calculated Fields

For doing the exercise we have to follow the steps mentioned below.

Step-1: Create a tabular report as it was described in Chapter-1, but with the following SQL Script of “Products” table of “Northwind” database.

select ProductID,ProductName,QuantityPerUnit,UnitPrice,UnitsInStock from Products

And report design will look like.

Step-2: Create a custom code as it was given in “Chapter-7-Working with Expression and Custom Code” with the following function.

Public Shared Function CalcValue(ByVal UnitPrice As Single,ByVal UnitInStock As Single) As Single
CalcValue = UnitPrice * UnitInStock
End Function

Step 3: Choose Data Set from Menu view>Report Data as shown.

Right Click and choose “Add Calculated field” at DataSet1 as shown, the DataSet Properties window opens up and put the name of calculated field as “TotalValue”.

and click on the Expression button “fx” as shown in image marked with red circle.

Write the following expression in the “fx” function.

=Code.CalcValue(Fields!UnitPrice.Value,Fields!UnitsInStock.Value)

As an example, here we have taken a simple calculation but Expression supports any complex calculations.

Step 4: Drag and drop the “TotalValue” column from Dataset1 to the Report Designer as last column and run the report. You will be able to see the following report.

Step-5. Now have to color the entire row having Total value>1000. For This paste the following code to the custom code window.

Public Shared Function SetColor(ByVal TotalValue As Single) As String
SetColor= “Transparent”
If TotalValue >= 1000 Then
SetColor= “Green”
End IF
End Function

As it is given in the image and click on ok button.

Step-6: Now right click on column “Product ID” and choose Text Box Properties

And choose “Fill” tab and click on “fx” function rounded with red color circle as shown below.

And write the following code to the Expression Box.

=Code.SetColor(Fields!TotalValue.Value)

Step 7: Repeat Step-6 for every column and run the report, you will be able to see the following output.

Tutorial for creating SSRS Report: