Live Chat Icon For mobile
Live Chat Icon

Using Calculated Fields in Reports

Calculated fields play a key role in any business analytics process. A calculated field is a custom column that derives its value by performing a function on values from other data table fields. This field includes built-in functions as well as formulas and other columns in the underlying data table. With calculated fields, you can make simple arithmetic operations or complex mathematical and logical operations, type conversions, and more. In reports, you can create a calculated field with measure or dimension fields from the underlying data source and built-in functions. Then, easily configure that field to certain report items.

Let’s consider a scenario: we may want to display the total value of items in a Products table, but we don’t have a Total field in the table in our database. That’s when we need a calculated field that returns the total product value for each product. In this blog post, we will see how to create this calculated field and visualize it in a table report item using Bold Reports Designer.

First, create a new report or open an existing report in Bold Reports Designer. I am going to showcase a demo using an already-created table report that runs against the Products table from the Northwind database. Please refer to the Data SourceData Set, and Table Report documentation to see the creation of a data source, data set, and table report in Bold Reports Designer.

Product Details report design
Product Details report design

How to create a calculated field

You can easily createdelete, and update a calculated expression in a report and configure the calculated field in a report item like other, normal numeric columns as a quantitative measure.

Open the DATA pane in configuration panel. The data sets available in the report will be listed here.

Data pane
Data pane

Hover over the data set in which you want to create the calculated field and open the kebab menu.

Fields option listed in context menu
Fields option listed in context menu

Then, click Fields in the menu. Now, the available fields in the selected data set will be listed in the Fields dialog.

Data set fields dialog
Data set fields dialog

In the Fields dialog, you can add a new static (query) field or a calculated field to the existing data set.

Add field menu
Add field menu

Here, we want to create a calculated field. So, click Calculated Fields in the Add menu. It will add a new row with two empty text boxes called Field Name and Field Source.

New calculated field
New calculated field

Enter the Field Name and click on the square icon on the right side of the Field Source text box. Here, I am providing the field name Total.

Add field name
Add field name

Click Expression in the menu and it will open an Expression Builder.

Now, we are going to multiply the UnitPrice and UnitsInStock columns to calculate the total. So, we set the expression value as:

= Fields!UnitPrice.Value * Fields!UnitsInStock.Value

Enter this expression in the Expression builder and click OK.

Enter expression to calculate total
Enter expression to calculate total

Now, the expression is assigned to the Field Source text box. Click OK.

Expression assigned in value field
Expression assigned in value field

The newly added calculated field is represented with λ in the data pane.

New data set field
New data set field

Refer to the Data Set Fields documentation to learn more about the options available in the Fields dialog.

How can you use a calculated field?

Next, I am going to display the newly created calculated fields in the table report item. For that, I am adding a new column to the table and assigning the Total field in it.

Using calculated field in table
Using calculated field in table

Refer to the Insert Column and Assign Data documentation to learn detailed designing steps. Then, apply currency formatting in the Total cell. Refer to the Formatting a Report blog to format data in the table.

Currency Formatting
Currency formatting

Report preview

Now, preview the report. At run time, the total product value for each product is calculated and displayed in the correlating cell.

Total value calculate at run time
Total value calculate at run time

In the previous example, we created a calculated field using simple arithmetic calculation. You can also build complex mathematical expressions or logical expressions that perform functions on data set fields to create a new calculated field. Here are some calculated expression examples used in business reports.

  • While generating an invoice report, we may need to calculate the price of each product based on its UnitPrice, Quantity, and Discount fields from the data set. To calculate the price of each product, use the following expression.
    = Fields!Quantity.Value * Fields!UnitPrice.Value - (Fields!Quantity.Value * Fields!UnitPrice.Value * Fields!Discount.Value)
  • A salesperson may want to automatically apply a discount of 30% if an order is greater than $3,000 for a specific month in a year. We can use the following expression.
    = IIF((Fields!Sales.Value >3000) AND (DATEPART(“M”, CDate(Fields!OrderDate.Value)) = 5), Fields!Sales.Value*0.3, Fields!Sales.Value)

Conclusion

I hope this blog provided a clear idea about the benefits of using calculated fields and how to achieve this using Bold Reports Designer. To explore further, go through our sample reports and Bold Reports documentation. I have also attached the generated .rdl file for further reference.

If you have any questions, please post them in the comments section. You can also contact us by submitting them through our contact page, or if you already have an account, you can log in to submit them.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official TwitterFacebookLinkedInPinterest, and Instagram pages for announcements about upcoming releases.

Leave a Reply