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 Source, Data Set, and Table Report documentation to see the creation of a data source, data set, and table report in Bold Reports Designer.
How to create a calculated field
You can easily create, delete, 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.
Hover over the data set in which you want to create the calculated field and open the kebab menu.
Then, click Fields in the menu. Now, the available fields in the selected data set will be listed in the Fields dialog.
In the Fields dialog, you can add a new static (query) field or a calculated field to the existing data set.
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.
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.
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.
Now, the expression is assigned to the Field Source text box. Click OK.
The newly added calculated field is represented with λ in the data pane.
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.
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.
Report preview
Now, preview the report. At run time, the total product value for each product is calculated and displayed in the correlating cell.
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 Twitter, Facebook, LinkedIn, Pinterest, and Instagram pages for announcements about upcoming releases.
