Creating Calculated Fields & Measures Activity

 

 

 

Step

Action

1. 

Note: Begin Task 1: Explore a calculated measure.

2. 

Note: This activity uses the sample view 10. Calculated Fields and Measures provided in the repository.

3. 

Login as joeuser/joeuser

4. 

Search for 10. Calculated

5. 

The search results appear.

 

Click 10.Calculated Fields and Measures to open the view.

6. 

Note: The 10. Calculated Fields and Measures view shows examples of how to use calculated fields and measures.

 

Next, begin looking at that view.

7. 

In the Filters panel to the left of the Filters heading, click the Filters minimize/maximize button to minimize the Filters panel.

8. 

Note: The Filters panel is minimized.

9. 

Next, inspect a calculated measure and the Formula Builder.

 

Calculated measures and calculated fields are recognizable by the fx in the icon.

 

In the Measures panel, right-click the Years Since Remodel measure.

10. 

Click the Edit menu item.

11. 

Note: The Edit Calculated Measure window appears with the Formula Builder tab selected.

 

Next is an overview of fields, lists and controls on the Edit Calculated Measure window. 

12. 

Note: The name of the calculated measure is in the Measure Name field.

13. 

Note: A formula can be typed manually in the Formula field.

14. 

Note: The operator toolbar, located just below the Formula field, provides basic math and logical operators. Insert an operator into a formula by clicking an operator in the operator toolbar. 

15. 

Note: To insert a field or measure into the formula, double-click a field or measure in the Fields and Measures list.  The list includes other calculated fields and measures and non-calculated fields and measures. 

16. 

Note: The Formula Builder displays a list of available functions. Double-click a function to insert it into the formula.

17. 

Click the Absolute function in the Functions list to see the function description.

18. 

Note: The Absolute function description appears.

19. 

Note: Check the Show arguments in formula checkbox to include placeholders for function arguments when a function is inserted into a formula.

20. 

Click the Validate button to validate the formula.

21. 

Note: A green message Validation Successful! to the right of the Validate button indicates the formula is valid.

22. 

Click the Cancel button.

23. 

Note: The Edit Calculated Measure window closes.

 

 

Task 1: Explore a calculated measure, is complete.

24. 

Begin Task 2: Delete a calculated measure.

25. 

In the Measures panel, right-click Years Since Remodel.

26. 

Note: The context menu does not have a Delete menu item.

 

 

You cannot delete a calculated measure or field that is:

- Used in another calculated field's or measure's formula

- Used in the view (appearing as a row or column)

- Used in a filter

 

You must first delete the calculated field from the places it is used before a Delete option will appear in its context menu.

27. 

In the Rows section of the layout band, click the x to the right of Years Since Remodel (Median) to remove it from the view.

28. 

Note: The measure is removed from the layout band and the view table.

29. 

In the Measures panel, right-click Years Since Remodel.

30. 

Now that this calculated field is no longer used, Delete is an option in its context menu.

 

Click the Delete menu item.

31. 

In the Confirm dialog, click the OK button.

32. 

Note: The Years Since Remodel measure is deleted from the Measures panel.

 

Task 2: Delete a calculated measure, is complete.

33. 

Begin Task 3: Create a calculated measure.

34. 

Point to the Options icon, on the right side of the Measures panel title bar.

35. 

Click the Create Calculated Measure... menu item.

36. 

Note: The New Calculated Measure window appears.

 

The Formula Builder tab is selected.

37. 

Enter the name: Years Since Remodel into the Measure Name field.

38. 

In the Functions list, click ElapsedYears.

39. 

Note: The function description appears.

40. 

Note: Verify that the Show arguments in formula checkbox is checked. If it is not, click the checkbox.

41. 

In the Functions list, double-click ElapsedYears.

42. 

Note: The ElapsedYears function appears in the formula.

 

 

Placeholders appear for the two arguments because the Show arguments in formula checkbox is checked.

43. 

Replace the first ElapsedYears argument placeholder "DateFieldName1" (including the double quotes) with the function Today().

 

Position your cursor at the end of  "DateFieldName1".  Then backspace until you have deleted it including both sets of double quotes.  The cursor will be in position for the next step.

44. 

