Text mode Matrix Report

[DavidCo3]

Hi all - I'm open to either a direct answer or any alternative ways of doing this !

• I have an Assignment report
• I want to group the report rows by the assigned User, and the columns by two custom Task fields
• The custom Task fields are not available as groupings (via the UI) however I have been able to use text mode to get the regular report grouping the way I want it.
• However, I would really like to have the values to be summed up and presented in a Matrix format. I am not aware of any way to do a text mode matrix report.
• At the moment, my workaround is to export to Excel and use a pivot table. Any other ideas?

Thanks.

[AnthonyIm]

I LOVE the assignment object type for reporting but have run into the same issue you have with not being able to use the matrix groupings because fields are not in the UI. I don't have an answer but I want to get an alert on this thread if someone has figured it out! ;)

[Doug_Den_Hoed__AtAppStore]

Hi David. I spent a little time this morning chasing your Matrix Grouping using the API. Here's how an example similar to yours looks under the covers, in JSON:

{ { "group": [ {

"linkedname": "assignedTo",

"namekey": "view.relatedcolumn",

"namekeyargkey": [

"assignedTo",

"name"

],

"orientation": "H",

"valuefield": "assignedTo:name",

"valueformat": "string"

},{

"linkedname": "assignedBy",

"namekey": "view.relatedcolumn",

"namekeyargkey": [

"assignedBy",

"Current Crew"

],

"orientation": "V",

"valuefield": "assignedBy:Current Crew",

"valueformat": "customDataLabelsAsString"

},{

"linkedname": "assignedTo",

"namekey": "view.relatedcolumn",

"namekeyargkey": [

"assignedTo",

"Is Supervisor"

],

"orientation": "V",

"valuefield": "assignedTo:Is Supervisor",

"valueformat": "customDataLabelsAsString"

} ] ,"textmode": "false"

} }

Your mission, if you choose to accept it, is to follow this pattern and find a way in regular text mode on a report grouping to in fact instruct the report to format a matrix grouping, and then extend that approach to switch to the Task custom fields you seek, instead of the two Assigned To custom fileds in the example above.

This message will self destruct in 10 seconds.

As might your experiment.

Regards,

Doug

[DavidCo3]

I'll give it a go! Thanks Doug :)

[DavidCo3]

Hi Doug,

I finally got around to having another look at this, and your tip was spot-on! My notes and sample text-mode grouping are below:

Instructions:

Use text-mode to create a standard set of groupings. The key items to add which are different, are the 'orientation' lines (H for Horizontal and V for Vertical). Once it is working for you as a standard grouping, change the report to a matrix grouping. Don't change the grouping fields, but you can still go in and adjust the other Matrix Settings. The only issue seems to be that when the report loads, the Matrix tab doesn’t show by default, even it is set in the settings.

Sample text-mode grouping:

textmode=true

group.0.namekey=view.relatedcolumn

group.0.namekeyargkey.0=assignedTo

group.0.namekeyargkey.1=name

group.0.linkedname=assignedTo

group.0.valuefield=assignedTo:name

group.0.valueformat=string

group.0.orientation=H

group.1.namekeyargkey.0=task

group.1.namekeyargkey.1=ProductTypeRep

group.1.namekey=view.relatedcolumn

group.1.valuefield=task:ProductTypeRep

group.1.linkedname=task

group.1.valueformat=customDataLabelsAsString

group.1.orientation=V

group.2.valuefield=task:TaskTypeRep

group.2.linkedname=task

group.2.valueformat=customDataLabelsAsString

group.2.namekeyargkey.0=task

group.2.namekeyargkey.1=TaskTypeRep

group.2.namekey=view.relatedcolumn

group.2.orientation=V

[Doug_Den_Hoed__AtAppStore]

Attaboy, David! Well done! Regards, Doug

[SunilDe]

David,

I tried your logic. Instead of custom fields, I have an Workfront reference field. The matrix report will give me only the teamID and not the team name. I would like to see the team name in the matrix and the chart.

group.0.linkedname=owner

group.0.namekey=view.relatedcolumn

group.0.valuefield=owner:homeTeamID

group.0.namekeyargkey.0=owner

group.0.namekeyargkey.1=homeTeamID

group.0.valueformat=string

group.1.linkedname=owner

group.1.namekey=view.relatedcolumn

group.1.valuefield=owner:name

group.1.namekeyargkey.0=owner

group.1.namekeyargkey.1=name

group.1.valueformat=string

textmode=false

[DavidCo3]

Hi Sunil - try this:

group.0.linkedname=owner

group.0.namekey=view.relatedcolumn

group.0.namekeyargkey.0=owner

group.0.namekeyargkey.1=homeTeam Name

group.0.valuefield=owner:homeTeam:name

group.0.valueformat=string

group.1.linkedname=owner

group.1.namekey=view.relatedcolumn

group.1.namekeyargkey.0=owner

group.1.namekeyargkey.1=name

group.1.valuefield=owner:name

group.1.valueformat=string

textmode=true

[SunilDe]

Wanted it in a matrix format. Also, Team name is showing No value.

[DavidCo3]

Sunil – the instructions are there to show how to put it into a matrix format.

What do you mean that the Team name is showing no value? I tested this in an Hours report and it worked fine. What kind of report are you using?

[DavidNg1]

I've tried manual matrix text mode for 2 different use case, but it does not seem to work either time. Is it possible to use valueexpression for custom text mode matrix? Every time I flip to text mode after the grouping "successfully" take, the value expression group gets deleted/kicked out. Any comments would be appreciated. TIA.

Sample code below:

textmode=true

group.0.namekey=view.relatedcolumn

group.0.orientation=H

group.0.namekeyargkey.0=owner

group.0.namekeyargkey.1=name

group.0.linkedname=owner

group.0.valuefield=owner:name

group.0.valueformat=string

group.1.orientation=H

group.1.valueexpression=IF({hourTypeID}.{name}="Out of Office","OOO","Work Hours")

group.1.name=Hour Type

group.1.valueformat=HTML

group.2.valuefield=entryDate

group.2.linkedname=direct

group.2.valueformat=atDateWithOutTimeAsDayString

group.2.namekey=entryDate

group.2.orientation=V

group.2.groupdatesby=DY

group.2.notime=true

[Cameron_Ward]

Following up on this one. I would love to see if anyone has an answer to DavidNg1's last question.