Creating Calculated Fields & Measures Activity

In this activity you complete the following eight tasks:

1. Explore a calculated measure

2. Delete a calculated measure

3. Create a calculated measure

4. Configure a summary calculation

5. Create a calculated field using a string function

6. Examine a Boolean calculated field using the IF function

7. Examine the IN operator and date/time constants

8. Examine aggregate functions and levels

*Audio is included in this module.*

Begin Task 1: Explore a calculated measure.

Login as** joeuser/joeuser**

Search for **10. Calculated**

The search results appear.

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

Click the **Filters** **minimize/maximize button** to minimize the Filters panel.

The **Filters** panel is minimized.

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

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.

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

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

Click the **Delete** menu item.

In the **Confirm** dialog, click the **OK** button.

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

Click the **Create Calculated Measure...** menu item.

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

In the **Functions** list, click **ElapsedYears**.

In the **Functions** list, double-click **ElapsedYears**.

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

For the purpose of training, when you double-click the **Today** function in the **Functions** list, the replacement occurs.

In the **Functions** list, double-click **Today.**

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

For the purpose of training, when you click **Integer_Offset**, the argument placeholder will be removed.

Click **Integer_Offset**.

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

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

Click the **Create Measure** button to complete calculated measure creation.

Begin Task 2: Delete a calculated measure.

For the purpose of training, you are already logged in as joeuser and the view** 10. Calculated Fields and Measures** is displayed in the Ad Hoc Editor.

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

Next, begin looking at that view.

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.

Click the **Edit** menu item.

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.

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

A formula can be typed manually in the **Formula** field.

The **operator toolbar**** **provides basic math and logical operators. Insert an operator into a formula by clicking an operator in the operator toolbar.

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.

Click the **Validate** button to validate the formula.

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

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

The **Absolute** function description appears.

Validation is successful.

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

The **Edit Calculated Measure** window closes.

Task 1: Explore a calculated measure, is complete.

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.

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

Task 2: Delete a calculated measure, is complete.

Begin Task 3: Create a calculated measure.

The** New Calculated Measure** window appears.

The** Formula Builder** tab is selected.

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

The **ElapsedYears** function appears in the formula.

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

The function description appears.

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.

The **Today** function now appears as the first argument.

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.

The **Today** function now appears without an argument.

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

For the purpose of training, when you click **"DateFieldName2"**, the argument is removed and the cursor is in place for the next step.

Click **"DateFIeldName2"**.

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.

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

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.

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

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.

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.

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

**Measures**

Right-click **Years Since Remodel.**

Click the **Add to Rows** menu item.

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

For the purpose of training, when you click **Years Since Remodel** it is repositioned.

Click **Years Since Remodel**.

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

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.

Begin Task 4: Configure Summary Calculation.

For the purpose of training, the **Store Sales 2013** measure appears in the view.

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

Click the **Edit** menu item.

Click the **Summary Calculation** tab.

Next, change the **Summary Calculation**.

Click the **Calculation **drop-down list.

Click the **Min** list item.

Click the **Save** button.

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

Right-click **Store Sales 2013**.

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**.

Point to the **Change Summary Calculation **menu item.

Click the **Average** list item.

**The Edit Calculated Measure** window appears.

The **Summary Calculation** appears** . **The Summary Calculation is

The Summary Calculation satisfies the question: For **Years Since Remodel**, for every store in the country, what is the median value?

This is an aggregate function because it looks at a collection of values to determine the median.

At the row **Mexico **/ **Years Since Remodel**, and the column **Non_Recyclable **/ **Ancient**,** **the** c**urrent 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.

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.

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

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

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.

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.

Click the **Create Calculated Field...** menu item.

Enter **Store Location **into the **Field Name** field.

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

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

First, delete **"TextFieldName" **including the double quotes.

For the purpose of training, when you click **"TextFieldName" **it will be deleted.

Click **"TextFieldName"**.

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

The **New Calculated Field **window appears.

The **Concatenate** function with argument placeholders appears in the formula.

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

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

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

For the purpose of training, when you click ** 'text string'** the replacement occurs.

Click **'text string'**.

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

For the purpose of training, when you click the ellipsis, it will be deleted.

Click the** ellipsis**.

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

Click the **Validate** button.

Click the **Create Field** button.

**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**

Validation is successful.

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.

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

Click the **Edit** menu item.

Click the **Cancel** button.

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

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.

Click the **Edit** menu item.

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

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

Click the **Cancel** button.

The **Edit Calculated Field **window appears.

In this case, the **Contains** function tests to see if the **Product Name** field contains the string **Soda.** The result will be **True** or **False.**

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.

In this case, when the **Contains** function returns **TRUE**, the **IF** function returns **YES**. When the **Contains** function returns **FALSE**, the **IF** function returns **NO**.

The **Edit Calculated Field** window closes.

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

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

The **Edit Calculated Field **window appears.

The **IN** operator returns **TRUE** or **FALSE**.

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.

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

The **Edit Calculated Field** window closes.

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

Begin Task 8: Examine aggregate functions and levels.

For the purpose of training the **Levels Example** view is open in the Ad Hoc Editor.

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

