Hello everyone, I created a custom report and added a few custom text-mode columns that are not associated with any custom form fields. The report works and I'm seeing good results.

[TedMa]

The problem I'm having, I'm not able to create grouping to my new columns. I'm thinking I'm missing an expression in my custom column to allow this to be use as a field for grouping.

Does anyone have any suggestion or have a recommendation?

Here's my "Text Mode" Script:

displayname=System PASS

linkedname=direct

namekey=System PASS

textmode=true

valueexpression=IF({DE:System 1}="PASS",1,0)+IF({DE:System 2}="PASS",1,0)+IF({DE:System 3}="PASS",1,0)

valuefield=Sytem PASS

valueformat=customDataLabelsAsString

[Heather_Kulbacki]

Hi Ted,

Unfortunately, you can't create a grouping of a text mode column. You'd need to create a calculated custom field do to the same thing that your column is doing, then add that field to your report and group on the field.

https://one.workfront.com/s/article/Calculated-Custom-Fields-Vs-Calculated-Columns-280214936#when-to...

[TedMa]

Hi Heather,

Thank you for your feedback, I will create a calculated custom field.

[AnthonyIm]

Hi - I'm confused by your code.

First, I see you have both a valuefield and a valueexpression. You should only have one. valuefield is when you are just using the field and valueexpression when you want to do a calculation of some kind.

Second, I'm not getting the plus signs you have. Is that supposed to be a SUM calculation? If so, you can try something like SUM(IF({DE:System 1}="PASS",1,0),IF({DE:System 2}="PASS",1,0),IF({DE:System 3}="PASS",1,0)) However, I'm not sure it will recognize your 1's and 0's as numbers to do the SUM. If I need a sum, I usually have it on the custom form itself.

Third, when doing a calculation, you almost always want the valueformat to be HTML or string.

Not really sure this would work, but this is what I think your code should be:

group.0.displayname=System PASS

group.0.valueexpression=SUM(IF({DE:System 1}="PASS",1,0),IF({DE:System 2}="PASS",1,0),IF({DE:System 3}="PASS",1,0))

group.0.valueformat=HTML

[TedMa]

Hi, Thank you for your suggestion. The result is not what I'm looking for. I think the best way is to create a custom calculation field that Heather's recommending. Oh, by the way, thank you for mentioning the difference between valuefield and valueexpression. It was very helpful.