If using Brio 5.x, see Brio 5.x Data Functions.

CONTENTS

• Concatenation
• Concatenation with a Delimiter
• Count
• Count Distinct
• Decode
• Grouping
• to Hours, Minutes, Seconds
• Name Formatting
• Nvl
• Percent of Column
• Random
• Rounding
• Substring
• Upper & Lower Case
• Logic Tips

1. In Query section, select Request button and then select Query menu (In Results section, select Results menu).
2. Select Add Computed Item.
3. Name the new data item in the Modify Item dialog box.
4. Press the Functions... button on right in dialog box.
5. Select String Functions from the Function Categories window in the Functions dialog box.
6. Select Concat from the Functions available in the window to the right.
7. Complete the table by referencing the first column name (or text) and the second column name (or text).
8. Click OK in the Functions dialog box.
9. Click OK in the Modify Item dialog box.
10. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, select Request button and then select Query menu.
2. Select Add Computed Item.
3. Name the new data item in the Modify Item dialog box.
4. Press the Reference button on right in dialog box.
5. Reference the topic (table) and first data item and click OK
6. Type two vertical bars or pipes, i.e. ||
7. Type the delimiter between single quotes, i.e. for a delimiter of a comma followed by a space, type ', '
8. Type two vertical bars or pipes, i.e. ||
9. Press the Reference button on right in dialog box
10. Reference the topic (table) and second data item and click OK.
11. Click OK in the Modify Item dialog box.
12. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line.
2. Press the Functions... button on right in dialog box.
3. Select Aggregate Functions from the Function Categories window in the Functions dialog box.
4. Select Count from the Functions available in the window to the right.
5. Complete the table by referencing the column name.
6. Click OK in the Functions dialog box.
7. Click OK in the Item Properties dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line.
2. Press the Functions... button on right in dialog box.
3. Select Aggregate Functions from the Function Categories window in the Functions dialog box.
4. Select Count Distinct from the Functions available in the window to the right.
5. Complete the table by referencing the column name.
6. Click OK in the Functions dialog box.
7. Click OK in the Item Properties dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line (In Results section, select Results menu, select Add Computed Item and name the new data item in the dialog box).
2. Press the Functions... button on right in dialog box.
3. Select Conditional Functions from the Function Categories window in the Functions dialog box.
4. Select Decode from the Functions available in the window to the right.
5. Complete the table by adhering to the "expression, search, result, default" format. Reference the data element you wish to decode as your expression followed by a comma, type the value you wish to search for followed by a comma, type the result (replacement) for such a value followed by a comma and a default value or data element to be returned in records that do not contain the value that was specified in the "search" criteria. Many search and result values can be nested within the Decode statement.
6. Click OK in the Functions dialog box.
7. Click OK in the Modify Item dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

1. In Results section, select the column from which to select values and select Results menu.
2. Select Add Grouping Column.
3. Name the new grouped column in the Grouped Column dialog box.
4. Press New Group button, name the first new group and click OK.
5. Complete the table by selecting the desired values from the Available Values window and clicking on the << button to add values to the window.
6. Repeat the last two steps as many times as necessary to form all appropriate groups in the new grouped column.
7. Click OK in the Grouped Column dialog box.
8. To review/modify the grouped column, select the column, right-click and select Modify Column.

1. In Query section, select Query menu, select Add Computed Item and name the new data item in the dialog box.
2. In the panel, type To_HHMISS followed by a left paren
3. Next, use the Reference button to select Seconds data element from the table and type a right paren.
4. Click OK in the Modify Item dialog box.
5. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, select Query menu, select Add Computed Item and name the new data item in the dialog box.
2. In the panel, type Namefmt followed by a left paren.
3. Next, type the Mode letter in single quotes, followed by a comma.
   
   Possible Modes are:
   A Smith, J.
   B Smith Jr., Dr. John Robert
   C Dr. John Robert Smith,Jr. (for Commencement Program. Same as type R except for "," Jr and Sr suffixes.)
   D Smith Jr., Dr. John R
   F John Smith
   G John Smith Jr.
   H Dr. John Smith Jr.
   I J. Smith
   J J. Smith Jr.
   L Smith
   M John R. Smith
   N John R. Smith Jr.
   R Dr. John Robert Smith Jr.
   S Smith Jr
   X John
   Y Robert
   
   
4. Use the Reference button to select the Name data element from the table and type a comma.
   • If the Prefix and Suffix data elements are not available in the table:
     1. Type a set of single quotes, followed by a comma.
     2. Type a second set of single quotes, followed by a right paren.

        Namefmt('M','Table.Name','','')

   • If the Prefix and Suffix data elements are available in the table:
     1. Use the Reference button to select the Prefix data element from the table and type a comma.
     2. Use the Reference button to select the Suffix data element from the table and type a right paren.

        Namefmt('R','Table.Name','Table.Prefix','Table.Suffix')

5. Click OK in the Modify Item dialog box.
6. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line (In Results section, select Results menu, select Add Computed Item and name the new data item in the dialog box).
2. Press the Functions... button on right in dialog box.
3. Select Conditional Functions from the Function Categories window in the Functions dialog box.
4. Select Nvl from the Functions available in the window to the right.
5. Complete the table by referencing the column name and entering the replacement for a null value.
6. Click OK in the Functions dialog box.
7. Click OK in the Modify Item dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

1. In Pivot section, populate the Outliner to display the desired data items.
2. Add a Total column by selecting the appropriate column header tab (to the right of the column header) and pressing the sigma button (or right-click and select Add Totals).
3. Alt-click on the Total column heading. This selects the Total column and values contained within it.
4. Select Pivot menu, select Data Function and select % of Column (or right-click, select Data Function and select % of Column).

1. In Query section, select Query menu, select Add Computed Item and name the new data item in the dialog box.
2. In the panel, type Random.Random followed by a left paren
3. Next, type the maximum possible random number (for example, 100 would return a random value from 1 to 100, inclusive) and type a right paren.
4. Click OK in the Modify Item dialog box.
5. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line (or select item and select Modify).
2. Press the Functions... button on right in dialog box.
3. Select Numeric Functions from the Function Categories window in the Functions dialog box.
4. Select Round from the Functions available in the window to the right.
5. Complete the table by specifying the number or digits to the right of the decimal to which the server should round (use a zero to indicate the return of a whole number).
6. Click OK in the Functions dialog box.
7. Click OK in the Item Properties dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, select Request button and then select Query menu (In Results section, select Results menu).
2. Select Add Computed Item.
3. Name the new data item in the dialog box.
4. Press the Functions... button on right in dialog box.
5. Select String Functions from the Function Categories window in the Functions dialog box.
6. Select Substr from the Functions available in the window to the right.
7. Complete the table by referencing the column name, the first character you wish to display and the total number of characters you wish to include in the display of the substring.
8. Click OK in the Functions dialog box.
9. Click OK in the Modify Item dialog box.
10. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line (In Results section, select Results menu, select Add Computed Item and name the new data item in the dialog box).
2. Press the Functions... button on right in dialog box.
3. Select String Functions from the Function Categories window in the Functions dialog box.
4. Select Upper from the Functions available in the window to the right.
5. Complete the table by referencing the column name.
6. Click OK in the Functions dialog box.
7. Click OK in the Modify Item dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

1. In Query section, double click the intended item on the Request Line (In Results section, select Results menu, select Add Computed Item and name the new data item in the dialog box).
2. Press the Functions... button on right in dialog box.
3. Select String Functions from the Function Categories window in the Functions dialog box.
4. Select Initcap from the Functions available in the window to the right.
5. Complete the table by referencing the column name.
6. Click OK in the Functions dialog box.
7. Click OK in the Modify Item dialog box.
8. To review/modify the computed item double click on the item name on the Request Line.

• Item names are not case-sensitive.
• Type the word null (no quotes) into the expression panel to represent null values.
• All text string constant values entered in expressions must be enclosed in double quotes.
• All date constant values entered in expressions must be enclosed in single quotes.
• Numbers can be entered without quotes.
• To join items with a space or other character, simply reference or type items and strings into the expression panel and join them with the + operator (for example, City + ‘, ‘ + State). To join without additional characters, use the Concat function.
• If, then, else logic statements are assembled in natural language form.
  For example:
  if Stu_Cnt_Current >= Stu_Cnt_Freeze then Stu_Cnt_Current else Stu_Cnt_Freeze
  Returns the Stu_Cnt_Current value if that value is greater than or equal to the Stu_Cnt_Freeze value, otherwise it returns the Stu_Cnt_Freeze value.
• Computed items with expressions that yield mixed data type values (for example, if Score > 50 then Score else ‘Fail') default to Char (String) data type.
• In division operations, the divisor may not be null or equal to zero. If a data item serves as the divisor in an expression (for example, 5000 / Units_Sold) and includes null or zero values, first create a computed item using if/then/else logic to remove null and zero values, and then compute the item containing the division operation.
• Two date items may be subtracted, but not added. The Add Month function adds an integer value to a date.
• You may not nest functions inside the Sum, Cume, Chr, and Breaksum functions.