Double-click the **Today** function.

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

For the purpose of training, when you click **Integer**, it is deleted.

Click **Integer**.

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

For the purpose of training, when you click the blank space at the end of the formula, an ending parenthisis will be inserted.

Click the **blank space** at the end of the formula.

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

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

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

Task 4: Configure a Summary Calculation, is complete.

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.

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

The first argument is now **"Country"**.

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

Validation is successful.

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).

For the purpose of training, when you click **ElapsedYears,** the move occurs.

Click **ElapsedYears**.

Validation is successful.

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

For the purpose of training, when you click the closing parenthesis, it will be removed.

Click the closing **parenthesis**.

The closing parenthesis is removed.

Validation is successful.

The formula is now correct.

Next add the **Years Since Remodel **measure back into the view.

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

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

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

The **Edit Calculated Measure **window appears.

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

The **PercentOf** function description appears.

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

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.

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

The resulting formula is:

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

Next, examine current value and level using examples.

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.

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**.

Click the **Edit** menu item.

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.

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

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

Click the **Edit** menu item.

The **Edit Calculated Measure** window appears.

Click the **Summary Calculation** tab.

The **Summary Calculation** tab appears.

Click the **Calculation Drop-down**.

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.

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.

For the purpose of training, when you click the insertion point at the beginning of the formula, the appropriate additions to the formula will be made.

Click **the insertion point** at the beginning of the formula.

Click the **Validate** button.

Click the **Save** button to save the custom formula.

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

Click the **Edit** menu item.

Click the **Summary Calculation** tab.

For the purpose of training, when you click the formula, it will be replaced with an expanded formula that accounts for null values in the chart, and rounds percentages to two decimal places.

Click **the formula**.

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

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**.

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**.

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**.

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.

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**.

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.

Validation is successful.

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.

The **Edit Calculated Measure** window appears.

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.

Validation is successful.

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

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

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.

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

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

** Note: **The

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

Creating Calculated Fields & Measures Activity

**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

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

In the **Functions** list, click **ElapsedYears**.

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

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

The function description appears.

Click the **Add to Rows** menu item.

Click the **Edit** menu item.

Click the **Min** list item.

Click the **Create Calculated Field...** menu item.

Enter **Store Location **into the **Field Name** field.

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

Click the **Edit** menu item.

Click the **Edit** menu item.

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

Double-click the **Today** function.

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

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

Click the **Edit** menu item.

Click the Filters button.

Right-click the Add to Rows Create Filter Edit object.

Click the Show Layout Band list item.

Click the Years Since Remodel (Median) object.

Right-click the object.

Click the Delete object.

Click the object.

Point to the Create Calculated Measure... object.

Click the Create Calculated Measure... object.

Enter the desired information into the Measure Name: field. Enter

a valid value

e.g.

"Years Since Remodel"

.Click the ElapsedDays list item.

Double-click the object.

Click in the Formula: field.

Click the Remodel Date list item.

Click the Validate button.

Double-click the Round list item.

Click the Create Measure button.

Right-click the object.

Click the Edit object.

Click the object.

Click the Absolute list item.

Click the Cancel button.

Double-click in the Formula: field.

Click in the Formula: field.

Double-click in the Formula: field.

Click in the Formula: field.

Right-click the Add to Rows Create Filter Use as Field Edit Delete object.

Click the Add to Rows list item.

Right-click the Add to Rows Create Filter Edit object.

Click the Edit list item.

Click the Summary Calculation object.

Click the Calculation: list.

Click the Min list item.

Click the Save button.

Right-click the Add to Rows Create Filter object.

Right-click the Change Summary Calculation None Average CountAll CountDi object.

Point to the Change Summary Calculation None Average CountAll CountDi list item.

Click the Average list item.

Point to the Create Calculated Field... object.

Click the Create Calculated Field... list item.

Enter the desired information into the Field Name: field. Enter

a valid value

e.g.

"Store Location"

.Double-click the Concatenate list item.

Click the object.

Click the object.

Double-click the State list item.

Click the object.

Click the object.

Right-click the Create Filter Edit object.

Click the Edit list item.

Click the object.

Right-click the Add to Columns Add to Rows Create Filter Use as Measure object.

Click the Edit list item.

Click the object.

Click the IF list item.

Click the object.

Double-click the Today list item.

Click the Round Rounds a number to a specified number of digits. Round("N object.

Click the Validate button.

Click the Validate button.

Double-click the Country list item.

Click in the Formula: field.

Click the Validate button.

Click the Validate button.

Click the Validate button.

Right-click the Sales % Group list item.

Click the Edit object.

Click the PercentOf list item.

Click the Cancel button.

Click the Percent of RowGroup object.

Right-click the object.

Click the Edit object.

Click the Cancel button.

Click the object.

Right-click the object.

Click the Edit object.

Click the object.

Click the object.

Click the object.

Click the object.

Click the object.

Click the Save button.

Right-click the Percent of RowGropu object.

Click the Edit list item.

Click the Summary Calculation object.

Click the Validate button.

Click the Save button.

DPIINFODIV

Frame ID: