Below is a list of examples that you can use when adding a new formula.
Formula: [vprEmployees].[FirstName] + ' ' + [vprEmployees].[LastName]
Result: One field displaying the employee’s first name, a space, and then the employee’s last name
Formula: [vfaAssets].[UnitCost] * [vfaAssets].[Quantity]
Result: Unit cost multiplied by quantity to calculate total cost (asset value)
Formula: ([vfaAssets].[TotalCost] - [vfaAssets].[SalvageValue]) / [vfaAssets].[UsefulLife]
Result: Total cost less the salvage value divided by the number of years of useful life to calculate the depreciated amount per year for an asset
Formula: LEFT([vprEmployees].[MiddleName], 1)
Result: An employee’s middle initial (first character from the left part of the field)
Formula: RIGHT([vprEmployees].[LastName], 4)
Result: Last four characters of an employee’s last name (last four characters from the right part of the field)
Formula: SUBSTRING([vprEmployees].[LastName], 4, 3)
Result: Three characters of an employee’s last name starting with the fourth character
Formula: UPPER([vprEmployees].[LastName])
Result: An employee’s last name in all uppercase even though it may have been keyed in upper- and lowercase letters
Formula: LOWER([vprEmployees].[LastName])
Result: An employee’s last name in all lowercase even though it may have been keyed in upper- and lowercase letters
Formula: LEN([vprEmployees].[LastName])
Result: Number of characters of an employee’s last name
Formula: YEAR([vfaAssets].[PurchaseDate])
Result: Year when an asset was purchased
Formula: MONTH([vfaAssets].[PurchaseDate])
Result: Month when an asset was purchased
Formula: DAY([vfaAssets].[PurchaseDate])
Result: Day when an asset was purchased
Formula: CASE WHEN [vprEmployees].[EmployeeStatus] = 'Terminated' THEN [vprEmployees].[TerminationDateLatest] ELSE NULL END
Result: If an employee has an Employee Status of terminated, the latest termination date will print; otherwise, will be blank
Tip: This formula would be defined with Date as the Data Type.
Formula: CASE WHEN [vprEmployees].[EmployeeStatus] = 'Terminated' THEN CONVERT(VARCHAR, [vprEmployees].[TerminationDateLatest], 101) ELSE 'N/A' END
Result: If an employee has an Employee Status of terminated, the latest termination date will print (using the format of mm/dd/yyyy); otherwise, N/A will print
Tip: This formula would be defined with Text as the Data Type.
Formula: CASE [vprEmployees].[EmployeeStatus] WHEN 'Terminated' THEN 'Terminated On '+CONVERT(VARCHAR, [vprEmployees].[TerminationDateLatest], 101) WHEN 'Retired' Then 'Retired On '+CONVERT(VARCHAR, [vprEmployees].[TerminationDateLatest], 101) ELSE 'Not Applicable' END
Result: If an employee has an Employee Status of terminated, Terminated On followed by the latest termination date will print (using the format of mm/dd/yyyy); or if an employee has an Employee Status of retired, Retired On followed by the latest termination date will print (using the format of mm/dd/yyyy); otherwise, Not Applicable will print
Tip: This formula would be defined with Text as the Data Type.
Formula: DATEDIFF(YEAR, [vfaAssets].[PurchaseDate], CONVERT(DATE,'07/01/2001'))
Result: Number of years the district has had the asset as of July 1, 2001
Formula: DATEDIFF(YEAR, [vfaAssets].[PurchaseDate], GETDATE())
Result: Number of years the district has had the asset as of the current date (based on the current date set on the computer)
Formula: DATEDIFF(MONTH, [vfaAssets].[PurchaseDate], CONVERT(DATE,'07/01/2001'))
Result: Number of months the district has had the asset as of July 1, 2001
Formula: (DATEDIFF(MONTH, [vfaAssets].[PurchaseDate], CONVERT(DATE,'07/01/2001')))/12.00
Result: Number of years and months the district has had the asset as of July 1, 2001
Formula: DATEDIFF(DAY, [vfaAssets].[PurchaseDate], CONVERT(DATE,'07/01/2001'))
Result: Number of days the district has had the asset as of July 1, 2001
Formula: DATEADD(YEAR, 2, [vfaAssets].[PurchaseDate])
Result: A date equaling the purchase date of an asset plus two years
Formula: DATEADD(MONTH, 6, [vfaAssets].[PurchaseDate])
Result: A date equaling the purchase date of an asset plus six months
Formula: DATEADD(DAY, 20, [vfaAssets].[PurchaseDate])
Result: A date equaling the purchase date of an asset plus 20 days
Formula: DATEADD(YEAR, 1, GETDATE())
Result: A date equaling one year from the current date (based on the current date set on the computer)