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

Preview of Target Report show Calculated Field
Preview of Target Report show Calculated Field

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


Design the Query
Design the Query

And report design will look like.

Design View of Report
Design View of Report

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.

Open Report Data from View menu
Open Report Data from View menu

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”.

Right Click and choose Add Calculated field
Right Click and choose “Add Calculated field”

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

Click on the Expression button fx
Click on the Expression button “fx”

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.

Sample Calculation formula
Sample Calculation formula

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.

Drag and drop the TotalValue column
Drag and drop the “TotalValue” column

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.

image017
Paste the code to the custom code window

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

right click on column “Product ID” and choose Text Box Properties
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.

click on “fx” function rounded with red color
Click on “fx” function rounded with red color

And write the following code to the Expression Box.

=Code.SetColor(Fields!TotalValue.Value)

ssrs expression window
SSRS expression window

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

Preview of final report
Preview of final report

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



Leave a Reply

Your email address will not be published. Required fields are marked *

*

four × 3 =