In his previous post, Samer outlined Report Types. Today we will continue discussing advanced reporting topics to talk about calculated fields.
When creating reports, we might want information that is not directly available from the database. We can add calculated fields to the model in order to make these often-used calculations available across the report.
Calculated fields help customize existing fields through expressions. Expressions are used throughout the report definition to specify or calculate values for parameters, fields, and constants.
Let us try 2 examples to create different calculated fields using the report “Account Expiration Date”.
Create a calculated field to convert the field “Domain Name” to uppercase and display it in the report.
To create a calculated field, open the layout designer of the report in “Reporting Console”.
Right-click anywhere within the Field List window, and select “Add Calculated Field”. (See Fig. 1)
This will create a calculated field with a default name “calculatedField1”.
Right-click the calculated field and select “Edit Expression” to open the expression editor. (See Fig.2)
Within the expression editor, you have 5 panes as explained below (See Fig. 3) :
- The top pane for editing the expression.
- We type the expression here with the help of built-in functions.
- Short cut to different operators to use within the expression.
- List a set of operators that are commonly used in expressions.
- Categories of objects that can be used within expressions.
- There are several functions, operators, existing fields, constants and parameters that can be used in the expression.
- The list of functions based on the selected category.
- The functions include groups such as Math, Date-Time, Logical, String and Aggregate.
- Description and syntax help for the selected function.
- A brief description of the selected function, with an example highlighting the syntax.
Our goal is to create a calculated field that converts the “Domain Name” to uppercase.
So clearly, we need a function from the “String” category to change the value to uppercase.
Look for the function in panel 4 that converts a string to uppercase and double-click to add it to the expression editor. (See Fig. 4)
As you can see from the description and syntax, the function “Upper()” is the correct choice and it takes an argument of type “String”, which, in our case, is the “Domain Name”.
Select the category “Fields” and double click the field “Domain Name”. This will add the field “Domain Name” as an argument to the function “Upper()”. (See Fig. 5)
Click OK and use it in the report.
To use the new calculated field in the report, drag and drop the calculated field from the “Field List” window onto the report. (See Fig .6)
Click Preview and you will see the domain name in uppercase.
Let us try another example, to display the text “No Date” in case the expiration date is not available.
Create a new calculated field using the previous steps and open the “Expression Editor”.
For this expression, we have to verify if a date exists for a given entry and accordingly display the required text. It is a logical choice. So let us pick the Logical group and pick the Iif(,,) function.
The Iif(,,) function has three parts:
- True Part
- False Part
If the expression evaluates to “true”, then the value specified in True Part is returned.
In our case, the expression has to verify whether the field “Expiration Date” is empty or not.
There is a function to verify that =>” IsNullOrEmpty().”
Now, let us construct the complete expression (See Fig. 7)
The function has all 3 required parts:-
- IsNullOrEmpty(Expiration Date(Domain User))
- True Part
- ‘No Date’
- False Part
- [Expiration Date (Domain User)]
Click OK to save the expression. Drag and drop the new calculated field onto the report to replace the existing field “Expiration Date”. (See Fig. 8)
Click Preview to verify if the text “No Date” is displayed in the report if there is no Expiration Date set.
Calculated fields help you create powerful and flexible reports. Unlike applications such as Microsoft Office Excel where you work with data directly in a worksheet, Report Designers’ calculated fields function allows you to work with expressions that are placeholders for data.
To see the actual data from the evaluated expressions, you must preview the report. When you run the report, the report processor evaluates each expression as it combines report data and report layout elements, such as tables and charts.
In the next installment on advanced reporting, Aarti will explain the power of a custom query report.