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, rightclick 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, doubleclick a field or measure in the Fields and Measures
list. The list includes other calculated fields and measures and
noncalculated fields and measures.

16.

Note: The Formula Builder displays a
list of available functions. Doubleclick 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, rightclick 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, rightclick 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, doubleclick 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.
Doubleclick 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, doubleclick 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, doubleclick 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.

Rightclick 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, rightclick 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 dropdown 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 NonRecyclable
and Ancient, which store has the minimum value for Years Since
Remodel: That value is 47.00.
Each cell displaying a subtotal 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.
Rightclick 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 noncalculated
fields and measures.
In the table, rightclick 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, doubleclick 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, doubleclick
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, doubleclick 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, rightclick 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, rightclick 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'19700311':ts'19851231')
In the first IF function argument, the IN
operator tests if the value in Remodel Date is between the
dates 19700311 and 19851231.
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, rightclick 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 / MidSized 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, rightclick 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, rightclick 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 Dropdown.

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, rightclick 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.
