Calculation Examples

Brio Calculation Examples
Concatenation- Joining of multiple columns together to make one column:
Method 1: Banner field1 + " " + Banner field2 – Use this format in Resuls
Method 2: Concat( Banner field1, Banner field2 ) or Srpstdnt.Rln_Last_Name || ', ' ||Srpstdnt.Rfn_First_Name - Use this format in Query
Example: Spriden.Spriden_Last_Name+", "+Spriden.Spriden_First_Name
Substring- Returns n number of characters of the column based upon the designated starting character:
Substr (Banner field1, 1, 3 )—returns the first three characters of the Banner field
Example: Substr (Tcs_Uid, 7, 5 ) – where the Tcs_Uid field contains "20110310001" this will return the CRN "10001"
Decode – Substitute an output value for a database value
Example: DECODE (Spbpers.Spbpers_Sex,'M','Male','F','Female','Other')
If/Then- Provide conditional reasoning for groupings or calculations:
Examples:
if (Period_To_Date_Amt <= 1000 ){"Group 1" } else {"Group 2" }
if (Email_Alias == null) {Goremal_Email_Address} else {Email_Alias} (note double equal sign)
if (Period_To_Date_Amt <= 1000 ) {1000} else {Period_To_Date_Amt}
if (Sfbrgrp_Activity_Date != null) {"Y"} else {"N"} (NOTE: this is a "not equal" condition)
if ((Test_Scores == 'Y') && (Recruit == 'N' ) && (Application == 'N') && (Workcard== 'N') && (Transfer == 'N' ) && (Student == 'N') && (Registration == 'N') && (Housing== 'N') && (Faculty== 'N') && (Accounts_Receivable == 'N') && (Financial_Aid == 'N') && (Admissions_Imaging == 'N') && (Financial_Aid_Imaging== 'N')) {"OK" }
Multiple If/Then- Similar to If/Then, but applies multiple conditions:
if (Period_To_Date_Amt <= 1000 ) {"Group 1"} else{
if (Period_To_Date_Amt <= 2000 ) {"Group 2" }else{
if (Period_To_Date_Amt <= 3000 ) {"Group 3" }else{
Period_To_Date_Amt } } }
Computing Age (in the Results):
MonthsBetween ( Sysdate() , Spbpers_Birth_Date  )/12
Today's date
Use the function Sysdate(). This will return today's date for use in a calculated field.
Changing a date to a text field
Dates can be difficult to manage, especially if you want to concatenate them to other fields. Also, if you wish to use a date field in a pivot table to do calculations per day, month, year, etc., you should change the date to a character field.
Use the ToChar function to convert a date to a text string:
ToChar ( Spbpers_Birth_Date, "mm/dd/yy" )
AddMonths- Shows actual Fiscal Quarter for any date (multi-step process):
AddMonths ( date, 6 )
Cume function does a running total
Use in the Results section: Cume(Rev_Amount).
Instr (Instring function) – returns the position in a string where specified value occurs
Example of Instr with a substr to strip away the Apt number from an address.
if (Substr ( Street_address, 1, (Instr ( Street_address, 'Apt', 1, 1 ) )>0) ) {Substr ( Street_address, 1, (Instr ( Street_address, 'Apt', 1, 1 )-1 )) } else {Street_address}