Use Additional Functions in Report Studio

Null and Zero Functions

ClickTime’s Report Studio includes functions that help you clean up your data by handling null (empty) values or 0 values. These are especially helpful when you're building custom formulas or visualizations where blanks or zeros might interfere with formatting, totals, or logic.

Null and Zero Conversion

NullToEmpty(Value)

Converts a null (blank) value to an empty string "".
Useful when you want to display a blank instead of "null" in a table or visualization.

Example:
NullToEmpty([Notes])
Returns: "" if the Notes field is blank, rather than displaying "null"

NullToZero(Value)

Converts a null (blank) value to 0.
Helpful for calculations that expect numeric input.

Example:
NullToZero([Actual Hours])
Returns: 0 if no hours were logged

ZeroToNull(Value)

Converts a value of 0 to null.
This is often used when you want to hide or exclude zero values from visualizations or tables.

Example:
ZeroToNull([Reimbursable Amount])
Returns: null if the amount is 0

Tips for Using Null and Zero Functions

• These functions help clean up visualizations by preventing unwanted "null" or "0" values from appearing.

• You can combine them with string or numeric functions:

  • Upper(NullToEmpty([Notes]))

  • NullToZero([Budget Hours]) + NullToZero([Additional Hours])

• These are particularly useful for custom metrics, pivot tables, and conditional formatting.

Exploring Further

Check the Formula Editor in Report Studio to browse other formatting and data cleaning functions. Combining NullToZero with aggregation functions like Sum() or Avg() can improve the accuracy and readability of your reports.

Let me know if you'd like to create a quick reference table for these too, or build a section on best practices for handling missing data!

 

Logical Functions 

ClickTime’s Report Studio includes logical functions that help you apply conditional logic to your data. These are especially helpful when you're building custom metrics, filters, or visual labels that change based on certain criteria.

Conditional Expressions

IF(condition, value_if_true, value_if_false)

Returns one value if the condition is true, and another if it's false.
Great for categorizing or flagging data based on business rules.

Example:
IF([Total Hours] > 40, "Overtime", "Standard")
Returns: "Overtime" if hours exceed 40, otherwise "Standard"

Not(condition)

Reverses the result of a condition.
Useful when excluding values or flipping a true/false test.

Example:
Not([Client Status] = "Active")
Returns: True if the client is not active

Tips for Using Logical Functions

• Use IF() to label records, flag issues, or create dynamic text fields.

• Use Not() to simplify conditions that would otherwise require multiple filters.

• Combine with other function types:

  • IF(IsNull([Task Name]), "Missing Task", [Task Name])

  • IF(Not([Is Billable]), "Non-Billable", "Billable")

Exploring Further

You can find IF() and Not() in the Formula Editor under Logical functions. For more advanced logic, explore functions like IsNull(), Between(), And(), and Or().

These tools make it easy to create flexible, intelligent reports that respond to your data automatically.