creating reports Tag

The “My Tasks Due This Week Report” will impress all of your work license users now that you’ve leveraged advanced Views and Groupings. The benefits of adding advanced Groupings to reports and object lists are:

  • An Admin can group data in ways that aren’t native in the standard user interface
    • Our grouping uses an advanced valueexpression to categorize each task rather than leveraging a task field
  • An Admin can control how the grouping displays to your users
    • Our grouping categorizes each task as either LATE, ON TIME, UPCOMING depending on the day of the week and the task’s planned completion date
  • An Admin can enhance the user experience by creating dynamic advanced groupings
    • Our advanced grouping updates day by day and recategorizes each task using an advanced calculation

 

Before I share the Task and Assignment text mode code, it is important to note that you cannot create a chart using advanced Groupings or Views. Charts must use either native object fields or custom fields.

The text mode code for both Task and Assignment reports is found below:

Task Report Advanced Grouping Text Mode:

 

1
2
3
4
5
6
7
textmode=true
group.o.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”)))
group.0.valueformat=HTML

 

Assignment Report Advanced Grouping Text Mode:

1
2
3
4
5
6
7
group.0.valueexpression=IF(DAYOFWEEK({task}.{plannedCompletionDate})
<DAYOFWEEK(DATE($$TODAY)),”LATE”,IF(DAYOFWEEK({task}.
{plannedCompletionDate})=DAYOFWEEK(DATE($$TODAY)),”ON
TIME”,IF(DAYOFWEEK({task}.
{plannedCompletionDate})>DAYOFWEEK(DATE($$TODAY)),”UPCOMING”,”N/A”)))
group.0.valueformat=HTML
textmode=true

 

I forgot to explain in the “Create Reports Like a Pro! Advanced Views Part III” blog the differences between the Task and Assignment report syntax. If you review the Task object within the API Explorer, you’ll notice that an Assignment is both a referenced and collected object to the Task object. If you create an Assignment report and you need to reference the task object in your text mode, you need to first specify the task object and then the field. For example, in the code above, when I reference the plannedCompletionDate, I preface the field with {task}. This tells Workfront that I am calling the Task object’s planned completion date value. Therefore, whenever you create an object report that references a connecting object, add the object enclosed by curly brackets first, followed by a period, and then the referenced object’s field enclosed by curly brackets.

Next up, you will learn how to build advanced Filters to help find the exact information you are looking for.

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.