Page tree

Versions Compared


  • This line was added.
  • This line was removed.
  • Formatting was changed.


 write indicator formulas and import template transformation scripts


A lot of the power in ProjectorBI comes from writing formulas to manipulate your data. This page is not an exhaustive explanation of all methods, but it does cover examples that are common when manipulating Projector data. It is written to target those with at least some programming experience. If you do not have any programming experience, do not worry! We try and provide both business context and code examples to aid you in creating your own formulas. If you have trouble understanding the system, you can work with a Projector Consultant to teach you, or have them implement your ideas.

Formulas in ProjectorBI are driven by Groovy, a superset of Java. Because we need to protect the system from things like infinite loops or otherwise expensive code, only a subset of Groovy API is supported. Of that subset, you may find that the syntax has been changed. If you already know Groovy or Java, you'll likely be able to learn this quickly. There are three main sources of documentation to learn about formulas.

  1. This page as an introduction
  2. BellaDati documentation to learn about specific formula functions and how they work
  3. Groovy documentation to learn coding syntax and conventions.

Formulas are used in two main areas of ProjectorBI. First, they are used when building your own indicators in reports. For example, a formula indicator to calculate accrued vacation. Second, they are used when importing data to transform it. For example, convert a double to an integer on import. You can also use formulas to modify values already stored in a data set, but that is rare.

The following links go directly to BellaDati's documentation. These are the pages I find most useful when writing formulas.

Permissions and Settings

Users with the role Data Set Manager can apply formulas to data as it is imported. They can also transform existing values in a data set via formula.

Users with the role Report Editor can create indicators driven by formulas.


If you are more familiar with statically typed languages like Java or C#, you may find this introduction to Groovy useful -

As the ProjectorBI code editor isn't the most sophisticated of development environments, you may find it appropriate to use a web-based Groovy editor such as to play with Groovy.

Some pointers when you are first getting started ( )

  • Semi-colons are optional and often not used at all
  • Return keyword is optional. The last evaluated statement is returned (which could be inside an if/else statement)
  • def keyword is of type object. Used for holding dynamically typed variables. 
    • def myString = "123"
    • def myInteger = 3
    • def myDouble = 3.0
  • Parenthesis can be omitted for top-level expressions. println("Hello") is the same as println "Hello". When used with closure, parenthesis may be omitted. For example, list.each { println "hi" } instead of list.each ( { println "hi" } )
  • Single line comment with //comment 
  • block-comment with /* comment */
  • When using strings, single quotes are a java.lang.string. Double quotes create a Gstring (Groovy string) which can be templated
    • def javaString = 'Hi there'
    • def groovyString = "Hi there" //template string supports neat tricks like "My name is $name". $name will be dynamically replaced by the variable it holds.

Full Groovy API is described here ( ). Remember that ProjectorBI only supports a subset of that API and that the syntax may be different.

When to use

There are three places in ProjectorBI where you are likely to use formulas. They are in:

  • Import templates - transform values as they are imported into a data set. Useful for massaging imported data as it enters the system to conform to your business needs.
  • Attribute/Indicator transformations - perform a one time transformation of an attribute or indicator in a data set. Useful for correcting values that are already in a data set.
  • Reports - define indicators programmatically for reports. Useful when you need to compute a value from other indicators. For example, Utilization = Hours Worked / Hours Supposed to Work.

Indicator and Attribute Access

This section shows you how to access indicators and attributes in your formulas. The method you use depends on where you are using the formula (as just discussed in the previous section). The following three code blocks explain how each area works for indicator and attribute access.

Code Block
titleImport Template Editor
//cast data to the appropriate type before trying to manipulate it
//new columns, marked with an *, are not accessible by code until they are saved for the first time. You can access them by column number instead.
//attribute example
def location = value('L_LOCATION')
//indicator examples
def age = integerValue('M_AGE')
def util = doubleValue('M_BILLABLE_UTILIZATION')
//access via column number example
def column1 = value(1)  //you can access column data by its column location rather than its code

Code Block
titleAttribute/Indicator Transformations
//Use value functions. Formula editor automatically casts to the correct type.
//attribute example
def location = value('L_LOCATION')
//integer examples
def i = value('M_INDICATOR_INTEGER')
def d = value('M_INDICATOR_DOUBLE')

