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
Chapter-8-SSRS (Reporting Services) Working with Calculated fields
Chapter-9-Sorting of Column and Custom Paging in SSRS
Chapter-10 : Creating and Deploying Web Services for SSRS
Chapter-11 : Creating SSRS Report by consuming Web Service Data Source
Chapter-12 : Working with Data Bars in SSRS
Chapter-13-Displaying Color in Data Bar Based on Condition
Chapter-14- Displaying Data Labels along with Data Bars in SSRS
Chapter-15-Displaying Indicators in SSRS
Chapter-16-Creating SSRS Linked Report
Chapter-17 Adding or Creating Drop Down Parameter List in SSRS
Chapter-18 Creating Group above or stepped report in SSRS
Chapter-19-Creating Multi Value Parameter Report in SSRS
Chapter-20-Using CSS Stylesheet in SSRS