Think Like A Workfront Admin: Creating Reports Like a Pro! Advanced Groupings | Part 2
In Part I, we discussed what an Advanced Grouping is, what it looks like, and how it is formatted differently from an Advanced View. In addition, we discussed a user challenge and the solution. The challenge is that our work license users want to group their Tasks by whether the Task is LATE, ON TIME, or UPCOMING within the current week. We can only do this through an Advanced Grouping.
How to write the custom text mode to solve the challenge?
First, navigate to the “My Tasks Due This Week” report we previously created in the “Creating Reports Like a Pro! Advanced Views” series. Select the Report Actions dropdown and choose Edit. Next, select the Groupings tab. To the far right you will see two options: Switch to Matrix Grouping and Switch to Text Mode. Choose, Switch to Text Mode.
Our valueexpression function will use the following functions, formulas, and fields:
Functions:
- textmode
- 0.valueexpression
- 0.valueformat
Formulas:
- IF
- DAYOFWEEK
- DATE
Fields:
- plannedCompletionDate
- $$TODAY
We will start with our most complex line of code, which is the group.0.valueexpression. The valueexpression function uses the IF formula to determine if the planned completion date of the task is LATE, ON TIME, or UPCOMING throughout the week. We need to create a dynamic “today” value so that the data is processed using today’s date (regardless of when the user views the report. To do this, we will use the wildcard field $$TODAY and extract the date using the DATE formula. Lastly, for both fields $$TODAY and plannedCompletionDate, we need to extract the day of the week in order to analyze if the plannedCompletionDate is less than, equal to, or greater than today’s date so that we can group the task accurately. To do this, we will leverage the DAYOFWEEK formula.
The textmode is:
|
1
2
3
|
group.0.valueexpression=IF(DAYOFWEEK({plannedCompletionDate})
<DAYOFWEEK(DATE($$TODAY)),”LATE”,IF(DAYOFWEEK({plannedCompletionDate})=DAYOFWEEK(DATE($$TODAY)),”ON
TIME”,IF(DAYOFWEEK({plannedCompletionDate})>DAYOFWEEK(DATE($$TODAY)),”UPCOMING”,”N/A”)))
|
The second line of text mode will specify the valueformat of the output (valueexpression). For our example, the second line of text mode will be group.0.valueformat=HTML. We can use HTML for almost any valueexpression if you don’t know which format to leverage. We can use the string format, or str. The third and last line of text mode will specify that we are using text mode to create the Grouping, rather than the standard user interface. The third line of text mode will be textmode=true.
When you are finished writing each line of text mode, you should end up with the following block of code:
After you select “Done” and “Save + Close”, your end result will be:
In Part III, I will share the custom text mode code for both the Task and Assignments reports.