Code Block
titleFormula Indicators
//indicator example
def hours = M_PERSON_HOURS
//attribute example
def location = memberValue('L_LOCATION')   //attributes are accessible through the function memberValue(). The function only works if you have a drill-down for that attribute. (Why? - if you ask for the location without a drill-down, which of potentially many locations should be returned?)

Data Types

Groovy is a dynamic language and will attempt to cast variables to the appropriate type when necessary. Dynamically typed variables are declared using the def keyword. Def is actually an object in Groovy. Def is implicitly declared for all assignments, so you don't even need to type it out. But we type it out in the examples here for clarity's sake.

Code Block
//using def
def age = 35
def name = "Tom"
println "$name is $number years old"
println age + 10
//def is an optional keyword and is used implicitly when not specified 
age2 = 35  //notice we didn't put def in. still works.
//you can see the class of an object by calling .class
def a = value('M_UNITS')
a.class //returns java.math.BigDecimal

Native Types

You can also explicitly declare your data types using the primitive java types. However, we typically recommend using def and letting Groovy handle the job of figuring out the correct data type. A full explanation of primitives and class-based data types are covered here -

Code Block
byte b1 = 42
short s1 = 20000
int i1 = 1000000000
long l1 = 1000000000000000000
float f1 = 3.5f
double d1 = 3.5e40
boolean = true

//objects (can be used instead of primitives). Notice they generally start with a capital letter.
Byte b2 = 42
Short s2 = 20000
Integer i2 = 1000000000
Long l2 = 1000000000000000000
Long l3 = 42L
Float f2 = 3.5f
Double d2 = 3.5e40
Double d3 = 3.5d
BigInteger bi = 30g
BigDecimal bd = 3.5g
Boolean = true


Casting between data types

Code Block
//declare a string
def string = "300"

//cast string to integer
def i3 = string.toInteger()
def i4 = string as int
def i5 = string as Integer
def i6 = new Integer(string)
def i7 = Integer.parseInt(string)
def i8 - integerValue(string)

titleTricky Data Types

Import Templates treat everything as strings. You'll need to specifically cast to the appropriate data type.

Code Block
//cast to integer
def i = integerValue('M_INDICATOR')
//cast to double
def d = doubleValue('M_INDICATOR')

You'll likely use the following casts most frequently. 

Data TypeCast FunctionExampleNotes
Indicatornonedef a = M_INDICATORreturns java.math.BigDecimal
Dynamicvalue()def a = value('M_INDICATOR')

By leveraging def, we let Groovy do the heavy lifting of sorting out the data type. When using value() in the context of formula indicators, here are the data types returned.

value('M_PERSON_HOURS') .class == java.lang.Double
value('L_ENGAGEMENT_PRIMARY_COS').class  == class java.lang.String 
value('L_MONTH') .class == org.joda.time.Partial 

When working in the context of import templates, value always returns a string. Make sure you use one of the other casting methods if you need to perform things like mathematical operations.

value('M_PERSON_HOURS') .class == class java.lang.String 
value('L_ENGAGEMENT_PRIMARY_COS').class  == class java.lang.String 
value('L_MONTH') .class == class java.lang.String 

IntegerintegerValue()def i = integerValue('M_INDICATOR_INTEGER')
DoubledoubleValue()def d = doubleValue('M_INDICATOR_DOUBLE')

Integer i = 1000
String s = i.toString()

Only works on object data types, not primitives. (i.e. - def and Integer work. int does not)
LocalDatedateValue()def d = dateValue()LocalDate is a class that only exists within ProjectorBI . You cannot use Groovy date objects.
LocalTimetimeValue()def t = timeValue()LocalTime is a class that only exists within ProjectorBI . You cannot use Groovy time objects.

Conditions and Loops

Because of the danger of client code triggering infinite loops or other performance degrading code, there is a very limited number of methods that you can use in ProjectorBI. This section details the available methods. Loops like while and for are not allowed.

There are a few functions which allow you to simulate loops and are protected from causing performance issues. Please see membersSum and forEachRow further down this page.

If / Else