In the Functions list, scroll down until you see the Today function.

 

Double-click the Today function.

45. 

Note: The Today function now appears as the first argument.

46. 

Remove the Today function argument placeholder Integer_Offset.  When no argument is specified, it returns today's date.

 

Position your cursor at the end of Integer_Offset.  Then backspace until you have deleted it.

47. 

Note: The Today function now appears without an argument.

48. 

Replace the second ElapsedYears argument placeholder "DateFieldName2" including the double quotes.

 

Position your cursor at the end of the "DateFIeldName2".  Then backspace until you have deleted it including both sets of double quotes.  The cursor will be in position for the next step.

49. 

In the Fields and Measures list, double-click Remodel Date.

50. 

Note: Remodel Date now appears as the second argument of the ElapsedYears function. 

 

The resulting formula is: 

ElapsedYears(Today(), “Remodel Date”)

51. 

Click the Validate button.

52. 

Note: If the message Validation Successful! does not appear to the right of the Validate button, review the previous steps, make the necessary corrections and try again.

53. 

Next, add a function to round the value returned by the ElapsedYears function. 

 

The entire ElapsedYears formula now in the formula field needs to be the argument to the Round function.

 

First, set up to insert the Round function. 

 

Click at the beginning of the Formula field to set the insertion point.

54. 

In the Functions list, double-click the Round function.

55. 

Note: Round is inserted at the beginning of the formula with two argument placeholders.

56. 

Note: Read the function description. It shows two arguments: NumberFieldName and Integer.

 

In this form, NumberFieldName is rounded, and the Integer argument specifies the number of decimal places.

 

There is another form of the Round function Round(NumberFieldName) where the number of decimal places is not specified, and assumed to be 0. Use this second form of the function.

57. 

Delete the Round function's second argument placeholder, Integer.

 

Position your cursor at the end of Integer.  Then backspace until you have deleted it and the preceding comma.

58. 

The second argument of the Round function is deleted.

 

Move the trailing text: ElapsedYears(Today(), "Remodel Date") in place of the Round argument "NumberFieldName" (including the double quotes). 

 

59. 

Note: The resulting formula is:

 

 

Round(ElapsedYears(Today(), "Remodel Date"))

60. 

Click the Validate button.

61. 

Note: If the message Validation Successful! does not appear to the right of the Validate button, review the previous steps, make the necessary corrections and try again.

62. 

Introduce an error into the formula.  Remove the last closing parenthesis.

63. 

Click the Validate button.

64. 

Note: An error message appears to the right of the Validate button in red text explaining the syntax error.

 

It says: Missing ")" at end of expression.

65. 

To fix the syntax error, insert a parenthesis at the end of the formula.

66. 

Note: There are now two parentheses at the end of the formula.

67. 

Click the Validate button.

68. 

Note: Evaluate the formula from the inside out:

 

 

- The Today() function returns today's date

- The Remodel Date is the date of the remodel

- ElapsedYears(Today(), "Remodel Date") returns the number of years between today and the Remodel Date. 

- Round(…) then rounds that number to a whole number with 0 decimal places.

69. 

Click the Create Measure button to complete calculated measure creation.

70. 

Note: The new measure appears in at the bottom of the Measures panel.

 

Scroll to the bottom of the Measures panel to see it.

71. 

Note: Next add the Years Since Remodel measure back into the view.

72. 

Right-click Years Since Remodel.

73. 

Click the Add to Rows menu item.

74. 

Note: Years Since Remodel is positioned last in the Rows area of the layout band.

75. 

Drag Years Since Remodel to the second position right after Country.

76. 

Note: The Years Since Remodel measure is now in the second position in the Rows area of the layout band.

 

The view now appears as it did before you deleted the Years Since Remodel calculated measure.

 

Task 3: Create a calculated measure, is complete.

77. 

Begin Task 4: Configure Summary Calculation.

78. 

In the Measures panel, right-click the Years Since Remodel measure.

79. 

Click the Edit menu item.

80. 

Note: The Edit Calculated Measure window appears.

81. 

Click the Summary Calculation tab.

82. 

Note: The Summary Calculation appears. The Summary Calculation is Sum.

 

