While creating reports in SSRS, we might need information that is not directly available in the Database. For instance, we may have to show Profits to end-user, but we don’t save Profits in our Database. In these situations, we can create or add calculated fields to the existing Dataset and use them in SSRS reports.
TIP: SQL Server Reporting Services allows us to use powerful Expressions to create Calculated Fields in SSRS. So, we can either use existing fields or create a custom expression using the expression window.
We are going to use the below-shown report to explain, How to create Calculated Fields in SSRS Reports. Please refer to Table Report, Data Source, and Dataset articles in SSRS to understand the creation of Data Source, Dataset, and Table report.
Steps involved in Creating Calculated Fields in SSRS
First, Select the Report Dataset and right-click on the Dataset to open the context menu. From the context menu, Please select Add Calculated Fields.. option as shown below. Or Click on the Dataset Properties option and then select the Fields tab
Once you click on the Add Calculated Fields option, a new window called Dataset Properties with Fields tab will open in SSRS. If you observe the below screenshot, a new row with two empty text boxes under Field Name and Field Source will add.
- Field Name: The name you specify here will display as a Column name in your Dataset.
- Field Source: This is the value we are going to display in Reports. We can assign static value or use the fx button to write the custom expression.
We assigned a Field Name as Total and clicked the fx button to write the custom expression.
For now, we are going to add Sales Amount and Tax Amount as Total so, we set the expression value as
=Fields!SalesAmount.Value+Fields!TaxAmt.Value
Click Ok to add the Expression. Next, we would like to show you how to add one more Calculated Field from this Dataset Properties window in SSRS.
First, Click on the Add button will open the context menu with two options. Here we have to select the Calculated Filed option as shown below
Here, We assigned Field Name as Static and Field Source as 10000. It means the Static column will repeat 10000 to each row present in that report.
Click Ok to finish creating Calculated Fields in SSRS Dataset.
Next, We added the newly created calculated fields (Total and Static columns) to the SSRS Report as shown below
Click on the Preview button to see the SSRS calculated fields report preview.
From the above screenshot, you can observe that Static Column is repeating 10000 to each column.
Comments are closed.