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:
Chapter-1: Creating a basic Tabular SSRS Report using Report Wizard
Chapter-2 : Report Deployment on SQL Server 2012 Reporting Server
Chapter-3:Opening/Calling the deployed SQL Server Reporting Server report from Asp.net
Chapter-4 Creating SSRS Matrix Report / Cross Tab Report
Chapter-5 Creating a Sub Report in SSRS
Chapter-6: Creating a Drill Down Report in SSRS
Chapter-7: Working with expressions and custom code in SSRS