It sums all of the Years Since Remodel values for all stores in the country for each of the categories specified by the columns, and displays those values.

 

This is an aggregate function because uses a collection of values to determine the sum

83. 

Next, change the Summary Calculation.

 

Click the Calculation drop-down list.

84. 

Click the Min list item.

85. 

Click the Save button.

86. 

Note: The Summary Calculation is Min.  For Years Since Remodel for all stores in the country, it finds and displays the minimum.

 

87. 

Note:  At the row Mexico / Years Since Remodel, and the column Non_Recyclable / Ancient, the current value is 47.00. The Summary Calculation determines, for all the stores in Mexico that have the column attributes Non-Recyclable and Ancient, which store has the minimum value for Years Since Remodel: That value is 47.00.

 

Each cell displaying a sub-total or total uses the Min Summary Calculation.

88. 

In the Measures panel, if the Sales node is not already expanded, expand it. 

 

Notice there are measures that are not calculated; they are missing the fx in the icon.

 

Right-click Store Sales 2013.

89. 

Note: In the context menu, there is no Edit menu item leading to a Summary Calculation setting.

 

The Edit menu item is only available for calculated fields and measures.

90. 

There is another method of changing the Summary Calculation that is accessible to both calculated and non-calculated fields and measures.

 

In the table, right-click Years Since Remodel.

91. 

Point to the Change Summary Calculation menu item.

92. 

Click the Average list item.

93. 

Note: The Summary Calculation is Average.  For Years Since Remodel for all stores in the country, it computes and displays the average.

 

This last method for changing Summary Calculation is the only method available for fields and measures that are not calculated.

94. 

Note: The measure name displayed in the Rows area of the layout band has changed to show the summary calculation name Average.

 

Years Since Remodel (Average)

 

Notice that each calculated field and measure in the layout band displays the name of its summary calculation in this way.

95. 

Note: Task 4: Configure a Summary Calculation, is complete.

96. 

Begin Task 5: Create a calculated field using a string function.

97. 

Next, create a calculated field that uses a string function to concatenate Country and State. For example, USA, CA

 

Point to the Fields panel Options Icon to the right of the Fields panel title.

98. 

Click the Create Calculated Field... menu item.

99. 

Note: The New Calculated Field window appears.

100. 

Enter Store Location into the Field Name field.

101. 

In the Functions list, double-click the Concatenate function.

102. 

Note: The Concatenate function with argument placeholders appears in the formula.

103. 

Replace the first argument placeholder "TextFieldName" with the Country field.

 

Delete "TextFieldName" including the two sets of double quotes.

104. 

In the Fields and Measures list, double-click the Country list item.

105. 

Note: The first argument is now "Country".

106. 

Replace the second argument placeholder, 'text string' with ', ' including single quotes. This concatenates a text comma to the string being built.

 

The resulting formula is:

Concatenate("Country",',', ...)

107. 

Note: There is already a comma after the text you just entered, right before the ellipsis.  

 

This comma is not like the one you just entered.  It will not be part of the text string being built.  It is the delimiter that separates arguments in the function signature. 

108. 

In the function signature, an ellipsis appears where a third argument would be placed.  Delete the ellipsis.

109. 

In the Fields and Measures list, double-click State.

110. 

Note: State is now added as third argument. The formula is now:

Concatenate("Country", ', ', "State")

 

If the value of the Country field is USA and the value of the State field is CA, the text string returned by this formula is: USA, CA

111. 

Click the Validate button.

112. 

Note: If the message Validation Successful! does not appear to the right of the Validate button, review the previous steps, make the necessary corrections and try again.

113. 

Click the Create Field button.

114. 

Note: The New Calculated Field window closes.

 

 

The new Store Location calculated field appears at the bottom of the Fields panel.

 

Task 5: Create a calculated field using a string function, is complete.

115. 

Begin Task 6: Examine a Boolean calculated field using the IF function.

116. 

Examine the formula expression for the calculated field Carbonated [Y/N]?, and the function description.

 

In the Fields panel, right-click the Carbonated [Y/N]? field.

117. 

Click the Edit menu item.

118. 

Note: The Edit Calculated Field window appears.

119. 

In the Functions list, click the Contains function to display that function description.

120. 

