Introduction

The Pierce Application Core (PAC) is included in the County’s modern custom-developed applications including Legal Information Network Exchange (LINX), PALS Plus, Forensic Death Information System (FDIS), Person, School Threat System (STS) and IDPPOP.

PAC Components

PAC includes the following powerful configuration components that enable a power user to tailor an application to meet the needs of the users.

Conditions

Expressions

Definition

Expressions are made up of functions that use values, variables, or other functions.

Values

Values may be text or numbers.

“Pierce County”
987
444.03
There are two special text values.
“true” in lower case letters may be used in place of the Boolean true.
“false” in lower case letters may be used in place of the Boolean false.

Example:

HTMLLINK(“Pierce County”, “http://www.piercecountywa.org/”, “_blank”)

Variables

Variables are data source properties that have been included in a query.

person.firstName
person.lastName

Example:

CONCATENATE(person.firstName, person.lastName)

Functions

Functions are a way to make a new value from existing values, variables, and/or function results.

Example:

DAYOFYEAR(NOW())

Combination

A mix of values, variables, and functions may be used.

Example:

DATEFORMAT(NOW(), “dd/MM/YYYY”, “UTC”, “America/Los_Angeles”)

Nesting

Expressions may be nested.

Example: NOW function is nested in the DATEADD function which is nested in the DATEDIFF function.

DATEDIFF(“Days”,NOW(),DATEADD(1,”YEAR”,NOW()))

Supported Functions

Date and Time
Function Description Example

CURRENTTIMEMILLIS

Calculate the difference, measured in milliseconds, between the current time and midnight on January 1, 1970 UTC.

CURRENTTIMEMILLIS()

  • Takes no values, variables, or functions

Say today was Jan 6th, 2017 at 21:21:55, CURRENTTIMEMILLIS() would be 1483737715213

DATEADD

Create a new date by adding to an existing date.

DATEADD(number, datepart, date)

  • number: amount to add

    • may be

      • positive or negative

      • a whole number

      • a variable that contains a whole number

      • a function that gives a whole number as the result

  • datepart: part of the date to add to

    • should be in double quotes, is case insensitive, and may be

      • day

      • month

      • year

      • hour

      • minute

      • second

  • date: date to add to

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Dec 25th, 2017, DATEADD(1, “YEAR”, NOW()) would be Dec 25th, 2018

DATECREATE

Create a date from a day, month, and year.

DATECREATE(day, month, year)

  • day, month, and year

    • may be

      • a whole number

      • a variable that contains a whole number

      • a function that gives a whole number as the result

DATECREATE(22, 1, 1975) would be January 22nd, 1975

DATEDIFF

Calculate the difference between two dates.

DATEDIFF(unit-of-measure, first-date, second-date)

  • unit-of-measure: time unit of measure

    • should be in double quotes, is case insensitive, and may be

      • any of these common values

        • years

        • days

        • hours

        • millis

        • months

        • seconds

        • weeks

    • first-date and second-date: dates to determine the amount of time between

      • may be

        • a variable that contains a date

        • a function that gives a date as the result

Say today was Apr 4th 2012, DATEDIFF(“Days”, NOW(), DATEADD(1, “year”, NOW())) would equal 365

Say today was Feb 1st 2012, DATEDIFF(“days”, NOW(), DATEADD(1, “YEAR”, NOW())) would equal 366

DATEFORMAT

Format a date as text.

DATEFORMAT(date, format, optional-existing-time-zone, optional-convert-to-time-zone)

  • date: date to format

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

  • format: the way the date should be displayed

    • should be in double quotes, IS case sensitive, and may be

      • any of these date time format symbols

    • optional-existing-time-zone and optional-convert-to-time-zone:

      • does not have to be included

      • should be in double quotes, is case insensitive, and may be

        • any of the Canonical IDs in the available time zones

Say today was Apr 14th, 2007, DATEFORMAT(NOW(), “dd/MM/YYYY”) would be 14/04/2007

Say today was Jul 9th, 2016, DATEFORMAT(NOW(), “dd/MM/YYYY”, “UTC”, “America/Los_Angeles”) would change today’s UTC date appear as 09/07/2016 and be PDT time zone

DATEPARSE

Using a text format pattern, create a date from text.

DATEPARSE(format, text-date)

  • format: the way the date should be displayed

    • should be in double quotes, IS case sensitive, and may be

      • any of these date time format symbols

  • text-date: text to convert to a date

    • date and time parts should match the order and separators in the format text

DATEPARSE(“MM/dd/yyyy”,”1/22/1975″) would create a date of Wed Jan 22 00:00:00 PST 1975

DATEPARSE(“M/d/yy h:m”,”1/22/75 3:59″) would create a date of Wed Jan 22 03:59:00 PST 1975

DATEPARSE(“h:m”, “3:59”) would create a date of Thu Jan 01 03:59:00 PST 1970

DATEPART

Get the year, month, or day from a date.

DATEPART(date, datepart)

  • date: date to get a part of

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

  • datepart: part of the date to get

    • should be in double quotes, is case insensitive, and may be

      • Year

      • Month

      • DayOfMonth

      • DayOfYear

      • DayOfWeek

      • DayOfWeekInMonth

      • Second

      • Millisecond

Say today was Dec 31st, 1999, DATEPART(NOW(), “year”) would be 1999

DAYNAME

Get the day name (Monday, Tuesday, Wednesday, …) of a date.

DAYNAME(date)

  • date: date to get the name of the day in the week

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Apr 28th, 2014, DAYNAME(NOW()) would be Monday

DAYOFMONTH

Get the day (1 – 31) of the date.

DAYOFMONTH(date)

  • date: date to get the number of the day in the month

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Apr 28th, 2014, DAYOFMONTH(NOW()) would be 28

DAYOFWEEK

Get the day (1 – 7 Sunday being 1) of the week from a date.

DAYOFWEEK(date)

  • date: date to get the number of the day in the week

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Apr 28th, 2014, DAYOFWEEK(NOW()) would be 2

DAYOFYEAR

Get the day (1 – 356 or 366) of the year from a date.

DAYOFYEAR(date)

  • date: date to get the number of the day in the year

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Feb 13th, 1983, DAYOFYEAR(NOW()) would be 44

HOUR

Get the hour (0 – 11) of the date.

HOUR(date)

  • date: date to get the hour for a 12 hour clock

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say the time was 12:56 am, HOUR(NOW()) would be 0

Say the time was 2:56 pm, HOUR(NOW()) would be 2

HOUROFDAY

Get the hour of the day (0 – 23) of the date.

HOUR(date)

  • date: date to get the hour for a 24 hour clock

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say the time was 11:56 pm, HOUROFDAY(NOW()) would be 23

Say the time was 2:56 pm, HOUROFDAY(NOW()) would be 14

MINUTE

Get the minute (0 – 59) of the date.

MINUTE(date)

  • date: date to get the minute in the hour

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say the time was 11:56 pm, MINUTE(NOW()) would be 56

MONTH

Get the month (1 – 12) of the date.

MONTH(date)

  • date: date to get the number of the month

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Feb 13th, 1983, MONTH(NOW()) would be 2

MONTHNAME

Get the month name from the date or from the month number (1 – 12).

MONTHNAME(date)

  • date: date to get the name of the month

    • may be

      • a whole number (1 -12)

      • a variable that contains a date or a whole number (1 – 12)

      • a function that gives a date or a whole number (1 – 12) as the result

Say today was Feb 13th, 1983, MONTHNAME(NOW()) would be February

MONTHNAME(2) would be February

NOW

Get the current date/time.

NOW()

  • Takes no values, variables, or functions

Say today was Jan 6th, 2017 at 21:21:55, NOW() would be Jan 6th, 2017 at 21:21:55

QUARTER

Get the quarter (1 – 4) of the date.

QUARTER(date)

  • date: date to get the number of the quarter in the year

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Oct 31st, 2004, QUARTER(NOW()) would be 4

SECOND

Get the second (0 – 59) of the date.

SECOND(date)

  • date: date to get the second in the minute

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say the time was 11:56:09 pm, SECOND(NOW()) would be 9

WEEKOFMONTH

Get the week (1 – 6) of the month from a date.

WEEKOFMONTH(date)

  • date: date to get the number of the week in the month

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Jul 4th, 2011, WEEKOFMONTH(NOW()) would be 2

WEEKOFYEAR

Get the week (1 – 53) of the year from a date.

WEEKOFYEAR(date)

  • date: date to get the number of the week in the year may be

    • a variable that contains a date

    • a function that gives a date as the result

Say today was Jul 4th, 2011, WEEKOFYEAR(NOW()) would be 28

YEAR

Get the year of the date.

YEAR(date)

  • date: date to get the year

    • may be

      • a variable that contains a date

      • a function that gives a date as the result

Say today was Oct 31st, 2004, YEAR(NOW()) would be 2004
General
Function Description Example

BOOLEAN

Converts the value to a Boolean.

NOTE: “true” and “false” may be used in place of BOOLEAN(“true”) and BOOLEAN(“false”).

BOOLEAN(value)

  • value: value to be converted

    • may be

      • true or false text in double quotes

      • a variable that contains true or false text

      • a variable that contains a true or false Boolean

      • a function that gives either true or false text as the result

      • a function that gives either true or false Boolean as the result

BOOLEAN(“true”) would be true as a Boolean

Say the school was a public school, BOOLEAN(School.privateSchool) would be false as a Boolean

BOOLEAN(“yes”) would be false as a Boolean since “yes” is not a valid value

BOOLEAN(42) would be false as a Boolean since 42 is not a valid value

ENCODEASBASE64STRING

Encodes an array of bytes as a Base64 encoded text.

ENCODEASBASE64STRING(array-of-bytes, optional-type-prefix)

  • array-of-bytes: array to be converted

    • may be

      • a variable that contains an array of bytes

      • a function that gives an array of bytes as the result

  • optional-type-prefix:

    • any of these common shortcut values in double quotes

      • image

      • jpeg

      • jpg

      • png

    • or any properly formatted type prefix

      • Pattern

        • data:[<MIME-type>][;charset=<encoding>][;base64],

      • Example

        • data:image/png;base64,

ENCODEASBASE64STRING(Photo.Data, “png”) would be the content of Photo.Data encoded to Base 64 text

ENCODEASBASE64STRING(Photo.Data, “data:image/jpeg;base64,”) would be the content of Photo.Data encoded to Base 64 text

EXPRESSION

Converts the value to an expression and evaluates it.

EXPRESSION(value)

  • value: value to be converted

    • may be

      • text in double quotes that represents an expression

      • a variable that contains text that represents an expression

      • a function that gives text that represents an expression as the result

EXPRESSION(“ADD(1, 2)”) would be 3

Say GREETING contained ‘CONCATENATE(“Hello “, NAME)’ and NAME contained ‘Pierce County’, EXPRESSION(GREETING) would be Hello Pierce County

JSONARRAY

Creates a JSON array with the given values.

JSONARRAY(value, optional-value)

  • value: value to be converted

    • may be

      • whole or decimal number

      • text in double quotes

      • a date

      • a Boolean

      • any variable

      • any function

      • any combination

JSONARRAY(3,”too”,1) would be an array that contained the number 3 in the first position, “too” in the second position, and 1 in the third position

JSONARRAYGET

Gives back the item in a JSON array with a 0 based index.

JSONARRAYGET(json-array, index)

  • json-array: array to find an item in

    • may be

      • JSON array

      • a variable that contains a JSON array

      • a function that gives a JSON array as the result

  • index: location in the array to retrieve the item

    • may be

      • whole number

      • a variable that contains a whole number

      • a function that gives a whole number as the result

JSONARRAYGET(JSONARRAY(3,”too”,1), 2) would be too

JSONARRAYLENGTH

Gives the number of items in a JSON array.

JSONARRAYLENGTH(json-array)

  • json-array: array to get the number of items in it

    • may be

      • JSON array

      • a variable that contains a JSON array

      • a function that gives a JSON array as the result

JSONARRAYLENGTH(JSONARRAY(3,”too”,1) would be 3

JSONOBJECT

Creates a JSON object with the given keys and values.

NOTE: The values must be in pairs. First value is the key to the value and the second value is the value itself.

JSONOBJECT(key, value, optional-key, optional-value)

  • key: value to be the key to find the value in the object

    • may be

      • whole or decimal number

      • text in double quotes

      • a date

      • a Boolean

      • a JSON array

      • any variable

      • any function

      • any number of optional-keys as long as they are paired with an optional-value

  • value: value to be paired with a key

    • may be

      • whole or decimal number

      • text in double quotes

      • a date

      • a Boolean

      • a JSON array

      • any variable

      • any function

      • any number of optional-values as long as they are paired with an optional-key

Say FIRST contained ‘Emily’, JSONOBJECT(“first-name”, FIRST, “age”, 42) would be

key first-name that has a value of Emily

key age that has a value of 42

JSONOBJECTGET

Gives back the value in a JSON object with a given key.

JSONOBJECTGET(json-array, key)

  • json-array: array to find an item in

    • may be

      • JSON array

      • a variable that contains a JSON array

      • a function that gives a JSON array as the result

  • key: value that matches a key in the JSON object

    • may be

      • whole or decimal number

      • text in double quotes

      • a date

      • a Boolean

      • a JSON array

      • any variable

      • any function

JSONOBJECTGET(JSONOBJECT(“first-name”, FIRST, “age”, 42), “age”) would be 42

JSONPATH

Gives back the value in a JSON formatted text with a given path.

JSONPATH(json-formatted-text, $.path)

  • json-formatted-text: text to find an item in

    • may be

      • JSON formatted text

      • a variable that contains JSON formatted text

      • a function that gives a JSON formatted text as the result

  • $.path: value that matches a text key in the JSON formatted text

    • may be “$.” plus

      • text in double quotes

      • a variable that contains text

      • a function that gives text as the result

JSONPATH(“age: 42”, “$.age”) would be 42

STRING

Converts the value to text.

STRING(value)

  • value: value to be converted

    • may be

      • a whole or decimal number

      • text in double quotes

      • a date

      • a Boolean

      • any variable

      • any function

STRING(5493.07) would be 5493.07 as text instead of a decimal number

Say the school is a private school, STRING(School.privateSchool) would be true as text instead of a Boolean

STRING(DATECREATE(28,2,2017)) would be Tue Feb 28 00:00:00 PST 2017 as text instead of a Date

UUID

Creates a globally unique 36 character identifier.

UUID()

  • Takes no values, variables, or functions

UUID()’s identifiers look like:

  • 067e6162-3b6f-4ae2-a171-2470b63dff00

  • 54947df8-0e9e-4471-a2f9-9af509fb5889
HTML
Function Description Example

HTML

Creates a simple HTML tag.

HTML(tag, text)

  • tag: name of element

    • may be

      • any of the following in double quotes

      • h1 h2 h3 h4 h5 h6

      • p

      • em

      • strong

  • text: content of tag

    • may be

      • text in double quotes

      • a variable that contains text

      • a function that gives text or an HTML element as the result

Say Person.Last Name was Smith, HTML(“h2”, Person.Last Name) would be <h2>Smith</h2>

HTML(“p”, HTMLIMAGE(“http://www.piercecountywa.org/images/layout/design21/ClicktoHomeHP.jpg”)) would be <p><img src=’http://www.piercecountywa.org/images/layout/design21/ClicktoHomeHP.jpg’ ></p>

HTMLIMAGE

Creates an HTML image tag.

HTMLIMAGE(url, alternate-text)

  • url: path to resource

    • may be

      • a path as text in double quotes

      • a variable that contains a path in text

      • a function that gives a path in text as the result

  • alternate-text: a description of the image

    • may be

      • text in double quotes

      • a variable that contains text

      • a function that gives text as the result

HTMLIMAGE(“http://www.piercecountywa.org/images/layout/design21/ClicktoHomeHP.jpg”) would be <img src=’http://www.piercecountywa.org/images/layout/design21/ClicktoHomeHP.jpg’ alt=’Pierce County Logo – a white mountain on a blue background’>

HTMLLINK

Creates an HTML link tag.

HTMLLINK(text, url, optional-open-location)

  • text: text to display for link

    • may be

      • text in double quotes

  • url: path to resource

    • may be

      • text in double quotes

  • optional-open-location: how the browser should open the resource

    • does not have to be included

      • if not included, resource will open in current tab

    • may be

      • any of these common values in double quotes

        • _blank

          • opens in a new tab/window

        • _self

          • opens in current tab

      • or any from the target keyword complete list in double quotes

HTMLLINK(“Pierce County”, “http://www.piercecountywa.org/”, “_self”) would be <a href=’http://www.piercecountywa.org/’ target=’_self’>Pierce County</a>
Logical
Function Description Example

AND

Gives true when all of the values are true.

AND(value1, value2, optional-value)

  • value1, value2, optional-value: values on which to perform an ‘and’

    • may be

      • a variable that contains a boolean (true or false) value

      • a function that gives a boolean (true or false) as the result

      • any number of optional-values

Say a person’s phone number is published on the Pierce County intranet and they do want to recieve snow alerts, AND(PhoneType.personPhones.intranetInd, PhoneType.personPhones.pcwarnInd) would be true

Say a person’s phone number is not published on the Pierce County intranet and they do want to recieve snow alerts, AND(PhoneType.personPhones.intranetInd, PhoneType.personPhones.pcwarnInd) would be false

Say a person’s phone number is not published on the Pierce County intranet and they do not want to recieve snow alerts, AND(PhoneType.personPhones.intranetInd, PhoneType.personPhones.pcwarnInd) would be false

BETWEEN

Gives true when the first value is inclusively between the second and third values.

BETWEEN(between, first, last)

  • between: value to determine if it’s between or the same as either of the other two values

    • may be

      • a whole or decimal number

      • text in double quotes

      • any variable

      • any function

  • first: “smallest” value

    • must be same kind of value as between

  • last: “largest” value

    • must be same kind of value as between

BETWEEN(5, 1, 10) would be true

BETWEEN(-3, -1, 5) would be false

Say the person was hired on Apr 4th, 2004, BETWEEN(Person.regularHireDt, DATECREATE(4,4,2004), DATECREATE4,4,2014) would be true

EQUALS

Gives true when the two values are equal. Text comparison is case sensitive.

EQUALS(value1, value2)

  • value1 and value2: values to determine if they are equal

    • may be

      • a whole or decimal number

      • text in double quotes – case sensitive

      • any variable

      • any function

EQUALS(2, 2) would be true

EQUALS(“squirrel”, “SQUIRREL”) would be false

EQUALS(DATECREATE(22, 1, 1975), NOW()) would be false

EQUALSIGNORECASE

Gives true when the two strings are equals regardless of case.

EQUALSIGNORECASE(value1, value2)

  • value1 and value2: values to determine if they are equal

    • may be

      • a whole or decimal number

      • text in double quotes – case insensitive

      • any variable

      • any function

EQUALSIGNORECASE(2, 2) would be true

EQUALSIGNORECASE(“squirrel”, “SQUIRREL”) would be true

EQUALSIGNORECASE(DATECREATE(22, 1, 1975), NOW()) would be false

GREATERTHAN

Gives true when the first value is greater than the second value.

GREATERTHAN(value1, value2)

  • value1: value to determine if it’s greater than the second value

    • may be

      • a whole or decimal number

      • text in double quotes – case insensitive

      • any variable

      • any function

  • value2: value to determine if it’s less than the first value

    • must be same kind of value as value1

GREATERTHAN(10, 2) would be true

GREATERTHAN(2, 2) would be false

GREATERTHAN(“dog”, “dog”) would be false

GREATERTHAN(“Dog”, “dog”) would be false [because of the capital letter]

GREATERTHANOREQUAL

Gives true when the first value is greater than or equal to the second value.

GREATERTHANOREQUAL(value1, value2)

  • value1: value to determine if it’s greater than or equal to the second value

    • may be

      • a whole or decimal number

      • text in double quotes – case insensitive

      • any variable

      • any function

  • value2: value to determine if it’s less than the first value

    • must be same kind of value as value1

GREATERTHANOREQUAL(10, 2) would be true

GREATERTHANOREQUAL(2, 2) would be true

GREATERTHANOREQUAL(“dog”, “dog”) would be true

GREATERTHANOREQUAL(“Dog”, “dog”) would be false [because of the capital letter]

IF

Gives the second or third value depending on whether the first value is true.

IF(examined-value, true-value, false-value)

  • examined-value: value on which to determine if true or false

    • may be

      • a variable that contains a Boolean (true or false) value

      • a function that gives a Boolean (true or false) as the result

  • true-value: value to return if examined-value evaluates to true

    • may be any type of value

  • false-value: value to return if examined-value evaluates to false

    • may be any type of value

Say a question on a form does not have an answer, IF(ISNULL(Form Answer.Answer), 0, 10) would be 0

Say a question on a form does have an answer, IF(ISNULL(Form Answer.Answer), 0, 10) would be 10

Say today was Jan 18, 2017 and the address was updated Sep 18, 2016, IF(GREATERTHAN(Address.lastUpdated, DATEADD(-1, “year”, NOW())), “Address was updated in the last year”, “Address was not updated in the last year”) would be Address was updated in the last year

IN

Gives true when the first value is found in any of the other provided values.

IN(find-value, value, optional-value)

  • find-value: value to match to the other provided values

  • value: value to compare to the value being found

  • optional-value: other values to compare to the value being found

  • all must be of the same kind of value

  • all may be

    • whole or decimal number

    • text in double quotes

    • a variable that contains a whole or decimal number or text

    • a function that gives a whole or decimal number or text as the result

    • any number of optional-values

IN(42, 57) would be false

IN(42, 57, 42, 13, 999, 13, 0) would be true

Say the person’s last name was Black, IN(Person.Last Name, “Smith”, “Blackstone”, “Jones”) would be false

Say the person’s last name was Black, IN(Person.Last Name, “Smith”, “Black”, “Jones”) would be true

ISNULL

Gives true when the value is null.

(Null is not a value.)

ISNULL(variable-or-function)

  • variable-or-function: value to evaluate if it’s been set to something

    • may be

      • a variable

      • a function

Say a decedent’s social security number is not set, ISNULL(Decedent.SSN) is true

LESSTHAN

Gives true when the first value is less than the second value.

LESSTHAN(value1, value2)

  • value1: value to determine if it’s less than the second value

    • may be

      • a whole or decimal number

      • text in double quotes – case insensitive

      • any variable

      • any function

  • value2: value to determine if it’s greater than the first value

    • must be same kind of value as value1

LESSTHAN(10, 2) would be false

LESSTHAN(2, 2) would be false

LESSTHAN(“dog”, “dog”) would be false

LESSTHAN(“Dog”, “dog”) would be true [because of the capital letter]

LESSTHANOREQUAL

Gives true when the first value is less than or equal to the second value.

LESSTHANOREQUAL(value1, value2)

  • value1: value to determine if it’s less than or equal to the second value

    • may be

      • a whole or decimal number

      • text in double quotes – case insensitive

      • any variable

      • any function

  • value2: value to determine if it’s greater than the first value

    • must be same kind of value as value1

LESSTHANOREQUAL(10, 2) would be false

LESSTHANOREQUAL(2, 2) would be true

LESSTHANOREQUAL(“dog”, “dog”) would be true

LESSTHANOREQUAL(“Dog”, “dog”) would be true [because of the capital letter]

NOT

Turn true to false and false to true.

NOT(value)

  • value: value to reverse

    • may be

      • a variable that contains a boolean (true or false) value

      • a function that gives a boolean (true or false) as the result

Say an adoption docket number is not set, NOT(ISNULL(Adoption.docketNum)) would be false

Say an adoption docket number is set, NOT(ISNULL(Adoption.docketNum)) would be true

OR

Gives true when any of the values are true.

OR(value1, value2, optional-value)

  • value1, value2, optional-value: values on which to perform an ‘or’

    • may be

      • a variable that contains a boolean (true or false) value

      • a function that gives a boolean (true or false) as the result

      • any number of optional-values

Say a person’s phone number is published on the Pierce County intranet and they do want to recieve snow alerts, OR(PhoneType.personPhones.intranetInd, PhoneType.personPhones.pcwarnInd) would be true

Say a person’s phone number is not published on the Pierce County intranet and they do want to recieve snow alerts, OR(PhoneType.personPhones.intranetInd, PhoneType.personPhones.pcwarnInd) would be true

Say a person’s phone number is not published on the Pierce County intranet and they do not want to recieve snow alerts, OR(PhoneType.personPhones.intranetInd, PhoneType.personPhones.pcwarnInd) would be false
Number
Function Description Example

ABS

Calculate the absolute value of a number.

ABS(value)

  • value: value to get the absolute value of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

ABS(-12) would be 12

ABS(9.5) would be 9.5

ACOS

Calculate the arccosine of a number.

ACOS(value)

  • value: value to get the arccosine of

    • may be

      • a whole or decimal number between -1 and 1 inclusive

      • a variable that contains a whole or decimal number between -1 and 1 inclusive

      • a function that gives a whole or decimal number between -1 and 1 inclusive as the result

ACOS(-1) would be 3.141592653589793

ADD

Calculates the sum of the numbers.

ADD(value1, value2, optional-value)

  • value1, value2, optional-value: values to add together

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

      • any number of optional-values

ADD(1, 2) would be 3

ADD(3, 4.5, 6, 7, 8.9) would be 30.3

ASIN

Calculate the arcsine of a number.

ASIN(value)

  • value: value to get the arcsine of

    • may be

      • a whole or decimal number between -1 and 1 inclusive

      • a variable that contains a whole or decimal number between -1 and 1 inclusive

      • a function that gives a whole or decimal number between -1 and 1 inclusive as the result

ASIN(-1) would be -1.5707963267948966

ATAN

Calculate arctangent of a number.

ATAN(value)

  • value: value to get the arctangent of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

ATAN(1) would be 0.7853981633974483

CEIL

Calculate the ceiling of a number.

CEIL(value)

  • value: value to round up to the nearest whole number

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

CEIL(12.67) would be 13.0

CEIL(-1.1) would be -1.0

CEIL(4.3) would be 5.0

CEIL(4) would be 4.0

COS

Calculate cosine of a number.

COS(value)

  • value: value to get the cosine of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

COS(1) would be 0.5403023058681398

COT

Calculate cotangent of a number.

COT(value)

  • value: value to get the cotangent of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

COT(1) would be 0.642092615934331

DEGREES

Convert radians to degrees.

DEGREES(value)

  • value: value to convert

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

DEGREES(1) would be 57.29577951308232

DIVIDE

Divide each number by the next number.

DIVIDE(dividend, divisor, optional-divisor)

  • dividend: value to be divided

  • divisor: value to divide by

  • oprional-divisor: any number of additional values to divid by

    • any may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

DIVIDE(10, 2) would be 5.0

DIVIDE(6.4, 8) would be 0.8

DIVIDE(1.2, 2.3, 3.4, 4.5) would be 0.03410059676044331

DOUBLE

Convert the value to a double.

DOUBLE(value)

  • value: value to convert

    • may be

      • a whole or decimal number or a text number in double quotes

      • a variable that contains a whole or decimal number a text number in double quotes

      • a function that gives a whole or decimal number a text number as the result

DOUBLE(“1.23”) would be 1.23

DOUBLE(“3”) would be 3.0

DOUBLE(3) would be 3.0

EXP

Calculate the value of e to the x.

EXP(power)

  • e: base value that is a mathematical irrational constant (similar to π) and approximately equal to 2.718281828459045

  • power: value to raise the base by

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

EXP(0) would be 1.0

EXP(1) would be approximately 2.718281828459045

EXP(-1) would be approximately 0.36787944117144233

EXP(2) would be approximately 7.38905609893065

FLOOR

Calculate the floor of a number.

FLOOR(value)

  • value: value to round down to the nearest whole number

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

FLOOR(12.67) would be 12.0

FLOOR(-1.1) would be -2.0

FLOOR(4.3) would be 4.0

FLOOR(4) would be 4.0

LOG

Calculate natural (e) logarithm of a number.

LOG(result)

  • e: base value that is a mathematical irrational constant (similar to π) and approximately equal to 2.718281828459045

  • result: value to find the raised exponent of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

LOG(0) would be infinity

LOG(2.718281828459045) would be 1.0

LOG(0.367879441171442) would be approximately -1.0

LOG(7.389056098930649) would be approximately 2.0

LOG(10) would be approximately 2.302585092994046

MAX

Gives the largest number.

MAX(value1, value2, optional-value)

  • value1, value2, optional-value: values to compare

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

      • any number of optional-values

MAX(10, 5) would be 10

MAX(5.2, 10) would be 10

MAX(1.1, 3.3, 2.2, 0) would be 3.3

MIN

Gives the smallest number.

MIN(value1, value2, optional-value)

  • value1, value2, optional-value: values to compare

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

      • any number of optional-values

MIN(10.2, 5) would be 5

MIN(5, 10) would be 5

MIN(1.1, 3.3, 2.2, 0) would be 0.0

MOD

Calculate the remainder after one number is divided by the other number.

MOD(dividend, divisor)

  • dividend: value to be divided

  • divisor: value to divide by

    • either may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

MOD(14, 5) would be 4

MULTIPLY

Multiply each number with the next number.

MULTIPLY(value1, value2, optional-value)

  • value1, value2, optional-value: values to multiply together

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

      • any number of optional-values

MULTIPLY(10, 2) would be 20.0

MULTIPLY(6.4, 8) would be 51.2

MULTIPLY(1.2, 2.3, 3.4, 4.5) would be 42.227999999999994

NUMBERFORMAT

Format a number as a string.

If format does not accommodate the provided precision, value will be rounded up.

NUMBERFORMAT(number, format)

  • number: number to format

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

  • format: the pattern to use for formatting

    • should be in quotes, IS case sensitive

    • may be

      • any of these common values

      • 0: if no corresponding digit will show a 0

      • #: if no corresponding digit will ignore

      • .

      • ,

      • –

      • or any from the Number Format Pattern Syntax

NUMBERFORMAT(4.241234, “.”) would be 4.24

NUMBERFORMAT(4.241234, “00.##”) would be 04.24

NUMBERFORMAT(4.241234, “00.00”) would be 04.24

PI

Gives the number for π.

PI()

Takes no values, variables, or functions

PI() would be approximately 3.141592653589793

POW

Calculate the base to the exponent power.

POW(base, power)

  • base: value to be raised

  • power: value to raise the base by

    • either may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

POW(7, 2) would be 49

POW(7, -2) would be 0.02040816326530612

POW(-7, 2) would be 49

POW(-7, -2) would be 0.02040816326530612

RADIANS

Convert degrees to radians.

RADIANS(value)

  • value: value to convert

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

RADIANS(45) would be 0.7853981633974483

RANDOM

Pick a random number inclusively between two numbers.

RANDOM(first, last)

  • first: smallest value

  • last: largest value

    • either may be

      • a whole positive number

      • a variable that contains a whole positive number

      • a function that gives a whole positive number as the result

RANDOM(0, 10) would be a whole number between 0 and 10 inclusive

ROUND

Round the number.

If there is a tie (5), value will be rounded up.

ROUND(value)

  • value: value to round to the nearest whole number, 5 rounds up.

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

ROUND(12.67) would be 13

ROUND(-1.1) would be -1

ROUND(4.3) would be 4

ROUND(4) would be 4

ROUNDTODECIMAL

Round the number to a specific number of decimal places. Optionally choose to round up or round down – if not specified and there is a tie (5), value will be rounded up.

ROUNDTODECIMAL(value, number-decimal-places, optional-rounding-direction)

  • value: value to round to a specific number of decimal places.

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

  • number-decimal-places: number of decimal places to round to.

    • may be

      • a whole number

      • a variable that contains a whole number

      • a function that gives a whole number as the result

  • optional-rounding-direction: indicates to round up or round down. True rounds up and false rounds down. If not specified and there is a tie (5), value will be rounded up.

    • may be

      • a Boolean

      • a variable that contains a Boolean

      • a function that gives a Boolean as the result

ROUNDTODECIMAL(12.675, 2) would be 12.68

ROUNDTODECIMAL(12.675, 2, true) would be 12.68

ROUNDTODECIMAL(12.675, 2, false) would be 12.67

ROUNDTODECIMAL(-1.15, 1) would be -1.2

ROUNDTODECIMAL(4.398215, 1) would be 4.4

ROUNDTODECIMAL(4.398215, 3) would be 4.398

SIN

Calculate sine of a number.

SIN(value)

  • value: value to get the sine of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

SIN(1) would be 0.8414709848078965

SQRT

Calculate the square root of a number.

SQRT(value)

  • value: value to get the square root of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

SQRT(9) would be 3.0

SQRT(163.84) would be 12.8

SUBTRACT

Subtract one number from next number.

SUBTRACT(value1, value2, optional-value)

  • value1, value2, optional-value: values to subtract from each other

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

SUBTRACT(10, 3) would be 7.0

SUBTRACT(3, 10) would be -7.0

SUBTRACT(3, 10.5, 9) would be -16.5

SUBTRACT(10.5, 9, 3) would be -1.5

TAN

Calculate tangent of a number.

TAN(value)

  • value: value to get the tangent of

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

TAN(1) would be 1.5574077246549023

TRUNC

Gives the whole part of a number.

TRUNC(value)

  • value: value to truncate

    • may be

      • a whole or decimal number

      • a variable that contains a whole or decimal number

      • a function that gives a whole or decimal number as the result

TRUNC(9.78) would be 9

TRUNC(9) would be 9

TRUNC(-9.78) would be -9

TRUNC(0.0004) would be 0

TRUNC(-0.0004) would be -0
Text
Function Description Example

CONCATENATE

Combines multiple text values.

CONCATENATE(value1, value2, optional-other-values)

  • value1, value2, and optional-other-values: values to combine

    • all may be

      • text in double quotes

      • a variable that contains text

      • a function that gives text as the result

      • add as many optional-other-values as desired

Say NAME contained ‘Pierce County’, CONCATENATE(“Hello “, NAME) would be Hello Pierce County

DECODEBASE64STRING

Decode a base 64 encoded text to an array of bytes.

DECODEBASE64STRING(value)

  • value: value to be converted

    • may be

      • a variable that contains text that contains a base 64 representation of an image

      • a function that gives a base 64 representation of an image as the result

Say Base64Text containted a base 64 text value, DECODEBASE64STRING(Base64Text) would be a byte array of that text

ENDSWITH

Gives true when the value ends with the other value.

ENDSWITH(whole, part)

  • whole: value to check if it ends with another value

  • part: ending value

  • either may be

    • text in double quotes

    • any variable that contains text

    • any function that gives text as the result

Say the person’s first name was Betty, ENDSWITH(person.First Name, “ett”) would be false

INDEXOF

Find the index (starting with 0) of the first instance of the second value in the first value.

INDEXOF(whole, part)

  • whole: value to check where the part starts

  • part: value to find where it starts in the whole

    • either may be

      • text in double quotes

      • any variable that contains text

      • any function that gives text as the result

INDEXOF(“hello world”, “w”) would be 6

INDEXOF(“hello world”, “world”) would be 6

INDEXOF(“hello world of worlds”, “world”) would be 6

INDEXOF(“hello world”, “worlds”) would be -1

INDEXOF(“hello world”,”h”) would be 0

INDEXOF(“hello world”,”H”) would be -1 [because of the capital letter]

INTEGER

Convert the value to an integer.

INTEGER(value)

  • value: value to convert

    • may be any

      • a whole or decimal number

      • text in double quotes

      • any variable that contains whole or decimal number or text

      • any function gives whole or decimal number or text as the result

INTEGER(“6”) would be 6

INTEGER(6.4) would be 6

LASTINDEXOF

Find the index (starting with 0) of the last occurrence of the second string in the first string.

LASTINDEXOF(whole, part)

  • whole: value to check where the last matching part starts

  • part: value to find the last matching in the whole

    • either may be

      • text in double quotes

      • any variable that contains text

      • any function that gives text as the result

LASTINDEXOF(“hello world”, “l”) would be 9

LASTINDEXOF(“hello world”, “lo”) would be 3

LASTINDEXOF(“hello world of worlds”, “world”) would be 15

LASTINDEXOF(“hello world”, “worlds”) would be -1

LASTINDEXOF(“hello world”, “h”) would be 0

LASTINDEXOF(“hello world”,”H”) would be -1 [because of the capital letter]

LENGTH

Gives the count of letters, numbers, and symbols contained in the text.

LENGTH(value)

  • value: value to count

    • either may be

      • text in double quotes

      • any variable that contains text

      • any function that gives text as the result

LENGTH(“hello”) would 5

LENGTH(“4.321”) would be 5

LOWERCASE

Convert text to all small letters.

LOWERCASE(value)

  • value: value to change small letters

    • may be

      • text in double quotes

      • any variable that contains text

      • any function that gives text as the result

LOWERCASE(“Hello”) would be hello

LOWERCASE(“HELLO”) would be hello

LOWERCASE(“9:30 PM”) would be 9:30 pm

REPLACE

In the first value, replace all occurrences of what’s in the second value with what’s in the third value.

REPLACE(whole, part-to-replace, replace-with)

  • whole: value to have part of replaced

  • part-to-replace: value to find the first occurrence in the whole to be replaced

  • replace-with: value to put in place of the original value

    • all may be

      • text in double quotes

      • any variable that contains text

      • any function gives text as the result

REPLACE(“hello world”, “hello”, “hi”) would be hi world

REPLACE(“1234.12”, “12”, “99”) would be 9934.99

STARTSWITH

Gives true when the first value starts with the second value.

STARTSWITH(whole, part)

  • whole: value to check if it starts with another value

  • part: starting value

    • all may be

      • text in double quotes

      • any variable that contains text

      • any function gives text as the result

STARTSWITH(“Hello there”, “Hello”) would be true

STARTSWITH(“Hello there”, “hello”) would be false [because of the small letter]

STARTSWITH(“123456”, “1234”) would be true

STARTSWITH(“1234xyz”, “1234”) would be true

SUBSTRING

Gives the letters, numbers, and symbols found between and including the start and finish numbers. Beginning of value would be the number 0.

SUBSTRING(whole, start-location, optional-end-location)

  • whole: value to copy letters, number, and symbols from

    • may be

      • text in double quotes

      • any variable that contains text

      • any function that gives text as the result

  • start-location: where to start in the whole to begin copying

    • may be

      • whole number

      • any variable that contains a whole number

      • any function that gives a whole number as the result

  • optional-end-location: where to stop in the whole to end copying

    • does not have to be included (if not included, will copy to the end of the text)

    • may be

      • whole number

      • any variable that contains a whole number

      • any function that gives a whole number as the result

SUBSTRING(“ABCDEFG”, 2, 5) would be CDE

SUBSTRING(“ABCDEFG”, 0, 5) would be ABCDE

SUBSTRING(“ABCDEFG”, 4) would be EFG

TEMPLATE

Use a pattern to build text.

TEMPLATE(pattern, %value1, optional-%values)

  • pattern: sequence of %specifiers, letters, numbers, and symbols.

    • %specifiers will be replaced in order with the %values

    • there must be the same number of %specifiers as %values in the pattern

    • %specifiers may be

      • any of these common format specifiers

        • %d

          • specifier for a whole number

        • %f

          • specifier for a decimal number

          • if format has less precision than the number provided it will be rounded up

        • %s

          • specifier for text

        • %%

          • show the percent sign

      • or any from the format specifiers in the ‘FORMATTED OUTPUT WITH printf‘ section

  • %value1: value to put in place of the first %element

  • optional-%values: value to put in place of the other %specifiers in the order they appear

  • %values may be any kind of value

TEMPLATE(“My %s %s!”, “Pierce”, “County”) would be My Pierce County!

Say a person’s name was John Q. Public, TEMPLATE(“%s %s %s”, Person.Last Name, Person.Middle Name, Person.First Name) would be Public Q. John

TEMPLATE(“PC!”,”Pierce”,”County”) would be PC! as no %s was provided

Say today was Oct 24th, 1991, TEMPLATE(“Today is: %s”, DATEFORMAT(NOW(), “YY MMMM 13”)) would be Today is: 24 October 91

TEMPLATE(“%d days to go!”, 99) would be 99 days to go!

TEMPLATE(“%d %s”, 99, ” days to go!”) would be 99 days to go!

TEMPLATE(“%f”, 13) would be 13.000000

TEMPLATE(“%.2f”, 13) would be 13.00

TRIM

Remove spaces found at the very beginning and very end of text.

TRIM(value)

  • value: value to remove extra spaces from

    • may be

      • text in double quotes

      • a variable that contains text

      • a function that gives text as the result

TRIM(” hello sunshine! “) would be hello sunshine!

TRIM(” hello sunshine! “) would be hello sunshine!

UPPERCASE

Convert text to all capital letters.

UPPERCASE(value)

  • value: value to change to capital letters

    • may be

      • text in double quotes

      • any variable that contains text

      • any function that gives text as the result

UPPERCASE(“Hello”) would be HELLO

UPPERCASE(“heLLO”) would be HELLO

UPPERCASE(“9:30 pm”) would be 9:30 PM

Constraints

Security

Reporting

Queries

Introducing Dynamic Queries

Reports

How to Create a Simple Jasper Report

Prerequisites: 1) Create a query that does not contain subqueries and 2) download and install Jaspersoft® Studio.

Run your query and export the result as an “XML (Report)”. Save the downloaded XML file to MyReports in your Jaspersoft work space. The default location is C:\Users\$USERNAME\JaspersoftWorkspace\MyReports. (Replace $USERNAME with your login name.)

In Jaspersoft Studio, create a data adapter as follows:

  • Select File / New / Data Adapter.

  • Select “XML document” as the type of data adapter.

  • Enter the query name as the name of the data adapter.

  • Select the XML file from the C:\Users\$USERNAME\JaspersoftWorkspace\My Reports. (Replace $USERNAME with your login name.)

  • Choose “Use the report Xpath expression when filling the report”

  • Press the Finish button.

  • Set the XPath to /root/queries/query/results/DataObject.

Set the REPORT_PATH defaultValueExpression to C:/Users/$USERNAME/JaspersoftWorkspace/MyReports. (Replace $USERNAME with your login name.)

To be continued…

Workflow

Task Definitions

Task Events

Forms

Tags

Definition

Tags are a means to associate components, such as queries and reports, to multiple categories.

Guidelines

Industry Recommendations

  • Establish a tagging system

  • Make tags consistent

    • Singular vs. plural

    • Nouns vs. verbs

    • Capitalization

    • Usage of spaces

    • Usage of special characters

  • Keep tag names short

  • Err on the side of using too many tags rather than too few tags

  • Don’t replicate terms

  • Limit usage of abbreviations

  • Use words that identify what is recognizable about the item you are tagging

  • Revisit tag selection from time to time

Pierce County Requirements

  • Make tags consistent

    • Use mixed case for better readability

    • Use spaces for better readability

    • Do not use anything other than letters, numbers, or spaces to avoid duplicates where the only difference might be a hyphen

  • Err on the side of using too many tags rather than too few tags

    • Say you needed to tag queries with the following values: Blue, Red, Yellow, Blue Red, Blue Yellow, Red Yellow, Blue Red Yellow

    • If short tag names were used, you would only have to create 3 tags

    • If long tag names were used, you would have to create 7 tags

Query Tag Values Needed Short Names Short Name Usage Long Names Long Name Usage

Blue

Blue

Blue

Blue

Blue

Red

Red

Red

Red

Red

Yellow

Yellow

Yellow

Yellow

Yellow

Blue Red

Blue + Red

Blue Red

Blue Red

Blue Yellow

Blue + Yellow

Blue Yellow

Blue Yellow

Red Yellow

Red + Yellow

Red Yellow

Red Yellow

Blue Red Yellow

Blue + Red + Yellow

Blue Red Yellow

Blue Red Yellow