Code Block
def val = value('M_HOURS') as double
if (val > 100) {
    result = "Too high"
} else {
    result = "Success"
return result;


Code Block
def val = value('M_HOURS') as double
return val > 100 ? "Too high" : "Too low"


Notice the power of Groovy's switch statement. You can check for values in an array, within a number range, etc.

Code Block
def x = '5' as string
switch (x) {
    case "Specific string value":
         result = "Contains specified string value"
    case [4, 5, 'a', 'b']:
         result = "Is 4, 5, a, or b."
    case 12..30:
         result = "In range"
    case Number:
         result = "Is number"
         result = "Default"

Math Functions

A full list is available here -

Some examples and commonly used options are in this table.

Math FunctionExampleNotes
MaxMax(1,2) = 2Return the larger of two numbers.
MinMin(1,2) = 1Return the smaller of two numbers.
CeilCeil(3.5) = 4Round up to nearest integer.
FloorFloor(3.5) = 3Round down to nearest integer.
RoundRound(1.3) = 1Round to nearest integer
AbsAbs(-3) = 3Return absolute value


When accessing attributes or indicators from a method, you must use their unique codes. Codes can be encased in either single or double quotes. This guide uses single quotes because double quoted strings are actually Gstrings and have some special features that could unintentionally be invoked.


Returns the first alphabetically, lowest number, earliest date, or earliest time - depending on the type of attribute/indicator you are looking at. If no data or non-existent attribute code, then blank string is returned. 

For example, if you had a hundred rows of data that all contained dates, return the earliest date.

Often used for retrieving the Actuals Cut Off Date (ACOD) in our formulas.

Code Block
def date = firstValue('L_ATTRIBUTE_CODE')
def time = firstValue('L_ATTRIBUTE_CODE')
def String = firstValue('L_ATTRIBUTE_CODE')
def BigDecimal = firstValue('M_INDICATOR_CODE')


Returns the last alphabetically, highest number, latest date, or latest time - depending on the type of attribute/indicator you are looking at. If no data or non-existent attribute code, then blank string is returned. 

For example, if you had a hundred rows of data that all contained dates, return the latest date.

Often used for retrieving the Actuals Cut Off Date (ACOD) in our formulas.

Code Block
def date = lastValue('L_ATTRIBUTE_CODE')
def time = lastValue('L_ATTRIBUTE_CODE')
def String = lastValue('L_ATTRIBUTE_CODE')
def BigDecimal = lastValue('M_INDICATOR_CODE')


Limit the drill down members based on your parameters. You can comma separate a list of items. 

Code Block
//find revenue for contractors in north america and europe
members('[L_RESOURCE_TYPE=Contractor][L_LOCATION=North America,Europe]', {M_PERSON_HOURS * M_RATE} ) 


Filters are used to extract rows of data that fit your needed criteria. For example, if a data set contains rows of data for both projected and actual data, you can limit the results to just one type. You can also limit based on indicator values. For example, only show me negative cost cards.  Filters can also be nested to further refine the information shown.

Code Block
//filter based on attribute values. Returns aggregated revenue across a group of cost centers
filter("L_COST_CENTER in ('Paris','Berlin','London')") {
    return M_REVENUE
//filter based on indicator values. Returns rows where more than 100k of revenue was earned.
filter('M_REVENUE > 100000') { L_COST_CENTER_COUNT }
//nest filters to return actual and chargeable hours
filter("L_DATA_TYPE in ('Actual')") { 
	filter("L_TIME_CARD_CHARGEABLE_Y in ('Yes')") {       
		return value('M_PERSON_HOURS')

//the previous formula could also be written this way
filter("L_DATA_TYPE in ('Actual') AND L_TIME_CARD_CHARGEABLE_Y in ('Yes')") {       
		return value('M_PERSON_HOURS')

//you can check for nulls like this
//if a filter excludes all rows from the result set, rather than returning zero, it returns a null. You should check for this and convert to 0 where appropriate
filter("L_ENGAGEMENT_TYPE in ('xxxxxxxxx')") {
      tmbudget = M_BUDGETED_RESOURCE_DIRE;
if (tmbudget == Double.NaN) { tmbudget = 0 }

For full documentation on filters please see


When performing mathematical operations on indicators, it may be necessary to perform that math on each row as opposed to the aggregated row data. The easiest way to imagine this is to think of a table. If you have a data set with Rate and Hours, you want to calculate revenue earned. Well that's simple. Use the formula:

Revenue = M_HOURS * M_RATE

However, if you did this, your "answer" might be wrong. See the table below.


Notice how you end up with 900 of revenue. That is because the multiply operation occurred on the aggregated row data. If you instead used this formula

Code Block
//calculate revenue earned at the row basis
revenue = forEachRow( 'M_HOURS * M_RATE', 'SUM')

In this case you get the correct answer - $200 in revenue. 

You'll find forEachRow useful when you have multiple rows that must be combined into a single virtual row of data. 

membersSum() membersMin() membersMax() membersAvg() eachMember()

Unfortunately these formulas can introduce large performance problems. We recommend that they not be used if possible. Try ForEachRow(), FirstValue(), or LastValue() as alternatives. In the event those functions cannot fit your needs, then consider these functions. The members() functions loop over all rows in a data set, performing an operation as they go. The loop is limited to 5000 operations, so large data sets won't work with membersSum. The data is simply truncated with no warning. See ProjectorBI and Performance for more.

Code Block
count += membersSum( M_UNITS )  //sum units across all rows


Most useful when using CrossValue. Return's the attribute code of the current drill down.

Code Block
//current drill down is cost center
memberIdentifier()   //[L_COST_CENTER='Europe']

//if there are multiple drill-downs on a table then memberIdentifier will return all of them
memberIdentifier()   //[L_ENGAGEMENT_CODE = 'P00001' AND L_PROJECT_CODE='P00001-001']
//use with cross value to pass current drill down along
crossValue('GINSU.GINSU', memberIdentifier(), 'M_REVENUE')   //revenue in the Ginsu data set, limited by current drill down

//in the event that you need to filter on just a single drill-down, see crossValue section of this help instead


Used to get an attribute's current value. Can only be used if you are actually drilling down on that attribute. For example, look at the table below which has three drill-down attributes specified.

Engagement CodeProject CodeProject ManagerBudget


Code Block
//get deepest drill down's attribute value
def a = memberValue()    //returns Project Manager
//get drill-down from specific attribute
def a = memberValue('L_PROJECT_CODE')     //returns project code
//get drill-down based on relative depth
def a = aggregatePrevLevel(2){ memberValue() }   //returns engagement code (notice the integer 2. So we go two levels up from deepest drill down)


Go up N steps in the drill-down tree and aggregate an indicator there. Useful when you need to know the amount of an indicator relative to a higher level drill down. For example, Tom worked 20 hours. All resources worked 100 hours. Tom contributed 20% of all hours worked.

Cost CenterResourceHoursAggregatePrevLevel(1)


Code Block
//go up one level in the drill down tree
aggregatePrevLevel(1) { M_PERSON_HOURS }
//calculate percentage relative to parent drill down
M_PERSON_HOURS / aggregatePrevLevel(1) { M_PERSON_HOURS }


Return the current row's rank in context of its drill down. 

Code Block
//find rank
def i = rank( M_PERSON_HOURS )    //for the person that worked the fifth most hours, you'd get "5" as the result


Value() extracts the current row's value and casts it to the most appropriate data type. It should really only be used when transforming attribute/indicator values. Which basically means - almost never.

Code Block
//value is smart enough to convert to the appropriate data type
Integer a = value('M_INTEGER')
BigDecimal a = value('M_DOUBLE')
String a = value('L_LOCATION')
def a = value('L_DATE')  //remember that date object isn't accessible in ProjectorBI, so use generic def instead
def a = value('L_TIME')  //remember that date object isn't accessible in ProjectorBI, so use generic def instead
String a = value()       //get the current attribute/indicator's value. Codes only need to be passed in if you are accessing another members value.

The only other place value() is of use is in the import template editor. Here, it always returns an attribute/indicator as a string. Unless you are extracting strings, you'll likely want to use one of its sister methods to extract the value and cast to the appropriate type. 

  • integerValue()
  • doubleValue()
  • dateValue()
  • timeValue()

If you wanted to you could also forgo the above methods and cast values using typical Groovy.

Code Block
 //there are several ways to cast variables
Integer i = integerValue()
Integer i = value() as Integer
//remember that date object is unique to ProjectorBI 
def d = dateValue()
def d = date(value())


Date manipulation is used so often in ProjectorBI formulas that I've broken date methods into their own section. Here you will learn how to manipulate dates, limit data shown based on dates, and aggregate data between dates.

ProjectorBI uses the Groovy partial class to handle dates and time -

Just use def when working with dates. If for some reason you need to declare an actual date object, you have to use the full class path.

Code Block
//create dates this way. 
//actualDate() gets today's date as a string. date() turns a string into a date object. 
def myDate = date(actualDate())
//but if you really want to, you can declare a date object this way
org.joda.time.Partial myDate = date(actualDate()) 

For the purposes of this documentation, we'll be referencing these date object types. 

  • LocalDate - just the date portion
  • LocalTime - just the time portion
  • LocalDateTime - both date and time
  • Partial - returned by firstValue and lastValue (documentation on partials is incomplete. need to flesh this out.)

titleDates vs Strings

 When you are reviewing the following date methods, make sure you understand the object type they are expecting as inputs or produce as outputs. Does the method produce a string? Does it produce a date?

A full explanation of ProjectorBI dates can be found at this link -

Date Formats

By default, all dates in ProjectorBI are assumed to be in the format M/dd/yyyy or 3/01/2015.

Code Block
actualDate() // returns 3/02/2015 for March 2, 2015.
//Month does not have a preceding zero
//Day does have a preceding zero
//Four digit year
//Fore-slashes as separators

Time is assumed to be in the format H:mm:ss a or 3:01:02 PM.

Code Block
actualTime() // returns 3:01:02 PM for 3 hours, 1 minute, 2 seconds in the afternoon.
//Hour does not have a preceding zero
//Minute and second do have a preceding zero
//AM/PM is used instead of a 24 hour clock

A domain administrator can change the format for both dates and time from Manage Domain | Format Settings link. If you change the format, formulas may need to be updated.


Convert a string to a LocalDate object. You can also perform date math within the string input to change the date. Modifiers are:

  • y  - year
  • q - quarter
  • m - month
  • w - week
  • d - day
Code Block
String d = '1/22/2015'                       //get a specific date
def myDate = date(d)                         
String today = actualDate()                  //get today's date
def myDate = date(today)
def myDate = date('actualYear() - 1y')       //get the first day of the current year
def myDate = date('actualYear() - 1y - 1d')  //get the last day of the previous year
def myDate = date('actualDate() - 3w')       //three weeks ago from today


Convert a string to a LocalTime object

Code Block
String t = '3:15:01 PM'
def myTime = time(t)
String now = actualTime()
def myTime = time(now)


Convert a string to a LocalDateTime object

Code Block
 String dt = '3/02/2015 3:15:01 PM'


Convert a LocalDate, LocalTime, or LocalDateTime object to a string.

Code Block
def d = date('1/22/2015')
String s = toString(d, 'MMM dd, yyyy') // Mar 22, 2015

toString() Java

If you use a crossValue() to get a date object, it returns a java date object. None of the functions here work with java date objects. So, you need to cast it to a string, then back into an LocalDate object. Here is how to do that.

Code Block
def resourceStartDate = crossValue('AAM__PTO_INPUT_2018',  '[L_RESOURCE_DISPLAY_NAME={' + resource +'}]', 'L_START_DATE_FIRSTVALUE')
resourceStartDate = resourceStartDate.toString()  //cast to string
resourceStartDate = date(resourceStartDate) //cast to LocalDate


Get the part of a date or time that interests you.

Code Block
String s = datetimePart('2018-04-29', 'dayOfWeek') //returns Friday (must use date format yyyy-MM-dd)


ActualDate returns today's date as a string. You can control the format by passing in a format string. There are a number of variations of this method, but they all work the same.

  • actualDay() - today's date
  • actualWeek() - date of Monday in the past
  • actualMonth() - first day of the current month
  • actualQuarter() - first day of the current quarter 
  • actualYear() - first day of the current year
Code Block
//get today's date
String today = actualDate()
//get today's date and format it
String today = actualDate('yyyy-MM-dd') 
//get current date and turn it into a LocalDate object
def date = date(actualDate())
//pretend today's date is 7/23/2015. You can call the following methods to get the start date of a day, week, month, quarter, or year.
String today = actualDay()      // 7/23/2015
String monday = actualWeek()    // 7/20/2015
String month = actualMonth()    // 7/1/2015
String month = actualQuarter()  // 7/1/2015
String month = actualYear()     // 1/1/2015


ActualTime returns the current time as a string. You can optionally format that time by passing in a format string.

Code Block
//get current time
String now = actualTime() 

//get current time and format it
String now = actualTime('HH:mm:ss')
//get current time and turn it into a LocalTime object
def now = time(actualTime())


Given a date, add time to it

Code Block
//plus(localdate/localtime/localdatetime, string, integer)  -- string is the component of the datetime you want to change -- days, weeks, minutes, seconds, months, years, etc.
//get me next week
def today = actualDate()           //returns string
def dToday = date(today)           //convert to a date object
return plus(dToday, 'weeks', 1)   //take away a week


Given a date, subtract time from it

Code Block
//minus(localdate/localtime/localdatetime, string, integer)  -- string is the component of the datetime you want to change -- days, weeks, minutes, seconds, months, years, etc.
//get me last week
def today = actualDate()           //returns string
def dToday = date(today)           //convert to a date object
return minus(dToday, 'weeks', 1)   //take away a week


Look at a previous date period. Useful when comparing past performance to current performance. For example, show me revenue last month vs. revenue this month. 


  • Day
  • Week
  • Month
  • Year
Code Block
double revenue = M_REVENUE
double lastMonthRevenue = prev('MONTH', { M_REVENUE })

double revenueGrowth = abs(revenue - lastMonthRevenue) / revenue * 100


Look at a coming date period. Useful when comparing future performance to current performance. For example, show me how sales are increasing or decreasing in the future.


  • Day
  • Week
  • Month
  • Year
Code Block
double revenue = M_REVENUE
double nextMonthRevenue = next('MONTH', { M_REVENUE })

double revenueGrowth = abs(revenue - nextMonthRevenue) / revenue * 100


Get an indicator's value at a particular point in time. That time period can be a single day or a longer time period.

Code Block
dateAt('1/01/2015', { M_REVENUE }) //how much revenue is associated with this date
dateAt('1/01/2015', 'MONTH', { M_REVENUE })  //how much revenue was earned in the month 1/01/2015


Allows you to specify which date attribute should be used for calculating an indicator. Mostly useful for KPIs where you need to calculate a value based on a date range. Why is it useful in such a specific case? KPIs only allow you to filter all of the underlying data based on a date range. If you want to show two KPIs next to each other, each based on different date ranges, then you can't do that by default in a KPI. By leveraging dateAttribute() you can though. Because it is used for date ranges, you' see it almost always used in conjunction with dateInterval().

Code Block
dateAttribute('L_MONTH') { M_REVENUE }                               //this doesn't really do anything. Gets all revenue associated with the attribute L_MONTH. Which is really just the same as calling def a = M_REVENUE
dateAttribute('L_MONTH') { dateInterval(start, end) { M_REVENUE } } //but when combined with date interval, we can get revenue between two dates. Now you can show nice KPIs like "all revenue to date" next to "revenue last month"


Aggregate data between two dates. Often used in conjunction with dateAttribute() when building KPIs. DateInterval is useful for retrieving values between two dates that break the current views context. For example, say you have a view for the current year, broken down by month. When accessing indicator values you'll see the data limited to the current month. With DateInterval() you can broaden that date range to whatever you want. Also see prev() and next() which are useful for getting adjacent buckets of data. For instance, to compare last month's revenue to this month's revenue.

Code Block
//note that if your data set has multiple date fields this formula may not work. Try date Attribute instead.

//specify dates and return values between them
dateInterval('1/01/2015', '1/15/2015', M_REVENUE)
//dynamically calculate dates and return values between them
startDate = 'actualMonth() - 12m'
endDate = 'actualMonth() + 2m - 1d'
dateInterval(startDate, endDate, { M_REVENUE} ) 


Strips the entire date context when calculating an indicator. For example, your view shows the current year broken down by month. The data set that drives it contains five years of data. When using this function, it will return the five years of data.

Code Block
M_PERSON_HOURS //get hours for the current view's context. If view was for year broken down by month, returns the hours for the current month
withoutDateTime() { M_PERSON_HOURS } //get hours for the entire dataset regardless of view's time interval or the view's current context


Starting at a particular date, cumulate all preceding records. Useful when you want to see how things are increasing over time. In the example below the green line is month to month revenue. The black line is that revenue accumulating through the course of the year.

Code Block
//sum revenue for the last year
def start = date(actualDate())  //get today's date and turn it into a date object
def lastYear = minus(start, 'years', 1) //subtract one year
String sLastYear = toString(lastYear, 'M/dd/yyyy')
def revenue = cumulateFromDate(sLastYear, 'M_REVENUE')  


Similar to cumulateFromDate(), but accepts a timestamp instead of a date.

Code Block
def revenue = cumulateFromTime('12:00:00', 'M_REVENUE')  

viewDateFrom() / viewDateTo()

If you have a chart that is limiting displayed data via a date interval, then you may want to know the start and end dates for your formulas. These functions give access to that data.

Code Block
startDate = viewDateFrom()   //returns a string
endDate = viewDateTo()		 //return a string
startDate = date(viewDateFrom())  //cast to date
endDate = date(viewDateTo())	  //cast to date

viewTimeFrom() / viewTimeTo()

If you have a chart that is limiting displayed data via a time interval, then you may want to know the start and end times for your formulas. These functions give access to that data.

Code Block
startTime = viewTimeFrom()   //returns a string
endTime = viewTimeTo()		 //return a string

startTime = date(viewTimeFrom())  //cast to time
endTime = date(viewTimeTo())	  //cast to time

Get Parts of Date

You can retrieve the individual pieces of a date or time object. For example, the year 

Code Block
Integer i = year('1/01/2015')      //2015
Integer i = month('3/01/2015')     //3
Integer i = week('12/31/2015')     //52 (last week of year)
Integer i = dayOfWeek('1/02/2015') //Monday == 1, Tuesday ==2
Integer i = dayOfYear('2/3/2015')  //34th day of year

Compare Dates

These methods allow you to compare dates and get the time difference between dates. These functions require that you pass in date objects.

Code Block
def start = date('1/01/2015');
def end   = date('12/31/2015');
boolean b = isAfter(end, start)   //true, december 2015 is after january 2015
boolean b = isBefore(end, start)  //false, december 2015 is not before january 2015
boolean b = isEqual(start, start) //true, same date

Get time differentials.

Code Block
def start = date('1/01/2015');
def end   = date('12/31/2016');
Integer i = daysBetween(start, end)    //730 days
Integer i = weeksBetween(start, end)   //104 weeks
Integer i = monthsBetween(start, end)  //24 months
Integer i = yearsBetween(start, end)   //1 year

Context Date and Time

If your view contains a Time and Formulas component, then you can return information in context of that component. For example, my view shows me data broken down by month. Get me the year this month is in. 

This feature only works when using the Time and Formulas feature of views. It does not work for date attributes that are added as drill down paths. If that sounds confusing, then perhaps this screenshot will help. This is what you have to be using.

Methods only work at the deepest time interval. In other words, if you call contextDay(), but the view is showing Months, then contextWeek() returns based on the first day of the month.

Code Block
Imagine we are looking at a view and it is broken down to the day level - 01/02/2015
String s = contextDay()      //the actual date 1/02/2015
String s = contextWeek()     //first Monday of the week 12/29/2014
String s = contextMonth()    //first day of the month 1/01/2015
String s = contextQuarter()  //current quarter's first day 01/01/2015
String s = contextYear()     //jan 1 of current year 01/01/2015

Attribute Count and Distinct Count

You can calculate metrics about your attributes. For example, how many projects are in the Opportunity stage?  Just append _COUNT or _DCOUNT to get the number, or distinct number of records.

Code Block
 Integer opportunities = L_PROJECT_STAGE_COUNT

Indicator Average, Min, and Max

Not only can you access an indicator via its formula name, you can also access the sum, average, min, max, and distinct count using the @ symbol. The calculation of those values in done within the context of the view. That is to say, if your view is broken down by resource, then requesting M_PERSON_HOURS@AVG would return the average number of hours a single resource worked.

Code Block

You can make similar calls across time intervals

Code Block

Report Filters

Reports can optionally have all views filtered by an attribute. For example, even though the underlying data set contains information from all cost centers, all views on this report should only show information about a specific cost center. You can access these report filters using the @ sign. First, visit the filters for your report. Then note down the filter name. Finally, prepend the filter with @ and use closure to access an indicator.

Code Block
def a = @CostCenterFilter { M_PERSON_HOURS }

Variable type
Return type
Array of Strings

Cross Reference Data Sets

You may find that you need to reference data from another data set. Typically we see this done by performing a join between two data sets. The report is then built off of the resulting joined data. However, you can also use a feature called CrossValue() that skips the joined data set. In order to cross reference, you'll need the target data set's unique code and the attribute/indicator you are looking up. The data set code can be found under the Cross-Reference dropdown in the formula editor, or by visiting the data set and looking under the More menu. 


CrossValue is often used in conjunction with memberIdentifier() which returns the current drill down attribute code(s). This is useful if you want to filter the CrossValue call using the currently applied drill downs.

Code Block
//get person hours from the Ginsu report
//get distinct count of resources 
//get distinct count of resources for a specific cost center
//get distinct count of resources for a specific cost center and date range
//get distinct count of resources for your current drill-down
crossValue('GINSUBYMONTH.GINSUBYMONTH', memberIdentifier(), 'L_RESOURCE_DCOUNT')        

//in the event that the source data set and target data set have different attribute codes, you can use memberValue() instead of memberIdentifier(), hard-coding the attribute value
//in this case I hard coded the attribute value to L_COST_CENTER2 (the source was just L_COST_CENTER)
crossValue('GINSUBYMONTH.GINSUBYMONTH', '[L_COST_CENTER2="' + memberValue() + '"]', 'L_RESOURCE_DCOUNT')

//you can specify an attribute in memberValue() if you want to get a specific drill down value

//if your crossValue returns a null you'll need to check for that, otherwise operations using it will return a null value too
if (barBudget != null) { return barBudget } else { return 'No Budget'}
//get attribute with FIRST or LAST value

In Practice

Here are some common charts you might want to build and the indicators formulas we used to build them.

Revenue Targets

The screenshot below shows a bunch of circles as compared to a gray bar. The gray bar is your target. When the circle is above the gray bar, you exceeded your target and we color it blue. When below, you missed your target and we color it red. 

Gray bar - Use CrossValue to get the target for a given month

Code Block

Solid red dots - actuals that missed target

Code Block
//Get our target for the current month
//Filter to just actual data
filter("L_DATA_TYPE in ('Actual')"){
  //calculate total revenue
  //see if we beat our target
  if (totalRevenue < target) {
      return totalRevenue                         //if we missed the target, return the revenue we did earn
  else {
  	  return null                                  //if we exceeded the target, don't show anything


When using KPIs, you'll often want to show a subset of data based on dates. In the screenshot below, I show 2014 and 2015 revenue.

Code Block
//Revenue 2014
def start =  date('actualYear() - 1y')                       //Jan 1 of last year
def end =  date('actualYear() - 1d')                         //Dec 31 of last year

//only look at actuals
filter("L_DATA_TYPE in ('Actual')") { 
    //based on months in last year, get me total revenue
	dateAttribute('L_MONTH') {
		dateInterval(start, end) { M_SYSTEM_REVENUE + M_ODC_REVENUE }
//Revenue 2015
def start =  date('actualYear()')                       //Jan 1 of this year
def end =  date('actualYear() +1y - 1d')                //Dec 31 of this year

//only look at actuals
filter("L_DATA_TYPE in ('Actual')") { 

    //based on months in this year, get me total revenue
	dateAttribute('L_MONTH') {
		dateInterval(start, end) { M_SYSTEM_REVENUE + M_ODC_REVENUE }

Use Actuals Through Date

In many data sets you'll have a use actuals through date. If you need to retrieve that date, you can do so with the following code. Keep in mind that we are making the assumption here that every row in the data set has the same actuals cut off date. If the dates vary row by row, you'll need to compensate.

Code Block
//get actuals cut off date
def acod = firstValue('L_ACTUALS_CUTOFF_DATE')
//when you call actualDate, it returns a *string*. You'll need to convert it to a date data type before you can start manipulating it.
def today = actualDate() //returns string
def dToday = date(today) //convert to a date
return minus(dToday, 'weeks', 1) //take away a week