Note: The Contains function returns True if the first argument contains the second argument as a substring, otherwise it returns False.

121. 

Note: In the expression in the Formula field, the Contains function tests to see if the Product Name field contains the string Soda. The result will be True or False.

122. 

In the Functions list, click the IF list item to display the function description.

123. 

Note: The IF function tests if the first argument is True or False.  If it is True, it returns the second argument. If it is False, it returns the third argument.

124. 

Note: In the expression in the Formula field, when the Contains function returns TRUE, the IF function returns YES.  When the Contains function returns FALSE, the IF function returns NO.

125. 

Click the Cancel button.

126. 

Note: The Edit Calculated Field window closes.

 

 

Task 6: Examine a Boolean calculated field using the IF function, is complete.

127. 

Begin Task 7: Examine the IN operator and date/time constants.

128. 

In the Fields panel, right-click the Store's Era field.

129. 

Click the Edit menu item.

130. 

Note: The Edit Calculated Field window appears.

131. 

Note:  The IN operator returns TRUE or FALSE

 

 

"Remodel Date" in (ts'1970-03-11':ts'1985-12-31')

 

In the first IF function argument, the IN operator tests if the value in Remodel Date is between the dates 1970-03-11 and 1985-12-31.

 

The date range between the parentheses is expressed using two time stamp constants.

132. 

Note: The syntax ts'some date' designates a time stamp constant. 

 

 

Other constant types are:

- d'some date' which designates a date constant

- t'some time' which designates a time constant

133. 

Click the Cancel button.

134. 

Note: The Edit Calculated Field window closes.

 

 

Task 7: Examine the IN operator and date/time constants, is complete.

135. 

Begin Task 8: Examine aggregate functions and levels.

136. 

Search for Levels Example and then open that ad hoc view.

137. 

In the Measures panel, right-click Percent of RowGroup.

138. 

Click the Edit menu item to examine the aggregate functions and levels.

139. 

Note: The Edit Calculated Measure window appears.

140. 

In the Functions list, click PercentOf to display the function description.

141. 

Note: The PercentOf function description appears.

 

 

The PercentOf function is an aggregate function and has an argument to specify level.

142. 

Note: Aggregate functions are functions that use a collection of values to compute a result.  For example, PercentOf, Min, Max, Median and Sum. 

 

Aggregate functions can be used only in the formulas of calculated measures.  They cannot be used in formulas of calculated fields.

143. 

Note: Many aggregate functions accept an optional level, which specifies the grouping to use for the aggregate.

 

 

The level options are:

- Current (default)

- RowGroup

- RowTotal

- ColumnGroup

- ColumnTotal

- Total

 

The levels RowGroup and RowTotal are discussed ahead in this activity.

 

For a detailed explanation of level options see the JasperReports User Guide Release 5.6, section 4.6.6.1 Levels in Aggregate Functions.

144. 

Note: Examine the formula for this measure.  The current value specified is Store Sales 2013 and the level specified is RowGroup.

145. 

Next, examine current value and level using examples.

 

Click the Cancel button.

146. 

The Percent of RowGroup calculated measure uses the level RowGroup as the second argument in the formula:

PercentOf("Store Sales 2013", 'RowGroup').

 

This causes the generation of a section in the crosstab named Percent of RowGroup.

 

Click the to the left of the Percent of RowGroup section heading to expand the section.

147. 

Note: The level RowGroup refers to a Row Group Total.  There is a Row Group Total for each group shown in the crosstab.

148. 

Note: Next, see what the columns in the section of the crosstab called Percent of RowGroup describe about the Store Sales 2013 current value.

 

In row Canada / Deluxe Supermarket, column Store Sales 2013 / false, examine the current value, (Item 1) 102.17.

 

Percent of RowGroup (Item 2) (100%) describes that Item 1, the current value of 102.17, is 100% of Item 3, the RowGroup total 102.17

149. 

Note: Similarly, at row Canada / Mid-Sized Grocery, column Store Sales 2013 / true, examine the current value, (Item 1) 19.75 .

 

Item 2 (49.81%) describes that Item 1, the current value of 19.75, is 49.81% of Item 3, the RowGroup total 39.65

150. 

