Calculated Fields
While creating reports, 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: SSRS allows us to use powerful Expressions to create Calculated Fields so, we can either use existing fields or create custom expression using expression window.
We are going to use below shown report to explain, How to create Calculated Fields in SSRS Reports. Please refer SSRS Table Report, Data Source and Dataset articles to understand the creation of Data Source, Dataset and Table report. If you observe the below screenshot, It was a normal Table report with Product Name, Color, Sales Amount and Tax Amount columns.
Steps to Create Calculated Fields
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 be opened. If you observe the below screenshot, a new row with two empty text boxes under Field Name and Field Source will be added.
- Field Name: The name you specify here will be displayed as 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 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
Click Ok to add the Expression. Next, we would like to show you, How to add one more Calculated Filed from this Dataset Properties window.
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, Static column will repeat 10000 to each and every 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 report preview.
From the above screenshot you can observe that, Static Column is repeating 10000 to each every every column.
Thank You for Visiting Our Blog