Note: In row Canada / Deluxe Supermarket, column Store Sales 2013 / Totals, examine the current value, (Item 1) 122.07.

 

Item 2 (86.07%) describes that Item 1, the current value of 122.07, is 86.07% of Item 3, the RowGroup total 141.82.

151. 

Next, explore the same PercentOf function using the level RowTotals.

 

The last Totals row of the crosstab represents RowTotals. It is the total of all row group totals.

 

In the Measures panel, right-click Percent of RowTotal.

152. 

Click the Edit menu item.

153. 

Note: The Edit Calculated Measure window appears.

 

 

In the Formula field, notice that the PercentOf function's second argument designating level, is RowTotal.

 

This causes the generation of the section named Percent of RowTotal in the crosstab.

154. 

Click the Cancel button.

155. 

In the crosstab, see the Percent of RowTotal section. 

 

Click the + to the left side of the Percent of RowTotal section heading to expand the section.

156. 

Note: In row Canada / Deluxe Supermarket, column Store Sales 2013 / true, examine the current value, (Item 1) 19.90.

 

Item 2 (24.75%) describes that Item 1, the current value of 19.90, is 24.75% of Item 3, the RowTotal 80.40.

157. 

Next, examine a calculated measure's Summary Calculation tab.

 

In the Measures panel, right-click Percent of RowGroup.

158. 

Click the Edit menu item.

159. 

The Edit Calculated Measure window appears.

 

Click the Summary Calculation tab.

160. 

The Summary Calculation tab appears.

 

Click the Calculation Drop-down.

161. 

There are three options:

 

None - No values are displayed for subtotals and totals in the chart.

 

AggregateFormula - (Default) Uses the formula specified under the Formula Builder tab to calculate values displayed for subtotals and totals in the chart.

 

Custom - Uses a custom formula specified in this tab, creating values to display for subtotals and totals in the chart. For example, in addition to displaying the value computed by the PercentOf function, you may want to add a % symbol.

 

Click the Custom menu item.

162. 

Build a custom formula that leverages the existing formula to add a percent sign; concatenate a percent sign to the value currently displayed in the chart.

 

Use the tools in the window to build the custom formula:

Concatenate(PercentOf("Store Sales 2013", 'RowGroup'), '%')

 

For your convenience, you may copy and paste this formula from the CopyAndPaste file.

163. 

Note: The Concatenate function adds a % symbol to the results of the original PercentOf formula.

164. 

Click the Validate button.

165. 

Note: If the message Validation Successful! does not appear to the right of the Validate button, review the previous steps, make the necessary corrections and try again.

166. 

Click the Save button to save the custom formula.

167. 

Note: The Edit Calculated Measure window closes and the view is updated with the custom Summary Calculation formula applied. 

 

Based on the % values displayed, notice there is more work to do to complete the formula.  The formula needs to account for null values in the chart and round percentages to two decimal places.  Do that next.

168. 

In the Measures panel, right-click Percent of RowGroup.

169. 

Click the Edit menu item.

170. 

Note: The Edit Calculated Measure window appears.

171. 

Click the Summary Calculation tab.

172. 

Replace the existing formula with the following expanded formula to account for null values in the chart and round percentages to two decimal places:

IF(PercentOf("Store Sales 2013", 'RowGroup') == null, null, Concatenate(Round(PercentOf("Store Sales 2013", 'RowGroup'), 2), '%'))

 

For your convenience, you may copy and paste this formula from the CopyAndPaste file.

173. 

Click the Validate button.

174. 

Note: If the message Validation Successful! does not appear to the right of the Validate button, review the previous steps, make the necessary corrections and try again.

175. 

Click the Save button.

176. 

Note: The Percentage of RowGroup section of the chart displays percentages with a % symbol.

177. 

Note: Task 8: Examine aggregate functions and levels, is complete.

178. 

Congratulations!

 

In this activity you completed the following eight tasks:

1. Explored a calculated measure

2. Deleted a calculated measure

3. Created a calculated measure

4. Configured a summary calculation

5. Created a calculated field using a string function

6. Examined a Boolean calculated field using the IF function

7. Examined the IN operator and date/time constants

8. Examined aggregate functions and levels

End of Procedure.